Operations (sample payloads)

Main operations

Add values to spreadsheet

Appends values to a spreadsheet. Sample Input

\{
    "spreadsheet_id": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7Q",
    "worksheet_name": "Sheet1",
    "cell_range": "A1:C1",
    "major_dimension": "ROWS",
    "value_input_option": "RAW",
    "values": [
        [
            "John Doe",
            "30",
            "New York"
        ]
    ],
    "insert_data_option": "INSERT_ROWS",
    "include_values_in_response": true,
    "response_value_render_option": "FORMATTED_VALUE",
    "response_date_time_render_option": "FORMATTED_STRING"
\}

Sample Output

Create column headers for sheet

Add column headers to your worksheet. Sample Input Sample Output

\{
    "spreadsheetId": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7",
    "updatedRange": "Sheet1!A1:E1",
    "updatedRows": 1,
    "updatedColumns": 5,
    "updatedCells": 5
\}

Create empty column or row

Add a column or row to the end of a Google Sheet. Sample Input Sample Output

Create row

Add a row to a Google Sheet. Sample Input Sample Output

Create spreadsheet

Create a new spreadsheet for your Google Sheets. Sample Input Sample Output

Create worksheet

Add a worksheet to a spreadsheet. Sample Input

\{
    "spreadsheet_id": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7Q",
    "title": "New Sales Data",
    "row_count": 100,
    "column_count": 10
\}

Sample Output

{
    "spreadsheetId": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7Q",
    "replies": [
        {
            "addSheet": {
                "properties": {
                    "sheetId": 987654321,
                    "title": "New Sales Data",
                    "index": 1,
                    "sheetType": "GRID",
                    "gridProperties": \{
                        "rowCount": 100,
                        "columnCount": 10
                    \}
                }
            }
        }
    ]
}

Delete column

Remove a column from a worksheet. Sample Input

\{
    "spreadsheet_id": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P",
    "worksheet_id": 0,
    "start_index": "C",
    "how_many": 2
\}

Sample Output

{
    "spreadsheetId": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P",
    "replies": [
        {}
    ]
}

Delete row

Remove row from a worksheet. Sample Input

\{
    "spreadsheet_id": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7Q",
    "worksheet_id": 0,
    "start_index": 5,
    "how_many": 2
\}

Sample Output

{
    "spreadsheetId": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7Q",
    "replies": [
        {}
    ]
}

Delete worksheet

Delete a worksheet from a spreadsheet. Sample Input

\{
    "spreadsheet_id": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7",
    "worksheet_id": 123456789
\}

Sample Output

{
    "spreadsheetId": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7",
    "replies": [
        {}
    ]
}

Get row by index

Find a row by its index. Sample Input

\{
    "spreadsheet_id": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7Q",
    "worksheet_name": "Sheet1",
    "row_number": 5
\}

Sample Output

\{
    "range": "Sheet1!A5:Z5",
    "majorDimension": "ROWS",
    "values": [
        [
            "John",
            "Doe",
            "johndoe@example.com",
            "123-456-7890",
            "Marketing",
            "Manager",
            "2022-01-15",
            "75000"
        ]
    ]
\}

Get rows

Get data from a worksheet of a spreadsheet. Sample Input

{
    "spreadsheet_id": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7Q",
    "worksheet_name": "Sales Data",
    "number_of_rows": 5,
    "filter": \{
        "column_heading": "Total Sales",
        "operator": "Greater than",
        "value": 1000,
        "sort_direction": "descending"
    \},
    "format_response": true
}

Sample Output

{
    "results": [
        \{
            "Date": "2023-05-01",
            "Product": "Widget A",
            "Quantity": 50,
            "Unit Price": 25,
            "Total Sales": 1250
        \},
        \{
            "Date": "2023-05-02",
            "Product": "Widget B",
            "Quantity": 30,
            "Unit Price": 40,
            "Total Sales": 1200
        \},
        \{
            "Date": "2023-05-03",
            "Product": "Widget C",
            "Quantity": 25,
            "Unit Price": 45,
            "Total Sales": 1125
        \},
        \{
            "Date": "2023-05-04",
            "Product": "Widget A",
            "Quantity": 40,
            "Unit Price": 25,
            "Total Sales": 1000
        \},
        \{
            "Date": "2023-05-05",
            "Product": "Widget B",
            "Quantity": 35,
            "Unit Price": 40,
            "Total Sales": 1400
        \}
    ]
}

