Take a moment to think about life without Google Sheets. Scary isn’t it?
Sheets is an incredible piece of software and has pretty much revolutionized collaborative data storage.
A host of interesting tools have been built atop Google Sheets but that is a topic for another article. This one is about the revamped Sheets app on Integromat and to tell you about some really cool things it can do.
Here are the newly added Triggers:
1. Watch Changes
Now changes can trigger a scenario, instantly! Yes, I know you have been waiting for this forever. Now every change you make in a spreadsheet can be sent to an Integromat scenario via webhooks.
Whether you modify an existing cell or add a new one, every change can trigger a scenario. This is made possible via the Integromat add-on for Google Sheets. To learn more about it, go here.
IMPORTANT: You need to keep in mind that the Watch Changes module only works for changes made to a sheet manually and not via a script or the API.
2. Perform a Function
The INTEGROMAT function can trigger a scenario and fetch the output, also instantly!
This is honestly more interesting than it sounds. Let me explain with the help of a real-world example.
Let’s say you track your expenses on a sheet and wish to send a Slack message for every new expense added and fetch the message delivery date and time back to the sheet; nifty right? You can even use a Sheet function such as SUM inside the INTEGROMAT function.
The Perform a Function module sends data via a webhook to trigger a scenario instantly and the output is returned back to the sheet via the Perform a Function - Responder module.
You can do so much more than the example above; here’s another good use case.
IMPORTANT: Again, keep in mind that to execute the INTEGROMAT function, you need to manually make a change in the sheet and that changes made via the API are unable to execute the function.
And once again, Integromat’s awesome Sheets add-on makes this possible and you only need to add it to your account once to start using it in all your Google Sheets.
Here are the newly added Actions:
1. Clear a Cell
You can clear a specific cell by entering its ID such as A11.
2. Clear a Row
You need to enter a Row number to clear its contents. Please note that this module is different from the Delete a row module.
3. Create a Spreadsheet
This is a much-requested action and is really useful too. You can not only create a new spreadsheet but also create multiple sheets within it and specify the names and index of each.
This module also allows you to choose the timezone and the number format for the spreadsheet. You can learn more about it here.
Here are the newly added search modules:
1. Get Range Values
Often you need to fetch values of a certain range of cells rather than all the cells in one or more columns. Well, now you can simply specify the range such as A1:D5 and fetch the values as separate bundles of data for each row.
So bundle 1 will contain the values of the cells A1, B1, C1 and D1, and there will be 5 such bundles corresponding to the 5 rows.
This module also lets you specify the following:
- Whether a table contains headers or not
- Whether the value should be formatted, unformatted or be returned as a formula
- Whether the date should be a serial number or a formatted string
Here’s a detailed explanation of the above mentioned options.
2. Search Rows
Earlier you had to use the Select rows module and specify a filter written in the Google Spreadsheet API syntax to search for a row.
The process was cumbersome and complicated. In the new Sheets app, it has been replaced by the Search rows module which is super easy to use.
It even works with sheets that do not contain headers or column names and the filtering is just like Integromat’s inbuilt filtering where you can specify AND or OR rules.
Additionally, like the Get Range Values module, the Search Rows module also allows you to specify how you would like to render the values and the dates. You can learn more about this module here.
If you’re familiar with the Google Query Language, then this module is for you. While you can perform complex searches using this module, do keep in mind that the output of this module does not contain the Row IDs. Read more about it here.