Using Google Apps Script with Cloud IoT Core

Gus Class
Gus Class
Jun 20 · Unlisted

Recently, we have enabled support in Apps Script for the Google Cloud IoT Core API with via Google Advanced Services. This allows you to access the IoT Core features from all of the GSuite Apps (sheets, docs, and so on) and build apps with Apps Script extensions like App Maker.

This is exciting because it gives you new options for IoT fleet management and logistics. For example, you could create an App Maker app to register a device or access device state from Google Sheets. In this post, I’ll try and give you a simple example of how to get started and a demo for inspiring you to make your own apps.

Getting Started

Start on the GSuite developer hub and create a new script by clicking the button in the top left of the page.

Click on the Resources > Advanced Google Services menu item. Enter a new project name (this is separate from the standard Google Cloud Platform project and is just for Apps script). It will take a little time for the project to load.

Scroll down to Cloud IoT API and toggle the “on” switch. Use an existing Google Cloud Platform project with Cloud IoT Core enabled (or create a new one) and get the project number from the Google Cloud Console as shown in the following image.

The project number is displayed on the Project Info pane of the Google Cloud Console.

From Apps Script, click the Resources > Cloud Platform project menu item and enter your project number from the console into the text field. Test the autocomplete feature by typing in “CloudIoT.”, with the period, in the script editor area and waiting for suggestions to appear. If the suggestions appear, the Cloud IoT API has been enabled.

Try the following example to list out all registries in a given project and region, modifying projectID and cloudRegion as appropriate for your project name and region:

Note If you’re seeing Cloud IoT API has not been used in project 428196946214 before or it is disabled it’s because you have not updated the projectId variable in the example code.

View the output under View > Stackdriver Logging. If the variables in the example snippet were updated to match your registry ID and region, you will see output similar to the following image.

If you’re seeing the list of registries created in your project, you have successfully accessed the Cloud IoT Core API from Apps Script.

Reading Device States

Now that you’ve verified the API is working, it’s time to try something a little more practical. Let’s try making a function that lists the devices in a registry and retrieves the device state. The following example does exactly this: retrieves the list of devices in the registry, queries for their state history, and then outputs the converted values as an ASCII string.

Create a function with the example code, update the name variable to your device registry name, the projectId to your Google Cloud project ID, and then click run from the menu bar.

After running the function, select View > Stackdriver Logging from the site menu to look at the logs. You will see lists of states for the devices in your registry including binary data converted to ASCII. The time that the device state was updated is also available in the states objects returned from the API but is unused in this example.

Note this example assumes you’re storing ASCII data in your state. For other types of data, you would need to parse the binaryData member of the device state response differently.

Charting the State data in Google Sheets

Now that you have the state data, it’s time to chart it by calling your Apps Script function from Google sheets. Start by navigating to https://sheets.new to create a new spreadsheet. Add the following rows:

Cloud Region | <your-cloud-region>
Project Name | <your-project-name>
Registry Name | <your-registry-name>

After you’ve added the rows, your sheet should be something like the following example image.

Sheet with 3 rows and column values for each row

Select Tools > Script Editor to access Apps Script from Sheets. As before, open the Resources menu and set the Cloud Platform Project, set the Cloud Project number, then enable CloudIoT in Advanced Google Services.

Image showing CloudIoT enabled in Advanced Google Services

Now with the sheet in place, the following enhancement to the snippet from earlier will retrieve device state based on the cells in the sheet and will retrieve device states for all the devices in the registry.

Select the updateData function you added using the code and click the run button to update your sheet with the devices in your registry and their state data.

At this point, you can easily retrieve the device state data for your device registries and place it into sheets for charting or editing!

Demo

If you don’t have a device registry with readily plottable data, you can follow along here. First, you will need to generate an RSA key pair for connecting your devices to the Google Cloud IoT Core device bridge. The following command will do this:

Now you will have the rsa_private.pem key file that should only be on your device as well as the rsa_cert.pem file that is given to Google Cloud for verifying the authenticity of a device.

Run the following command to create a registry with 10 devices in it using the credentials you just generated. Note that in practice you would want to use different keys for each device but for simplicity, we’ll just reuse the same key.

Now that you have a registry with 10 devices in, try updating your sheet to use the values for the device registry you just created by setting the registry name to sheetshooter. The device Ids will appear in the sheet.

Now, it’s time to connect using MQTT to set the device state. Start by cloning

git clone https://github.com/googlecloudplatform/python-docs-samples

or downloading the Google Cloud Python sample repo. Navigate to the iot/api-client/mqtt_example folder, e.g.

cd python-docs-samples/iot/api-client/mqtt_example

From here, create a virtual environment and install all of the sample dependencies, for example, you would run the following commands on *nix environments.
virtualenv env
source env/bin/activate
pip install -r requirements.txt

Now, copy the private key you created in previous steps to the sample folder. For example, if you created it in your $HOME folder, you would run:

cp $HOME/rsa_private.pem .

Change line 451 to hardcode the payload to the message counter plus a random value:

payload = '{}'.format(i + (random.random()*25)

Now, set the environment variable for your Google Cloud Project and run the program for each of your devices to generate state data for each device.

Note Make sure you replace the first line of the shell code with your project Id.

The code will connect all of the devices we created in the previous step and will then transmit state data on behalf of all the devices. State data should not be transmitted very frequently so the sample code is deliberately delaying the updates.

After the device states have been simulated, you’re ready to read the values back into the sheet. Run the updateData function again from the script editor and then return to your sheet. If you want to plot the simulated data values from your devices, select the cells in the sheet with the data.

Example of how the simulated state data could look in a sheet

To turn the simulated data into a chart, select Insert > Chart from the menubar with the imported data highlighted.

Plot of the simulated device state data, note the random variability similar to the range provided on line 451 of the Python MQTT example

Conclusions and Next Steps

The addition of IoT Core in Google Advanced Services greatly simplifies the task of producing a dashboard for reporting device state. If you were looking to build a simple solution for getting at-a-glance details for a fleet of devices in the field, this is a great approach.

If you wanted to make the solution more robust, you could add the following:

  • Automate the generation of the plot / store the device states on a separate sheet page
  • Support pagination on the devices list operation in Apps Script
  • Use different public/private key pairs when registering the devices

Additional interesting scenarios for using Google Sheets for operations on Cloud IoT Core include:

  • Device registration using columnar data for the devices
  • Device communication (sending commands and updating configurations) using sheets
  • Device management (CRUD) using a Google Sheet

The list of potential applications is probably much longer than this but hopefully this gets you inspired to try it out and do more with Apps Script and Google Cloud IoT Core!

See Also

Unlisted

Gus Class

Written by

Gus Class

All stack, led enthusiast, hecker.