A complete user's guide on how to use the Pure Mitti Fulfillment Spreadsheet.
Overview - Shipments - Shipment Tracker Script - Samples - Inventory - Menu Items Reference
This document contains everything that you need to know in order to use the Pure Mitti Fulfillment Spreadsheet.
In this section, we will discuss the high level purpose of every tab in this spreadsheet. In later sections, more in-depth explanations and tutorials will be provided for the main use cases for this tool. At the end, we'll go over all of the supplementary functions provided in the menu.
- Fulfill Orders
Whether it be sending out a small sample or logging the tracking information for a huge wholesale order, the main purpose of this spreadsheet is to assist you with fulfilling orders for Pure Mitti.
- Check the Shipping Status of an Order
Every once in awhile, you should check the status of all the orders we have shipped out recently. This will allow us to ensure that shipping is going smoothly, or catch any issues early if there are any. More on how to do this in the Shipments section.
- Update Inventory Stock
It is incredibly important to Pure Mitti that we maintain an accurate record of our inventory at all times. This means updating the inventory when a new shipment arrives, and doing regular inventory recounts. More on how to do this in the Inventory section.
- Analysis
The Shipment Log tab is a record of all the shipments we have sent out starting in 2024. The benefit of having so much data on our shipments stored all in one place is that doing analysis is vastly easier. Using this data, we have created some charts in the Metrics tab to better understand the relationship between different metrics.
Overview - How to Use It - Shipment Statuses & Tracking
Fig 1. An example image of what the Shipments tab could look like.
The Shipments tab is a very powerful tool we use for sending out shipments and for checking on shipping status. Generally, you want to treat this tab as mostly view-only with the exception of the Notes column and the Items column. Otherwise, you will interact with this page using the tools provided to you in the Menu.
The Shipments Tab can be divided into 2 sections:
- Incoming Orders
- Outgoing Shipments
Orders that come from Shopify, Faire, and PodFoods will automatically be populated in the Incoming Orders section, and likewise will move to the Outgoing Shipments tab once shipped. Etsy has not yet been integrated.
All other orders you must add and update manually.
Note: Faire orders must be accepted before they will show up in the Incoming Orders section.
Once an order is detected as being delivered, it will automatically clear from the Shipments tab and will go to the Shipment Log where additional data about the shipment will be logged.
Fig 2. An example image of the Incoming Orders section
The first thing you want to do when fulfilling orders is to check the Incoming Orders section. This will give you an overview of what orders you need to fulfill. Etsy orders will not appear here.
For these orders, add notes regarding any extras that were sent (flyers, shelf toppers, sample soaps, etc.), and adjust the Items Ordered column if needed (item changes, errors with the script, etc.).
As stated previously, these orders will automatically be moved to the Outgoing Shipments section and then to the Shipment Log.
Once you have fulfilled all of the orders that come from these portals, you may still have manual orders to fulfill. In order to add and and update these orders, you must utilize some of the Menu Item functions.
Note: Manual Orders in this context will be defined as orders that are not inputted through a system, but rather asked for directly in-person on through email.
Fig 3. A screenshot of the Menu Items emphasizing the Manual Order section
➕ Manually Add New Order
- Use this to add a manual order to the Incoming Orders section.
↪️ Move Manual Order to Shipment
- Once a manual order has been shipped out, use this to move the order to the Outgoing Shipments section.
➕ Manually Add Shipment to Log
- If a manual order was shipped out before it was added to the incoming orders section, use this to add it to the Outgoing Shipments section.
Sometimes, you will use the Shipment tab to check the shipping status of the orders you have recently sent out. Here are the common shipment statuses you will find and what you should do about them.
🔵 PRE_TRANSIT or UNKNOWN or CONFIRMED or not found
-
The package has not yet been scanned by the carrier.
-
This usually changes within a day or two at most. If a shipment has this status for a few days and has been given to the carrier, copy the tracking number and check the status on the carrier website for more detailed information.
Even if a package was not scanned at its drop-off location, it still may be scanned at a later date as it travels and can still be delivered.
🟡 TRANSIT
-
The package is en route to the customer.
-
This is the most common tracking status you will see. Once a package has been scanned by the carrier its status will change to this up until when it gets delivered.
-
Once a shipment has this status, an ETA may be added at the bottom of its notes if it's available. If it is a few days past the ETA date, copy the tracking number and check the status on the carrier website for more detailed information. The shipment might be delayed or sometimes even lost.
🟢 DELIVERED
-
The package has successfully been delivered to the customer.
-
Most of the time you won't see this status because the spreadsheet will check every hour for shipments of this status and move it to the Shipment Log tab. In the case that you do see this status, just give it some time and it will automatically go away.
Now here are some of the less common statuses and what they mean.
OUT_FOR_DELIVERY
-
The package is out for delivery.
-
This will most likely go away within a day and will be replaced with DELIVERED but may sometimes be replaced with DELIVERY_ATTEMPTED.
DELIVERY_ATTEMPTED
-
The package had an unsuccessful delivery attempt and will make another attempt soon.
-
This will likely go away within a few days once a successful delivery attempt is made. If this status remains, copy the tracking number and check the status on the carrier website for more detailed information.
API Calls & Get New Orders - Check If Shipped - Check If Transit - Check If Delivered
Even if you aren't going to be interacting with the source code, it would still be beneficial to understand at a high level what is going on "behind the scenes".
Major Sections:
- API Calls
- Get New Orders
- Check If Shipped
- Check If Transit
- Check if Delivered
📌Each major section of the script is scheduled to run once every hour.📌
The first part of this script starts with making API Calls to the apps we use to retrieve all of our order data. This part is the most important because without this data there would be nothing to display in the spreadsheet.
Think of an API like a waiter at a restaurant.
You give the waiter your order, the waiter gives your order to the kitchen, and then once its done your waiter comes back with your order. It's the same process with an API.
You give the API your request (in this case we want data about our orders), the API gives that request to Shopify/PodFoods/Etsy's backend where that data is stored, and then the API gives us whatever the backend returns.
All of the API logic is stored in Api Calls.gs (not uploaded to this repo for security reasons). The data retrieved from these calls are stored in separate sheets that are hidden by default. You actually find these sheets by clicking the "All Sheets" in the bottom left corner of the spreadsheet.
For Shopify and PodFoods, we split up the data that we retrieve from the API calls into two categories:
-
New Orders: Still need to be fulfilled and will show up in the Incoming Orders section.
-
Shipped Orders: Shipping label purchased and will show up in the Outgoing Shipments section.
Faire orders are currently tracked through the Shopify API.
Once we have all of the data stored in the spreadsheet, the next main function that runs is Get New Orders. All it does is add new orders to the Incoming Orders section if they aren't already there. Any of the functions regarding API calls or getting new orders can be run manually using one of the Menu Item functions.
Fig 5. Screenshot of Menu Items for Running Api Calls
🟠 Etsy
- Not implemented yet
⚫ Shippo
- Refresh Shippo Backend - Refreshes data on all Shippo orders in the Outgoing Shipments section.
🟢 Shopify
- Refresh Shopify Backend - Refreshes data on all Shopify orders including Incoming Orders and Outgoing Shipments.
- Update Shopify Incoming Orders - Add any new orders to the Incoming Orders section.
🟣 Pod Foods
- Refresh Pod Foods Backend - Refreshes data on all Pod Foods orders including Incoming Orders and Outgoing Shipments.
- Update Pod Foods Incoming Orders - Add any new orders to the Incoming Orders section.
⟳ Refresh Backend for All Apps
- Refreshes data for all apps including Incoming Orders and Outgoing Shipments.
Now that all of the new orders have been added, the next part of the script is Check If Shipped.
As the name suggests, this part of the script checks every order in the Incoming Orders section to see if it has shipped. The way it does this is by getting the order ID of the new order and searching for it in the list of shipped orders. If there is a match, then the order has been shipped and must be updated. If there is no match then it has not been shipped yet.
If you want to manually run this part of the script you can use the following Menu Item function:
🚚 Update All Outgoing Shipments
Once an item has been shipped out, we can run the Check If Transit part of the script to update the ETA for all orders in the Outgoing Shipments section.
The ETA will be appended to the bottom of each order's notes that have the status of 🟡TRANSIT. If any updates are made to the ETA as the package is being shipped, the ETA in the spreadsheet will update accordingly. Until the carrier provides an ETA, the notes will remain unchanged.
If you want to manually run this part of the script you can use the following Menu Item function:
⏰ Update ETA
As packages finally reach the customer, the final part of the script that cleans everything up is Check If Delivered.
This part of the script works by checking the status of all orders in the Outgoing Shipments section. Any orders that have a status of 🟢DELIVERED will be removed from the Shipments tab and added to the Shipment Log tab. Inside the Shipment Log, you can see the history of all orders we have sent out since 2024 along with additional data such as Order Number, Order Value, Shipping Cost, Address, and more.
If you want to manually run this part of the script you can use the following Menu Item function:
📦 Update All Delivered Shipments
Note: Samples tab is not fully finished yet
Fig 6. Add New Orders Menu Item category
To add an order to the samples tab, click on the Add New Orders Menu Item category and click:
➕ New Sample Request
- This will open up a dialog where you can input the information about the order (Name, Date, Address, etc.) and then select which items you would like to add to the sample request. Once you click submit, a new entry in the Samples tab will be added.
Eventually there will be functionality for marking the an order as fulfilled and adding tracking information to it.
Fig 7. A screenshot of the In-Stock Inventory section
Note: Treat the numbers shown here as read-only. The more you manually edit on this side, the more likely it is for mistakes to occur.
The Inventory tab is the home for all things related to maintaining an accurate inventory count. Here you can find the current state of our inventory as well as a user interface for logging all inventory that comes and goes. This user interface will be called the Inventory Entry Editor and will be covered in the following section.
Before covering the Entry Editor though, there are two Menu Item functions that are used for the Inventory tab.
Fig 8. Menu Item Functions for the Inventory Tab
🛍️ Move Bags from Storage to Floor
- Opens up a dialog where you select which type of bag you want to update, and then you input how many bags you are moving from the storage to the floor. Use this function whenever you take bags from storage and bring them down to the floor.
🫧 Add New Soap Month
- Opens up a date picker dialog. When a new shipment of soaps arrive, use this function and input the date of the shipment's arrival to add a new soap batch month to the inventory count. You can then record the quantity of soaps that have been ordered in this shipment in the Inventory Entry Editor under the new date.
The user interface that is used to log what inventory is coming in and out is called the Inventory Entry Editor. You can see the current entry being edited on the right side of the Inventory Tab.
The term "Entry" refers to a list of items that represent the movement of inventory on a particular day.
When the Create New Entry section is empty, you may click EDIT to start working on a new entry. The items that appear in this section are part of the current entry. Once the details have been finalized, click EXPORT to push the entry to the log and update the inventory count. That entry may no longer be edited, and you may click EDIT to start a new entry.
Each entry may have a maximum of 11 items at the time of writing.
Fig 9. A Sample Inventory Entry
What is displayed on the spreadsheet in Fig. 9 is a sample inventory entry created using the Entry Editor. In this format you may make slight adjustments to the entries, but removing or adding items must be done with the Entry Editor.
What can be adjusted outside of the Entry Editor?
-
Quantity: Overwrite the value with the desired value. Input a negative value (red) to indicate that inventory is being sent out, and input and positive value (green) to indicate that new inventory has arrived.
-
Unit: Click the arrow to open up a dropdown menu. You may select either a single unit or a case of that item. (See Case Pack Reference for how many units is in a case for each item.)
-
Item: Click the arrow to open up a dropdown menu. You may only select between different variations of that item type (other soaps, bags, dishbars, and hair products). To change the item to a different type, open the Inventory Entry Editor, delete the item, and then add the desired item.
-
Month- FOR SOAPS ONLY. Click the arrow to open up a dropdown menu. You may select any of the available soap month batches. Please reference the inventory count displayed on the left side of the Inventory Tab when selecting which soap month.
To open the Inventory Entry Editor, click on where it says EDIT (look at Fig. 9 for reference) and the Editor dialog will appear. After loading briefly, you will be met with a screen pictured below.
Fig 10. The Inventory Entry Editor in Use
If no items were previously added to this entry, the box labelled as Item Entries in Fig. 10 would be empty. Any items that were previously added to this entry and shown on the spreadsheet will be displayed here.
In order to add an item entry, you must follow these steps:
-
Click on the name of the item you wish to add to this entry.
-
Select the quantity. Input a negative value (red) to indicate that inventory is being sent out, and input and positive value (green) to indicate that new inventory has arrived.
-
Select the unit. You may select either a single unit or a case of that item. (See Case Pack Reference for how many units is in a case for each item)
-
FOR SOAPS ONLY. Select the month. A dropdown of all the soap batch months with the corresponding number of soaps in stock will appear to the right.
Note: To remove an item, hover over the item you wish to remove. It will turn red, and if you click on it you will remove it.
Fig 11. A screenshot of the Menu Items
🟠 Etsy
- Not implemented yet
⚫ Shippo
- Refresh Shippo Backend - Refreshes data on all Shippo orders in the Outgoing Shipments section.
🟢 Shopify
- Refresh Shopify Backend - Refreshes data on all Shopify orders including Incoming Orders and Outgoing Shipments.
- Update Shopify Incoming Orders - Add any new orders to the Incoming Orders section.
🟣 Pod Foods
- Refresh Pod Foods Backend - Refreshes data on all Pod Foods orders including Incoming Orders and Outgoing Shipments.
- Update Pod Foods Incoming Orders - Add any new orders to the Incoming Orders section.
⟳ Refresh Backend for All Apps
- Refreshes data for all apps including Incoming Orders and Outgoing Shipments.
🚚 Update All Outgoing Shipments
- Checks every order in the Incoming Orders section to see if it has shipped. If an order is detected to be shipped, it will be move it to the Outgoing Shipments section.
⏰ Update ETA
- Updates the ETA for all orders in the Outgoing Shipments section. The ETA will be appended to the bottom of each order's notes that have the status of 🟡TRANSIT.
📦 Update All Delivered Shipments
- Checks all orders in the Outgoing Shipments section that have a status of 🟢DELIVERED will be removed from the Shipments tab and added to the Shipment Log tab
➕ Manually Add New Order
- Use this to add a manual order to the Incoming Orders section.
↪️ Move Manual Order to Shipment
- Once a manual order has been shipped out, use this to move the order to the Outgoing Shipments section.
➕ Manually Add Shipment to Log
- If a manual order was shipped out before it was added to the incoming orders section, use this to add it to the Outgoing Shipments section.
🛍️ Move Bags from Storage to Floor
- Opens up a dialog where you select which type of bag you want to update, and then you input how many bags you are moving from the storage to the floor. Use this function whenever you take bags from storage and bring them down to the floor.
🫧 Add New Soap Month
- Opens up a date picker dialog. When a new shipment of soaps arrive, use this function and input the date of the shipment's arrival to add a new soap batch month to the inventory count. You can then record the quantity of soaps that have been ordered in this shipment in the Inventory Entry Editor under the new date.
➕ New Sample Request
- This will open up a dialog where you can input the information about the order (Name, Date, Address, etc.) and then select which items you would like to add to the sample request. Once you click submit, a new entry in the Samples tab will be added.
Depending on the item type, a "case pack" will refer to a specific quantity typically used for wholesale orders. Listed below is a reference for how many items are in a case per item type.
Per Case:
- Soaps: 10 soaps
- Dishbars: 10 dishbars
- Bags: 12 bags
- Hair Products: 6 jars