Best answer

Trigger When Entry to Sheets Not Made during TIme Period


Userlevel 1

I need to have a zap triggered when there is NO entry made during a given period of time (X days before today’s date). I have a Sheet that collects data entered from a Form. Users are supposed to use the form to log their mood on a daily basis. I want to get notified when they did NOT do that so that I can check in with them. 

icon

Best answer by andywingrave 9 April 2020, 00:01

Hey Peter - There are a few ways you can do this (Although I would recommend Airtable for your use case over Google Sheets) because it will allow you to achieve your desired result without any workarounds. 

However, for sheets, my recommendation is that you create a VLOOKUP in a new tab where the condition is ‘User Update = “” ‘ (i.e. Blank) 

From there you can trigger your zap.

View original

This topic has been closed for comments

5 replies

Userlevel 7
Badge +9

Hey Peter - There are a few ways you can do this (Although I would recommend Airtable for your use case over Google Sheets) because it will allow you to achieve your desired result without any workarounds. 

However, for sheets, my recommendation is that you create a VLOOKUP in a new tab where the condition is ‘User Update = “” ‘ (i.e. Blank) 

From there you can trigger your zap.

Userlevel 1

Thank you very much for your response! I was able to create a VLOOKUP and managed to set it up so that there was a cell which was either 0 or 1 depending on whether there was a recent entry. However I could not figure out how to make that condition trigger a Zap. I set up the Zap to check that value once a day but it doesn’t seem to work. 

Userlevel 7
Badge +11

Hi Peter, when you say that the Zap wasn’t working, could I ask what the trouble was? Was the Zap not triggering?

 

One trouble with using Google Sheets is that when you use the New or Updated Spreadsheet Row trigger, if the value in a cell changes to something that the Zap has already seen, the Zap wont trigger. For example, let’s say row 8 had a 1 in it, and then changed to a 0, that would trigger the Zap. If the Row was changed again to have a 1 again, the Zap wouldn’t trigger. 

 

How about this - in the lookup in the sheet you say that if there was a recent entry, the value in the cell is ‘0’. If there hasn’t been an update in the last two days, the value in the cell is changed to today’s date. You would then use the New or Updated Spreadsheet Row trigger, using that column as the trigger. Does that sound like it would help?

Userlevel 1

I am going to try doing this with a javascript add-on to Sheets. I appreciate everyone’s thoughtful answers. This is a wonderful community!

Userlevel 7
Badge +11

Thanks for letting us know @Peter at Gateway! Fingers crossed that the add-on gets the job done 🙂