Pushing data from the photon into google sheets

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

Thanks for the encouragement.

I have followed just the instructions posted here, all of the them including the html and .js

http://railsrescue.com/blog/2015-05-28-step-by-step-setup-to-send-form-data-to-google-sheets/

Although it appears the script attached to the sheet runs I still get the same error. So there appears to be a problem with Google or the script.

I like you am a newbie and the web programming completely befuddles me. There are too many moving parts and one mistake makes the whole thing fail. Might have to find a local pro to look at this.

I noticed that @awardblvr has the Auth portion of the web hook filled in. Is this username and passwork the particle login or the Google account login? Is it supposed to be filled in? Mine is blank.

Don’t give up yet. If you are following the google script instructions then it will eventually work. I had the same problems.

My tips. Make sure you have Timestamp as the title of row 1 column A. Then start testing the script when you publish it in the google sheets app script page. If you get errors do not worry. Happens to me every time. I found that if I close everything. Then open it back up, copy the code from the tutorial and past it over again, run the setup, publish, and test. It will eventually work. Don’t ask me why but it does for me. I have to do this every time. I know it does not make any sense but I fought it all day one time and now I just do the same procedure twice and the dang thing works. If I don’t it comes up with errors.

1 Like

Sorry I saw your question after I posted. I would start with Gus’s post and link before trying to do multiple entries into one string like awardblvr is doing. I think his post is tailored more towards getting multiple data entries sent via one webhook. I am still in kindergarten when it comes to webhooks, JSON, C++, ETC. I am very close to getting multiple entries into a JSON file but not there yet. Like I said start with one, something simple and then go from there.

Keep asking for help here. The people on this board are extremely helpful, especially to newbies like you and me. I didn’t even know what C++ was or webhooks two weeks ago. Now I am logging data from a 4-20 ma sensor and logging into google. Granted it took me a few bottles of wine and a lot of curse words but with the help here I got it… kind of. lol.

You got this man.

2 Likes

One more thing before I peace out for the night. I read all the questions and comments on the Hackster page that Gus posted to. I added this post to hopefully help noobs like me. Its basically the same thing as I noted above but thought it could not hurt to post it here as well.

I wanted to share my experience with this. It has been thrilling and incredibly frustrating. In my experience, if you are getting “undefined” in your columns switch your webhook from JSON to Web Form, or from Web Form to JSON and enter the same parameters. I have two identical apps with identical webhooks. Only the names are different. One will post to sheets with JSON, the other only Web Form. Also if you are having trouble with the google script app not testing correctly try pasting the code again and running through the setup process. This has happened to me every time and I find if I just past the code over again it fires right up. Good luck and keep at it. Cheers.

A few dozen tries and a lot less hair, but I’m now pushing data to Google Sheets.

I’m tired, but happy. I’ll explain tomorrow but in short it was a script editor thing, how you run and deploy/update scripts is important.

Going to sleep like a rock tonight :slight_smile:

Thanks everyone for your help

2 Likes