Get spreadsheet details

Get details about a spreadsheet, including information about its worksheets. Sample Input Sample Output

{
    "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
    "properties": {
        "title": "Sample Spreadsheet",
        "locale": "en_US",
        "autoRecalc": "ON_CHANGE",
        "timeZone": "America/New_York",
        "defaultFormat": {
            "backgroundColor": \{
                "red": 1,
                "green": 1,
                "blue": 1
            \},
            "padding": \{
                "top": 2,
                "right": 3,
                "bottom": 2,
                "left": 3
            \},
            "verticalAlignment": "BOTTOM",
            "wrapStrategy": "OVERFLOW_CELL",
            "textFormat": {
                "foregroundColor": {},
                "fontFamily": "Arial",
                "fontSize": 10,
                "bold": false,
                "italic": false,
                "strikethrough": false,
                "underline": false,
                "foregroundColorStyle": {
                    "rgbColor": {}
                }
            },
            "backgroundColorStyle": {
                "rgbColor": \{
                    "red": 1,
                    "green": 1,
                    "blue": 1
                \}
            }
        },
        "iterativeCalculationSettings": \{
            "maxIterations": 100,
            "convergenceThreshold": 0.001
        \},
        "spreadsheetTheme": {
            "primaryFontFamily": "Arial",
            "themeColors": [
                {
                    "colorType": "TEXT",
                    "color": {
                        "rgbColor": \{
                            "red": 0,
                            "green": 0,
                            "blue": 0
                        \}
                    }
                },
                {
                    "colorType": "BACKGROUND",
                    "color": {
                        "rgbColor": \{
                            "red": 1,
                            "green": 1,
                            "blue": 1
                        \}
                    }
                }
            ]
        }
    },
    "sheets": [
        {
            "properties": {
                "sheetId": 0,
                "title": "Sheet1",
                "index": 0,
                "sheetType": "GRID",
                "gridProperties": \{
                    "rowCount": 1000,
                    "columnCount": 26
                \}
            }
        },
        {
            "properties": {
                "sheetId": 1,
                "title": "Sheet2",
                "index": 1,
                "sheetType": "GRID",
                "gridProperties": \{
                    "rowCount": 1000,
                    "columnCount": 26
                \}
            }
        }
    ],
    "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit"
}

Get total active rows

Returns total active rows in a sheet. Sample Input

\{
    "spreadsheet_id": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7Q",
    "worksheet_name": "Sales Data"
\}

Sample Output

{
    "rows": 1000
}

Get worksheet data

Get data from a worksheet of a spreadsheet. Sample Input

\{
    "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
    "worksheet_name": "Sheet1"
\}

Sample Output

\{
    "range": "Sheet1!A1:Z1000",
    "majorDimension": "ROWS",
    "values": [
        [
            "Name",
            "Age",
            "City"
        ],
        [
            "John Doe",
            "30",
            "New York"
        ],
        [
            "Jane Smith",
            "25",
            "Los Angeles"
        ],
        [
            "Bob Johnson",
            "45",
            "Chicago"
        ],
        [
            "Alice Brown",
            "35",
            "Houston"
        ]
    ]
\}

Get worksheet data using range

Get data from a worksheet of a spreadsheet using a cell range. Sample Input

\{
    "spreadsheet_id": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7Q",
    "worksheet_name": "Sheet1",
    "cell_range": "A1:C5"
\}

Sample Output

Insert empty column

Insert a column into a Google Sheet. Sample Input

\{
    "spreadsheet_id": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7Q",
    "worksheet_id": 0,
    "start_index": "C",
    "how_many": 2,
    "inherit_from_before": true
\}

Sample Output

Insert empty row

Insert a row into a Google Sheet. Sample Input

\{
    "spreadsheet_id": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7Q",
    "worksheet_id": 0,
    "start_index": 5,
    "how_many": 2,
    "inherit_from_before": true
\}

Sample Output

{
    "spreadsheetId": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7Q",
    "replies": [
        {}
    ]
}

List worksheet column headers

List columns of a worksheet. Sample Input

\{
    "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
    "worksheet_name": "Sheet1"
\}

Sample Output

\{
    "range": "Sheet1!A1:Z1",
    "majorDimension": "ROWS",
    "values": [
        [
            "First Name",
            "Last Name",
            "Email",
            "Phone Number",
            "Date of Birth",
            "Address",
            "City",
            "State",
            "Zip Code",
            "Country"
        ]
    ]
\}

