Writing to Google Spreadsheet using only a webhook

[rickkas7]

Did you know your can write directly to a Google Spreadsheet using a webhook, without needing to use a third-party service like IFTTT, or a Google Cloud Function?

And you can execute Javascript (Google Apps Script) to manipulate the data before saving it. Since it’s part of G Suite Sheets, you aren’t charged extra Google Cloud Platform charges!

There’s now an application note for it:

https://github.com/particle-iot/app-notes/tree/master/AN011-Publish-to-Google-Sheets

Topics include:

• Getting Started
• One Sheet Per Device
• Mapping Device IDs to Names (manually)
• Mapping Device IDs to Names (Using Particle Cloud API)
• Separate Spreadsheets by Device ID
• Setting Columns by Key

[rayz]

@rickkas7, thanks for another great tutorial. This was well-timed for me as my Blink-connected basement humidity monitor had recently stopped working, so I decided I’d simplify things by going directly to Google Sheets. This was my first time using a webhook and it was painless.

It is also pretty straightforward to graph the Google Sheets data. But I can’t find a way to make a scrolling graph that would, say, display the last 24 hours of data. Do you know if this is possible?

Thanks

[Magrathea]

Thanks @rickkas7 that’s really helpful!
I’m looking for a way to do some kind of an opposite thing:

1. Have a spreadsheet that will hold some keys+values
2. With a click of a button it will be passed back to the electron with a webhook.

I was also thinking about creating a simple web form to do this but I want to keep track and save the last values somewhere.
Do you have an idea for a quick solution to that?

[Magrathea]

Actually… I got it to work partially.
I created a new sheet in the test spreadsheet called “settings”.
I created a table like this one:

Parameter	Value
a	0
b	123
c	456
d	789

and I added this piece of code at the end of “doPost” function that replaces the last three lines (result {“ok”:true}…)

  var spreadsheetId = "<<Enter your spreadsheeet code here>>";
  var rangeName = 'settings!A2:B5';
  var values = Sheets.Spreadsheets.Values.get(spreadsheetId, rangeName).values;
  if (!values) {
    Logger.log('No data found.');
    return ContentService.createTextOutput({"a":"0","b":"1","c":"2","d":"3"})
    .setMimeType(ContentService.MimeType.JSON);
  } else {
    for (var row = 0; row < values.length; row++) {
      Logger.log(' - %s, %s', values[row][0], values[row][1]);
    }
    var result = {
        "a" : values[0][1],
        "b" : values[1][1],
        "c" : values[2][1],
        "d" : values[3][1]
      };
    Logger.log(JSON.stringify(result));
    return ContentService.createTextOutput(JSON.stringify(result)).setMimeType(ContentService.MimeType.JSON);
 

Now it also logs the content that was sent (in apps script control panel) and sends back the response to Particle API in JSON and that can be read back in the device using another great library by @rickkas7 :

rickkas7/JsonParserGeneratorRK

Now I need to see how I can create some kind of form to interact with Apps Script…