Google Sheets tips and examples

explore the following tips and examples to help you build your {{scenario plural lowercase}} with google sheets modules set up a google sheets webhook the {{product name}} 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 {{product name}} account, add either the perform a function or watch changes module to your {{scenario singular lowercase}} , 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 add a custom button in a sheet to trigger a {{scenario singular lowercase}} in {{product name}} insert the webhook > custom webhooks module/trigger into the {{scenario singular lowercase}} and configure it (see the webhooks docid 1yhunj8jvzyxip9cf3ps1 article) once you have configured the custom webhook module, be sure to save the {{scenario singular lowercase}} copy the webhook's url run the {{scenario singular lowercase}} 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 design a button 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, runmake and 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 {{product name}} , verify that the {{scenario singular lowercase}} has successfully executed this will only trigger a {{scenario singular lowercase}} to run when scheduling is enabled the linked webhook url will not return any data but instead will trigger the {{scenario singular lowercase}} to run and allow the connected modules to return data delete multiple rows 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 singular lowercase}} 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 singular lowercase}} 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 google sheets map delete row run the {{scenario singular lowercase}} to delete values that match the filter criteria from the sheet get empty cells from a google 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 make an api call 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 make an api call bundle output post and get images from google sheets 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( ) image formula 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 store dates in a spreadsheet 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 store dates in a spreadsheet date 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 google sheets date format example usage limits 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 use a function in the following {{scenario singular lowercase}} , a function is used to convert a currency value according to current exchange rates create a {{scenario singular lowercase}} with the following modules use a function example in the google sheets > perform a function module, generate a webhook and paste it into the {{scenario singular lowercase}} 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 insert converted value run the {{scenario singular lowercase}} 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