Google Sheet Webhook
Google Sheet Adaptor
An in-house Google Sheet adaptor service that allows users to give Google Sheet instructions with API requests. It enables users to interact with their Google Sheets data programmatically. With this tool, users can write API requests that will perform various operations on their Google Sheets data, such as reading, writing, updating, and deleting data.
The API command is composed in this format, see instruction format below in Features.
{
“instructions”: [
instruction-1,
instruction-2,
…
]
}
Constraints
1 integration per ME Account
300 API calls per minute for every users combined
no
write_row
yet
Parameters
:param sheet_id:
- str id of the sheet:param worksheet_id:
- str id of the worksheet:param key:
- str must not be duplicated, used to identify the response, user will get this key back in the response:param operation:
- object one of the 7 operations:param cell:
- str cell location, ex. "A1":param value:
- str value to be written:param values:
- list[str] values to be written:param row:
- str row number
Note: all data is in string format to allow further upgrades, serializers will check if the data is valid ex. For now only sheet_id=”0”
is supported not sheet_id=”sheet-name”
Features
read-cells
- returns cells’ values given its location in “A1” format
Note: read-cells
and read-cell
are different instructions
{
"sheet_id":"1Y39YjKqK0eFGueNhYIR3_n6nDL7k4oT4u-A64URgKco",
"worksheet_id":"0",
"key":"batch-read-1",
"operation":"read-cells",
"data":{
"cells":[
"A1",
"A2",
"A3"
]
}
},
write-cells
- writes/updates a cell’s value given its location in “A1” format
Note: write-cells
and write-cell
are different instructions
{
"sheet_id":"1Y39YjKqK0eFGueNhYIR3_n6nDL7k4oT4u-A64URgKco",
"worksheet_id":"0",
"key":"batch-write-1",
"operation":"write-cells",
"data":{
"cells":[
{
"cell":"A5",
"value":"A5"
},
{
"cell":"A6",
"value":"A6"
}
]
}
}
read-cell
- returns a cell’s value given its location “A1” format
{
"sheet_id": str,
"worksheet_id": str,
"key": "read-cell-1", # must not be duplicated, used to identify the response
"operation": "read-cell",
"data": {
cell: "A1"
}
}
write-cell
- writes/updates a cell’s value given its location in “A1” format
{
"sheet_id": str,
"worksheet_id": str,
"key": "write-cell-1",
"operation": "write-cell",
"data": {
"cell": "A2",
"value": "some value",
}
}
read-row
- returns a row given its index in “1” format
{
"sheet_id" str,
"worksheet_id" str,
"key": "read-row-1",
"operation": "read-row",
"data": {
"row": "1"
}
}
vlookup-write-row
- findssearch_key
given a column then writes to that row
Note: only write to the first one found for now to prevent excessive rate limit usage
A
B
C
1
Cat
2
Mouse
Example:
search_key
= Mouse
column = “A”
Write to row 2 using the following payload:
{
"sheet_id": str,
"worksheet_id": str,
"key": "lookup-and-write-1",
"operation": "vlookup-write-row",
"data": {
"column": "A",
"search_key": "Mouse",
"write_values": ["Mouse", "some value", "some value"]
}
}
vlookup-write-cell
- similar tovlookup-write-row
but the program findssearch_key
given column and write to the cell onwrite_column
A
B
C
1
Cat
2
Mouse
ex. search_key = Mouse
Column = “A”
Write_column = “B”
Write to B2 using the following payload:
{
"sheet_id": str,
"worksheet_id": str,
"key": "lookup-and-write-2",
"operation": "vlookup-write-cell",
"data": {
"column": "A",
"search_key": "Mouse",
"write_column": "B",
"write_value": "Value"
}
}
append-row
- finds the last row with no data then write a row to it
{
"sheet_id": str,
"worksheet_id": str,
"key": "append-row-1",
"operation": "append-row",
"data": {
"values": ["some value", "some value"],
}
}
Sample Usage
Change the sheet_id
, worksheet_id
, to the IDs of the spreadsheet to be integrated to.
Sample Request Body:
{
"instructions":[
{
"sheet_id":"1Y39YjKqK0eFGueNhYIR3_n6nDL7k4oT4u-A64URgKco",
"worksheet_id":"0",
"key":"read-cell-1",
"operation":"read-cell",
"data":{
"cell":"A2"
}
},
{
"sheet_id":"1Y39YjKqK0eFGueNhYIR3_n6nDL7k4oT4u-A64URgKco",
"worksheet_id":"0",
"key":"write-cell-1",
"operation":"write-cell",
"data":{
"cell":"B5",
"value":"some value"
}
},
{
"sheet_id":"1Y39YjKqK0eFGueNhYIR3_n6nDL7k4oT4u-A64URgKco",
"worksheet_id":"0",
"key":"read-row-1",
"operation":"read-row",
"data":{
"row":"1"
}
},
{
"sheet_id":"1Y39YjKqK0eFGueNhYIR3_n6nDL7k4oT4u-A64URgKco",
"worksheet_id":"0",
"key":"lookup-and-write-1",
"operation":"vlookup-write-row",
"data":{
"column":"B",
"search_key":"[email protected]",
"write_values":[
"2",
"[email protected]",
"some value",
"val",
"some val"
]
}
},
{
"sheet_id":"1Y39YjKqK0eFGueNhYIR3_n6nDL7k4oT4u-A64URgKco",
"worksheet_id":"0",
"key":"lookup-and-write-2",
"operation":"vlookup-write-cell",
"data":{
"column":"B",
"search_key":"[email protected]",
"write_column":"C",
"write_value":"pumidol"
}
},
{
"sheet_id":"1Y39YjKqK0eFGueNhYIR3_n6nDL7k4oT4u-A64URgKco",
"worksheet_id":"0",
"key":"append-row-1",
"operation":"append-row",
"data":{
"values":[
"some value",
"some value"
]
}
},
{
"sheet_id":"1Y39YjKqK0eFGueNhYIR3_n6nDL7k4oT4u-A64URgKco",
"worksheet_id":"0",
"key":"batch-read-1",
"operation":"read-cells",
"data":{
"cells":[
"A1",
"A2",
"A3"
]
}
},
{
"sheet_id":"1Y39YjKqK0eFGueNhYIR3_n6nDL7k4oT4u-A64URgKco",
"worksheet_id":"0",
"key":"batch-write-1",
"operation":"write-cells",
"data":{
"cells":[
{
"cell":"A5",
"value":"A5"
},
{
"cell":"A6",
"value":"A6"
}
]
}
}
]
}
All Success Response Body:
{
"code": 200,
"message": "Success.",
"data": {
"read-cell-1": {
"success": true,
"data": "2"
},
"write-cell-1": {
"success": true
},
"read-row-1": {
"success": true,
"data": [
"ID",
"email",
"first_name",
"last_name"
]
},
"lookup-and-write-1": {
"success": true
},
"lookup-and-write-2": {
"success": true
},
"append-row-1": {
"success": true
},
"batch-read-1": {
"success": true,
"data": [
"ID",
"2",
"some value"
]
},
"batch-write-1": {
"success": true
}
}
}
Partial Success Response:
{
"code": "some_string_code",
"message": "some message"
data: {
"read-cell-1": {
"success": false,
},
"write-cell-1": {
"success": true,
},
"read-row-1": {
"success": false,
},
"lookup-and-write-1": {
"success": true,
},
"lookup-and-write-2": {
"success": false
},
"append-row-1": {
"success": true,
}
}
}
Last updated
Was this helpful?