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 - finds search_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 to vlookup-write-row but the program finds search_key given column and write to the cell on write_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":"[e-mail]",
            "write_values":[
               "2",
               "[e-mail]",
               "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":"[e-mail]",
            "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,
           }
       }
   }