Data to Google Sheets (more complex JSON)

[davidgraham]

I’ve been experimenting with using the method in AN011 to send data to Google Sheets with a webhook. I have it working, to a point. I’m using the method described in ‘Setting Columns by Key’.

My JSON payload is formatted like this example:

{
  "time": {
    "timeSec": 1647441595,
    "timeStr": "Wed Mar 16 14:39:55 2022"
  },
  "device": {
    "id": "e00fce68b6eb9c94xxxxxxxxxxx",
    "name": "sps30-boron",
    "runMode": "Sleepy",
    "sampInt1": 3,
    "sampInt2": 3,
    "pubInt": 15,
    "opMode": "Normal",
    "numReadings": 5,
    "intTempC": -128,
    "lipoV": 0.07,
    "lipoSoC": 72.41,
    "vinV": 0.47
  },
  "sht85": {},
  "sps30": {
    "sn": "8D7A13B3F16FADAC",
    "pm1": 16.66,
    "pm2.5": 18.5,
    "pm10": 19.36,
    "typ_sz": 0.59,
    "desc": "Description"
  }
}

You can see that each attached sensor has its own object (with additional objects for the time and diagnostic info).
[Side note 1: the reason for structuring the JSON payload in this way is that I can build it in blocks, so the function that reads each sensor builds the JSON object for that sensor, and then it’s put together when it’s time to publish].
[Side note 2: the reason of storing the time is that I’m using PublishQueueAsyncRK to queue publish events when the device is not connected to the Particle Cloud, so the actual time they are published won’t represent the time the data were collected].

If I create spreadsheet column headings with the names time, device, sps30 etc, the appropriate cell is populated with a string containing the corresponding JSON object, as expected. So far, so good.

However, I don’t want cells populated with JSON objects, I want to extract the individual data values for each sensor. So, for example, I would like column headings relating to the SPS30 sensor: sn, pm1, pm2.5 etc. I’m sure this is possible, but I don’t know how to modify the Javascript to achieve this (I have never coded Javascript).

Any pointers would be gratefully received!

The script I’m currently using is essentially unmodified from the application note (with the exception of the test JSON payload):

function test() {
  var e = {};
  e.parameter = {};
  e.parameter.event = 'sheetTest1';
  e.parameter.data = '{"time":{"timeSec":1647440509,"timeStr":"Wed Mar 16 14:21:49 2022"},"device":{"id":"e00fce68b6eb9cxxxxxxxxxx","name":"sps30-boron","runMode":"Sleepy","sampInt1":3,"sampInt2":3,"pubInt":15,"opMode":"Normal","numReadings":5,"intTempC":-128.00,"lipoV":0.07,"lipoSoC":73.40,"vinV":0.49},"sht85":{},"sps30":{"sn":"8D7A13B3F16FADAC","pm1":17.88,"pm2.5":19.68,"pm10":20.43,"typ_sz":0.61,"desc":"Description"}}';
  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 publishedAt = new Date(e.parameter.published_at);

  var dataObject = {};
  try {
    dataObject = JSON.parse(e.parameter.data);
  }
  catch(e) {
  }

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = ss.getActiveSheet();

  var labelRange = sheet.getRange("A1:1");

  var labels = labelRange.getValues();

  var row = [];

  for(var ii = 0; ii < labels[0].length; ii++) {
    var label = labels[0][ii];
    if (label === 'Device ID') {
      row.push(e.parameter.coreid);
    }
    else 
    if (label == 'Published At') {
      row.push(publishedAt);
    }
    else {
      Logger.log('label=' + label + ' value=' + dataObject[label]);
      if (dataObject[label] != undefined) {
        row.push(dataObject[label]);
      }
      else {
        row.push('');
      }
    }
  }

  sheet.appendRow(row);

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

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

[dreamER]

Hi,
here is a dirty and quick method how to obtain the data for key sps30 in your dataObject

.

function test() {
  var e = {};
  e.parameter = {};
  e.parameter.event = 'Test';
  e.parameter.data = '{"time":{"timeSec":1647440509,"timeStr":"Wed Mar 16 14:21:49 2022"},"device":{"id":"e00fce68b6eb9c94701fccdb","name":"sps30-boron","runMode":"Sleepy","sampInt1":3,"sampInt2":3,"pubInt":15,"opMode":"Normal","numReadings":5,"intTempC":-128.00,"lipoV":0.07,"lipoSoC":73.40,"vinV":0.49},"sht85":{},"sps30":{"sn":"8D7A13B3F16FADAC","pm1":17.88,"pm2.5":19.68,"pm10":20.43,"typ_sz":0.61,"desc":"Description"}}';
  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 publishedAt = new Date(e.parameter.published_at);

  var dataObject;
  try {
    dataObject = JSON.parse(e.parameter.data);
    //console.log('label' ,dataObject.device.id);
  }
  catch(e) {
  }
 
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = ss.getActiveSheet();
  //var sheet = ss.getSheets()[0];
 // var labelRange = sheet.getRange(7,1);

  //var labels = labelRange.getValues();
  var i = 1;
  var row = [];
  for (key in dataObject.sps30){
     console.log(key);
     console.log(dataObject.sps30[key]);


        // all in 6 rows and 2 columns - (parallel)

     var labelKeys = sheet.getRange(i,1);
     var values = sheet.getRange(i,2);
     
     labelKeys.setValue(key);
     values.setValue(dataObject.sps30[key]);
     
         // All in one row (last) - (in series)
     
     row.push(key);
     row.push(dataObject.sps30[key]);

     i++;
}
  sheet.appendRow(row);

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

  return ContentService.createTextOutput(JSON.stringify(result))
}

and results:

I don’t know too much about google sheets but, as you mentioned, looks like JS so should work

hope this help a little or give you some hints

Please note I changed the: e.parameter.event = 'Test';

[Mjones]

Just a word of caution, its good practice to remove your device ID when pasting your code.

[davidgraham]

Thanks Michael. A useful reminder. I have obfuscated it now.

[davidgraham]

Thank you. There’s enough in this example for me to achieve what I need