This article explains how to connect Iterop to Google Sheet.
Google side
- Go to Google’s API management module: https://console.developers.google.com/apis/library
- Allow the use of the Google Sheet API.
Iterop side
- Go to Iterop Deisgn in the “Services” tab.
- Create a new service with the base of the URL: https://sheets.googleapis.com/

Fill in the fields as follows:
- Redirect URL : http://localhost
- Authorize token URL : https://accounts.google.com/o/oauth2/auth?access_type=offline&approval_prompt=force
- Access token URL : https://accounts.google.com/o/oauth2/token
- Desired scope : https://www.googleapis.com/auth/spreadsheets
Click on validate to get the state and the code in the URL of the page.
Write one (or more) value in your sheet
Google help: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/append
Configuration

- Method : POST
- Path : v4/spreadsheets/${spreadsheetId}/values/${range}:append
Variable in the path
Variable |
Possibility |
Function |
includeValuesInResponse |
True |
Include the values in the answer |
False |
Do not include values in the answer |
|
valueInputOption |
RAW |
The values will be stored as is |
USER_ENTERED |
The values will be interpreted |
|
insertDataOption |
OVERWRITE |
Do not insert a line |
INSERT_ROWS |
Inserts a line |
|
responseValueRenderOption |
FORMATTED_VALUE |
Value formatted in the response (calculation performed) |
UNFORMATTED_VALUE |
Unformatted value in the response (calculation not performed) |
|
FORMULA |
Returns the formulas |
|
responseDateTimeRenderOption |
SERIAL_NUMBER |
Date in number format (used for Lotus) |
FORMATTED_STRING |
Date in string format |
Body
{ “range”: “${range}””,
“majorDimension”: “UNSPECIFIED_DIMENSION”,
“values”: [
“${value}””
]
}
Runtime data

- SpreadsheetId is the part of the google sheet URL between /d/ and /edit
- Example: if the URL is: https://docs.google.com/spreadsheets/d/11vp8HYKDm865236fRZpwIAdgdkiJx9EcXDOsMHSQ/edit#gid=387179624 then SpreadsheetId: 11vp8HYKDm865236fRZpwIAdgdkiJx9EcXDOsMHSQ
- Range : the name of the sheet is not mandatory if there is only one.
- Value : either value alone in the form [“A”] ( possibility to put A but in the body put [“${value}”]) or in the form [“A”, “B”, “C”],[“X”, “Y”, “Z”] which will give
A | B | C |
X | Y | Z |
Answer
It is possible to retrieve the answer in a JSON.

Modify one (or more) value(s) in the sheet
Google Help: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/update
Configuration

- Path : v4/spreadsheets/${spreadsheetId}/values/${range}
Variable in the path
Variable |
Possibility |
Function |
includeValuesInResponse |
True |
Include the values in the answer |
False |
Do not include the values in the answer |
|
valueInputOption |
RAW |
The values will be stored as is |
USER_ENTERED |
The values will be interpreted |
|
responseValueRenderOption |
FORMATTED_VALUE |
Value formatted in the response (calculation performed) |
UNFORMATTED_VALUE |
Unformatted value in the response (calculation not performed) |
|
FORMULA |
Returns the formulas |
|
responseDateTimeRenderOption |
SERIAL_NUMBER |
Date in number format (used for Lotus) |
FORMATTED_STRING |
Date in string format |
Body
{ “range”: “${range}””,
“majorDimension”: “UNSPECIFIED_DIMENSION”,
“values”: [
“${value}””
]
}
Runtime data

- SpreadsheetId is the part of the google sheet URL between /d/ and /edit
- Example: if the URL is: https://docs.google.com/spreadsheets/d/11vp8HYKDm865236fRZpwIAdgdkiJx9EcXDOsMHSQ/edit#gid=387179624 then SpreadsheetId: 11vp8HYKDm865236fRZpwIAdgdkiJx9EcXDOsMHSQ
- Range : the name of the sheet is not mandatory if there is only one.
- Value : either value alone in the form [“A”] ( possibility to put A but in the body put [“${value}”] ) or in the form [“A”, “B”, “C”],[“X”, “Y”, “Z”] which will give
A | B | C |
X | Y | Z |
Answer
It is possible to retrieve the answer in a JSON.

Read one (or more) value in sheet
Google Help: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/get

- Method : GET
- Path : v4/spreadsheets/${spreadsheetId}/values/${range}
Variable in the path
Variable |
Possibility |
Function |
majorDimension |
ROWS |
Online operation |
COLUMNS |
Operates in columns |
|
DIMENSION_UNSPECIFIED |
Not specified |
|
ValueRenderOption |
FORMATTED_VALUE |
Value formatted in the response (calculation performed) |
UNFORMATTED_VALUE |
Unformatted value in the response (calculation not performed) |
|
FORMULA |
Returns the formulas |
|
DateTimeRenderOption |
SERIAL_NUMBER |
Date in number format (used for Lotus) |
FORMATTED_STRING |
Date in string format |
Body
No bodysuit.
Runtime data

- SpreadsheetId is the part of the google sheet URL between /d/ and /edit
- Example: if the URL is: https://docs.google.com/spreadsheets/d/11vp8HYKDm865236fRZpwIAdgdkiJx9EcXDOsMHSQ/edit#gid=387179624 then SpreadsheetId: 11vp8HYKDm865236fRZpwIAdgdkiJx9EcXDOsMHSQ
- Range : the name of the sheet is not mandatory if there is only one.
Answer
It is possible to retrieve the answer in a JSON. (that’s the point)

Delete one (or more) value(s) in sheet
Google Help: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/clear
Configuration

- null
- Method: POST
- Path: v4/spreadsheets/${spreadsheetId}/values/${range}:clear
Body
No body
Runtime data

- SpreadsheetId is the part of the google sheet URL between /d/ and /edit
- Example: if the URL is: https://docs.google.com/spreadsheets/d/11vp8HYKDm865236fRZpwIAdgdkiJx9EcXDOsMHSQ/edit#gid=387179624 then SpreadsheetId: 11vp8HYKDm865236fRZpwIAdgdkiJx9EcXDOsMHSQ
- Range : the name of the sheet is not mandatory if there is only one.
Answer
It is possible to retrieve the answer in a JSON.
