Google Sheet

This article explains how to connect Iterop to Google Sheet.

Google side

1. Go to Google’s API management module: https://console.developers.google.com/apis/library
2. Allow the use of the Google Sheet API.

Iterop side

1. Go to Iterop Deisgn in the “Services” tab.
2. 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.

List of useful operations :