Create a FREE automatic flow to email a random google sheet row a day using Pipedream
pipedream.com is an excellent tool I’ve recently found to automate flows, which I found way superior (and cheaper) to Zapier and automate.io, that I’ve both used for a while.
Pipedream has a very generous free tier so you’ll probably use it forever for free like me. In case you need to use it more, I really think it’s worth the money as doing those operations manually (or delegating to a developer to develop and maintain) will surely cost you way more money and stress.
One of the things I like the most is the possibility to add custom javascript code to take the output from a step (e.g. Google Sheet data) and transform it as an input for the next step (e.g. Gmail), focussing on the business logic and not wasting time with APIs.
Sending a random Google Sheet row to your Gmail account every day
I have a Google sheet that I use as a flashcard system for me to learn things with this format:
I wanted a daily mail with the question in the title. that I can click and check the answer is correct.
How to implement
Create an account with pipedream.com using Google (probably simpler as you’ll also use Google APIs), and create a project.
Go inside the project and choose New -> Workflow. Leave the default options and click Create Workflow.
STEP 1: Trigger
A “flow” is composed of a trigger (starting the workflow) followed by one or more steps in sequence.
In our case the trigger is a Schedule as we want the flow to start every day, So create the trigger, search for Schedule, and click on it.
Choose Daily Schedule
then Save and continue
Once created, pipedream offers a very useful tool to generate a sample event (so you can test it and don’t have to wait for it to happen).
Triggers and steps both have information that is carried to the next step. In case of a schedule we don’t need to feed the trigger data (the date), so just click on the “+” button at the bottom.
STEP 2: get values from Google Sheet
You can now add the step to get the Google sheet rows. Add Google Sheets and Get Values.
Select your Spreadsheet and the sheet by search (it’ll ask to connect your Google account if you haven’t done it already)
Click Test to see the output.
What we have here is steps.get_values.$return_value being a multi-dimensional array being your sheet. The correspondence is obvious. Like any programming language, the numbering starts with zero, so the cell on the first row and first column (A1) is steps.get_values.$return_value[0][0]
Observe the output from the step. This will be fed to the next step.
We don’t need anything else, so just click the Plus icon to create another step.
Step 3: get a random row using Node (Javascript)
Things are now becoming more interesting.
Create a step Node and Run Node Code
You’ll now have a small code editor. Inside the “run” method you have the steps object that is the one above with the sheet array.
Optional: play with that by adding console.log(steps.get_values.$return_value[0]) and click Test. In the Logs tab you’ll see the question and answer
Add the code to grab a random row.
After having filtered the valid values (both question and answer not empty), I’m taking a random row, then calculating in what row the question was in the original sheet (before filtering).
This code returns the question, the answer, the number of questions, and the question cell position. See the second screenshot if not clear.
const wholeSheet = steps.get_values.$return_value;
const validRows = wholeSheet.filter(r=>!!r[0] && !!r[1]);
const randomIndex = Math.floor(Math.random() * validRows.length);
const [q,a] = validRows[randomIndex];
const rowNumber = "A "+(wholeSheet.map(a=>a[0]).indexOf(q));
return [q, a, validRows.length, rowNumber];
run” function. And the “Logs” tab will print the “console.log(variable)” you write
Nothing else to do here. Click on “+” below the step to add the final step.
Step 4 (last): send to Gmail
Create the final action Gmail and then Send Email
Add yourself as addressee
Subject:
I want “word of the day: <the question> [<position in the sheet>]” so as subject enter the following:
word of the day: {{steps.node.$return_value[0]}} [{{steps.node.$return_value[3]}}]Body:
I want to print the answer, but also the link to the sheet and the number of total words, so here is the body:
{{steps.node.$return_value[1]}}
Edit: https://docs.google.com/spreadsheets/d/...<add the link to the sheet>
Total words: {{steps.node.$return_value[2]}}When you click on a field, you’ll get a popup to select a path of the data from the previous step so you can double check here that paths are correct
Click Test so send the email
Done !
Check your email and you’ll see this.
Manage the workflow (pause)
If you are happy with the result, don’t forget to Deploy the workflow and you’ll get an email a day.
In case you don’t want the email for a while, don’t delete it, or you’ll lose all the config and the custom code. Just click Pause in the list of workflows and you won’t receive any email until you resume it
Conclusion
Pipedream is paid only if you have more than 3 accounts (you are using two, Gmail and Google sheet), 5 active workflows (we just added one), and 100 credits a day (we just use a few for this workflow). See details in the docs, limits page.
It’s basically a swiss-knife for your personal, your businesses, and your company.
I don’t recommend implementing those workflows with custom code from scratch. Implementing the connection with APIs is a real waste of time considering oAuth with callback URL, token refreshes, staging envs, testing account, google approval process, deployment, monitoring and debugging misbehaviours.
Click here to create a free account so you’ll get some useful tips on the mail.
Clap if useful. Follow me for more.
Let me know in the comments if you want more of these.
