Overview
Google Sheets is an app available on IOT servers that pulls data from a customer’s Google Sheet, such as the example given below. It adds a node that will pull a sheet from the Google Sheet file when it receives an injection/payload.
Installing
To begin, you first need to install the GSheets package.
Go to the IOT server’s homepage and go to the Apps Manager.
Search for the Google Sheets package and download it.
Keep in mind that installing packages in an IOT server can fail at times.
If you install a package and it does not look like it went through, it is normal to delete and reinstall a package.
The Flow Template
Now you need to grab the GSheet flow template from the repository.
In Braincube, go to the Braincube Axone site and click “iotbraincubeaxone5” under the IoT Server section.
Enter the Data Flows Manager and go to the REPO – Connectivity flow.
There you will find the GSheet Connection Template.
Copy this template to the needed IOT server.
You can do so by selecting the entire template (click and drag to select everything), enter the hamburger menu at the top-right, click Export, and you can save the nodes to your clipboard.
You can then go to the needed server, click the Import option in the menu, paste the code from your clipboard, and click Import at the bottom.
Setup
Now that you have the GSheet template in the IOT server, you can start to configure it.
Open the GSheet - get node.
The fields that are required to be filled out are Credentials, Method, Spreadsheet ID, and Cells.
For Credentials, the customer needs to create a Google Service Account with access to the Google sheet and share with you the JSON Credentials for it.
Method is set to Get Cells by default, which only reads from the sheet. Since we are only ever supposed to read from a data source, this should never be changed to any of the other modes.
Spreadsheet ID is the ID of the sheet in Google Drive. This can be found in the URL of the sheet.
Cells refers to the sheet in the GSheet file you will be grabbing from.
In this example, the sheet would be Sheet1.
You can also limit it to pull only certain columns.
If you only wanted to grab columns A through F, you would write it out as ‘Sheet1’!A:F
The Filter Out Newlines function node is used for getting rid of any newline characters that were entered in the cells. Without this, the CSV node can have some issues converting it.
The SkipLines function node is used for skipping lines at the beginning of the file.
It is set to skip 0 lines by default, but it can be changed to skip more by changing the “lines” variable in the node.
For the CSV node, nothing should need to be changed unless a different delimiter has been requested. For some reason, the “Skip first x lines” option in this node generally does not work, hence why a node has been created for it.
The setFileName function node is currently set up for use within the GAF GCP domain since this template has not been used anywhere else. (This is subject to change.) If it is used anywhere else, you are free to change the last 2 lines of the code where it grabs the MX name and formats the file name.
If it is being used at a GAF GCP site, all you should need to change is the part of the formatted string in the last line where it says “FILENAME”. This will be how it shows up in MX.
For the file node, if it is within the GAF GCP environment, this should remain unchanged.
If it is used in a different environment that is not setup in the same way, you may have to change the file node into a Braincube Transfert node.
Verifying Data Flow
Before you enable the inject node and start sending data (remember to configure the timestamp node to send data at the required frequency), it is generally a good idea to disconnect the file node and use a debug node to verify that data is being pulled correctly.
Once it is verified that the IOT server is grabbing the GSheet data, you can reconnect the file node. Then you can go into the MX’s entrance files to make sure that data is being sent after an inject has occurred.
Please note: GSheet connector cannot grab GSheet files that are in a .xlsx format, which is indicated with this label near the file name