Google Sheets modules

after connecting to the google sheets app, you can use the following modules to build your {{scenario plural lowercase}} sheets perform a function {{product name}} allows you to use the custom function make function in google sheets similarly to built in functions like average, sum, etc it allows you to perform the function in make and return the result back to the sheet the function make function accepts as many parameters as you need you must have a sheets add on see the example of the module usage in the google sheets tips and examples docid\ jksdupktkenfm n ndfpz article perform a function responder returns processed data as a result of the make function or integromat function this module is to be used together with the perform a function module field description response type select whether you insert text or a number into the sheet value map the value from the previous module you want to insert into the sheet add a sheet adds a new sheet field description connection google sheets docid\ nefhj6m7mksbmjx w9ip search method select an option to choose the spreadsheet in which you want to create a sheet search by path select from all enter manually spreadsheet id select, search for, or enter the id of the spreadsheet that contains the sheet you want to add a sheet to you can extract the spreadsheet id from the spreadsheet url for example, the url is the following https //docs google com/spreadsheets/d/abc1234567/edit#gid=0 where abc1234567 is the spreadsheet id properties title enter the name of the new sheet index enter the sheet position the default is 0 (places the sheet in the first place) create a spreadsheet creates a new spreadsheet field description connection google sheets docid\ nefhj6m7mksbmjx w9ip title enter the name of a new spreadsheet locale the locale of the spreadsheet in one of the following formats an iso 639 1 https //en wikipedia org/wiki/list of iso 639 1 codes language code such as en , an iso 639 2 https //en wikipedia org/wiki/iso 639 2 language code such as haw , if no 639 1 code exists, a combination of the iso language code and country code, such as en us recalculation interval the amount of time to wait before volatile functions are recalculated on change volatile functions are updated upon every change on change and every minute volatile functions are updated upon every change and every minute on change and hourly volatile functions are updated upon every change and hourly time zone select the time zone of the spreadsheet number format select the default format of all cells in the spreadsheet text text formatting, e g 1000 12 number number formatting, e g, 1,000 12 percent percent formatting, e g 10 12% currency currency formatting, e g $1,000 12 date date formatting, e g 9/26/2008 time time formatting, e g 3 59 00 pm date time date+time formatting, e g 9/26/08 15 59 00 scientific scientific number formatting, e g 1 01e+03 sheets add sheets to the new spreadsheet create a spreadsheet from a template creates a new spreadsheet from a template sheet field description connection google sheets docid\ nefhj6m7mksbmjx w9ip search method select or map the template spreadsheet id from which you want to create the spreadsheet search by path select from all enter manually drive select or map the drive where you want to create the spreadsheet template spreadsheet id select the template from which you want to create the spreadsheet if the spreadsheet contains tags like {{name}} , they are retrieved below your file must contain at least one tag for this module to work title enter a name for the spreadsheet new drive location select or map the drive to store the new spreadsheet new document's location select or map the folder, where the new spreadsheet should be placed copy a sheet copies a sheet to another spreadsheet field description connection google sheets docid\ nefhj6m7mksbmjx w9ip search method select or map the option to choose the spreadsheet that you want to copy search by path select from all enter manually drive select or map the drive location where the spreadsheet that you want to copy is located spreadsheet id select or map the spreadsheet id you want to copy you can extract the spreadsheet id from the spreadsheet url for example, the url is the following https //docs google com/spreadsheets/d/abc1234567/edit#gid=0 where abc1234567 is the spreadsheet id destination drive location select or map the drive location where you want to store the copied spreadsheet destination spreadsheet id select or map the copied spreadsheet id add a conditional format rule creates a new conditional format rule at the given index all subsequent rules' indexes are incremented field description connection google sheets docid\ nefhj6m7mksbmjx w9ip search method enter the spreadsheet id to which you want to create the conditional format rule search by path select from all enter manually spreadsheet id enter the spreadsheet id to which you want to create the conditional format rule you can extract the spreadsheet id from the spreadsheet url for example, the url is the following https //docs google com/spreadsheets/d/abc1234567/edit#gid=0 where abc1234567 is the spreadsheet id sheet id enter the sheet id to which you want to create the conditional format rule range select or map the copied spreadsheet id enter the range of rows and columns to which you want to apply the conditional rule format for example, a1\ d25 index the zero based index where the rule should be inserted format rule select or map the rule for the conditional format rule condition select or map the condition and enter the value for the format rule for more information, see the boolean and gradient conditions cell format select or map the cell background color text format set the text format such as foreground color, bold, italic or strikethrough rename a sheet renames a specific sheet field description connection google sheets docid\ nefhj6m7mksbmjx w9ip search method select an option to choose the spreadsheet in which you want to create a sheet search by path select from all enter manually spreadsheet id select or map the spreadsheet id where a sheet you want to rename is located you can extract the spreadsheet id from the spreadsheet url for example, the url is the following https //docs google com/spreadsheets/d/abc1234567/edit#gid=0 where abc1234567 is the spreadsheet id sheet id enter the sheet id you want to rename new sheet name enter a new name of a sheet get range values returns a sheet's content defined by range values field description connection google sheets docid\ nefhj6m7mksbmjx w9ip search method select an option to choose the spreadsheet whose range value you want to get search by path select from all enter manually spreadsheet id select or map the spreadsheet id for the spreadsheet where you want to retrieve range values you can extract the spreadsheet id from the spreadsheet url for example, the url is the following https //docs google com/spreadsheets/d/abc1234567/edit#gid=0 where abc1234567 is the spreadsheet id sheet name select the sheet you want to get the range content from range enter the range you want to get, e g a1\ d25 table contains headers row with headers enter the range of the table headers, e g a1\ f1 if you leave the field empty, make will suppose that the header is in the first row of the specified range value render option formatted value the values in the reply will be calculated and formatted according to the cell's formatting formatting is based on the spreadsheet's locale, not the requesting user's locale for example, if a1 is 1 23 and a2 is =a1 and formatted as currency, then a2 will return "$1 23" unformatted value the values will be calculated, but not formatted in the reply for example, if a1 is 1 23 and a2 is =a1 and formatted as currency, then a2 will return the number "1 23" formula the values will not be calculated the reply will include the formulas for example, if a1 is 1 23 and a2 is =a1 and formatted as currency, then a2 will return "=a1" date and render option serial number instructs date, time, datetime, and duration fields to be output as doubles in "serial number" format, as popularized by lotus 1 2 3 the whole number portion of the value (to the left of the decimal) counts the days since december 30th 1899 the fractional portion (to the right of the decimal) counts the time as a fraction of the day for example, january 1st 1900 at noon would be 2 5 2 because it's 2 days after december 30th 1899, and 5 because noon is half a day february 1st 1900 at 3 pm would be 33 625 this correctly treats the year 1900 as not a leap year formatted string instructs date, time, datetime, and duration fields to be outputted as strings in their given number format (which is dependent on the spreadsheet's locale) list sheets gets a list of all sheets in a spreadsheet field description connection google sheets docid\ nefhj6m7mksbmjx w9ip search method select an option to choose the spreadsheet whose range value you want to get search by path select from all enter manually spreadsheet id select or map the google spreadsheet id you want to retrieve sheets from you can extract the spreadsheet id from the spreadsheet url for example, the url is the following https //docs google com/spreadsheets/d/abc1234567/edit#gid=0 where abc1234567 is the spreadsheet id delete a sheet deletes a specific sheet field description connection google sheets docid\ nefhj6m7mksbmjx w9ip search method select an option to choose the spreadsheet whose range value you want to delete search by path select from all enter manually spreadsheet id select or map the spreadsheet id from which you want to delete the row sheet id select or map the sheet id you want to delete clear values from a range clears a specified range of values from a spreadsheet field description connection google sheets docid\ nefhj6m7mksbmjx w9ip search method select an option to choose the spreadsheet and sheet name whose value you want to clear search by path select from all enter manually spreadsheet id enter the spreadsheet id from which you want to clear the values you can extract the spreadsheet id from the spreadsheet url for example, the url is the following https //docs google com/spreadsheets/d/abc1234567/edit#gid=0 where abc1234567 is the spreadsheet id sheet enter a sheet name from which you want to clear the values range enter the range you want to clear for example, a1\ d25 delete a conditional format rule deletes a conditional format rule at the given index all subsequent rules' indexes are decremented field description connection google sheets docid\ nefhj6m7mksbmjx w9ip search method select an option to choose the spreadsheet and sheet name whose value you want to clear search by path select from all enter manually spreadsheet id enter the spreadsheet id from which you want to clear the values you can extract the spreadsheet id from the spreadsheet url for example, the url is the following https //docs google com/spreadsheets/d/abc1234567/edit#gid=0 where abc1234567 is the spreadsheet id sheet id enter the sheet id whose conditional format rule you want to delete index the zero based index of the rule to be deleted rows watch new rows triggers when a new row is added if a sheet contains a blank row, make doesn't process all subsequent rows field description connection google sheets docid\ nefhj6m7mksbmjx w9ip search method select a method to choose the spreadsheet whose rows you want to watch search by path select from all enter manually drive select google drive, where you have the spreadsheet whose rows you want to watch spreadsheet id select the spreadsheet id whose rows you want to watch you can extract the spreadsheet id from the spreadsheet url for example, the url is the following https //docs google com/spreadsheets/d/abc1234567/edit#gid=0 where abc1234567 is the spreadsheet id sheet name enter a sheet name in which you want search rows table contains headers select whether the spreadsheet contains the header row if the yes option is selected, the module doesn't retrieve the header row as output data, and variables in the output are then called by the headers if the no option is selected, the module retrieves the first table row, and the output variables are called simply a, b, c, d, etc row with headers enter the range of the header row, e g , a1\ f1 value render option formatted value the values in the reply will be calculated and formatted according to the cell's formatting formatting is based on the spreadsheet's locale, not the requesting user's locale for example, if a1 is 1 23 and a2 is =a1 and formatted as currency, then a2 will return "$1 23" unformatted value the values will be calculated but not formatted in the reply for example, if a1 is 1 23 and a2 is =a1 and formatted as currency, then a2 will return the number "1 23" formula the values will not be calculated the reply will include the formulas for example, if a1 is 1 23 and a2 is =a1 and formatted as currency, then a2 will return "=a1" date and time render option serial number instructs date, time, datetime, and duration fields to be outputted as doubles in "serial number" format, as popularized by lotus 1 2 3 the whole number portion of the value (to the left of the decimal) counts the days since december 30th, 1899 the fractional portion (to the right of the decimal) counts the time as a fraction of the day for example, january 1st, 1900 at noon would be 2 5 2 because it's 2 days after december 30th, 1899, and 5 because noon is half a day february 1st, 1900 at 3 pm would be 33 625 this correctly treats the year 1900 as not a leap year formatted string instructs date, time, datetime, and duration fields to be outputted as strings in their given number format (which depends on the spreadsheet's locale) limit set the maximum number of results that make will work with during one execution cycle add a row appends a new row to the bottom of the table field description connection google sheets docid\ nefhj6m7mksbmjx w9ip search method select an option to choose the spreadsheet in which you want to add a row search by path select from all enter manually drive select google drive to choose the spreadsheet in which you want to add a row spreadsheet id enter the spreadsheet id in which you want to add a row you can extract the spreadsheet id from the spreadsheet url for example, the url is the following https //docs google com/spreadsheets/d/abc1234567/edit#gid=0 where abc1234567 is the spreadsheet id sheet name enter a sheet name in which you want to add a row column range select the column range that you want to work with unformatted select or map whether the rows should be formatted or not based upon the spreadsheet's existing formatting value enter or map the desired cells of the row you want to add value input option user entered the values will be parsed as if the user typed them into the ui numbers will remain numbers, but strings may be converted to numbers, dates, etc , following the same rules that are applied when entering text into a cell via the google sheets ui raw the values the user has entered will not be parsed and will be stored as is insert data option insert rows rows are inserted for the new data overwrite the new data overwrites the existing data in the areas where it is written (note adding data to the end of the sheet will still insert new rows or columns so the data can be written ) update a row updates a row field description connection google sheets docid\ nefhj6m7mksbmjx w9ip search method select an option to choose the spreadsheet in which you want to update a row search by path select from all enter manually drive select google drive to choose the spreadsheet whose rows you want to update spreadsheet id enter the spreadsheet id whose rows you want to update you can extract the spreadsheet id from the spreadsheet url for example, the url is the following https //docs google com/spreadsheets/d/abc1234567/edit#gid=0 where abc1234567 is the spreadsheet id sheet name select the sheet you want to update a row in row number enter the number of the row you want to update values enter or map the values in the desired cells of the row you want to change (update) value input option user entered the values will be parsed as if the user typed them into the ui numbers will remain numbers, but strings may be converted to numbers, dates, etc , following the same rules that are applied when entering text into a cell via the google sheets ui raw the values the user has entered will not be parsed and will be stored as is bulk add rows (advanced) appends multiple rows to the bottom of the table field description connection google sheets docid\ nefhj6m7mksbmjx w9ip spreadsheet id enter the spreadsheet id in which you want to add rows you can extract the spreadsheet id from the spreadsheet url for example, the url is the following https //docs google com/spreadsheets/d/abc1234567/edit#gid=0 where abc1234567 is the spreadsheet id sheet name enter a sheet name in which you want to add rows column range select the column range that you want to work with unformatted select or map whether the rows should be formatted or not based upon the spreadsheet's existing formatting value enter or map the desired cells of the rows you want to add value input option user entered the values will be parsed as if the user typed them into the ui numbers will remain numbers, but strings may be converted to numbers, dates, etc , following the same rules that are applied when entering text into a cell via the google sheets ui raw the values the user has entered will not be parsed and will be stored as is insert data option insert rows rows are inserted for the new data overwrite the new data overwrites the existing data in the areas where it is written (note adding data to the end of the sheet will still insert new rows or columns so the data can be written ) bulk update rows (advanced) updates mulitple rows field description connection google sheets docid\ nefhj6m7mksbmjx w9ip spreadsheet id enter the spreadsheet id whose rows you want to update you can extract the spreadsheet id from the spreadsheet url for example, the url is the following https //docs google com/spreadsheets/d/abc1234567/edit#gid=0 where abc1234567 is the spreadsheet id sheet name select the sheet you want to update rows in column range select the column range that you want to work with unformatted select or map whether the rows should be formatted or not based upon the spreadsheet's existing formatting values enter or map the values in the desired cells of the rows you want to change (update) value input option user entered the values will be parsed as if the user typed them into the ui numbers will remain numbers, but strings may be converted to numbers, dates, etc , following the same rules that are applied when entering text into a cell via the google sheets ui raw the values the user has entered will not be parsed and will be stored as is search rows returns results matching the given criteria field description connection google sheets docid\ nefhj6m7mksbmjx w9ip search method select an option to choose the spreadsheet in which you want to search rows search by path select from all enter manually spreadsheet id enter the spreadsheet id in which you want search rows you can extract the spreadsheet id from the spreadsheet url for example, the url is the following https //docs google com/spreadsheets/d/abc1234567/edit#gid=0 where abc1234567 is the spreadsheet id sheet name enter a sheet name in which you want to search rows table contains headers if the option is selected, the module also retrieves the first table row, and variables in the output are then called simply a, b, c, d, etc no option is select filter set the filter for the row to be searched by set filter values you can also use logical operators, and/or in order to specify your selection sort order map or select the direction that rows should be sorted by order by select or map the option to arrange the search results field type select or map the field type to search the rows that match the specified type date number string value reoption formatted value the values in the reply will be calculated and formatted according to the cell's formatting formatting is based on the spreadsheet's locale, not the requesting user's locale for example, if a1 is 1 23 and a2 is =a1 and formatted as currency, then a2 will return "$1 23" unformatted value the values will be calculated, but not formatted in the reply for example, if a1 is 1 23 and a2 is =a1 and formatted as currency, then a2 will return the number "1 23" formula the values will not be calculated the reply will include the formulas for example, if a1 is 1 23 and a2 is =a1 and formatted as currency, then a2 will return "=a1" date and time render option serial number instructs date, time, datetime, and duration fields to be output as doubles in "serial number" format, as popularized by lotus 1 2 3 the whole number portion of the value (to the left of the decimal) counts the days since december 30th 1899 the fractional portion (to the right of the decimal) counts the time as a fraction of the day for example, january 1st 1900 at noon would be 2 5 2 because it's 2 days after december 30th 1899, and 5 because noon is half a day february 1st 1900 at 3 pm would be 33 625 this correctly treats the year 1900 as not a leap year formatted string instructs date, time, datetime, and duration fields to be outputted as strings in their given number format (which is dependent on the spreadsheet's locale) maximum number of returned rows the maximum number of rows make should return during one scenario execution cycle search rows (advanced) returns results matching the given criteria this module doesn't return a row number field description connection google sheets docid\ nefhj6m7mksbmjx w9ip search method select an option to choose the spreadsheet in which you want to search rows search by path select from all enter manually spreadsheet id enter the spreadsheet id in which you want search rows you can extract the spreadsheet id from the spreadsheet url for example, the url is the following https //docs google com/spreadsheets/d/abc1234567/edit#gid=0 where abc1234567 is the spreadsheet id sheet id enter the sheet id whose row you want to search query searches rows using google charts query language the language is similar to sql and it is possible to make complex queries unfortunately, the response doesn't contain ids of returned rows due to google charts, the service is intended for data visualization where the row numbers aren't needed you can find more information about the query language in the documentation https //developers google com/chart/interactive/docs/querylanguage an example select when b contains "example\@email com" maximum number of returned rows the maximum number of rows make should return during one scenario execution cycle clear a row clears values from a specific row field description connection google sheets docid\ nefhj6m7mksbmjx w9ip search method select an option to choose the spreadsheet in which you want to clear a row search by path select from all enter manually spreadsheet id enter the spreadsheet id in which you want to clear a row you can extract the spreadsheet id from the spreadsheet url for example, the url is the following https //docs google com/spreadsheets/d/abc1234567/edit#gid=0 where abc1234567 is the spreadsheet id sheet name enter a sheet name in which you want to clear a row row number enter the number of the row you want to clear, e g 23 delete a row clears values from a specific row field description connection google sheets docid\ nefhj6m7mksbmjx w9ip search method select an option to choose the spreadsheet in which you want to delete search by path select from all enter manually spreadsheet id enter the spreadsheet id in which you want to delete a row you can extract the spreadsheet id from the spreadsheet url for example, the url is the following https //docs google com/spreadsheets/d/abc1234567/edit#gid=0 where abc1234567 is the spreadsheet id sheet id enter a sheet id whose row you want to delete row number enter the number of the row you want to delete, e g 23 cells watch changes triggers when a cell is updated the module only watches for changes made in the google sheets app by the user script executions and api requests do not trigger this module the module does not watch for newly added rows to the sheet see the example of the module usage in the google sheets tips and examples docid\ jksdupktkenfm n ndfpz article update a cell updates a specific cell field description connection google sheets docid\ nefhj6m7mksbmjx w9ip search method select an option to choose the spreadsheet and sheet whose value you want to update search by path select from all enter manually spreadsheet id select or map the spreadsheet id for the spreadsheet where you want to update a cell you can extract the spreadsheet id from the spreadsheet url for example, the url is the following https //docs google com/spreadsheets/d/abc1234567/edit#gid=0 where abc1234567 is the spreadsheet id sheet name enter the name of the sheet where you want to update a cell cell enter the id of the cell you want to update, e g a5 value enter the new value value input option user entered the values will be parsed as if the user typed them into the ui numbers will remain numbers, but strings may be converted to numbers, dates, etc , following the same rules that are applied when entering text into a cell via the google sheets ui raw the values the user has entered will not be parsed and will be stored as is get a cell gets a cell field description connection google sheets docid\ nefhj6m7mksbmjx w9ip search method select an option to choose the spreadsheet and sheet whose value you want to get search by path select from all enter manually spreadsheet id select or map the spreadsheet id from which you want to get a cell you can extract the spreadsheet id from the spreadsheet url for example, the url is the following https //docs google com/spreadsheets/d/abc1234567/edit#gid=0 where abc1234567 is the spreadsheet id sheet id select or map the sheet id that contains the cell you want to retrieve data from cell enter the id of the cell you want to get, e g a5 clear a cell clears a specific cell field description connection google sheets docid\ nefhj6m7mksbmjx w9ip search method select an option to choose the spreadsheet and sheet whose value you want to clear search by path select from all enter manually spreadsheet id select or map the spreadsheet id for the spreadsheet where you want to clear a cell you can extract the spreadsheet id from the spreadsheet url for example, the url is the following https //docs google com/spreadsheets/d/abc1234567/edit#gid=0 where abc1234567 is the spreadsheet id sheet name enter the name of the sheet where you want to clear a cell cell enter the id of the cell you want to clear, e g a5 other make an api call performs an arbitrary authorized api call field description connection google sheets docid\ nefhj6m7mksbmjx w9ip url enter a path relative to https //sheets googleapis com/v4/ for example /spreadsheets/{{spreadsheetid}} for the list of available endpoints, refer to the google sheets api documentation method select the http method you want to use delete to delete an entry patch to make a partial entry update put to update/replace an existing entry post to create a new entry get to retrieve information for an entry headers enter the desired request headers you don't have to add authorization headers; we already did that for you query string enter the request query string body enter the body content for your api call