About Zapier Community
New to our Community? Get to know more about our space and how to make the most of your time here.

Code of Conduct Community FAQ's Zapier Common Questions What to Expect

Get Started With Zapier
New to Zapier? We have got you covered with our Zapier 101 Resources.

Zapier Essentials

Get Support
Have a question? Let's get you an answer!

How Do I...? Troubleshooting Code & Webhooks Developer Zone

Other Help Resources
Wait, there's more! Check out these Community sourced spaces for additional resources.

Zapier Help Center Hire an Expert

Zapier Products
Learn more about Zapier's new products.

Zapier Tables Zapier Interfaces

Zapier Reading
All of our best learning resources can be found here.

Featured Articles Show & Tell AI Hub Zapier Tools Hub

Learning Groups
Excited to meet other builders? Check out our interest areas full of doers, dreamers, and in-betweeners.

User Groups

Other Learning Resources
Some of our favorite additional ways to upskill your Zapier knowledge.

Zapier Learn Zapier Blog Zapier Webinars

Product Updates
Check out what is new and upcoming with Zapier with our regular product updates.

Product Updates

Early Access Program
Want to join Early Access? Learn more here.

Question

Multi-step Zap - Combine Google Sheets row/lookup data/email results weekly

  • 21 July 2023
  • 5 replies
  • 263 views
SteveVK

Hi All, 

Relatively new to Zapier and trying to solve a problem thats fairly convoluted. Hoping with all the new features this is possible in Zapier, rather than getting something custom built. 

Background

We have several Shopify stores that create custom portraits. As there are unique requests for each order, we have an app (lets call it App #1) that manages that. This app is not integrated with Zapier. The devs of that app have set up an export for us into Google Sheets (triggered when the order is paid), where some of the order metadata is sent. We also use another app (lets call it App #2) that manages the portrait approvals by the customer (this app IS integrated with Zapier with a current active zap).

BUT we only pay the artists when the portrait is approved by the customer, and that notification is what is exported by App #2, because it can be some time after the order was placed/paid for. Both apps export to Tab #1 and both reference the same order ID, so we end up with 2 lines for each order (App #1 exports the order info, App#2 exports the order approval notification). With me so far? 🙂 

We use artists to create these portraits, based on the options the customer selected during the order process. We need a way to calculate what they need paid for each order, using the options selected by the customer, which is what the export from App #1 metadata columns list. We only need to consider 2 of these columns - one returns a text string (the name of the background they chose) and the second returns a number (the number of people in the portrait). Each of our back ground styles and number of people options has a set price. Example costs only for the purpose of this question :) Backgroundname1 = $20, backgroundname2 = $30, Backgroundname3 = $0, 2 people = $20, 3 people = $30 and so on… Still with me? 😀

I know enough about Excel/Sheets to know you can have a table in another tab and do a lookup to find out a related value. I have all the backround names and number of people options along with their associated cost set up in Tab #2. 

Requirement

Using all the information above, I need to create a report thats automatically sent by email and/or WhatsApp message weekly (say on a Monday), for all approved orders for the previous week (Mon-Sun). Each order needs to be on its own row (so row 2 Order ID 1024, row 3 Order ID 1025 etc) and calculates and totals the cost for each order, so we know what we need to pay.  

I’m not set on any particular style for the report/export, i suspect an email/file attachment attachment would be more likely, xlsx or cvs perhaps. Here is what i imagine the Sheets version to look like:

 

I’m also happy to bring in other tools if we need e.g, Airtable etc but obviously the simpler the better. 

Hope all that made sense. Obviously happy to answer any questions or provide more info. 

Really appreciate anyones ideas or suggestions.

Thanks!
Steve

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

5 replies

Liz_Roberts
Userlevel 7
Badge +8

👋🏽 Hi @SteveVK! Let’s think through this one...

Could you share what specific apps you’re using to do what within your current workflow? That will help us determine what might be possible here!

SteveVK

Hi 🙂, sure. 

Ininfite Options is app 1 and Proofer is app 2. 

The company behind I.O. also has an automation app called MESA which is what exports the order details/metadata to the Sheet. I already talked with them about trying to solve the issue i’m describing but they dont have the functionality. 

So, I.O. provides the options on the product page, from which the custoer chooses what they want. They then checkout. MESA is triggered to then send the order and I.O. option info to the Sheet (as i described in the post above). The Proofer app records the order then sends a notification to the artist who then creates the draft portrait. They send that to the customer for review/edit requests, and when the cusomer is happy, they click approve. Proofer is integrated with Zapier, so the zap i have running is triggered at that point and sends the confirmation of approval to the same tab on the Sheet (the double entry that I described). 

This is the actual sheet with the export data:

The last test order (1023) is at the bottom and you can see the multiple rows. 

 

Hope that helps! Let me know if you have any more questions.

Thanks!

SamB
Rank
Userlevel 7
Badge +11

Hi @SteveVK! 👋
 

Ok, so it would involve multiple Zaps and formulas that need to be added to the Google Sheets spreadsheet but I believe it would be possible. 

To start with, what you’d want to do is create a worksheet in the current Google Sheets spreadsheet. Then use an IMPORTRANGE formula to pull in the information from the export. In that new worksheet, you’d have a new column with a formula that checks to see if the Date Approved is within the previous week. I did a quick test and it looks like this formula would work: 

 

=IF(AND(ISDATE(A2), WEEKNUM(A2, 2) = WEEKNUM(TODAY(), 2) - 1), "Yes", "No")

cb716fd39ff68c52d96a3ec80769552b.png

You’d then need to change the cell references (A2) to match the relevant column in the row.

Then you would set up a Zap which checks that specific worksheet for any rows where the Last Week? column contains the value YES. For example:

  • Trigger: Every Week (Schedule by Zapier) - runs every week on a specific day and time.
  • Action: Lookup Spreadsheet Rows (output as Line Items) (Google Sheets) - searches for multiple rows based on value in Last Week? column.
  • Action: Create Spreadsheet (Google Sheets) - creates new spreadsheet for the week be sure to have a specific keyword(s) present in the title for example “weekly approvals - Jul 24, 2023”.
  • Action: Create Loop From Line Items (Looping by Zapier) - creates a loop of rows to run through based on line items output by the Lookup Spreadsheet Rows action.  You can learn more about loops here: Loop your Zap actions
    • Action: Create Spreadsheet Row (Google Sheets) - adds each of the approved rows into the newly created spreadsheet. You’ll need to select the spreadsheet using a custom value and choose ID from the Create Spreadsheet action.

 

Now, that handles the creation of the file with details of last weeks approved orders but you can’t add a Send Email action within the loop or it will send it out multiple times! So you need an additional Zap:

  • Trigger: New Spreadsheet (Google Sheets) - runs when a new spreadsheet is added.
  • Action: Only Continue If (Filter by Zapier) - checks that the title of the spreadsheet contains certain keyword(s) for example “weekly approvals”, so the Zap would only run for the spreadsheets created by the previous Zap. You can learn more about using filters here: Add conditions to Zaps with filters
  • Action: Send Email (Gmail) - Sends email with the spreadsheet attached. In the Attachments field you’d select the either the File or File CSV field.

 

Do you think that approach will work? If I've misunderstood what you're looking to do or you run into any issues in setting this up just let us know. Always happy to help further! 🙂

SteveVK

Hi Sam, appreciate the response! Let me try it out and get back to you… Thanks!

rachael.silvano
Userlevel 5
Badge +7

Hi @SteveVK , checking in with how you're progressing? Let us know if there is anything else you need! -Rachael

Terms & Conditions