In this blog, you will learn hacks to keep functions working within your automations and how to save massive operations in Make (formerly Integromat) when updating multiple rows in Google Sheets.
Below is a Google Sheets formula used in column G.
It checks if E2, which is the Status, has a value. if E2 is equal to “Attended”, then we say, “He was really there”, and if it's not “Attended”, then we say, “Nope, he didn't show up”.
The formula is copied down the other cells in the columns. (If you don't know how to do it, press CTRL and shift on Windows then the arrow key down and then press control D. That will copy the formula completely down until the last row that you selected.)
Using the scenario we built in our first blog of the series, we first search the rows. We search for the email address or the value that we want to search for our search parameter. We get back one row only, and then we say not found or found.
If it's found, we update a row. If it's not found, we add a new row. Since we want to try adding new rows now and keeping formulas alive, we remove or unlink the update a row and remove the filter in the remaining path.
Thus, here we always add a new row. When we run this once, it adds that new row, which looks good. But our formula in column G is gone. The formula doesn't show up. Of course we have to add the attendance as well, but it doesn't show up. So how do we make that now?
There's a simple hack and that is we need to search rows twice. We need to clone the Search Rows module and then our first step is to get the last row that has a value. Search for let's say email exists, and we can sort the order by row number. Sort it in descending order, and set the maximum number of returned rows to 1.
Unlink the newly created Search Rows module from the others to run it once and get a result. We get row number 9. That is actually our last filled row.
Now that we have that one we actually don't need the second search rows module. We can directly go into the next part, and that would be, instead of adding a row, we use the Update a Row module. Using the row number from the returned result in the Search Rows module, we update a row. But this would overwrite our last row. So we need to use the Integromat math function, sum.
We do it like this: sum (Row number;1). This adds one to the row number value, which means it takes the next row, updates that one and inserts the data there.
We don't update the formula field because that is actually what we have inside the Google sheet, and we don't want to override it.
Run it once and you can see that the Google sheet kept the formula, but it still added the new data. So we have the formula, and it outputs whatever we have inside the formula.
The second part of this quick tutorial shows how you can do batch updates for Google Sheet. What we are going to use for this is an API endpoint, which is called Batch Update, and we are going to Make an API call module.
We fill this in the URL:
Map the spreadsheet ID if the previous search rows module. The method is POST. Run it once.
As seen in the output, it adds all the rows in bulk, and it uses only one operation for this.
This is how you can use the batch update.
So you see, Make (formerly Integromat) is one of the many software platforms you can use to grow your business. With Make (formerly Integromat) and other similar platforms, you can build your application from scratch and connect it to your existing business or customize it with Integromat's library of integrations and widgets. Integromat's platform is free for 30 days and it's easy to get started! You'll find dozens of business use cases on Integromat so you can see if it's right for you. Or if you have your own business idea, Integromat offers a range of tools to help you get started and make the most of your time and money.
And if you want to learn more on setting all that up, then join my advanced course, which is called Make Simplified Accelerator, where you can apply here. Also if you want to learn more about Make (formerly Integromat) functions, I have a Make Functions Cheat Sheet where I explain all the Make (formerly Integromat) functions with copy-and-paste-ready functions. Alright, I hope that was super helpful for you. Share it with anyone who you think it might be useful for.
*We're B2B. All our prices are net prices. VAT might apply.
