Operations (sample payloads)

Main operations
Copy

Add values to spreadsheet
Copy

Appends values to a spreadsheet.

Sample Input

1
{
2
"spreadsheet_id": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7Q",
3
"worksheet_name": "Sheet1",
4
"cell_range": "A1:C1",
5
"major_dimension": "ROWS",
6
"value_input_option": "RAW",
7
"values": [
8
[
9
"John Doe",
10
"30",
11
"New York"
12
]
13
],
14
"insert_data_option": "INSERT_ROWS",
15
"include_values_in_response": true,
16
"response_value_render_option": "FORMATTED_VALUE",
17
"response_date_time_render_option": "FORMATTED_STRING"
18
}

Sample Output

1
{
2
"spreadsheetId": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7Q",
3
"tableRange": "Sheet1!A1:C1",
4
"updates": {
5
"spreadsheetId": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7Q",
6
"updatedRange": "Sheet1!A1:C1",
7
"updatedRows": 1,
8
"updatedColumns": 3,
9
"updatedCells": 3,
10
"updatedData": {
11
"range": "Sheet1!A1:C1",
12
"majorDimension": "ROWS",
13
"values": [
14
[
15
"John Doe",
16
"30",
17
"New York"
18
]
19
]
20
}
21
}
22
}

Create column headers for sheet
Copy

Add column headers to your worksheet.

Sample Input

1
{
2
"spreadsheet_id": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7",
3
"worksheet_name": "Sheet1",
4
"data": [
5
"First Name",
6
"Last Name",
7
"Email",
8
"Phone Number",
9
"Date of Birth"
10
],
11
"value_input_option": "USER_ENTERED"
12
}

Sample Output

1
{
2
"spreadsheetId": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7",
3
"updatedRange": "Sheet1!A1:E1",
4
"updatedRows": 1,
5
"updatedColumns": 5,
6
"updatedCells": 5
7
}

Create empty column or row
Copy

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

Sample Input

1
{
2
"spreadsheet_id": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7Q",
3
"worksheet_id": 0,
4
"dimension": "COLUMNS",
5
"how_many": 3
6
}

Sample Output

1
{
2
"spreadsheetId": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7Q",
3
"replies": [
4
{},
5
{},
6
{}
7
]
8
}

Create row
Copy

Add a row to a Google Sheet.

Sample Input

1
{
2
"spreadsheet_id": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7Q",
3
"worksheet_name": "Sheet1",
4
"data": [
5
{
6
"column_heading": "Name",
7
"value": "John Doe"
8
},
9
{
10
"column_heading": "Age",
11
"value": 30
12
},
13
{
14
"column_heading": "Email",
15
"value": "johndoe@example.com"
16
},
17
{
18
"column_heading": "Active",
19
"value": true
20
}
21
],
22
"value_input_option": "USER_ENTERED"
23
}

Sample Output

1
{
2
"spreadsheetId": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7Q",
3
"updates": {
4
"spreadsheetId": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7Q",
5
"updatedRange": "Sheet1!A2:D2",
6
"updatedRows": 1,
7
"updatedColumns": 4,
8
"updatedCells": 4
9
}
10
}

Create spreadsheet
Copy

Create a new spreadsheet for your Google Sheets.

Sample Input

1
{
2
"title": "Q2 Sales Report",
3
"iterative_calculation_settings": {
4
"maxIterations": 100,
5
"convergenceThreshold": 0.001
6
},
7
"locale": "en",
8
"auto_recalc": "ON_CHANGE",
9
"time_zone": "America/New_York",
10
"sheets": [
11
{
12
"properties": {
13
"title": "Sales Summary",
14
"index": 0,
15
"grid_properties": {
16
"row_count": 100,
17
"column_count": 20
18
}
19
}
20
},
21
{
22
"properties": {
23
"title": "Product Details",
24
"index": 1,
25
"grid_properties": {
26
"row_count": 500,
27
"column_count": 10
28
}
29
}
30
}
31
]
32
}

Sample Output

