Pushing Data To Google Sheets - New Thread

Cloud Integrations
14

@all,

I think I may have solved the functionality - albeit in a very inelegant way.

I was unable to get the document properties approach to work as two devices connecting at the same time would get assigned the same row numbers. So, now each device, when it connects builds a giant array of the entire sheet and then searches for the deviceID. If found, it overwrites that row. If not, it appends to the end. I am glad that I do not have to pay for memory or compute here as the size of the array that each device creates will grow as the sheet grows. Will keep thinking of a better solution and open to suggestions.

In the mean time, here is the latest code:

/**
 * @OnlyCurrentDoc
 */

function test() {
  
  var e = {};
  e.parameter = {};
  e.parameter.event = 'GoogleSheetsExport';
  e.parameter.data = '[1,2,3,4,5,6,7,8,0.9]';
  e.parameter.coreid = 'e00fce68ae51b4fb8e2e05b4';
  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 = '{{Your API Key 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 range = sheet.getDataRange();
  var values = range.getValues();
  var rowContents = [e.parameter.coreid, deviceName, publishedAt];
  rowContents = rowContents.concat(dataArray);

  for(var rowIndex = 0; rowIndex < values.length; rowIndex++) {
    Logger.log(values[rowIndex][0]);
    if (values[rowIndex][0] == e.parameter.coreid) {
      rowContents = rowContents.concat(rowIndex);               // Concatenate the row number - for testing - to be removed
      sheet.getRange(rowIndex+1, 1, 1, rowContents.length).setValues([rowContents]);    // Overwrite the row
      Logger.log('Existing Entry - Overwritten');
      Logger.log(rowIndex);
      break;
    }
    else if (rowIndex == values.length -1) {
      rowContents = rowContents.concat(rowIndex);               // Concatenate the row number - for testing - to be removed
      sheet.appendRow(rowContents);                     // Add this row to the end of the sheet
      Logger.log('New Entry - Appended');
    }
  }

  var result = {};
  result.ok = true;

  return ContentService.createTextOutput(JSON.stringify(result))
    .setMimeType(ContentService.MimeType.JSON);
}

Hope this helps folks.

Chip

2 Likes