Mind Expression Docs
English
  • Mind Expression Docs
  • Why Mind Expression
  • Quickstarts
    • Create Scope
    • Add Subject
    • Test
    • Audit
  • Concepts
    • Scopes
    • Subjects
    • Components
    • Sandbox
    • Conversation history
    • Webhooks and escalation
    • Knowledge
    • Live Chat
    • Target Messages
    • Analytics
  • How-to Guides
    • Set up webhooks and escalation
    • Manage global keys
    • Customize messages
    • Educate AI
      • Manage ontologies
      • Recognition Check
      • Semantic Relations
      • Manage entities
    • Steps
    • Components
      • Normal Components
        • Conditions
        • Selection Classes
        • Parameter Bundles
      • Response Components
        • Component Actions
    • Build Q&A Subjects
      • Case 1. Online Payment
      • Case 2. Data Plans
    • Build Info Search Subjects
      • Case 1. Data Usage
      • Case 2. Payment History
    • Build Query Freestyle Subjects
      • Case 1. Data Purchase
      • Case 2. Change Mobile Plan
    • Intelligent Process
      • Introduction to Mindscript
      • Mindscript with Mind Expression
      • Frequently Asked Question
      • Package: Collections and Sequenceable
      • Package: Collections Unordered
      • Package: Collections-Strings
      • Package: Engine-Conversation
      • Package: Kernel-HTTP
      • Package: Kernel-Methods
      • Package: Kernel-Numbers
      • Package: Kernel-Dates
      • Package: Kernel-Objects
      • Package: Kernel Exceptions
      • Package: Cryptography
    • Test and debug
    • Integration
      • Mind Expression API
      • Chat Widget
      • LINE
      • Facebook Messenger
      • Viber
      • Instagram
      • Discord
      • WhatsApp
      • Google Sheet
        • Google Sheet Webhook
      • Google Calendar
        • Google Calendar Webhook
        • Google Calendar: Use Cases
    • Audit AI Activities
    • Live Chat
    • Target Messages
    • Back up, import and restore Scopes
  • Reference
    • Glossary
    • API Docs
Powered by GitBook
On this page
  • Google Sheet Adaptor
  • Constraints
  • Parameters
  • Features
  • Sample Usage

Was this helpful?

  1. How-to Guides
  2. Integration
  3. Google Sheet

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":"pumidol@mind.ai",
            "write_values":[
               "2",
               "pumidol@mind.ai",
               "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":"pumidol@mind.ai",
            "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,
           }
       }
   }
PreviousGoogle SheetNextGoogle Calendar

Last updated 2 years ago

Was this helpful?