I have been working on pushing data to Google Sheets and I thought I would share what I have learned and see if anyone can help me with one more refinement to this approach.
Use Case - My sleepy cellular devices send data to Ubidots every hour. However, Ubidots does not like textual data (except as context for a numerical variable) and it is not well suited for data that is related to system configuration as these values only rarely change. So, I thought it would be cool to send system configuration data for every device in my fleet once a day. That way, I could see what the current configuration was for all my devices and spot issues outside of the hourly reporting / events from Ubidots.
My starting point was the excellent (thank you again @rickkas7 ) application note:
https://docs.particle.io/datasheets/app-notes/an011-publish-to-google-sheets/
Note: There have been some changes in the Google App Script UI and you will need to select “Use Legacy Editor” in order to follow the tutorial. In fact, I do not think using the new editor is an option as you lose “versioning” and the ability to run the scripts “Anyone including anonymous” which are not (at least from what I can see) options anymore. This is a bit worrying as it implies that this approach may not work in the future.
I was able to follow the instructions and then modify the script to do the following: Add the device name and append each update as a new row on the current sheet. As I have over 180 devices in this product, this makes more sense than the “device per tab” or the “device per sheet” approach. For those who are interested, here is my script:
function test() {
var e = {};
e.parameter = {};
e.parameter.event = 'GoogleSheetsExport';
e.parameter.data = '[1,2,3,4,5,6,7,8]';
e.parameter.coreid = '1f0030001647ffffffffffff';
e.parameter.published_at = new Date().toISOString();
doPost(e);
}
function doPost(e) {
// e.parameter.event
// e.parameter.data
// e.parameter.coreid
// e.parameter.published_at "2016-04-16T13:37:08.728Z"
var particleApiToken = '{{Enter your API Token Here}}';
var publishedAt = new Date(e.parameter.published_at);
var cacheKey = 'deviceName';
var dataArray = [];
try {
dataArray = JSON.parse(e.parameter.data);
}
catch(e) {
}
var cache = CacheService.getScriptCache();
var deviceNameCache = cache.get(cacheKey);
if (!deviceNameCache) {
// The device name was not cached, so use the Particle Cloud API
var result = UrlFetchApp.fetch('https://api.particle.io/v1/devices?access_token=' + particleApiToken);
var resultJson = JSON.parse(result.getContentText());
deviceNameCache = {};
for(var ii = 0; ii < resultJson.length; ii++) {
deviceNameCache[resultJson[ii].id] = resultJson[ii].name;
}
cache.put(cacheKey, JSON.stringify(deviceNameCache));
}
else {
deviceNameCache = JSON.parse(deviceNameCache);
}
// Use the device name if known, otherwise use Device ID
var deviceName = deviceNameCache[e.parameter.coreid];
if (!deviceName) {
Logger.log('Unknown device ID');
return;
}
var sheet = SpreadsheetApp.getActiveSheet();
var row = [e.parameter.coreid, deviceName, publishedAt];
row = row.concat(dataArray);
sheet.appendRow(row);
var result = {};
result.ok = true;
return ContentService.createTextOutput(JSON.stringify(result))
.setMimeType(ContentService.MimeType.JSON);
}
and in my sketch:
/**
* @brief This function published system status information daily to a Google Sheet where I can monitor config / health for the fleet
*
* @details These are values that don't need to be reported hourly and many have string values that Ubidots struggles with. Testing this approach
* to see if it can give me a more consistent view of fleet health and allow me to see device configuration when it is off-line
*
* @link https://docs.particle.io/datasheets/app-notes/an011-publish-to-google-sheets/ @endlink
*
*/
void publishToGoogleSheets() {
char data[256]; // Store the date in this character array - not global
char solarString[16];
char verboseString[16];
(sysStatus.solarPowerMode) ? strncpy(solarString,"Solar",sizeof(solarString)) : strncpy(solarString,"Utility",sizeof(solarString));
(sysStatus.verboseMode) ? strncpy(verboseString, "Verbose",sizeof(verboseString)) : strncpy(verboseString, "Not Verbose",sizeof(verboseString));
snprintf(data, sizeof(data), "[\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%i sec\",\"%i%%\"]", solarString, lowPowerModeStr, currentOffsetStr, openTimeStr, closeTimeStr, sensorTypeConfigStr, verboseString, current.maxConnectTime, current.minBatteryLevel);
publishQueue.publish("GoogleSheetsExport", data, PRIVATE, WITH_ACK);
Log.info("published: %s", data);
}
I call this function once a day to collect the following information:
- Solar or Utility Power
- Low Power Mode (sleepy device)
- Time Zone setting (helpful to make sure daylight saving time algorithm is working)
- Park Open and Close time (device goes into ultra low power sleep when park is closed)
- Sensor type (Car counter, rpedestrian counter, etc)
- The longest connection time over the past day
- The lowest battery charge level over the past day
I hope is helpful to anyone looking to do the same thing. I also have two requests for help:
- Particle - I noted the security risk that using the API to get the device name. However, a manual approach is not feasible as the fleet grows. Any chance we could get “Read Only” API Tokens to enable this type of integration without the risk of a full access token? Does this already exist somehow?
- Community - I am still learning this App Script stuff so if anyone could suggest how to modify my script to do this, I would be very grateful: Instead of appending each new write as a new row, check to see if a row for a given device is already present and overwrite it. This way, the spreadsheet does not quickly grow to 1000s of rows and each devices’ most recent configuration is stored. Even hints are appreciated.
Also, if there is some wonderful and better way to do this, I am all ears.
Thanks,
Chip
I personally started with Google sheets and was actually using IFFT to get the data there for my early prototype. Now I store all the raw data into SQL and run queries to get the most recent data for each device.