Best Google Sheets Automation Tips with Integromat (Part 01)

Google Sheets is used everywhere. They are easy to access. You can integrate them with a lot of different systems, and you can automate it. This Google Sheets Automation series will show you how you can work with Google Sheets inside Make (formerly Integromat) and connect to any kind of software that has an API or to an app available in Make (formerly Integromat).

Google Sheets is an easily accessible database. It's free, and you can easily share it to other people, to clients and do all kinds of cool stuff with it. If you have already worked with Excel or Google Sheets before, you know that there are tons of functions you can use, so it's super powerful and it definitely makes sense to learn how to use it with Make (formerly Integromat) for automation. 

There are a ton of different modules that we can use with Google Sheets. We will just go through them quickly. I will explain some of them more in depth in the first part of the series, then I will continue to go into more advanced levels of using Google Sheets. 

Triggers

Let’s start with the different triggers. A trigger basically is saying, if something happens, then we want to start our automation. 

‍

Watch Rows triggers an automation when a new row is added to your Google Sheet.  This is not instant, which means you have to run it on a schedule i.e. every five minutes or once a day or something like that. It will then process all the rows that have been added since you ran it the last time. 

The Watch Changes requires a Sheets add-on, which you can install. It's an Make (formerly Integromat add-on that will instantly trigger the Make (formerly Integromat scenario when a cell is updated.  This doesn't need a schedule. So it will only run when there is a change. That is super effective and excellent for saving operations. 

The last one is Perform a Function.  This is quite advanced.  If an Make (formerly Integromat function is used inside of the Google sheet, then you can fire your scenario. 

Google Sheets actions

Make (formerly Integromat has a bunch of different Google Sheets actions like Add a Row, Update a Row, Clear a Row, Delete a Row, Get a Cell, Update a Cell, and Clear a Cell. We can also add sheets, we can create a spreadsheet, we can perform a function, and we can delete a sheet.

‍

Make an API Call 

Make an API Call is always helpful, because usually the API offers a lot more functionality, such as doing batch updates. But these are the main functions that we have. 

Searches

On Searches, we have Search Rows and Search Rows (Advanced), which allows you to do query based searches. Then, there’s the Get Range Values and List Sheets.

‍

These are the predefined modules that we have for Google Sheets.  For now, we will focus on three of them: Search Rows, Add a Row, and Update a Row.

This means first, we search our Google Sheets, find out if something is existing already inside of our sheet, then we split up our path to say, okay, if it exists already, then I want to update that row, and if it doesn't exist yet, then I want to create a new row for that data.

Below is a sample Google sheet with columns for ID, First Name, Last Name, and Email. 

‍

The first step is to select the spreadsheet in the Search Rows module. 

‍

You, then, select the sheet. 

‍

The column range is the range of active columns in your sheets.  A-Z is the lowest.  Make sure to set it based on the number of columns being used inside your Google sheet.  That makes it easier to map values in the other modules.

‍

For the filter, filter by a unique identifier, which in most cases is an email address, or it could be an ID, for example, user ID in a certain platform.  Search using “Equal to (case insensitive)”.  Use “case insensitive” because people might type the first letter of an email address or ID in uppercase. 

‍

The email address may come from a previous module. It could be, for example, from Google Calendar New Event.  There, you find the attendees.  Iterate through them, get all the emails of each attendee, and then search them in your database. Map the email address into the field.

Set the Maximum number of returned rows to 1 because you just have to get 1 person.  That email address or ID should be unique in your database. 

‍

Next, we add a row to the Google sheet and fill our database with values. 

‍

Mode

Under Mode, you could select “Map spreadsheet and sheet manually”.  Then map the Spreadsheet ID and Sheet to their corresponding fields.

‍

The disadvantage of using the “Map spreadsheet and sheet manually” is it only shows the “Values” as A, B and so on because it can't pull the header rows. The module has no idea yet what sheet it is. 

‍

If you choose “Select spreadsheet and sheet” and fill up the rest of the fields accordingly by selecting the correct values, then the module would recognize the headers and can be seen under “Values”. 

‍

Now knowing the column headers, you could put in the ID that is coming from a previous module.  Again, it could be from Google Calendar, for example, the person ID. 

For demonstration purposes, below is how it looks like with sample data. 

‍

Testing the Scenario

Run the scenario once.  Check the Google sheet and you would see the newly added values. 

‍

Notice that the values are bold. That's because it's taking the format of the previous row.  If you set the newly added row values to a regular font weight, then the succeeding rows would also be in a similar format, not bold. 

When you run the scenario again, the Google Sheets Search Rows module would now have an output.  It has found the previously inputted email address in the database. That is why it's giving back the ID, name and email.  You could use this data further.  You could send them to a CRM system or add more data to it.

‍

Adding Router

Next, add a router separating the paths for an existing and non-existing user data.  If the user exists, then update the row.   If the user does not exist, then create a new row. 

‍

Configure the Google Sheets Update a Row module.  Select the same spreadsheet.  This time, you have to select a row number.  Map the value coming from the previous module, the Search Rows module.  This identifies the row that would get updated.

‍

Update the values. For example, update the ID to 999. 

‍

You could also add another field like Column E, say, Status.  Let’s assume this is a webinar status indicating one’s attendance. 

‍

When you load the Update a Row module again, it should show the new column header.  Update the column value accordingly, for example, “Attended”.

‍

When you run the scenario what would happen is that it will first add a row and then also update a row because there are no filters set up. 

You need to set up a filter on the path from the Router to the Google Sheets Add a Row module.  The filter checks for the non-existence of a row number from the Search Rows module.  I like to use the row number and then just check if the row number exists or not.  However, you could use a value other than the row number.  So if the row number does not exist, then we add a new row.  If the row number exists, then we just update it. 

‍

On the path from the Router to the Update a Row module, set up another filter.  Use the row number in the Condition and select the basic operator, Exists. 

Setting up Filters

‍

Run the scenario.  It would update the ID and the Status in the Google Sheets. 

‍

You could use this kind of approach for all kinds of updates you want to make to your Google sheet database.  It's super easy. And it's highly effective! 

You could also use this for all kinds of other systems that you are using like a CRM system.  Search for the person first, and then if the person exists, you update it. And if the person doesn't exist, you create a new one. 

Key Takeaway

Google has made it possible for us to use Google Sheets for our everyday business activities. And we are able to automate repetitive tasks by utilizing Make (formerly Integromat). With Make (formerly Integromat) you can make your work easier by creating a custom scenario based on your specific needs that do most of the heavy lifting for you. Learn more about what else you can automate in Make (formerly Integromat) in the Make Simplified Accelerator.