Pushing data from the photon into google sheets


#62

Howdy,
I just did this again yesterday. I had no problems but I did have to log in as usual. Considering that I have about half a dozen google sheets with this code it may take a bit when doing it the first time. I remember when I did it for the very first time I had to futz with it. Meaning saving, deploying, hitting the test button over and over again until it just worked. One time I also had to switch the Webhook from Webform to JSON and then back to Webform. I would give it a few hours then try it again.

I know this sounds repetitive but you could try a new sheet and follow the instructions over again. Just to make sure there was not a missed step. New Sheet, New Script, New Webhook. Do it all over again. Once you get it you will be all set.


#63

Thanks. OK - I will try again later when the day job has been completed!


#64

I re-did it and I am getting a HTTP 302 error. It appears there is a problem with the javascript - which isn’t my field at all.

{“result”:“error”,“error”:{“message”:“Unexpected token: N”,“name”:“SyntaxError”,“fileName”:“Code (mesh-heatmap)”,“lineNumber”:44,“stack”:"\tat Code (mesh-heatmap):44 (handleResponse)\n\tat Code (mesh-heatmap):22 (doPost)\n"}}

That line in the script is this:

var json_obj = JSON.parse(e.postData.contents);

I guessed that the JSON is not correct to be parsed? - I have corrected a missing } pair but the thing is still kicking out the same error.


#65

can you post a screenshot of your javascript so we can see lines around 44? thanks


#66
//  1. Enter sheet name where data is to be written below
var SHEET_NAME = "mesh-heatmap";

//  2. Run > setup
//
//  3. Publish > Deploy as web app
//    - enter Project Version name and click 'Save New Version'
//    - set security level and enable service (most likely execute as 'me' and access 'anyone, even anonymously)
//
//  4. Copy the 'Current web app URL' and post this in your form/script action
//
//  5. Insert column names on your destination sheet matching the parameter names of the data you are passing in (exactly matching case)

var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service

// If you don't want to expose either GET or POST methods you can comment out the appropriate function
function doGet(e){
  return handleResponse(e);
}

function doPost(e){
   return handleResponse(e);
}

function handleResponse(e) {
  // shortly after my original solution Google announced the LockService[1]
  // this prevents concurrent access overwritting data
  // [1] http://googleappsdeveloper.blogspot.co.uk/2011/10/concurrency-and-google-apps-script.html
  // we want a public lock, one that locks for all invocations
  var lock = LockService.getPublicLock();
  lock.waitLock(30000);  // wait 30 seconds before conceding defeat.

  try {
    // next set where we write the data - you could write to multiple/alternate destinations
    var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
    var sheet = doc.getSheetByName(SHEET_NAME);

    // we'll assume header is in row 1 but you can override with header_row in GET/POST data
    var headRow = e.parameter.header_row || 1;
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    var nextRow = sheet.getLastRow()+1; // get next row
    var row = [];

    var json_obj = JSON.parse(e.postData.contents); 
    
    // loop through the header columns
    for (i in headers){
      if (headers[i] == "Timestamp"){ // special case if you include a 'Timestamp' column
        row.push(new Date());
      } else { // else use header name to get data
        row.push(json_obj[headers[i]]);
      }
    }

    sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);

    // return json success results
    return ContentService
          .createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
          .setMimeType(ContentService.MimeType.JSON);
  } catch(e){
    // if error return this
    return ContentService
          .createTextOutput(JSON.stringify({"result":"error", "error": e}))
          .setMimeType(ContentService.MimeType.JSON);
  } finally { //release lock
    lock.releaseLock();
  }
}

function setup() {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    SCRIPT_PROP.setProperty("key", doc.getId());
}

This is the error now:

{"result":"error","error":{"message":"Unexpected token: e","name":"SyntaxError","fileName":"Code (mesh-heatmap)","lineNumber":44,"stack":"\tat Code (mesh-heatmap):44 (handleResponse)\n\tat Code (mesh-heatmap):22 (doPost)\n"}}

Thanks


#67

right, but since here we do not see the lines, it would be cool if you post a screenshot
thanks!


#68

Sorry not sure I understand - which lines? I have posted the script and the error response. Do you mean the webhook setup?


#69

I had this same error code before. I tried google searches and forums but still could not find anything on that error code. So what I did was delete the sheet completely, start a new sheet. Re-copy the script code from the tutorial and again start over. I ran into a number of problems on the first try. Trust me, It will work!

Cheers,

//  1. Enter sheet name where data is to be written below
        var SHEET_NAME = "Sheet1";

//  2. Run > setup
//
//  3. Publish > Deploy as web app
//    - enter Project Version name and click 'Save New Version'
//    - set security level and enable service (most likely execute as 'me' and access 'anyone, even anonymously)
//
//  4. Copy the 'Current web app URL' and post this in your form/script action
//
//  5. Insert column names on your destination sheet matching the parameter names of the data you are passing in (exactly matching case)

var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service

// If you don't want to expose either GET or POST methods you can comment out the appropriate function
function doGet(e){
  return handleResponse(e);
}

function doPost(e){
  return handleResponse(e);
}

