Prerequisites
• Initial setup for Pressac EnOcean gateway
• A Google account
Navigate to the Google Sheets connection page by clicking “– Google Sheets” in the Connections sub-menu on the Pressac EnOcean gateway web-interface.
On this page you will find the following fields.
| Status | Status of the connection to Google Sheets, either Disconnected or Connected. |
| Connection | Connection to Google Sheets can be Enabled or Disabled from here. |
| Device identifier | What the device will appear as within the Google Sheet. When set to ‘Device Name’ the device name will be used and when set to ‘Device ID’ the unique device ID will be used. |
| Limit sheet cells | Allows you to enable the cell limit of the Google Sheet. |
| Cell limit | Allows you to limit the amount of cells per sheet to one, two, three, four or five million. Google Sheets with large amounts of cells negatively impact the performance. |
| Token | This token will be provided when you connect your gateway to the Google Platform. |
| Private key | Private key certificate of your Google project. This is generated/created when creating your credentials for your project. |
Setting up the Google Sheets connection
Creating and importing the Private Key
To use the Google Sheets connection on your gateway please follow the procedure below.
Create a Google account which can be done here.
Now you have created your Google account, log in to the Google Cloud platform console which can be found here.
Then create a new “Project”. You can do this by clicking Select a Project at the top left of the page:
Then select New Project in the top right-hand side of the pop-up window:
Give your project a name and click the CREATE button:
Now you’ve created your project, enable the Google Drive and Google Sheets API by clicking the “hamburger” icon (☰) in the top left corner, selecting APIs & Services > Library:
In the search box, enter Google Drive API
Search Google Drive API and click the ENABLE button:
Then repeat this process to enable the Google Sheets API:
Next, set up a “consent screen” for your project by clicking the “hamburger” icon (☰) in the top left corner, selecting APIs & Services > OAuth consent screen
then select External and click the CREATE button:
Fill out the information on the consent screen. This will pop up when you connect your gateway to Google Sheets. We recommend the following:
App information
App name: Pressac Gateway
User support email: (the email used to sign up)
Developer contact information
Email addresses: (the email used to sign up)
Once you have done this click the SAVE AND CONTINUE button.
Now you will be on the Scopes page. Click the ADD OR REMOVE SCOPES button
In the Filter box, enter “auth/drive.file” and select the scope shown:
Select the auth/drive.file scope and click the UPDATE button :
This will return you to the Scopes page. Click SAVE AND CONTINUE to proceed:
You don’t need to do anything with the Test users page so click the SAVE AND CONTINUE button again:
On the summary page, click BACK TO DASHBOARD:
In the Dashboard, your app should now be ready to publish. Click PUBLISH APP and then confirm:
The next step is setting up Credentials for the OAuth consent screen by clicking the “hamburger” icon (☰) in the top left corner and selecting APIs & Services > Credentials.
Then at the top of the page select CREATE CREDENTIALS > Service Account:
Enter a service account ID for the account, and click CREATE AND CONTINUE:
Go back to the Credentials tab and select the Service Account you just created:
Within this service account page, select KEYS:
Then select ADD KEY and choose Create New Key:
Select JSON and click the CREATE button:
This will download the JSON key to your PC downloads folder. Your Google Sheets account setup is now complete.
Connecting the Gateway to Google Sheets
Log in to the Pressac Smart Gateway Web interface, and select “Connections” and “Google Sheets.” Turn the connection on, and then click “Upload” in the Private Key field and upload the JSON file downloaded above:
The connection status should now change to “Connected” – the gateway is now sending data to the Google Sheets account. The data which is sent to the cloud is determined by the fields selected on the connection page:
| Timestamp | The time the gateway received a message from a physical device. |
| Gateway name | Gateway name assigned in the System > General screen. |
| Device location | Location of a device assigned on the edit device screen e.g. mainoffice. |
| Device values | Values received by the gateway from a physical device such as temperature, humidity, current etc. |
| Device value unit | Unit the device value is measured in if available e.g. °C. |
| Signal strength (dBm) | Signal strength between the device and gateway in dBm. |
Your gateway is now set up for Google Sheets.
Accessing your Google Sheets data
To access your Google Sheets data, scroll to the bottom of the Google Sheets connection page and click Launch Google Sheets. This will bring up a pop-up window where you will be able to select one of the spreadsheets which have been created by the gateway.
In order to access the data, you need to grant access to your Google account. You can enable this access through the Cloud console, or from the gateway itself, by entering your email address in the “Authorised Access” field and clicking the plus sign to add the account:
You should now be able to access the data by clicking “Launch Google Sheets” and selecting which spreadsheet you wish to view. A spreadsheet will be created for each sensor type.
