Pushing Data To Google Sheets - New Thread

Cloud Integrations
5

@rickkas7 ,

I am assuming that the API User feature is what you were referring to and this is a great idea. The only issue at this point is that, as I understand it, I would need a separate sheet for each project as I cannot set an organizational API user who can look up device names. @mike.sheward - Is this correct?

Over time, the Google Sheet was getting way too long as the script I posted above always appends a new row. I determined that it would be more useful to have each device occupy one row in the spreadsheet and new updates would overwrite the row. That way, the sheet did not get too long and always reflected the latest data (with timestamp).

Here is the code I used to accomplish this - the deviceID / row relationship is stored in document properties. There may be a better way but this seems to work.

I replaced this line in the sketch above:

sheet.appendRow(row);

with this:

  // This is the new section that determines if a row should be appended or overwritten
  // Row numbers are printed in the last column for testing and will be removed
  var documentProperties = PropertiesService.getDocumentProperties();           // Store properties in the document
  var rowIndex = documentProperties.getProperty(e.parameter.coreid);            // Index will tbe the deviceID

  if (!rowIndex) {                            // Row information not found - new row needed
    rowIndex = sheet.getLastRow() + 1;        // Get the last row number
    documentProperties.setProperty(e.parameter.coreid, rowIndex);    // Store the row number in the properties for next time
    row = row.concat(rowIndex);               // Concatenate the row number - for testing - to be removed
    sheet.appendRow(row);                     // Add this row to the end of the sheet
    Logger.log('New Entry - Appended');
  }
  else {
    row = row.concat(rowIndex);               // Concatenate the row number - for testing - to be removed
    sheet.getRange(Math.floor(rowIndex), 1, 1, row.length).setValues([row]);    // Overwrite the row
    Logger.log('Existing Entry - Overwritten');
  }

As I was testing, I realized that I may need to periodically reset the document properties so, I added this function which I can run from the App Script editor:

function propertiesReset() {
  // Deletes all document properties.
  var documentProperties = PropertiesService.getDocumentProperties();
  documentProperties.deleteAllProperties();
  Logger.log('Cleared All Document Properties');
}

I hope this is helpful.

Chip

2 Likes