Let's say you want to send yourself a random motivational message each day, and you have a collection of inspirational quotes in a Google Sheet. We want to build a Zap that will trigger each morning and choose one of the quotes to send to you on Slack. Here's how you can do that.
First, create your Google Sheet. For the first column, add the header 'Has quote' and have your quotes in the second column. In the first column, add this formula:
This will return the Value 'TRUE' if there's a quote in the quote column 'FALSE' if there isn't. You can copy this formula into every row - whether or not there's a quote in there now.
Add your quote in the second column, and then add a third column called 'Row ID'. This third column will tell the Zap which Google Sheets Row each quote is on, which we'll need.
Then create your Zap. In this case we're using Schedule as a trigger so that I can get an inspirational quote once a day.
The next step is a Google Sheets Lookup Spreadsheet Row action. Set up the step so that it is looking in the Has Quote column for the value 'FALSE'. That means that it will always find the first row in the sheet that doesn't have a quote in it.
Now we need a step that will generate a random row number. To do that, we'll use the Formatter by Zapier app choosing the 'Spreadsheet Style Formula' option in 'Numbers'. For the formula, we use:
This tells the Zap to pick a random number between 2 and the Row ID number from the previous step, minus 1. We're starting at 2 because Row 1 is the header row.
Now let's pick that quote!
Add another Lookup Spreadsheet Row step. This time, set the Lookup Column to the Row ID column and the Lookup value to be the output of the Formatter step.
With this set up, each time the Zap runs, it will find the last row in the sheet and then choose a random row between the first and last row with a quote in it.
Finally we can add the Slack action to send the quote. Obviously, you can use any action you like here!