Pushing Data To Google Sheets - New Thread

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:

  1. 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?
  2. 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

A read-only access token is available as part of Team Access Controls. Right now it’s an enterprise only feature, but keep an eye out for news about that. The idea is that you create a new Particle account. Give this account View Only access to your product. Then create a non-expiring token for that user account and embed that in your Google Sheets spreadsheet.

CloudConfigRK is similar to what you’ve done, but there are just more options available for storing the data locally and also retrieving it from different locations. It also shows how to add a button your Google Sheet to send the configuration to online devices.

It is possible to use the Google Apps Script with the current editor. There are a few additional things you need to set it up. Just follow the instructions here.

1 Like

@rickkas7 ,

Thank you for the tip on Team Access Controls, I will give that a shot.

Looking at CloudConfigRK, this looks like a dream come true. I was happy just seeing all my configuration data in one place. It seems like you have solved the issue of making changes as well. Thank you! I will start playing with this right away.

This is another great example of Particle providing tools that make it possible to scale with the platform.

Thanks,

Chip

@rickkas7, very exciting regarding read-only access token - are you able to share any rough timeline for when that might be available to non-Enterprise plans?