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.
Email de référence

You must be logged in with the address that will be used in your ITEROP space. Also check that you have write permissions.

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:

Click on validate to get the state and the code in the URL of the page.

Write one (or more) value in your sheet

This method does not overwrite the data. (example: if you write in box A2 and it is already filled in, the data will go to A3 (if it is empty).

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
  • 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

This method will overwrite the values already present.

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
  • 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

Possibility to set a range (example: ‘Sheet 1’!A1:B2 )

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

Possibility to set a range (example: ‘Sheet 1’!A1:B2

Answer

It is possible to retrieve the answer in a JSON.

List of useful operations :

Updated on 13 November 2020

Was this article helpful?

Related Articles