1
{
2
"spreadsheetId": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7Q",
3
"properties": {
4
"title": "Q2 Sales Report",
5
"locale": "en",
6
"autoRecalc": "ON_CHANGE",
7
"timeZone": "America/New_York",
8
"defaultFormat": {
9
"backgroundColor": {
10
"red": 1,
11
"green": 1,
12
"blue": 1
13
},
14
"padding": {
15
"top": 2,
16
"right": 3,
17
"bottom": 2,
18
"left": 3
19
},
20
"verticalAlignment": "BOTTOM",
21
"wrapStrategy": "OVERFLOW_CELL",
22
"textFormat": {
23
"foregroundColor": {},
24
"fontFamily": "Arial",
25
"fontSize": 10,
26
"bold": false,
27
"italic": false,
28
"strikethrough": false,
29
"underline": false,
30
"foregroundColorStyle": {
31
"rgbColor": {}
32
}
33
},
34
"backgroundColorStyle": {
35
"rgbColor": {
36
"red": 1,
37
"green": 1,
38
"blue": 1
39
}
40
}
41
},
42
"iterativeCalculationSettings": {
43
"maxIterations": 100,
44
"convergenceThreshold": 0.001
45
},
46
"spreadsheetTheme": {
47
"primaryFontFamily": "Arial",
48
"themeColors": [
49
{
50
"colorType": "TEXT",
51
"color": {
52
"rgbColor": {
53
"red": 0,
54
"green": 0,
55
"blue": 0
56
}
57
}
58
},
59
{
60
"colorType": "BACKGROUND",
61
"color": {
62
"rgbColor": {
63
"red": 1,
64
"green": 1,
65
"blue": 1
66
}
67
}
68
}
69
]
70
}
71
},
72
"sheets": [
73
{
74
"properties": {
75
"sheetId": 0,
76
"title": "Sales Summary",
77
"index": 0,
78
"sheetType": "GRID",
79
"gridProperties": {
80
"rowCount": 100,
81
"columnCount": 20
82
}
83
}
84
},
85
{
86
"properties": {
87
"sheetId": 1,
88
"title": "Product Details",
89
"index": 1,
90
"sheetType": "GRID",
91
"gridProperties": {
92
"rowCount": 500,
93
"columnCount": 10
94
}
95
}
96
}
97
],
98
"spreadsheetUrl": "https://docs.google.com/spreadsheets/d/1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7Q/edit"
99
}

Create worksheet
Copy

Add a worksheet to a spreadsheet.

Sample Input

1
{
2
"spreadsheet_id": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7Q",
3
"title": "New Sales Data",
4
"row_count": 100,
5
"column_count": 10
6
}

Sample Output

1
{
2
"spreadsheetId": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7Q",
3
"replies": [
4
{
5
"addSheet": {
6
"properties": {
7
"sheetId": 987654321,
8
"title": "New Sales Data",
9
"index": 1,
10
"sheetType": "GRID",
11
"gridProperties": {
12
"rowCount": 100,
13
"columnCount": 10
14
}
15
}
16
}
17
}
18
]
19
}

Delete column
Copy

Remove a column from a worksheet.

Sample Input

1
{
2
"spreadsheet_id": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P",
3
"worksheet_id": 0,
4
"start_index": "C",
5
"how_many": 2
6
}

Sample Output

1
{
2
"spreadsheetId": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P",
3
"replies": [
4
{}
5
]
6
}

Delete row
Copy

Remove row from a worksheet.

Sample Input

1
{
2
"spreadsheet_id": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7Q",
3
"worksheet_id": 0,
4
"start_index": 5,
5
"how_many": 2
6
}

Sample Output

1
{
2
"spreadsheetId": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7Q",
3
"replies": [
4
{}
5
]
6
}

Delete worksheet
Copy

Delete a worksheet from a spreadsheet.

Sample Input

1
{
2
"spreadsheet_id": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7",
3
"worksheet_id": 123456789
4
}

Sample Output

1
{
2
"spreadsheetId": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7",
3
"replies": [
4
{}
5
]
6
}

Get row by index
Copy

Find a row by its index.

Sample Input

1
{
2
"spreadsheet_id": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7Q",
3
"worksheet_name": "Sheet1",
4
"row_number": 5
5
}

Sample Output

