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 and then select New Project in the top right-hand side of the pop-up window.

    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 then searching Google Drive API and Google Sheets API. Select these, and click the ENABLE button.

    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 selecting External and clicking 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 and find the following “Google Drive API – /auth/drive.file – View and manage Google Drive files and folders that you have opened or created with this app”. Once you have selected this click the UPDATE button at the bottom of the page. Now click SAVE AND CONTINUE which will take you to the next page.

    You don’t need to do anything with the Test users page so click the SAVE AND CONTINUE button again, which will take you to the summary page which will give you an overview of what you have done.

    Scroll to the bottom of the summary page and click BACK TO DASHBOARD which should take you back to the OAuth consent screen if not, navigate back to it. Once you’re back on the dashboard of the OAuth consent screen click the PUBLISH APP button. This allows you to use any Google account on the gateway for the data.

    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 > OAuth client ID.

    Once you have done this, select the Application type dropdown menu and select TVs and Limited Input devices and input a name for this e.g., “Pressac gateway” then click the CREATE button.

    Now you have set up your Google API download your OAuth 2.0 Client IDs .JSON file by clicking the download icon on the credentials page, then import them into the Google Sheets connection using the Upload button on the connection page.

    Connecting to Google Sheets

    Once you have finished the above steps you will now need to connect Google Sheets to the Google account to which you would like the sensor data to go.

    Firstly, click the Set-Up Google Sheets button at the bottom of the page. This will then take you to a Google sign-in page which will allow you to select your Google account. After clicking this you will be asked to “confirm your choices”. This will allow the gateway to create Google Sheets with your sensor data.

    Now you will be greeted with a page that has a token code. Copy this and input it into your Google Sheets connection on the gateway in the Token field.

    After inputting your connection details click “Save”. The Google Sheets connection status will now change to “Connected” then will report the following data to the Google Sheets page.

    Included data – The data contained in the payload:

    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.

    Each sensor type will have its own spreadsheet which will be updated automatically when it receives a telegram, which makes it easier to display the data for the devices. A spreadsheet will be created once the cell limit has been reached.

    in Connections