Google Sheets tips and examples
Explore the following tips and examples to help you build your scenarios with Google Sheets modules.
In Make insert the Webhook > Custom webhooks module/trigger into the scenario and configure it (see the webhooksWebhooks article).
Once you have configured the custom webhook module, be sure to save the scenario.
Copy the webhook's URL.
Run the scenario.
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:
function runScenario() {
UrlFetchApp.fetch("https://hook.make.com/xxx...xxx");
}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 Make, verify that the scenario has successfully executed.
This will only trigger a scenario to run when Scheduling is enabled. The linked webhook URL will not return any data but instead will trigger the scenario 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 scenario.
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 scenario 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.
Run the scenario 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.
select * where E is null
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 scenario, a function is used to convert a currency value according to current exchange rates.
Create a scenario with the following modules:
In the Google Sheets > Perform a Function module, generate a webhook and paste it into the scenario 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 scenario.
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:
