Google AppScript to create REST API End Point for Particle Publish/Webhook Google Sheet logging


#1

I have documented a method to allow direct logging of particle data to google sheets using publish and webhooks.

My approach is a little different to some of the others as I required an implementation that allowed Photons to go into deep-sleep between sensor readings.

I would welcome any feedback and improvements.

Thanks, Dean


#2

I just wanted to point out that while I think this is really cool and interesting work, there is a much simpler way of getting the data into a Google spreadsheet using IFTTT that I wrote about here:

Both ways are good and I know you mention the simpler way in your readme. I would say that your method is certainly more advanced but beginners might have an easier time with IFTTT. Also by using IFTTT you can at least separate out the event name, timestamp, device, and event data into separate spreadsheet columns, so some of the pains associated with other methods are not there.

Here is some test data from last summer that I have saved–there are a lot of computers in my home office so it really was 90+ degF.

The columns are Event Name, Event Data, Device Name, and Timestamp. The spreadsheet has formatted the timestamp into a more human readable format, but you can turn that off.

Thanks for sharing this, especially your firmware!


#3

Thanks @bko for your feedback. Certainly IFTTT may be more familiar for some and will be a little simpler to setup for beginners but IMO not by much. I think my approach has a few advantages that beginners may be seeking;

  • A single publish results in multiple sensor values (eg temp, humidity, wind-speed and wind-direction) being placed into separate columns/cells within the worksheet - I couldn’t work out how to do this with IFTTT
  • The Google AppScript setup only needs to be completed once and after then each device has its own tab/sheet within the log, each with their own data-sets.

I have 5 photons capturing weather related data, so I see the following

http://imgur.com/w5FfX30

I guess everyone has varying requirements. The world of IoT is moving forwards in leaps and bounds and generating all this data but cost-effective (not free) simple methods by which to store and retrieve structured data still seems one-step away.


#4

One issue with the .json for the webhook is that you don’t have quotes around the parameter names, ie eventName, url, requestType. Without that I was getting this error when trying to create the webhook with cli v1.10.0:
tryParse error [SyntaxError: Unexpected token e]
Please check your .json file for syntax error.

BTW, I already have IFTTT setup but I found it occasionally misses event. Probably not a big deal in most cases but I’m using it for event logging which messes up my tracking. I’m also using a Google Sheets script to frequently poll an event variable (they aren’t frequent) but lately it takes google so long to access particle variables that it uses up my daily quota. Oddly the variable calls return near instantly when I try it and did for Google for the past year or two. So hopefully the webhook approach will get around these issues. Thanks for sharing.

–Update, turns out this method misses the same events as IFTTT and I’m not sure why. I added a delay after the publish but that didn’t help. Occasionally a second publish happens right after but it’s a different event name and that one doesn’t seem to get missed.


#5

How do I get timestamps to the second?


#6

Have you tried looking at the docs?

Time.local()


#7

The “published_at” field of the timestamp is sent from the Particle cloud with millisecond accuracy: ,"published_at":"2018-09-19T16:01:25.476Z"

The problem is convincing IFTTT and Google Sheets to use that accuracy.

If you use your own service that would be easy but I don’t know which part of the intergration (IFTTT or Google) is trimming the timestamp down.

You can also include your own timestamp in the string you send.


#8

This is probably a dumb question. How do I publish a timestamp?

This doesn’t work:

Particle.publish("comet", Time.local());


#9

Particle.publish() requires a string as payload parameter, but Time.local() returns an int.
Try looking at Time.format() or snprintf() to create a composite string that doesn’t only feature one type of data.