I’m trying to make a website for a meal prep business that currently takes orders via text message. I’ve set up their store on Squarespace and need some help massaging the data so that every night at midnight, they can print out what they need to make in the early morning.
Details are first, and at the very bottom is my progress.
Some details:
There are 10+ meals (e.g. chicken teriyaki, turkey BBQ burger, salmon teriyaki, shepherd’s pie)
Each meal has the exact same following product variants… amount of protein, type of carbs, amount of carbs, type of veggies, amount of veggies)
You can also order protein muffins (there’s ~8 different types)
You can also order meat in bulk (1lb, 2lb, 3lb, 4lb, 5lb)
total number of meals we need to make BY type (e.g. teriyaki chicken, turkey BBQ burger, salmon teriyaki)
total amount of protein needed to make, and by meal selected (e.g. teriyaki chicken and can choose 6oz, 7oz, or 8oz; but also needs to sum up bulk meat orders)
total amount of carbs needed to make, and by type (there are 4 types)
total amount of veggies needed to make, and by type (there are 3 types)
total amount of muffins to make, and by type (there are ~8 types)
Here are some snapshots of the data I get from Squarespace:
This test customer ordered 2 variations of Teriyaki Chicken (3 in first variation combo and 2 in second variation combo):
Total green beans: 3 orders (orders because we just fill the remaining space after protein and carbs)
Total broccoli: 2 orders (orders because we just fill the remaining space after protein and carbs)
Additionally, we wanted to figure out how to print out 4 orders per page vs. the Squarespace 1 order per person per page. This step is to make sure at the end of bulk-preparing everything, the team can quickly assemble the meals appropriately.
Any help would be appreciated! I’m a novice with data manipulation, so I’m not very far along. So far I’ve been able to bring in the data and have been experimenting with how to make the initial de-duped counts for totals.
Ultimately, your calculations will all be done by Sum by Group or Count by Group steps. Those steps can sum a column or count the number of rows, per unique value in a another column. So you could sum the total pounds of carbs to make per each type, or you could count the total number of meals to produce by type (like your first calculation listed).
Before doing that, you need to get your data into a shape that those steps will work well on. Looking at your screenshots, I think you should use a Pivot Columns step after your Expand JSON that is expanding the variations. Target the optionName column as the Pivot column, and the Option Value column as the Value column. That will flip those options into columns so that meals are not repeated in the rows. Make sure, though, that you have removed any old JSON columns that were already expanded using a Remove Rows step between the Expand JSON step and the Pivot Columns step. Otherwise the rows will not update properly.
Hey Brian, thanks for the quick answer. This all makes sense. I’m having a tough time figuring out which are the “old JSON columns” to remove and how to do it. Any guidance?
Hi @Ashwin_Khurana, the old JSON column you’d want to remove with Remove columns would be “lineItems”. This is because the step Expand JSON splits JSON object text into separate columns for each field in the object, while keeping the original text in its original column. Using Remove columns removes the JSON column after it has been expanded since the JSON blobs are unique values that would make the step Pivot columns fail at removing duplicates. By removing the unique column, we’re ensuring that the pivot step works properly by combining duplicate columns.
Thanks for your help, everyone. I think I got the calculations down
Need help with the last step of importing into Google Sheets.
Am hoping to set up an automated push of the order data at 11:59pm on Sunday night, Tuesday night, and Thursday night. These pulls would include orders from e.g. 12:00am Monday morning through 11:59pm Tuesday night.
Need to create a separate page within the exported sheet that simply pulls in individual orders with the following attributes: Name of customer + orders (with variant details). The purpose of the calculations we did above was to give the business the aggregate totals they’d need to make across all orders. This step is trying to create printouts with four orders by page so that the business can distribute and package meals according to the variants and amounts individual orders are comprised of.
Any help here would be much appreciated. Almost over the finish line!
I’m particularly confused with:
Can I drop the exports into a specific shared folder? Multiple people need access
Can it create a new file (not sure how it’ll be named) every time we push to Google (every Sunday, Tuesday, and Thursday night)?
Can I name the file based on the dates of the orders included?
Hey @Ashwin_Khurana - Glad to hear you’re getting close! Answering your questions inline:
Can I drop the exports into a specific shared folder? Multiple people need access
Unfortunately, our current Send to Google Sheets step doesn’t support exporting to a specific folder. If the sheets file that Parabola is appending to or overwriting to is stored in a shared folder, it will stay in that shared folder as Parabola updates your data. Can it create a new file (not sure how it’ll be named) every time we push to Google (every Sunday, Tuesday, and Thursday night)?
Yes, you’ll want to set up your Send to Google Sheets step like @David suggests in this thread. Make sure that you set it to Each time this flow is run it will create a new file, like it shows in David’s screenshot. This will automatically add a timestamp of the time your Google Sheets file was created. Can I name the file based on the dates of the orders included?
By default, Google will append the timestamp of when the file was created. Unfortunately, I guess in your use case, it’ll display the following day because the flow you run on Tuesday will be the order data from Monday. Since the timestamp is currently set by Google right now, there won’t be a way to workaround this for now.
Hope this helps! Let us know if you have any other questions.
I’ve got everything working up to here so far! Thanks for the help. Two more issues I need to solve for:
How do I make it so that the API will only create a new file for orders since “the last time this was run?” My report currently has a rule to run every MWF at 12:00am. For example, when it runs on Monday morning I want it to display all orders made after 12:00am the Friday before. Is this possible?
My current fix is part manual, meaning that someone has to go into Squarespace and manually mark orders as FULFILLED. I added a step after my expand columns and before all calculations to filter out all rows with a status of FULFILLED. Is there a better way to do this?
Now that I’ve totaled the number of OZ BY MEAL TYPE, I would like to do some math on these. For example, Mango chicken and teriyaki chicken are made with “chicken tenderloin” and the bbq chicken, chicken salad, and mushroom chicken are all made with “chicken breast.” I would like to add these specific meals by type to get the total aggregate amount in oz of chicken breast / chicken tenderloin / etc needed.
The best way to do this is to create an export to Google Sheets logging your completed orders. You’ll want to use that document to import completed orders into your flow and use a Combine Tables step with your main import.
Join your tables on a matching column, and try using a Compare dates step to compare the time difference between when the order was placed and the Current Time.
Add Filter rows step, you can filter out any rows that are greater than two days. That would work well for weekend orders.
To aggregate meals by the chicken type, use an If/else step to create a new column and insert the chicken type value based on the Protein / meal type value.
You can then use a Sum by Group to step to summarize the total ounces based on the chicken type.
I was able to use the If/Else steps correctly and I’ll be working on the combining tables to filter rows step today. Sum by group worked out as well.
Currently I’m getting 6-7 different sheets in the google doc export with all the different calculations I’m doing. Is there any way to format these columns or values into a one-pager or one sheet for simplicity?
I’m so glad to hear that worked! Since each input into your Google Sheet export has to be sent to a different sheet, I can suggest two methods.
The first method is to map every sheet directly in your Google Doc to a master sheet. That will certainly allow you the most visual flexibility since you can map a cell value anywhere on your master sheet.
The next method is to take your first calculation and create additional column headers matching the rest of the columns from your other calculations. The best way to do this is by using multiple Insert if/else column steps. Here is some mocked data:
You should now be able to use a Stack tables step to combine the calculations under their shared column headers. You may need to use a Reorder columns step to ensure the columns from each calculation are coupled together.
Thank you for that detailed explanation! I ended up finally making everything work with the following details:
I have every calculation working and going into a different sheet, but it’s clean now so it’s OK and easy to tab through. Only issue is I haven’t figured out how to make sure this thing formats correctly with spacing because my flow needs to create a new file eve
I haven’t implemented the “only orders since last time” step. As an intermediary with some manual help, I’ve filtered out all orders except those that are PENDING aka unfulfilled. No canceled, no fulfilled, no canceled. If the person manually checking off orders as fulfilled in squarespace does their job correctly, this should continue to work.
Unfortunately, I’m not sure how to work with or create master sheets / if it’ll still work when I’m adding a new file every time the flow runs.
Lastly, I’m wondering how it might be possible to create the following on its own sheet at the very end of the export:
• Name of the person who made the order
• Time the order was made
• Printout of the items ordered w/ variants and options
The first steps and calculations are all for easy bulk creation of the meals but at the end, they need to divvy up and prepare the meals based on who ordered what. It’s hard and annoying to do that manually in squarespace and they were hoping to somehow get a few orders on a page instead of having to print out a TON of pages. Any advice?
Looks awesome! How would you like this sheet to be formatted with correct spacing? I might recommend doing additional formatting in Google Sheets.
Let’s look at your Type of meal sheet pictured above. The meal types are in column A while the quantities are in column B. We can map the values in those columns to any cell or column in a “master sheet”. You can format your values using any spacing.
Try duplicating your export into two Google Sheets. The first creates a new file each day. The second one can be overwritten to utilize a master sheet.
To get your order information into a printable format, create a branch using the data from your initial Pull from an API step. To get your customer’s name, target the shippingAddress firstName, and shippingAddress lastName columns.
To find what time the order was placed, use the createdOn column. For the items ordered, target the lineItems.productName, and lineItems quantity columns. To see any variant info, look for the lineItems variantOptions optionName and lineItems variantOptions value columns.
You should now be able to export a list of order details that exist in one sheet.
Sweet, thanks so much for the help. I think this is the last bit now…
How would I print out the following? We need to know how many of each meal and specific configurations were ordered? For example:
3 Teriyaki chickens (meal type) with 6oz (protein amount), Jasmine Rice (carbs), 1/2 cup (carbs amount), and mixed vegetables (veggies).
There might be 2 more teriyaki chicken meals ordered but with a diff combo, like 7oz of protein, a different type and amount of rice, and different veggies.
My clients need a big list of all the different combos ordered and their quantity for rapid meal creation at the very end of their prep cycles. Sort of like this:
Teriyaki chicken, 6oz protein, jasmine rice, 1 cup, mixed vegetables (need to make = 2)
Teriyaki chicken, 7oz protein, jasmine rice, 1 cup, mixed vegetables (need to make = 1 )
Teriyaki chicken, 8oz protein, brown rice, 1/2 cup, broccoli (need to make = 5 )
etc
Any thoughts? Thank you
Edit: Here’s what I have so far, but I’m not sure if I’m doing this correctly… I branched off my pivot step that expands all my product options.
Try merging the values of all of your columns into a single column using the Insert text column step. You can merge all of the columns into a single column like this:
Teriyaki chicken, 8oz, Brown rice, 1/2 cup, Broccoli
Next, use a Count by group step to count the total number of instances of each Combination Meal. The new column should have the total number of meal combinations that need to be made. I’ve called this column Quantity to make.
You can use another Insert text step to merge the Combined Meal and Quantity to make columns: {Combined Meal} (need to make = {Quantity to make})
Your resulting table should have your desired output:
Teriyaki chicken, 8oz, Brown rice, 1/2 cup, Broccoli (need to make = 5)
Turns out I’m running into some edge cases here… it looks like my flows only work and run when every type of meal is ordered and when there are orders present.
How might I be able to solve for these edge cases:
When no meals have been ordered (there won’t be any meal variants being passed, which means my steps looking for or operating on columns created by that pivot, do not exist.
When a certain type of meal hasn’t been ordered, the if/else steps won’t run because it’s supposed to create a column that sums up e.g. the amount of [chicken breast] ordered across the meals as well as in bulk.
I realize that we might need to take a closer look at my flow and individual steps, but at a high level I’m guessing I need to insert a bunch of if/else’s to manually insert columns and fill the row elements with 0 or something like that so the flows can execute as if every meal type was ordered.
Thanks again
Edit / update: My current solution is adding a “insert text column” step after the initial pivot and before all the calculations. Adding the columns titled, “Protein amount,” “Carbs,” “Carbs amount,” and “Veggies.” The only issue is that Veggies takes a string and I don’t know how to put in a blank text space, so I just put the string, “None.”
As for fixing the If / else’s below, I made sure that in the absence of a particular type of meal and protein combo present, it will default to “Otherwise, use 0 as the default column value.”
Squarespace allows me to manually export using date parameters (I deleted and reordered a ton of columns to get to the screenshot above). There’s a bunch of extra columns in here but any time I try to recreate it, I can’t seem to:
sort by customer name
remove the redundant customer names to make it easy to view
@Ashwin_Khurana I think this may be possible, depending on your datasets and their structures/formats. Revisiting your flow screenshot from one of your replies two weeks ago:
Do you still have ≥6 inputs connected to your final ending flow step? Is your question whether it’s possible to recreate that table as the output of this flow, so that the output is a single Google Sheet instead of various Google Sheets you then manually combine together?
We have flow steps that in combination can likely recreate a table looking like your screenshot of “orders (1)” while also sorting customers by name and removing redundant rows or columns. Would this be a new flow, or a flow branch on your main one built earlier? Are the data inputs (i.e. Order ID, Billing Name, Shipping Name, etc) already pulled into an existing flow you’ve built?
@Adeline — Happy holidays to you and the team! Thanks for the quick responses.
I still do have 6 steps connected to the final sheet because each sheet being created is a separate view my clients were asking for, and each step correlates with a sequential part of their physical meal prep creation workflow.
Sheet 6 helps the team make all the orders by different configurations very quickly, and sheet 7 (the new one I’m trying to make) is the final step where they now label and sort all the meals created into bags for each customer by name.
This is what Squarespace pulls up when you manually view an invoice, and the screenshot I posted above is a CSV that is generated and edited / reorganized into the fields I’d like to create on the 7th sheet.
So far I have this directly branching from the expand JSON step, but this issue is the redundant order numbers, name, and product name. For example, there are two orders of Chicken teriyaki by the same person in two different configurations (protein amount, carbs, carbs amount, and veggies). I’m having trouble removing these extraneous pieces of information for visual clarity of the sheet output.
Hey @Ashwin_Khurana, followed up over email but in case others are trying to do something similar, here’s what I’d recommend:
If you add in an “Insert row numbers” step, you can number rows based on the order # (pretty new functionality that we added that I think is awesome…but I’m biased!):
Once you do that, you can use an If/Else step to say: if the row is equal to 1, insert name, but if the row does not equal 1, don’t include anything. That should make it so only the first row (by order #) has values for name or other columns.
