Best answer

Periodic scanning for missing data?

  • 8 April 2021
  • 3 replies
  • 134 views

Userlevel 1

I have a Google Sheet with four columns: Email address, thing 1, thing 2, and thing 3.

The possible values for the “thing” fields are “Yes” and NULL (empty / no data).

Is there a way to create a Zap that periodically (say weekly) looks for rows in the table that do NOT have a “Yes” in ALL three “thing” fields and then sends an email to the address in the row(s) with a message about what’s missing?  (The message would need to be verbose and not just the column heading.)

So the trigger would need to be “time” rather than creation or update of a row.

And I’d need something that translates a NULL value in, say, the thing 1 and thing 3 columns to something like, “You’re missing your widget and your doohickey” in the email body.

Is this possible via Zapier or too tricky?  TIA

icon

Best answer by XRay Tech 8 April 2021, 23:55

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.

3 replies

Badge +4

@webwiz This is a good question and a fun use case!

You can definitely schedule a Zap as you have described. To do this you set the trigger as the “Schedule” app by Zapier. This will allow you to schedule the automation to trigger Hourly, Daily, Weekly, or Monthly. Easy!

The next piece is a bit trickier. This might be a good use for code, but could also be accomplished using the Looping App by Zapier (This is a premium feature and may require upgrading your plan). To do this, I would use a Looping variable to Loop through each row in the spreadsheet and then check for some key values. I would do the brunt of the work using GSheets logic. I’d create three additional columns, one for each of the “things” and use the “if” function such that if the value in the corresponding cell is blank it returns the email text you want to send (=if(thing1=””, you need to fill out thing1)). Then I would add a fourth column that concatenates these three columns so you have an email body for each row corresponding to which fields are blank. Then make a filter step in your Zapier loop that checks if this concatenated cell is blank and if it isn’t proceeds to send an email with the email body you made in the sheet. This assumes the person’s email is also in the spreadsheet row with their responses. 

This is a pretty complex workflow, but should work for you as long as you have fewer than 500 rows in the spreadsheet. If you have more than that you will have to create separate Zaps that loop through 1-500, 501-100, etc. since there is a looping limit of 500 currently. 

Let me know if this works for you or if you need any clarification!

-XRay

Userlevel 1

Thanks @XRayTech I’ll give this a shot tomorrow.  Luckily, I don’t think I’ll have more than 100 rows.

Badge +4

@webwiz You may also need to use the “IFS” function so that you can set two different scenarios. One for if they say yes (output no a space or no text) and one for if they haven’t responded (output the prompt to answer that specific question).