Google Tables
All possibilities of bot interaction with Google tables.
This feature is available at the maximum tariff
How to prepare a Google table
How to write a json query
How to Write Lines to Specific Columns
How to Record Data into Specific Cells
How to delete records from specific cells
How to write to the first empty cell in a row
How to read data from the table
How to Find Text in a Table (Output the First Appropriate Row)
How to List All Matches in a Table
How to completely delete a row (with offset)
How to completely remove column (with offset)
How to make buttons to select a sheet in the table
How to work through your account
How to Work with Column Letters
How to Set Variables in Column Names
How to delete from the table
How to add a sheet to a table Potential error Google Table Functions (APIs)
In this article let’s look at how bots can interact with Google tables.
To use this functionality, you need:
Google table, open to editing by link
URL function
Query options
How to prepare a Google table
!!!
!!!
!!!
How to write a json query
Go to the settings of the block in which we will record. Select the type of POST-json query, insert the URL of the function we want, insert parameters in the JSON POST-parameters field (Figure 5)
To see what response comes to your request, write in the Answer {custom_answer} field, save and go through the chain. Next, you can save each value of the answer to variables in the Save Value from JSON Response field using the syntax:
parameters_from_query -> your_variable If the complex parameters come, then disassemble them as follows:
"cell_number":{"row":4,"col":2} cell_number|row -> Row; cell_number|col -> Column
Now let’s understand in more detail what the bots do with Google tables.
How to Write Lines to Specific Columns
You can collect a lot of data from the user and write them into the first free row in the table. It is done using the mapping function.
The heading must be filled in (at least one cell in the first row)
URL Functions: https://store.salebot.pro/function/gsheets
Query options {"id": "id table", "mapping":{"a"::"#{variable}", "b": "#{yet}", "c": "#{yet}", "d":"just text"}}}
If you want to write strings not on the first sheet, you should add the list_name parameter to the query:
{"id": "id table", "mapping":{"a":"just text", "b": "#{variable}"}, "list_name": "Sheet name"}
Where id is the id of your Google table. You can get it from the link to your Google table (what is highlighted in bold in the link below). https://docs.google.com/spreadsheets/d/1aUbbUaw2SRnJFAavv06Noa1EzumhyShKDm7ie6lYKc4/edit#gid=0
a, b, c, d are just column names list_name - the name of your sheet (for example, "File 2")
If there is no problem with the query execution, the answer comes {"number_row":8}. You can save this line number and use it for later work.
If you don’t want to risk restricting access to your table to your account, you must pass the creds_path parameter from the url of your data file.
More about this in Work through your account:
{"id": "id table", "mapping":{"a"::"#{variable}", "b": "#{yet}", "c": "#{yet}", "d":just text"creds_": "path"way to your data file for authorization"}}
How to Record Data into Specific Cells
URL Functions: https://store.salebot.pro/function/gsheets
Options {"id": "id table", "write":{"a1"::"#{variable}", "b3": "#{yet}", "c1": "#{yet}", "d20"just text"}}. Recording is done in specific cells that you specify (in our example a1, b3, c1, d20)
Column numbering comes from one.
If you want to write strings not on the first sheet, you should add the list_name parameter to the query:
{"id": "id table", "write":{"a1"::"#{variable}", "b3": "#{yet}", "c1": "#{yet}", "d20"just text"}, "list_name": "File name"}}
If there is no problem with the query, no parameters are returned.
If you don’t want to risk restricting access to your table to your account, you must pass the creds_path parameter from the url of your data file.
More about this in Work through your account:
{"id": "id table", "write":{"a1"::"#{variable}", "b3": "#{yet}", "c1": "#{more}", "d20"just text}, "cred_name"Sheet name", s_"path": Path to your data file with authorization}
!!!
How to delete records from specific cells
URL Functions: https://store.salebot.pro/function/gsheets
{"id": "id of the table", "remove":[a1", "b3", "c2"]}. Removes values from specific cells you specify (in our example a1, b3, c2)
Column numbering starts with number one.
If you want to remove cells from the first sheet, you should add the list_name parameter to the query:
{"id": "id of the table", "remove":[a1", "b3", "c2"], "list_name": "File name"}
If there is no problem with the query, no parameters are returned.
If you don’t want to risk restricting access to your table to your account, you must pass the creds_path parameter from the url of your data file. More about this in Work through your account:
{"id": "table id", "remove":[a1", "b3", "c2"], "list_name": "File name","creds_path": "The path to your data file for authorization"}
How to write to the first empty cell in a row
You can write the data to the specified row, the entry will take place in an empty cell to the right of the last filled cell. It is performed using the append_in_row function.
URL Functions: https://store.salebot.pro/function/gsheets
Query parameters {"id": "table id", "append_in_row":"8", "value": "written value"}
If you want to write rows not on the first sheet, you should add the list_name parameter: {"id": "id of the table", "append_in_row":", "value": "value", "list_name": "Name of the sheet"} Where is the id of your Google table. You can get it from the link to your Google table (what is highlighted in bold in the link below). https://docs.google.com/spreadsheets/d/1aUbbUaw2SRnJFAavv06Noa1EzumhyShKDm7ie6lYKc4/edit#gid=0
append_in_row - string number to write
value is the value that will be written into the cell
If there is no problem with the query execution, the answer comes {"number_col":10,"col_name":"J3"}. You can save these numbers and use them for further work.
If you don’t want to risk restricting access to your table to your account, you must pass the creds_path parameter from the url of your data file. For more information, see Work through your account: {"id": "id table", "mapping":"{"a":"#{variable}", "b": "#{more}", "c": "#{more}, "cred"simply text"},"s_": path to your data with"path}"
How to read data from the table
URL Functions: https://store.salebot.pro/function/gsheets
{"id": "id table", "read":{"a1":"a1", "b3": "b4", "c1": "c10", "d20":"a1"}} Set parameters to transmit ranges. In the example "a1":"a1" returns one cell and "c1":"c10" returns 10 values from the column c. The returned hash value from the cell address and values from it.
Column numbering starts with number one.
If you want to read values not on the first sheet, you should add the list_name parameter to the query:
{"id": "id table", "read"::{"a1":"a3", "b3": "b6"}, "list_name": "File name"}
If there is no problem with the query, the cell name and its contents will be returned:"{"A1":" u043f u0440 u0438 u0432 u0435", "A2":" u043f u0440 u0430"U043U0A"U0A0430." U0440A0A"U0A0430." u0444 u0443 u043d u043a u0446"} If you don’t want to risk restricting access to your table to your account, you must pass the creds_path parameter from the url of your data file. More about this in Work through your account:
{"id": "id table", "read"::{"a1":"a3", "b3": "b6"}, "list_name": "Sheet name","creds_path": "path path path to your data file for authorization"}
Last updated