1
{
2
"range": "Sheet1!A5:Z5",
3
"majorDimension": "ROWS",
4
"values": [
5
[
6
"John",
7
"Doe",
8
"johndoe@example.com",
9
"123-456-7890",
10
"Marketing",
11
"Manager",
12
"2022-01-15",
13
"75000"
14
]
15
]
16
}

Get rows
Copy

Get data from a worksheet of a spreadsheet.

Sample Input

1
{
2
"spreadsheet_id": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7Q",
3
"worksheet_name": "Sales Data",
4
"number_of_rows": 5,
5
"filter": {
6
"column_heading": "Total Sales",
7
"operator": "Greater than",
8
"value": 1000,
9
"sort_direction": "descending"
10
},
11
"format_response": true
12
}

Sample Output

1
{
2
"results": [
3
{
4
"Date": "2023-05-01",
5
"Product": "Widget A",
6
"Quantity": 50,
7
"Unit Price": 25,
8
"Total Sales": 1250
9
},
10
{
11
"Date": "2023-05-02",
12
"Product": "Widget B",
13
"Quantity": 30,
14
"Unit Price": 40,
15
"Total Sales": 1200
16
},
17
{
18
"Date": "2023-05-03",
19
"Product": "Widget C",
20
"Quantity": 25,
21
"Unit Price": 45,
22
"Total Sales": 1125
23
},
24
{
25
"Date": "2023-05-04",
26
"Product": "Widget A",
27
"Quantity": 40,
28
"Unit Price": 25,
29
"Total Sales": 1000
30
},
31
{
32
"Date": "2023-05-05",
33
"Product": "Widget B",
34
"Quantity": 35,
35
"Unit Price": 40,
36
"Total Sales": 1400
37
}
38
]
39
}

Get spreadsheet details
Copy

Get details about a spreadsheet, including information about its worksheets.

Sample Input

1
{
2
"spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"
3
}

Sample Output

1
{
2
"spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
3
"properties": {
4
"title": "Sample Spreadsheet",
5
"locale": "en_US",
6
"autoRecalc": "ON_CHANGE",
7
"timeZone": "America/New_York",
8
"defaultFormat": {
9
"backgroundColor": {
10
"red": 1,
11
"green": 1,
12
"blue": 1
13
},
14
"padding": {
15
"top": 2,
16
"right": 3,
17
"bottom": 2,
18
"left": 3
19
},
20
"verticalAlignment": "BOTTOM",
21
"wrapStrategy": "OVERFLOW_CELL",
22
"textFormat": {
23
"foregroundColor": {},
24
"fontFamily": "Arial",
25
"fontSize": 10,
26
"bold": false,
27
"italic": false,
28
"strikethrough": false,
29
"underline": false,
30
"foregroundColorStyle": {
31
"rgbColor": {}
32
}
33
},
34
"backgroundColorStyle": {
35
"rgbColor": {
36
"red": 1,
37
"green": 1,
38
"blue": 1
39
}
40
}
41
},
42
"iterativeCalculationSettings": {
43
"maxIterations": 100,
44
"convergenceThreshold": 0.001
45
},
46
"spreadsheetTheme": {
47
"primaryFontFamily": "Arial",
48
"themeColors": [
49
{
50
"colorType": "TEXT",
51
"color": {
52
"rgbColor": {
53
"red": 0,
54
"green": 0,
55
"blue": 0
56
}
57
}
58
},
59
{
60
"colorType": "BACKGROUND",
61
"color": {
62
"rgbColor": {
63
"red": 1,
64
"green": 1,
65
"blue": 1
66
}
67
}
68
}
69
]
70
}
71
},
72
"sheets": [
73
{
74
"properties": {
75
"sheetId": 0,
76
"title": "Sheet1",
77
"index": 0,
78
"sheetType": "GRID",
79
"gridProperties": {
80
"rowCount": 1000,
81
"columnCount": 26
82
}
83
}
84
},
85
{
86
"properties": {
87
"sheetId": 1,
88
"title": "Sheet2",
89
"index": 1,
90
"sheetType": "GRID",
91
"gridProperties": {
92
"rowCount": 1000,
93
"columnCount": 26
94
}
95
}
96
}
97
],
98
"spreadsheetUrl": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit"
99
}

Get total active rows
Copy

Returns total active rows in a sheet.

Sample Input

