Using Loom with Google Sheets

One of the common uses of Loom is to collect data and log it to a Google spreadsheet. The are not many hardware requirements, and while the software / Google sheet setup process has several steps, they are fairly short and easy.

Hardware

Loom offers logging to Google Sheets via a few different methods: (unchecked if still in development)

Ethernet
WiFi
4G Cellular

Setup Spreadsheet and Script

Create Sheet

Your spreadsheet can be created as normal by creating a new Google Sheet. You will need the spreadsheet ID, which can be found in the spreadsheets URL. An example URL is shown below :

https://docs.google.com/spreadsheets/d/1Hv2oME3sjumMXv36YkFV1QI83xnXu-f-ZrxULsXS_A/edit#gid=1643186642

The ID is the string of characters after the .../d/ and before the /edit#..., which in the above case would be: 1Hv2oME3sjumMXv36YkFV1QI83xnXu-f-ZrxULsXS_A.

Note: You do not have to create the tab you intend to write to, the Google script will create it automatically if it does not already exist. However, if you try to log data to a tab that already has data which was not generated by Loom and the script, the data may not be added. If this happens, you might try deleting the tab or logging to a different tab

Add Script

Your device does not send data directly to the spreadsheet you just created, it actually has to send it to a Google App Script. This script will format the data correctly, add timestamps, and then add the data to a tab within your spreadsheet, both spreadsheet and tab specified by your device.

Adding this script is easy. From your spreadsheet under the Tools menu, select Script Editor:

Select Tools > Script Editor

This will open the script editor. Delete any existing code in the editor. Then paste the entirety of the Spreadsheet.gs file into the editor. The provided script does not need any modification. Next you need to publish your script by:

Save File > Save
- Give your script a name
- Select Publish > Deploy as Web App
  - Note that you probably have to be the owner of the script to do this
  - It seems if using a shared Google Drive, even if you create the script and are a managing account for the shared drive this might not work. To get around this, you might make the script in a managing account of the shared drive, and put the spreadsheet itself in the shared drive.
In the window that appears, set:
- Project version to be new
- Execute app as to be Me (email)
- Who has access to the app to be Anyone, even anonymous
Click update
Copy the URL it provides

More Sheets

Now that you have setup the Google App Script you do not need to add a new script for each spreadsheet you want to log to. Other sheets created under the account that made the script can use the script.

Test Script

In order to make sure you have setup the script correctly, you will likely want to test adding data manually. To do this, form a URL in the following format:

https://script.google.com/macros/s/<SCRIPT-ID>/exec?key0=sheetID&val0=<SHEET-ID>&key1=tabID&val1=<TAB-NAME>&key2=deviceID&val2=<DEVICE-NAME>&key3=full_data&val3=datakey1~dataval1~datakey2~dataval2

In the above URL, replace the following:

<SCRIPT-ID> with the ID of the script you added and published previously
<SHEET-ID> with the ID of the Google Sheet you created previously
<TAB-NAME> with the name of the tab you want to create (if does not already exist) and log to
<DEVICE-NAME> a string or number representing the device that will be logging
Data: The values don't matter here, as this is just a test, but if you wanted to change the data that is sent to the spreadsheet, in the above format
- datakey1 represents the first data key, replace with a string identifying the data point column
- dataval1 represents the first data value, replace with a value you want to emulate logging
- The subsequent keys and values follow the same format, as as many as you want, with the tilde ~ character between all keys and values

Example

The URL you formed will look something like the following (Note that the IDs are not real script and sheet IDs)

https://script.google.com/macros/s/AWfycbwJ-UXlletXlx1fHj2WJFF7iLH3gvM7xYFS0oAO_pkDC9Wb_opK/exec?key0=sheetID&val0=1FPwr8Q9QyP6-VbW1lp_fyFMe58dboQUhLGtNW1gVmak&key1=tabID&val1=TestTab&key2=deviceID&val2=SomeDevice&key3=full_data&val3=key1~abc~key2~123

You can then copy the URL you formed and paste it into a browser search bar. If everything is working correctly, you should get a page saying "Ok" and the test data should have been added to your spreadsheet.

Device Configuration

Once you have setup the sheet and script and have tested them, the remaining work is on the Loom device. You will probably want to start with a basic example to make sure the can get data from the device to the spreadsheet before adding more sensors and other functionality.

Our GoogleSheetsWiFi or GoogleSheetsEthernet or GoogleSheetsLTE are good starting examples. Open it in the Arduino IDE

All of the GoogleSheets publishing examples require this basic amount of information, look further below to find the specific details for each method of publishing:

<SCRIPT-ID> with the ID of the script you added and published previously
<SHEET-ID> with the ID of the Google Sheet you created previously
<TAB-NAME> with the name of the tab you want to create (if does not already exist) and log to
This information will be put into the config.h configuration file.

For GoogleSheeetsWifi, you will need the following information:

The host wifi network name, this should just be the network you’re currently connected to
The host wifi network password, this is the password required for the network you’re connected to, leave as “” if there is no required password

For GoogleSheetsEthernet, you will need the following information:

A MAC address, get this from your organization
An IP address, you can use [192,168,0,1] if you don't have reason to change it

For GoogleSheetsLTE, you will need the following information:

An Access Point Name (APN), will be from your nano sim card provider. Most likely to be hologram
Potentially a GPRS username and password, if you have one, it will be provided by the nano sim card provider. If not, leave these two fields as “”
If you wired one of the analog pins(A1-A5) from the Feather M0 to pin 5 on the LTE Shield, the name of the pin will be needed to automatically turn on the LTE shield. If this is not wired, leave as “” and turn the LTE shield on manually with the Power button after it is plugged into a power source.

The object in the configuration for the Google sheets module looks like the following:

{
	'name':'GoogleSheets',							// Google Sheets module
	'params':[
	'Goog',															// Name of the module (not necessary to change unless you need to distinguish between multiple GoogleSheets modules) 
	7002,																// Ethernet:7001, WiFi:7002
  '/macros/s/<your-script-id>/exec',	// Script ID
  '<your-sheet-id>',									// Sheet ID
  true,																// True write to tab matching name of device, false to use tab name specified in next parameter
  '<your-tab-name>',									// Tab name to write to if previous parameter is false
]

The parameters in <brackets> are values that you will need to provide

Once the code compiles, upload it, open the Serial monitor an see if everything works properly. If so go continue to the next step, if not, review the previous steps

Customizing Device

Once you've made it this far, you should be able to run the example code to get data logged to your spreadsheet. Now you can tailor your device and the code to fit your project. Our Quick Start Guide and the code Documentation are likely good resources to start with to do this.

Notes

If the data set being sent to the Google sheet changes, the script should be able to detect the change and create a new header. However, if you delete the contents of the sheet, but not the tab itself, there is a possibility that a different data set will not be uploaded. To address this, if you want to empty a sheet tab, delete the tab, rather than just deleting the contents.

Loom Wiki Home • Code • Documentation

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Using Loom with Google Sheets

Table of Contents