Best Google Sheets Automation Tips with Make (formerly Integromat) | Advanced Queries and Sync to CRM (Part 02)

Note: The scenario screenshots are taken from Integromat.  However, the same principle will apply to Make (formerly Integromat).

‍

This is Part 2 of the Google Sheets series on Automation with Make (formerly Integromat).  This blog tells you how to keep track of what you have updated already in Google Sheets so as not to process each and every row all the time, and also how to use advanced queries for searching rows. We use those a lot in our agency.  

When we want to transfer data into our CRM system, we don't know what has been transferred already, and we don't want to process rows twice.  We just want to do it once.  So we usually add a column in the sheet, named for example, “Transferred to CRM”, to make it really understandable.  

‍

Once the data in the row has been transferred to the CRM, the column gets updated. 

Google Sheets Search Rows modules

In our Make (formerly Integromat) Scenario, we filter at the beginning to search only for data which doesn’t have a value in that particular column.

‍

We can either select a basic operator, “does not exist”, or if the column contains multiple values for statuses, then you can set the condition in the filter to not equal to “Yes”.   Since we want to process all rows in our Google sheet, we also search if email exists.

‍

Set the Maximum number of returned rows to 50 or the highest number of output bundles you want to have.  But for demonstration purposes, here we set it to 1.

‍

Run the scenario once.  Its output is 1 bundle.  That is from 1 row.

‍

Let’s simulate adding the data from the sheet to a CRM system.  Let’s use the Tools module as a placeholder of the CRM.  

‍

Configure the Tools module.  Set the Variable name and the Variable value.  The Variable value may be any value mapped from the Google Sheets Search Rows module.  Rename the module for identification purposes as “Placeholder - Save on CRM”.

‍

Afterwards once the process is finished and everything is successfully transferred, we want to update the row.  Here, we use the Google Sheets Update a Row module to update that specific row.  

‍

Configure the Update a Row module.  Under Row number, select the row number from our first module.

‍

Fill in with “Yes” under Transferred to CRM. 

‍

Running the scenario once, it finds that one row, transfers it to the CRM system, and  updates that row column “Transferred” to CRM with “Yes”. 

‍

One important thing to remember is to set up a filter after the search rows module. The condition is if a row number exists, then continue with the automation.  We would want to continue only when a row is found, otherwise it will run the rest of the scenario and will most likely fail because of missing parameters.

‍

Running the scenario again, you would see it has zero output because column F is not “not  equal” to “Yes” anymore.  It’s equal to “Yes”.  Hence, the condition is false.  Thus, it won't process the row twice.

‍

Of course, you can use a different value other than “Yes” under the “Transferred to CRM” column.  You could use something that reflects the status of a transfer.  That is very helpful. 

I suggest you always use a Transfer Status field or column every time you transfer data from one place to another.  If you're syncing something, for example, chat messages or something else, then you always have this kind of transfer column to check the current status. And then you can always go back and fix those that failed during the transfer. 

You can also add a router that directs to another path with another Update a Row module when there’s an error in the process.  Set a filter on that path with a condition when a field from the CRM does not exist.  

‍

Update that row in the sheet with an error code like “Failed” or something like that. And you can even add more description to it i.e. reason for the failure.  You can go into these runs and determine why it failed.  

‍

The next time you can just run the scenario again searching only for rows where the transfer to CRM status has failed and process them.

Google Sheets Search Rows Advanced module

The Search Rows Advanced module can be quite confusing. It uses the Query language, particularly the Google Charts Query language.  You can find more information if you click on the URL under the Query field, inside the module itself. 

‍

‍

Sample query: select *

The query usually starts with “select”.  The asterisk means all data.  

You can also say “select A, B, C” if you just want to get the data from column A, B, and C only. 

For our demonstration, we use the asterisk because we want to get everything.  This is our query statement:

select * where D = ‘info@techflow.ai’ 

We type “where” for our condition. So if column D is equal to a specific email address, then we want to get back the matched results. 

‍

This basically outputs the same values like we get in our search results module.

With the Search Rows Advanced module we can do a lot more advanced queries for our search.  We can create conditions dynamically like with Integromat functions or based on previous modules.  With the search results module, we are limited to the preset filters. So that is where the Search Rows Advanced module comes in handy. 

Multiple conditions are also possible.

For example: select * where D = ‘info@techflow.ai’ and F = ‘Failed’

Another thing we can do is to sort the search result by a specific column that is either alphabetical or numerical.  You can sort it by letter or by ID.  The syntax is to use “order by” followed by the column letter, after which indicate whether it’s in a descending (desc) or ascending (asc) order.

Query:  select * where D = ‘info@techflow.ai’ and F = ‘Failed’ order by A desc

‍

Now we can get into more complex stuff by using “not”.  

Query:  select * where D = ‘info@techflow.ai’ and not F = ‘Failed’ order by A desc

We get all rows where F is not failed.

‍

This will return rows with various statuses except those having “Failed”.   It is very helpful to use the Query language. 

Other stuff you can do with this module is grouping data. You can even sum data up. But that is  quite advanced already. You can find more about the Query language through the URL. 

One of the main advantages is being able to use mapping in formulating the query.  Here is a quick example. 

‍

The query is dynamic this way.

Last thing that I want to show you is that you can also do a numerical filter by ID.  For example, if column A is greater than 200.  This returns rows with column A values greater than 200.

‍

All right, so this is the second part of the Google Sheet Automation series with Make (formerly Integromat), I hope you liked it. 

In the next article of this series, I will talk about batch updating on Google Sheets, which will save you a ton of operations and also I’ll show you a hack on how you can keep using formulas within your rows on Google sheets and not overwrite them. when you add a new row or you update a row. 

Key Takeaway

Google has made it possible for us to use Google Sheets to keep everything running smoothly in the office. And they've taken this a step further by giving us Integromat which helps automate our business activities. By using Make (formerly Integromat) you can automate work processes with optimal accuracy, so don't waste any time and start automating today. Learn more about what else you can automate in Make (formerly Integromat) in the Make Simplified Accelerator.