Google Sheets tips and examples
Explore the following tips and examples to help you build your with Google Sheets modules.
The Google Sheets Add-on is currently not available, and the following procedure should be followed to use the Perform a Function and Watch Changes instant modules.
You can watch the video tutorial that explains the step-by step process, or follow the steps below.
To use the Google Sheets the Perform a Function and Watch Changes instant modules, you must first paste a script and your module's webhook address into your spreadsheet.
Log in to your account, add either the Perform a Function or Watch Changes module to your , and click Create a webhook or Add.
Optional: Enter a name for the webhook in the Webhook name field.
Click Save.
Next you will paste a script into your spreadsheet. Click here to access the script and copy the entire content.
Open the spreadsheet and go to Extensions > Apps Script. You will see a new project containing default content.
Delete the default content and paste the script you copied in Step 4. This script must be updated with the webhook address from your Google Sheets module.
To obtain the webhook address, go back to your scenario, click on the module, and click Copy address to clipboard.
Now return to the Apps Script project in your spreadsheet. At the beginning of the script, you will see the lines WATCH_CHANGE_WEBHOOK_URL = 'https://hook.eu1.make.com/xxx'; and PERFORM_FUNCTION_WEBHOOK_URL = 'https://hook.eu1.make.com/xxx';.
Depending on the module you are using, replace https://hook.eu1.make.com/xxx with the webhook address you copied in Step 7.
It is important to paste the full webhook address into the script to avoid any errors.
Click the Save icon.
In the left sidebar, click the Triggers icon.
Click Add Trigger.
In the Select event type field, select On edit, and click Save.
If prompted, authenticate your account and confirm access.
The Perform a Function or Watch Changes module will now send data through the webhook when the selected event occurs.
In insert the Webhook > Custom webhooks module/trigger into the and configure it (see the webhooks article).
Once you have configured the custom webhook module, be sure to save the .
Copy the webhook's URL.
Run the .
In Google Sheets, choose Insert > Drawing from the main menu bar.
Click the Text box icon:
Design a button and click Save and Close in the top-right corner:
The button will be placed in your worksheet. Click the three vertical dots in the button's top-right corner:
Choose Assign script from the menu.
Enter the name of your script (function). For example, runMakeand click OK:
Choose Extensions > Apps Script from the main menu bar.
Insert the following code:
Press Ctrl+S to save the script file, enter a project name, and click OK.
Switch back to Google Sheets and click your new button.
Grant the required authorization to the script.
In , verify that the has successfully executed.
This will only trigger a to run when Scheduling is enabled. The linked webhook URL will not return any data but instead will trigger the to run and allow the connected modules to return data.
To delete multiple rows based on filter criteria, use the Search Rows module linked to the Delete a Row module as in the following example:
Add the Search Rows module and the Delete a Row module to the .
Let's assume that you have a table where you need to delete all rows where column A equals Y.
Open the Search Rows module settings and set the fields as follows:
- Filter: Set A Equal to Y
- Sort order: Descending
- Order by: Row number
Add the Delete a Row module to the and connect it to the Search Row module.
Map the Row number item from the Search Rows module to the Delete a Row module's Row number field.
Get empty cells from a Google Sheet
Run the to delete values that match the filter criteria from the sheet.
Use the Search Rows (Advanced) module and use this formula to get empty columns.
Here "E" is the column and "is null" is the condition. You can create a more advanced query using Google Query Lang.
The following API call returns specified spreadsheet details.
URL:
/spreadsheets/{{spreadsheetID}}
Method:
GET
The result can be found in the module's Output under Bundle > Body:
When getting an image from Google Sheets, first make sure you enter the image as a formula. For example:=IMAGE("https://i.ytimg.com/vi/MPV2METPeJU/maxresdefault.jpg") making use of the =IMAGE(...)
After you have done so, open the Google Sheets module (e.g. Watch Rows, Search Rows, Get a Cell) and select Show advanced settings. Then select the Formula option in the Value render option field.
The output will be as shown below:
Then you can extra the URL using the replace function.
The output will just be the URL.
To be able to post an image, make sure to enter the =IMAGE(...) formula that will be used in the cell and then enter the Image URL address.
If you store a Date value in a spreadsheet without any formatting, it will appear as text in ISO 8601 format in the spreadsheet. However, Google Sheets formulas or functions that work with dates do not understand this text. For example, the formula =A1+10 will display the following error:
To help the GS to understand the date, format it with the formatDate(.) function. The correct format passed to the function as the second argument depends on the spreadsheet's locale settings. Choose File > Spreadsheet settings from the main menu to verify/set the locale.
Once you have verified/set the proper locale, determine the corresponding date and time format by choosing Format > Number from the main menu. The format is displayed next to the Date time menu item.
The following example shows the use of M/D/YYYY HH:mm:ss format for the United States locale:
If the error 429: RESOURCE_EXHAUSTED occurs, you have exceeded the API rate limit.
The Google Sheets API has a limit of 500 requests per 100 seconds per project, and 100 requests per 100 seconds per user. Limits for reads and writes are tracked separately. There is no daily usage limit.
See more details at developers.google.com/sheets/api/limits.
In the following , a function is used to convert a currency value according to current exchange rates.
Create a with the following modules:
In the Google Sheets > Perform a Function module, generate a webhook and paste it into the add-on in Google Sheets.
In the Currency > Convert an Amount between Currencies module, convert the mapped EUR amount to USD.
In the Google Sheets > Perform a Function - Responder module, insert the converted amount into the sheet cell.
Run the .
Enter the MAKE_FUNCTION into the desired cell to load the converted amount.
When the user changes the amount, the MAKE_FUNCTION re-calculates the Total - USD according to the current exchange rate:
