Skip to main content
Version: Streamsheets 3.0

Google Sheets Integration (Beta)

This is a step-by-step guide for connecting your Google Sheets Account to Streamsheets. Currently, the GoogleSheets Integration is in an early development stage and depends on a google service account.

Google Sheets Integration Settings​

GoogleSheets API Tokens are stored in the Integration Settings. Create a new Integration Account and select GoogleSheets.

     

Get your API token from the JSON file you can download in your google service account.
Copy e-mail and private key from the saved JSON file and enter them. You are now able to use the created Google Sheets Integration in any App.

Note: To get the private key, everything between the quotes must be copied! So -----BEGIN PRIVATE KEY-----...... to .... -----END PRIVATE KEY-----

Google Sheets Integration Functions​

Open an App and select the Integration menu in the top right toolbar, next to the connections. Choose Google Sheets and select your just created account.

Now you are able to use the GOOGLE.SHEETS.() functions:

First steps:

Create a new spreadsheet by tiggering =GOOGLE.SHEETS.SPREADSHEET.CREATE("NameOfYourSpreadsheet"). Make sure to only trigger it once, or you will keep creating more Spreadsheets. As a response Google sends back a JSON. There are a lot of information in there, but all we need is the so-called spreadsheet id. Use the DOT notation to print the id directly in a cell (=A7.spreadsheetId). Since we are using a service account and not your regular google account. You will have to share the just created sheet with yourself. Use the =GOOGLE.SHEETS.SPREADSHEET.SHARE(A7.spreadsheetId,"your.email@googlemail.com","writer") to add yourself as a "writer" to the created sheet. Same as above, you only need to trigger this function once. You will receive an e-mail to get direct access to your spreadsheet. Now you are ready to start interacting with your sheet. You can write cells, enter rows or only read information from your sheet.

The functions used in the example are:

  • =GOOGLE.SHEETS.ROW.INSERT(A7.spreadsheetId,"Sheet1",C11:F11,1)
  • =GOOGLE.SHEETS.ROW.INSERT(A7.spreadsheetId,"Sheet1",C12:F12,2)
  • =GOOGLE.SHEETS.CELL.WRITE(A7.spreadsheetId,"Sheet1","A2:A5",C12:C15)
  • =GOOGLE.SHEETS.CELL.READ(A7.spreadsheetId,"Sheet1","A:A",C17:F24,)

Create Google Service Account API Key​

Let´s start by creating your service account. Log into your Google Account. Open a menu called "Google cloud". You can find it at the Google-Apps Menu (3x3 Button).

If it is not listed there, then click on the "More from Google" button at the very bottom of the Apps drop-down list. Google Cloud hides under: Products -> For Business -> Google Cloud.
As soon as you opened Google Cloud, find the console in the top right corner and open it. In the left tree find "IAM & Admin" and select "Service Accounts".

Create a new project or if you want select and existing one.
The new project should now be selected automatically. Now create a new service account by clicking on the "+ Create service account" button. Assign an account name and leave optional fields blank.

Then in the service account overview click on the 3 dots and select "Manage keys".

In the overview: expand "Add key" and select "Create new key".

Select "JSON" and create it by clicking on "Create".

The private key + further information are then stored locally as a JSON file. IMPORTANT: the JSON file contains among other things the private key that we need for the login via Streamsheets!

Close confirmation field. Now you need to add the Google Sheets and Drive APIs to the project. Click on the hamburger menu in the upper left corner and select "APIs and Services" -> "Enabled APIs and Services"

Check in the overview if Drive and "Sheets API" are already listed. Probably not, then click on "+ Enable APIS and services".

Search for the "Sheets API"

Select the result and activte API.

Back to the overview. Do the same analog for Drive API, so again click on "+ Activate APIS and services", search for "Drive API", click result and activate the API.

Back to the overview and check if Drive and Sheets API show up

Now you can use the downloaded API Key in the Integration Account.