1
{
2
"spreadsheet_id": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7Q",
3
"worksheet_name": "Sales Data"
4
}

Sample Output

1
{
2
"rows": 1000
3
}

Get worksheet data
Copy

Get data from a worksheet of a spreadsheet.

Sample Input

1
{
2
"spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
3
"worksheet_name": "Sheet1"
4
}

Sample Output

1
{
2
"range": "Sheet1!A1:Z1000",
3
"majorDimension": "ROWS",
4
"values": [
5
[
6
"Name",
7
"Age",
8
"City"
9
],
10
[
11
"John Doe",
12
"30",
13
"New York"
14
],
15
[
16
"Jane Smith",
17
"25",
18
"Los Angeles"
19
],
20
[
21
"Bob Johnson",
22
"45",
23
"Chicago"
24
],
25
[
26
"Alice Brown",
27
"35",
28
"Houston"
29
]
30
]
31
}

Get worksheet data using range
Copy

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

Sample Input

1
{
2
"spreadsheet_id": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7Q",
3
"worksheet_name": "Sheet1",
4
"cell_range": "A1:C5"
5
}

Sample Output

1
{
2
"range": "Sheet1!A1:C5",
3
"majorDimension": "ROWS",
4
"values": [
5
[
6
"Name",
7
"Age",
8
"City"
9
],
10
[
11
"John Doe",
12
"30",
13
"New York"
14
],
15
[
16
"Jane Smith",
17
"25",
18
"Los Angeles"
19
],
20
[
21
"Mike Johnson",
22
"35",
23
"Chicago"
24
],
25
[
26
"Emily Brown",
27
"28",
28
"Houston"
29
]
30
]
31
}

Insert empty column
Copy

Insert a column into a Google Sheet.

Sample Input

1
{
2
"spreadsheet_id": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7Q",
3
"worksheet_id": 0,
4
"start_index": "C",
5
"how_many": 2,
6
"inherit_from_before": true
7
}

Sample Output

1
{
2
"spreadsheetId": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7Q",
3
"replies": [
4
{}
5
]
6
}

Insert empty row
Copy

Insert a row into a Google Sheet.

Sample Input

1
{
2
"spreadsheet_id": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7Q",
3
"worksheet_id": 0,
4
"start_index": 5,
5
"how_many": 2,
6
"inherit_from_before": true
7
}

Sample Output

1
{
2
"spreadsheetId": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7Q",
3
"replies": [
4
{}
5
]
6
}

List worksheet column headers
Copy

List columns of a worksheet.

Sample Input

1
{
2
"spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
3
"worksheet_name": "Sheet1"
4
}

Sample Output

1
{
2
"range": "Sheet1!A1:Z1",
3
"majorDimension": "ROWS",
4
"values": [
5
[
6
"First Name",
7
"Last Name",
8
"Email",
9
"Phone Number",
10
"Date of Birth",
11
"Address",
12
"City",
13
"State",
14
"Zip Code",
15
"Country"
16
]
17
]
18
}

List worksheets
Copy

List worksheets within a spreadsheet.

Sample Input

1
{
2
"spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"
3
}

Sample Output

1
{
2
"sheets": [
3
{
4
"properties": {
5
"sheetId": 0,
6
"title": "Sheet1",
7
"index": 0,
8
"sheetType": "GRID",
9
"gridProperties": {
10
"rowCount": 1000,
11
"columnCount": 26
12
}
13
}
14
},
15
{
16
"properties": {
17
"sheetId": 1234567890,
18
"title": "Sales Data",
19
"index": 1,
20
"sheetType": "GRID",
21
"gridProperties": {
22
"rowCount": 500,
23
"columnCount": 15
24
}
25
}
26
},
27
{
28
"properties": {
29
"sheetId": 987654321,
30
"title": "Expenses",
31
"index": 2,
32
"sheetType": "GRID",
33
"gridProperties": {
34
"rowCount": 200,
35
"columnCount": 10
36
}
37
}
38
}
39
]
40
}

Raw HTTP request (advanced)
Copy

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

Sample Input

