Best answer

Using Google Sheets with Formulas in Zap


Badge

Here is the zap I am trying to create

  1. Trigger when any spreadsheet row (Google Sheets) is updated
  2. If the cell in column G is over 100% (this cell is a simple (=f13/d13) formula in the spreadsheet itself)…
  3. Send Channel message in Slack 

I have tried EVERYTHING but I cannot get the trigger to work because the row that is being updated is Row 13 which has a formula in it so when I try to do a test trigger it will not pull the data from ANY row with a formula….but every row I need data from has a formula in it. So pretty much this trigger only works with rows that are irrelevant but none of the other triggers make any sense….

Someone please help! How can I get zapier to read any data within the rows that have formulas so I can trigger this thing? 

icon

Best answer by Troy Tessalone 30 June 2021, 22:01

View original

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

13 replies

Userlevel 7
Badge +14

Hi @Erica 

Please post screenshots of how your Zap steps are configured as well as how your GSheet is configured, thanks.

 

Steps

  1. Trigger: GSheets - New/Updated Row
  2. Action: Filter
  3. Action: Slack - Send Channel Message

 

Badge

@Troy Tessalone  Here are screenshots of the spreadsheets so you can see the dif formulas. What I am looking for is when G9, G12, G17….. reach 100% they trigger an zap

You can see the formula of those cells up top. That formula pulls from cells D and F. Cell D13 is just a number (no formula) but cell F13 is a formula pulling from all row 13 data  (See pic below) 

The zap I currently have set up is pictured below 

The last step is send slack message which works fine. 

Userlevel 7
Badge +14

@Erica 

GSheets need to be setup in a specific way to work with Zaps: https://zapier.com/help/doc/how-to-set-up-your-google-spreadsheet-to-work-with-zapier

  • The first row must have titles for any column headers you want to be able to see in your Zap.
  • There can be no completely blank rows before the end of your spreadsheet.
  • The second row must have content for any columns you want to be able to see in your Zap.

Check out the available help articles: https://zapier.com/apps/google-sheets/help

Badge

@Troy Tessalone  Thanks for your response. So even if I make those changes it still will not pull test data from any of the rows that have formulas...In the screenshot below you can see the changes I made to the spreadsheet per your guidance in red. All relevant columns have titles in 1st row. All relevant columns have data in second row. No empty rows

In my zap trigger step the test data it offers me is from row 49, 50, 51 or 52…..thats it. As you can see I need it to pull test data from row 48 

Userlevel 7
Badge +14

@Erica 

GSheets won’t work with grouped rows/columns.

Essentially the GSheet needs to be structured as a basic table with headers and rows.

Formulas can be used in the rows.

 

I suggest reviewing the available help articles for using GSheets in Zaps: https://zapier.com/apps/google-sheets/help

Badge

@Troy Tessalone I appreciate your help. So pretty much you cant use any advanced Google sheets with Zapier? Seems to me like I would have to use only the most basic of tables which would be fine if it wasn’t client facing but since it is thats a problem. The table needs to look good and be easy to use for clients first and foremost and if I cant do basic formatting like merge cells than seems like Zapier isn’t super useable in this situation. Am I not understanding this right? 

Userlevel 7
Badge +14

@Erica 

GSheets need to be setup in a specific way to work with Zaps: https://zapier.com/help/doc/how-to-set-up-your-google-spreadsheet-to-work-with-zapier

There are many help articles related to using GSheets in Zaps that I suggest reviewing : https://zapier.com/apps/google-sheets/help

Badge

Ok reposting the same article links over and over isn’t helping. I looked at the articles the first time you posted them. My point is the way the articles outline it MUST be set up does not allow for the sheet to ALSO be aesthetically pleasing enough to be client facing. And therefore that is a limitation of this Zapier and Google Sheet integration. 

Userlevel 7
Badge +14

@Erica 

It takes a more advanced approach to structure GSheets to work with Zaps AND be aesthetically pleasing AND usable.

The basic premise is to have a MASTER file that contains the raw data, where the data would be added/edited.

Then you’d reflect that data onto other GSheet tabs via formulas, which results in read-only capabilities of the data.

Some of what you’re describing leads toward custom app dev and possibly the need to explore other solutions beyond GSheets for what you are trying to achieve with a solution.

Badge

So i kind of did that. I created another tab with a basic table like I need to have for Zapier that just pulls data from the nicer table. It works!! It takes forever to run but it works!! 

Thank you so much. Do you happen to know if I can hide this tab will Zapier still be able to pull from it? 

Userlevel 7
Badge +14

@Erica 

Confirming Zaps using hidden tabs in a GSheet will still work.

 

The Google Sheets trigger is marked "instant" but it still takes a few minutes to trigger

The triggers for Google Sheets are unique among Zapier triggers. When there is a trigger event in the spreadsheet, Zapier gets a notification webhook from Google about this. After that, Zapier sends Google Sheets a request for new data, so it uses both the polling and instant trigger methods. This process takes about 3 minutes overall.

While not being "instant", these triggers are faster than regular polling ones, as they don't depend on the polling interval of the plan your account uses.

Badge

Thank you SO SO much for helping me with this! I really appreciate it! 

Erica - I see you’ve marked this as resolved, but just two quick questions about your system.

  1. Your search criteria is for 100%, but in your screenshot you’ve got values above that. If you surpass that value in your data input (e.g. 125%) , your zap will not trigger because it is not an exact match.
  2. Also, your system doesn’t seem to allow for ongoing use of the tracking - since it contains no date logic. Are you planning on resetting it each week after saturday to handle this?

Your setup for sure makes sense for humans, nice formatting and layout etc. But one of the things that can be overlayed on this is a small bit of Google Apps Script that copies values from one cell and pastes them in another location upon detecting a trigger (e.g. click ‘run script’ on a button inserted somewhere). Just a thought in case you’re looking for ways to continue developing your system

 

All the best!

Alistair