Pushing data from the photon into google sheets

Hi,
if you follow TO THE LETTER this tutorial:

I believe you'll be able to do what you want.
But remember when I said to the letter? The reason is that you may feel tempted to change a field name or something else, and it will not work at the end (and it will be hard to troubleshoot - so redoing the steps will most likely be the cure).

Good luck!
Gustavo.

What do you mean by this? I have never had to sign up for any test account, I use my plain google account...
Just curious
Gustavo.

And when I follow the provided links I end up at some FAQs that state this (translated from German as this is not displayed when viewing the page in English)

oh I think I get it now, your comment may apply to google docs via IFTTT, not to my tutorial on Hackster.

Am I right?

Yes, sorry. It was in response to @ransinha 's post - I didn’t check the relevance in connection to the thread’s original topic.

But maybe you can help her out.

1 Like

I gave up on IFTTT. I was bored with what it could do in about 5 minutes. The method described in the tutorial by GusGonnet is far better, and worth the effort. I use it A LOT.

Follow his tutorial carefully. Follow every link because there are additional instructions there. Read the comments too, The trials of others will make you stronger.
You will need to make these things, and pretty much in this order.

  1. A Google Sheet with labels at the top of each column where your data will go.
  2. A Google Java script which controls the behavior of the Sheet. The tutorial links to a page which shows how to do this. The script will be deployed as a web app which gets hit by your webhook.
  3. Particle firmware which publishes JSON strings like this:
    {
    variable name : value
    other variable: value2
    }
  4. A webhook which hits your Google Web App, is web form type, with query parameters like this:
    {
    GoogleSheetLabel1: {{variable name}}
    GoogleSheetLabel2: {{other variable}}
    }

Then value, and value2 are written to the Google Sheet in a POST.
This is what a publish string from my power monitor looks like:

{"data":"{"Date":"20180116", "Time":"14:50", "Volts":"238.70", "Amps":"2.17", "Watts":"516.21", "WattHrs":"8494.18"}","ttl":60,"published_at":"2018-01-16T22:49:58.634Z","coreid":"52003aBLAH373132","name":"QHour"}

1 Like

I’ve managed to get data in my Google Sheet but I was wondering, is there also a way to remove this data again, using my Photon and Webhook?

I don't know of any way to do that.
Gustavo.

Update: Perhaps Google changed something in their authorization process since this thread was last touched???

I keep getting

HTTP/1.1 401 Unauthorized
Cache-Control: no-cache, no-store, max-age=0, must-revalidate
Pragma: no-cache
Expires: Mon, 01 Jan 1990 00:00:00 GMT
Date: Fri, 27 Apr 2018 14:46:19 GMT
Content-Type: text/html; charset=utf-8
x-chromium-appcache-fallback-override: disallow-fallback
X-Content-Type-Options: nosniff
X-Frame-Options: SAMEORIGIN
X-XSS-Protection: 1; mode=block
Server: GSE
Alt-Svc: hq=":443"; ma=2592000; quic=51303433; quic=51303432; quic=51303431; quic=51303339; quic=51303335,quic=":443"; ma=2592000; v="43,42,41,39,35"
Accept-Ranges: none
Vary: Accept-Encoding
Transfer-Encoding: chunked

And yet the script is set for :

I do have webhooks integration settings set:

Is Particle up-to-date with Google’s always-caning authentication? Or, what could be wrong? What could I do to troubleshoot?

I DID copy/paste the URL from the script into a new browser (Safari) and ran it and got:

So it seems the script is working???

Hi, it could be.
However, have you tried setting the Who has access to the app: field to anyone, even anonymous, I see you only have it set to anyone:

image

OK… Thanks… Will try… However, I DID try EVERYTHING from the actual account user id where google-docs == particle-dev-userid… and everything seems to WORK.

BUT… I still don’t completely see the maping of variables from the published:

{"data":"This is my second logged message","ttl":60,"published_at":"2018-04-27T17:38:16.266Z","coreid":"bla-bla-bla","name":"googleDocs"}

through the json assignment table
default:
’ {
“name”: “{{{PARTICLE_EVENT_NAME}}}”,
“data”: “{{{PARTICLE_EVENT_VALUE}}}”,
“coreid”: “{{{PARTICLE_DEVICE_ID}}}”,
“published_at”: “{{{PARTICLE_PUBLISHED_AT}}}”
}’

or a custom one

{
  "log_msg": "{{{PARTICLE_EVENT_VALUE}}}"
}

to my column named:

Timestamp, log_msg, name, data, coreid

Thanks!

Alright folks… Got it all working… But there are either some changes in the google API or errors in the original code examples which are wrong. Here is the truth… Here is what WORKS as of this posting date.

You need to convert the contents to a json object to get the key/value pairs in a usable way.

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

and then access them with

row.push(json_obj[headers[i]]);

So… the Google Sheets Javascript code example in the referenced site http://railsrescue.com/blog/2015-05-28-step-by-step-setup-to-send-form-data-to-google-sheets/ needs to be updated to:

//  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 = [];

    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());
}

to get things working in the basic case where you use simple naming in the webhooks page (custom json):

{
  "msg_log": "{{{PARTICLE_EVENT_VALUE}}}"
}

And my column name in the spreadsheet is “msg_log”

OK… Now thigs are working!

(PS… I’m A C++ & Pyton developer in real-life… My Javascript knowledge is almost nil until this project. Others can improve substantially, for sure. But at least the basic use case is exemplified here.)

2 Likes

nice update, I have added a note in the original article in Hackster to link to it.
Thanks!
Gustavo.

1 Like

Thank you for the tutorial, nice work.

Although I seem to have messed something up but I’m not sure where to look.

It appears I’m reaching the script on google sheets, which is awesome but the script errors out because it can’t find the correct data.

It seems that the web hook is not including any data, am I reading that correctly? And if so where might I look for my faux pas

Thanks in advance for any help
Joerg Seiler

Hi Joerg,
how about we start with your webhook definition? Does it look like this?


Post a screenshot as well if you don’t mind
Gustavo.

Thanks for getting back so quickly,

It looks like this

Just to mention, I have used the script as updated by @awardblvr

then perhaps you should follow his advice on how to declare the webhook? Like this:

1 Like

I’m not sure what isn’t working, probably messed up beyond repair.

For now I’ve deleted both the google sheets web app and the web hook. I’m going to try this again from the beginning tonight.

1 Like

Howdy,
I get the same problem time to time. Even when I duplicate the webhook and code. What has worked for me on both occasions is to switch the webhook from either Web Form to JSON, or vise versa. Then try again. Not sure why but this has solved the problem both times I have tried it.

In an attempt to prove to myself that I was learning I created duplicate webhooks, sheets, and apps. The only difference are the names. Same code, etc etc. I have one that only posts using Web Form and the other only JSON. I was beating my head against the desk trying to figure it out. Did what you did and deleted everything, started over, no luck. Then I tried the simple switch and it worked. I am sure there is a more logical explanation that one of the more intelligent people in this thread can offer… I am a complete newbie at all this. So please only take my suggestion as such. Good luck and let me know if I can be of any less help. :slight_smile:

Cheers,
Tom