function handleResponse(e) {
  // shortly after my original solution Google announced the LockService[1]
  // this prevents concurrent access overwritting data
  // [1] http://googleappsdeveloper.blogspot.co.uk/2011/10/concurrency-and-google-apps-script.html
  // we want a public lock, one that locks for all invocations
  var lock = LockService.getPublicLock();
  lock.waitLock(30000);  // wait 30 seconds before conceding defeat.

  try {
    // next set where we write the data - you could write to multiple/alternate destinations
    var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
    var sheet = doc.getSheetByName(SHEET_NAME);

    // we'll assume header is in row 1 but you can override with header_row in GET/POST data
    var headRow = e.parameter.header_row || 1;
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    var nextRow = sheet.getLastRow()+1; // get next row
    var row = [];
    // loop through the header columns
    for (i in headers){
      if (headers[i] == "Timestamp"){ // special case if you include a 'Timestamp' column
        row.push(new Date());
      } else { // else use header name to get data
        row.push(e.parameter[headers[i]]);
      }
    }
    // more efficient to set values as [][] array than individually
    sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
    // return json success results
    return ContentService
          .createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
          .setMimeType(ContentService.MimeType.JSON);
  } catch(e){
    // if error return this
    return ContentService
          .createTextOutput(JSON.stringify({"result":"error", "error": e}))
          .setMimeType(ContentService.MimeType.JSON);
  } finally { //release lock
    lock.releaseLock();
  }
}

function setup() {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    SCRIPT_PROP.setProperty("key", doc.getId());
}

#70

hey, I meant line 44 of the script. You posted the code, however sometimes if I copy paste it in a local editor from something you copy-pasted from Google into this forum, it may or may not be the same line number. That is the reason why I was asking for a screenshot from the script right into the Google Docs interface where is declared.
But as @GasGen mentioned, copy pasting the script again does not hurt…
Thanks and I hope you are closer!
Gustavo.


#71

@GasGen @gusgonnet Thanks both for the encouragement. I used the script provided by @GasGen and left the Content-Type as default and it started to work.

Not quite what I was expecting though - maybe this is the script?

I was expecting just the field value - a number rather than all this {"NumBlobs":"{{{77}}}"}


#72

Yeaaaaaaa your almost there.

It’s not the script that is affecting your data its the way you are publishing it or matching it to your sheet. Now with that said I am only guessing as others here have much more knowledge than I do. Here is how my data gets published and a screen shot of the matching webhook to google sheets.

See these examples.

if (Particle.connected()) {
    publishStateString[0] = '\0';         // make sure the string is reset
    if (millis() - lastpub2 > 600000) {   // time for a full report?
      lastpub2 = lastpub = millis();      // reset BOTH timers as the full report also features the short report data
      snprintf(publishStateString         // use this for losant format, removed ADC
              , sizeof(publishStateString)
              , "{ \"cf\":\"%.1f\", \"v\":\"%.f\", \"c\":\"%.2f\"}" 
              ,flow, soc, fuel.getVCell() );
    }
    else if (millis() - lastpub > 60000) { // time for an short report
      lastpub = millis();
      snprintf(publishStateString
              , sizeof(publishStateString)
              , "{ \"cf\":\"%.1f\"}"
              , flow ); 
    }
    if (publishStateString[0])  // if the string was populated send it
      Particle.publish("POU", publishStateString, 60, PRIVATE, NO_ACK); //NOTE THIS IS FOR WEBHOOK POU

Now the screenshot of the matching google sheets webhook.

Screenshot of headers on google sheet. If there is no data being sent then the cell is blank. Make sure your cell headers match the webhook.

Good luck. You got this man. :slight_smile:


#73

Super - works exactly how I was hoping it would. Thanks for sharing your script and settings - main change was using the default form fields and extra rows for own data and also the format of the published message.

Just need to solve a google sheets challenge of turning a base64 encoded binary array back into a binary array. In Excel it requires a VBA subroutine so possible it can done in the same POST function script?

And this is just the data collection part to be able to perform machine learning!


#74

Seen this?


#75

Awesome. I knew you would get it. I actually can’t take any credit for the help. I would still be lost if it wasn’t for the help of @ScruffR, @RWB, @gusgonnet, and @peekay123. They saved me from myself, many times. @ScruffR actually wrote that publishing structure to help me get things going. Side note and major bonus, if you want to try ubidots the publish is already formatted to be read by their side. All I had to do was follow the short instructions, include the library, and create a ubidots webhook with the same name as the google one. Now I have data going to google sheets for backup and easy download, along with some fancy widgets over on Ubidots. I think I read there is a script for Ubidots to Google Sheets, but I like having redundant data backups and or collection.

Cheers
Tom


#76

happy to hear is working now!

hey not so fast! what kind of ML are you planning on doing?


#77

Classification and Pattern Recognition - I have a number of AMG8833 thermopile array sensors. They are there to detect human presence in a room space. I am trying to work out whether to do more analysis on the device or in the cloud - I am sending a Base64 encoded binary image to help with the classification plus analysis of the image with blob detection (essentially mass and location) plus other features - not sure yet what is significant. Also interested in @bsatrom project to port TensorFlow Lite Micro to Particle.


#78

No I hadn’t seen Joshua Tzucker’s site - thanks, that’s exactly what I was after. :running_man: better get going!


#79

Hey,

I was wondering if you ever run into an integration error like this. It seems to be happening every so often.

Thank you for your help!


#80

HTTP 409 would indiacte a request conflicting with the current state of the server.
What is your webhook doing?
Is the respective resource (e.g. file) also used by others or otherwise?


#81

Hi @ScruffR the webhook is sending json data to the google sheet and is being received by the script noted in this thread. I commented out the wait lock for the time being and I’m no longer getting the 409 errors, but now I’m getting a clock symbol next to the event logs under that integration. Do you know what that might mean? Here is what it looks like.