Automation Flows In Google Sheets

In programming, we have this notion of a “loop”. In a loop, you ask your program to iterate through a group of things, and for each thing, carry out some set of instructions. Loops aren't restricted to programming - we perform "loop-based" tasks all the time:

Any time you can describe your task in the format “For each ____ do ____” you’re dealing with a loop.

I’ve recently built a few applications from scratch and each time found myself doing tasks that required this flow - iterate through a list of things and carry out the same instructions for each thing.

I first saw it with Wanderwise (a travel planning app), where I’d compiled a list of great places in New York in a Google Sheet. For each item, I needed to call the Google Places API and fetch some information about the place (description, location, ratings, etc.) to then load into my database.

When building Delightful (an app to discover, rate, and review non-alcoholic beverages), I required a similar flow where my co-founder maintains a Google Sheet of non-alcoholic products and I periodically need to clean each item, incorporate additional data, and load into our database.

Google Sheets was the most intuitive place to store the inputs of these loop-based workflows. To actually execute the workflow (i.e. carry out some set of instructions on each row), I would need to be able to read & write into the sheet programatically. Luckily, Google exposes APIs for just that purpose. You can see my automation in action below:

What you see above is my script iterating through each row, calling the Google Places API with the location name to fetch the details I need, and then populating the results back into the sheet. Not only is this great from an automation standpoint, the fact that the output goes right back into the sheet gives me an interface to QA, make any edits, and then move forward with a cleaned version of the data.

This approach has now saved me countless hours across multiple projects. More importantly, it provides an easy and repeatable workflow that gets me off the ground very quickly. When you’re eventually dealing with “big data”, this process might not scale, but luckily most of us aren’t, especially in the early/middle stages.

This framework can be generalized for any use case where the inputs can be stored in a Google Sheet and the instructions you want to carry out for each row can be written in code. Examples that come to mind include:

1. You have a GPT prompt you need to use repeatedly but with different inputs. For example, you might have a list of public companies and for each one you want to ask Chat GPT to populate latest earnings info and commentary.
2. You manage a list of email addresses you need to send an email to.
3. You have a spreadsheet where you manage user feedback. For each new feedback item, you want to receive a Slack alert to notify you you need to take action.

Note also that any of these use cases can be combined - e.g. for each row of your spreadsheet, if you need to call 3 different APIs, you may absolutely do that.

If you found this helpful, please let me know at prashanthselvam@gmail.com! Happy to write a follow up post on how I actually implement such a workflow. I’m also available for freelance/consulting opportunities in a data engineering & fullstack engineering capacity, so if you’d like to work together, please reach out.