How to integrate Carriots with G-Suite
(formerly Google Apps for Work and Google Apps for your Domain)

Difficulty:

0. Introduction

If you have a Google account, you can read and/or send data from Carriots to the G-Suite via the Google API. G-Suite was formerly known as Google Apps for Work and Google Apps for your Domain.

In this example, we will read and append data from/into Google Sheets. Google Sheets offers many options to organize, visualize, share and export your data.

  • Project Schema

Google offers a client API to ease the authentication and authorization processes, in the Internet you can find several examples of how to use this client API. What we are showing here is how to make the requests to the Google API from scratch.

TOP

1. Create a new project into the Google console

First of all you need to have a Google account. If you do not have one, you must create it before continuing.

Type or paste this URL in your web browser: https://console.developers.google.com

If you are not logged into Google, you will be redirected to the Google login page

If you already have a Google account, log into Google using your account credentials. If it is the first time you get here, you will get blocked by a "Welcome" pop-up where you must accept the terms and conditions of use.

Now you should be inside the Google API Console, where you can create a new project associated to your account.

Click on the menu option named Project in the top left corner of the page and then click on "Create project".

  • Create Project

A new pop-up dialog will appear. Type the name of your project and click on "CREATE".

  • Project Creation

Make sure you are working on the new project you just created by clicking on the option menu named Project in the top left corner of the page, and then clicking on your brand new project. You can choose "View more projects" if your new project is not listed:

  • Project Selection

Due to we are going to work with Google Sheets API, you need to enable the necessary APIs for your project. Click on "ENABLE API" button in the upper half of the page:

  • Enable API

Now click on the link Drive API to select the API:

  • Enable Drive API

After selecting each API, it is necessary to enable it by clicking on the button "ENABLE" in the upper half of the page:

  • Enable Drive API

Now click on the menu option named Dashboard on the left side of the page to repeat these three last steps in order to enable the Sheets API (ENABLE API -> Sheets API link -> ENABLE).

TOP

2. Get the Google credentials

The Google credentials are what we need to authenticate our project in order to access the services that the Google API offers.

Click on the menu option named "Credentials" on the left side of the page.

You must click on the button "Create credentials" and then select "Service account key". The type of credentials referred as "Service account key" are used for accessing resources belonging to your own Google account.

  • Google credentials

We have used the same name for the Service account as we have used for the project, but you can choose the name you prefer.

By default we have chosen the Role called Viewer (Project -> Viewer).

  • Google credentials

Click on the button "Create" to obtain the JSON or P12 file including your private key for signing requests:

  • Private key

The JSON file is downloaded automatically into your machine. This JSON file includes almost all the useful information related to the service account, except the public key, which you can generate using OpenSSL, among other solutions. The content of the JSON file is:

This JSON file must be stored securely.

TOP

3. Create a Google spreadsheet

In a new browser tab, type or paste this URL to access to your Google Drive: https://drive.google.com/drive/u/0/my-drive

Right-click on the page and choose Google Sheets from the menu.

  • Create spreadsheet

Double-click on your new Spreadsheet document to open it, write the title you prefer and click on the menu option named "File", then select "Share...".

  • Share spreadsheet

Click on "Advanced" in the bottom right corner of the window.

  • Sharing settings

Type or paste the value of the property client_email from the JSON file downloaded in step 3 (it should look like access-control@access-control-153016.iam.gserviceaccount.com). As we are going to read and write in this document, the permissions must be "Can edit" (represented by the pencil). Finally click on "OK" button.

The URL in your browser tab must be something like https://docs.google.com/spreadsheets/d/1Mei9aTzb0N48psD8VJq1QD8yjTUHNDFJ8474c/edit#gid=0, where 1Mei9aTzb0N48psD8VJq1QD8yjTUHNDFJ8474c is the ID of your spreadsheet.

TOP

4. Append data from Carriots into your Google spreadsheet by using a listener

Log into Carriots using your Carriots account credentials.

If you are not familiar with dealing with listeners we strongly recommend to follow this tutorial in the first place: https://www.carriots.com/tutorials/create_listener

Now, create a listener that receives some data from a device, for example, and publish them into your spreadsheet. If you do not have a device ready to use, or you just want to check the listener code, you can paste it and execute in the console: https://cpanel.carriots.com/terminal/

The communication between the Google API and any other system must fulfil the requirements of the OAuth 2.0 specification. This basically consists of a 2 steps procedure:

· The first step consists on requesting a token. The do the first request a JWT (JSON Web Token) must be build.

· The second step is requesting the operation you originally wanted to do, adding the token obtained in step 1 to it.

  • Communication schema

For more info about the authentication and authorization processes see:
https://developers.google.com/identity/protocols/OAuth2
https://developers.google.com/identity/protocols/OAuth2ServiceAccount

To read more about how values are appended into a sheet of a spreadsheet document go to:
https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/append

  • Sample Spreadsheet

TOP

5. Read your spreadsheet data from a Carriots Listener

If you have accomplished the point 4, you should have by now a spreadsheet in your Google Drive in which the default sheet, called Sheet1 contains at least one row with the data you sent. If you have skipped the point 4, go back to it to read it before getting into the code.

To read the content in this sheet we must build the token and then make the read request.

Notice that the scope to build the JWT claim set is different than the scope in point 4, as we want to obtain a read-access token not an edition-access one.

To read more about how values are read from a sheet of a spreadsheet document go to:
https://developers.google.com/sheets/api/guides/values#reading
https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/get

The result of the execution should be something like:
[[row1 -> attribute1 value, row1 -> attribute2 value, row1 -> attribute3 value],
[row2 -> attribute1 value, row2 -> attribute2 value, row2 -> attribute3 value]]

TOP

6. From here on: Enhancing the project

Now you know how to read and append data from/to a Google spreadsheet document. You can enhance the project in many ways to fit all your visualization needs:

· Try to log data sent by your devices, in your Google spreadsheet. Try to build graphs with those data.

· Share info among several users with edition access by a Google spreadsheet. Then read these data and send them to your device or send an email including them.

TOP