1
{
2
"method": "GET",
3
"url": {
4
"endpoint": "/users"
5
},
6
"headers": [
7
{
8
"key": "Accept",
9
"value": "application/json"
10
}
11
],
12
"query_parameters": [
13
{
14
"key": "page",
15
"value": "1"
16
},
17
{
18
"key": "limit",
19
"value": "10"
20
}
21
],
22
"body": {
23
"none": null
24
},
25
"include_raw_body": false,
26
"parse_response": "true"
27
}

Sample Output

1
{
2
"response": {
3
"status_code": 200,
4
"headers": {
5
"Content-Type": "application/json",
6
"Cache-Control": "no-cache",
7
"X-RateLimit-Limit": "60",
8
"X-RateLimit-Remaining": "59"
9
},
10
"body": {
11
"users": [
12
{
13
"id": 1,
14
"name": "John Doe",
15
"email": "john.doe@example.com"
16
},
17
{
18
"id": 2,
19
"name": "Jane Smith",
20
"email": "jane.smith@example.com"
21
}
22
],
23
"total": 100,
24
"page": 1,
25
"limit": 10
26
}
27
}
28
}

Update cell value
Copy

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

Sample Input

1
{
2
"spreadsheet_id": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7",
3
"worksheet_name": "Sheet1",
4
"cell": "B5",
5
"value": "Updated Value",
6
"value_input_option": "USER_ENTERED"
7
}

Sample Output

1
{
2
"spreadsheetId": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7",
3
"updatedRange": "Sheet1!B5",
4
"updatedRows": 1,
5
"updatedColumns": 1,
6
"updatedCells": 1
7
}

Update row
Copy

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

Sample Input

1
{
2
"spreadsheet_id": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7Q",
3
"worksheet_name": "Sheet1",
4
"row_number": 5,
5
"value_input_option": "USER_ENTERED",
6
"row_data": [
7
{
8
"column_heading": "Name",
9
"value": "John Doe"
10
},
11
{
12
"column_heading": "Age",
13
"value": 30
14
},
15
{
16
"column_heading": "Email",
17
"value": "john.doe@example.com"
18
},
19
{
20
"column_heading": "Active",
21
"value": true
22
}
23
]
24
}

Sample Output

1
{
2
"spreadsheetId": "1A2B3C4D5E6F7G8H9I0J1K2L3M4N5O6P7Q",
3
"updatedRange": "Sheet1!A5:D5",
4
"updatedRows": 1,
5
"updatedColumns": 4,
6
"updatedCells": 4
7
}

Update values in spreadsheet
Copy

Update the values of a spreadsheet in a given range.

Sample Input

1
{
2
"spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
3
"range": "Sheet1!A1:C3",
4
"value_input_option": "USER_ENTERED",
5
"major_dimension": "ROWS",
6
"values": [
7
[
8
"Name",
9
"Age",
10
"City"
11
],
12
[
13
"John Doe",
14
"30",
15
"New York"
16
],
17
[
18
"Jane Smith",
19
"25",
20
"London"
21
]
22
],
23
"include_values_in_response": true,
24
"response_value_render_option": "FORMATTED_VALUE",
25
"response_date_time_render_option": "FORMATTED_STRING"
26
}

Sample Output

1
{
2
"spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
3
"updatedRange": "Sheet1!A1:C3",
4
"updatedRows": 3,
5
"updatedColumns": 3,
6
"updatedCells": 9,
7
"updatedData": {
8
"range": "Sheet1!A1:C3",
9
"majorDimension": "ROWS",
10
"values": [
11
[
12
"Name",
13
"Age",
14
"City"
15
],
16
[
17
"John Doe",
18
"30",
19
"New York"
20
],
21
[
22
"Jane Smith",
23
"25",
24
"London"
25
]
26
]
27
}
28
}

DDL operations
Copy

List worksheet column headers (DDL)
Copy

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

1
{}

Sample Output

1
{
2
"columnHeaders": [
3
"First Name",
4
"Last Name",
5
"Email",
6
"Phone Number",
7
"Date of Birth",
8
"Department",
9
"Employee ID"
10
]
11
}

List worksheet column headers using ID (DDL)
Copy

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

1
{}

Sample Output

1
[
2
"A",
3
"B",
4
"C",
5
"D",
6
"E",
7
"F",
8
"G",
9
"H",
10
"I",
11
"J"
12
]