Pushing data from the photon into google sheets


#22

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.


#23

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”}


#24

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?


#25

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


#26

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???


#27

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


#28

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!


#29

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.)


Webhook builder to google sheets
Webhook builder to google sheets
#30

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


#31

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


#32

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.


#33

Thanks for getting back so quickly,

It looks like this


#34

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


#35

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


#36

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.


#37

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


#38

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.


#39

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.


#40

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.


#41

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.