List worksheets

List worksheets within a spreadsheet. Sample Input

{
    "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"
}

Sample Output

{
    "sheets": [
        {
            "properties": {
                "sheetId": 0,
                "title": "Sheet1",
                "index": 0,
                "sheetType": "GRID",
                "gridProperties": \{
                    "rowCount": 1000,
                    "columnCount": 26
                \}
            }
        },
        {
            "properties": {
                "sheetId": 1234567890,
                "title": "Sales Data",
                "index": 1,
                "sheetType": "GRID",
                "gridProperties": \{
                    "rowCount": 500,
                    "columnCount": 15
                \}
            }
        },
        {
            "properties": {
                "sheetId": 987654321,
                "title": "Expenses",
                "index": 2,
                "sheetType": "GRID",
                "gridProperties": \{
                    "rowCount": 200,
                    "columnCount": 10
                \}
            }
        }
    ]
}

Raw HTTP request (advanced)

Perform a raw HTTP request with some pre-configuration and processing by the connector, such as authentication. Sample Input

{
    "method": "GET",
    "url": {
        "endpoint": "/users"
    },
    "headers": [
        \{
            "key": "Accept",
            "value": "application/json"
        \}
    ],
    "query_parameters": [
        \{
            "key": "page",
            "value": "1"
        \},
        \{
            "key": "limit",
            "value": "10"
        \}
    ],
    "body": {
        "none": null
    },
    "include_raw_body": false,
    "parse_response": "true"
}

Sample Output

Update cell value

Set the value of an individual cell in a Google Sheet. Sample Input

\{
    "spreadsheet_id": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7",
    "worksheet_name": "Sheet1",
    "cell": "B5",
    "value": "Updated Value",
    "value_input_option": "USER_ENTERED"
\}

Sample Output

\{
    "spreadsheetId": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7",
    "updatedRange": "Sheet1!B5",
    "updatedRows": 1,
    "updatedColumns": 1,
    "updatedCells": 1
\}

Update row

Update a row, by its number, within a worksheet. Sample Input

{
    "spreadsheet_id": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7Q",
    "worksheet_name": "Sheet1",
    "row_number": 5,
    "value_input_option": "USER_ENTERED",
    "row_data": [
        \{
            "column_heading": "Name",
            "value": "John Doe"
        \},
        \{
            "column_heading": "Age",
            "value": 30
        \},
        \{
            "column_heading": "Email",
            "value": "john.doe@example.com"
        \},
        \{
            "column_heading": "Active",
            "value": true
        \}
    ]
}

Sample Output

\{
    "spreadsheetId": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7Q",
    "updatedRange": "Sheet1!A5:D5",
    "updatedRows": 1,
    "updatedColumns": 4,
    "updatedCells": 4
\}

Update values in spreadsheet

Update the values of a spreadsheet in a given range. Sample Input

\{
    "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
    "range": "Sheet1!A1:C3",
    "value_input_option": "USER_ENTERED",
    "major_dimension": "ROWS",
    "values": [
        [
            "Name",
            "Age",
            "City"
        ],
        [
            "John Doe",
            "30",
            "New York"
        ],
        [
            "Jane Smith",
            "25",
            "London"
        ]
    ],
    "include_values_in_response": true,
    "response_value_render_option": "FORMATTED_VALUE",
    "response_date_time_render_option": "FORMATTED_STRING"
\}

Sample Output

{
    "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
    "updatedRange": "Sheet1!A1:C3",
    "updatedRows": 3,
    "updatedColumns": 3,
    "updatedCells": 9,
    "updatedData": \{
        "range": "Sheet1!A1:C3",
        "majorDimension": "ROWS",
        "values": [
            [
                "Name",
                "Age",
                "City"
            ],
            [
                "John Doe",
                "30",
                "New York"
            ],
            [
                "Jane Smith",
                "25",
                "London"
            ]
        ]
    \}
}

DDL operations

List worksheet column headers (DDL)

Note that DDL operations can only be called directly by Connectors API, or when using CustomJS in the Embedded solution editor for e.g. DDL-dependent data mapping

Sample Input

{}

Sample Output

\{
    "columnHeaders": [
        "First Name",
        "Last Name",
        "Email",
        "Phone Number",
        "Date of Birth",
        "Department",
        "Employee ID"
    ]
\}

List worksheet column headers using ID (DDL)

Sample Input

{}

Sample Output