Webhook builder to google sheets


#1

Friends,

I am attempting to post data to a google sheet from a particle device. I currently have a ambient temp & humidity sensor and a water temp sensor and want to have that data post to a google doc every 2 hours. I only have three values right now but I am wanting to make it easy to add more values if at all possible.

This is my first try interfacing with webhooks so please be brutally honest with what i am doing wrong. I have found a number of helpful tutorials that have helped me build the webhook but when the Particle.publish(data,dataStr) publishes i get an error in the particle logs(screenshot below).

The tutortial i found the most helpful, and followed the most closley is this one https://www.hackster.io/gusgonnet/pushing-data-to-google-docs-02f9c4

It was very helpful but i was (still am) confused on the formatting of the data in my particle web ide code, and how that all interacts with the JSON and the google script code.

I don’t want to use any third party services, as i want to keep it as simple as possible. Here is a screen shot of the webhook and where i think I am wrong.

I currently have no code in the google sheet, i only have it deployed as a web app,

here is the particle code i am using.

// This #include statement was automatically added by the Particle IDE.
#include "DS18.h"
// This #include statement was automatically added by the Particle IDE.
#include <Adafruit_DHT.h>
// This #include statement was automatically added by the Particle IDE.
#include <elapsedMillis.h>

#define ambientPin D4
#define DHTTYPE DHT11
#define SprayPin D2
#define WaterTempPin D3

/****************************INPUTS*****************************************/
int32_t OnTime = 10000;   //Time in milliseconds, how long you want to spray for
int32_t OffTime = 60000;    //Time in milliseconds, how long to be off in between cycles

/***************************************************************************/

elapsedMillis timer0;       // a running timer to keep track of ON time
bool Spraying = false;      // a flag used to tell us if the pump is on
int32_t mainOnTime = -1;
int32_t mainOffTime = -1;
int32_t resetTime = 40*24*3600*1000;


double ambTempF = -1;       //Setting up ambient sensor
double ambHumid = -1;
DHT dht(ambientPin, DHTTYPE);

double waterTempF=-1;       //Setting up water temp sensor
double waterTempC=-1;
DS18 sensor(WaterTempPin);

String resultstr=String(64);    // String to be used in the particle publish

void setup() {
    
    Serial.begin(9600);
    pinMode(SprayPin, OUTPUT);      // Telling the controller this pin will be used as a Digital output
    pinMode(ambientPin, INPUT);
    digitalWrite(SprayPin, LOW);    // On start up keep output off
    pinMode(WaterTempPin,INPUT);
    
   
    Particle.variable("waterTempF",waterTempF);
    Particle.variable("AmbientTemp", ambTempF);
    Particle.variable("Humidity", ambHumid);
    
    
}

void loop() {
    
    ambTempF = dht.getTempFarenheit();      //fetching ambient temp and humidity from DH11
    ambHumid = dht.getHumidity();
    
    
    //WATER TEMP SENSOR
    if(sensor.read()){
        waterTempF=sensor.fahrenheit();
        waterTempC=sensor.celsius();
        //Serial.println();
        //Serial.printf("Temperature %.2f C  %.2f F ", waterTempC,waterTempF );
       // Serial.println(waterTempC);
        //Serial.println(waterTempF);
    }
    
    
    if(timer0%5000==0){
        //Serial.println("Temp: ");
        //Serial.println(ambTempF);
        //Serial.println("Humidity: ");
        //Serial.println(ambHumid);
        //resultstr = String(ambTempF) +" "+ String(ambHumid);
        //Serial.println(resultstr);
        Particle.publish("googleDocs", "{\"my-name\":\"" + resultstr + "\"}", 60, PRIVATE);
        //Particle.publish("googleDocs", resultstr, 60, PRIVATE);
    }         //used for doing things at a certain time interval
    
    
    
    if(!Spraying){                  // turns the spray pin on
        if(timer0-mainOffTime>OffTime){
            Spraying=true;
            spayersOn();
        }
    }
    if(Spraying){                  // turns the sprayers off
        if(timer0-mainOnTime>OnTime){
            Spraying=false;
            spayersOff();
        
        }
    }
   
   
    
}

void spayersOn(){
    digitalWrite(SprayPin,HIGH);
    mainOnTime=timer0;
}
void spayersOff(){
    digitalWrite(SprayPin,LOW);
    mainOffTime=timer0;
}

any insight or good resources on how to pass data from server to server is greatly appreciated.


#2

I’d stay away from String and use snprintf() and a char buf[] instead.
Rationale behind that is provided all over this forum and has to do with heap fragmentation.

  char buf[64];
  snprintf(buf, sizeof[buf], "{\"temp\":\"%.2f\", \"hum\":\"%.2f\"}", ambTempF, ambHumid); 
  Particle.publish("googleDocs", buf, PRIVATE);

HTTP error 401 would suggest your authorisation failed.


#3

Thanks for the insight, is there an upper limit to size of the string i can construct in the buf variable? or is just limited by the size of the string you can pass through particle publish?

I don’t recall setting up the autorization in the webhook builder. I think i am going to try using the custom template, but still do not know where to put the authization information.

Here’s what i have so far:

{
    "event": "googleDocs",
    "url": "https://script.google.com/a/omitted",
    "requestType": "POST",
    "noDefaults": false,
    "rejectUnauthorized": true
}

my questions is where do I put the authorization, and the other information about the data i am sending?

Since making this post I tried using IFTTT and got the device to send data but the string i published didn’t show up in the columns . The time stamp and device name did no problem. It was hard to test what i was doing wrong as the applets don’t run on regular intervals.

I also noticed the google platform integration on the particle console, i haven’t checked it out yet, but would that be something that may be easier to interface with?

Thanks again


#4

update, well sorta:

I have looked into webhooks all day and feel a little more comfortable and a little more confused at the same time.

I was able to get the webhook integration to successfully run (according to the particle console). I think that it had to do with the web app being deployed to allow anyone to post to it, and that wasn’t possible with a school gmail account, so i used a personal one. However I modified my code and integration to be more like the original link i posted above. I did this to try and follow the tutorial as close as possible.

So it seems I am getting to data to the google script servers but there is nothing showing on the sheet. I have found google script code that seems like it’s built to do such a thing. this one for example. should i be looking into this further? With iftt the all of the parsing of the data, setting up the sheet, etc was done for me. Is the google script code needed to properly display the data passed by the webhook integration?


#5

Update. I have gotten the sheet to talk to the photon. The webhook is working has posted data using “Web From” way of processing the data but i had trouble getting more than one value to post on the sheet. So I am trying to use the Custom Template feature in the Webhook buidler and passing JSON data.

Here is the publish function I am using in my code

        char data[64];
        snprintf(data, sizeof(data), "{\"ambTempF\":%f,\"ambHumid\":%f}", ambTempF, ambHumid);
        Particle.publish("googleDocs", data, 60,PRIVATE);

and my webhook definition I used the format from this post.

{
    "event": "googleDocs",
    "url": "https://script.google.com/macros/blahblahblah",
    "requestType": "POST",
    "noDefaults": true,
    "rejectUnauthorized": false,
    "json": {
        "ambTempF": "{{{ambTempF}}}",
        "ambHumid": "{{{ambHumid}}}"
    }
}

I am using the google API script found in this post.

I have it to publish every 60 seconds and I know if works because of the Timestamp header function in the google script. Every time it hits the timestamp is posted but the other two rows have undefined. I have a hunch that it is how I am publishing the data or how the sheet is reading the data. Here is what the event log returns;
{"data":"{\"ambTempF\":32.000000,\"ambHumid\":0.000000}","ttl":60,"published_at":"2018-07-22T04:37:22.195Z","coreid":"Omitted","name":"googleDocs"}

I am so close i can taste it. I do appreciate having to figure it out for myself, helps me understand the inner workings better. Any insight is greatly appreciated.


#6

Friends,

Great news, I figured it out. Thanks to the weather of great tutorials and posts I was able to hack together something that works. I wanted to return and share the secret formula that worked for me, because i know i am not the only person struggling with this.

To be clear, the goal of this post was to figure out a way to get multiple data values from the photon to their own respective columns.

In my case I had three variable; ambTempF, ambHumid, and waterTempF. In my photon code the sensor data is updated and published every hour. using a ellapsedMillis timer, like so;

 if(timer0%publishInterval==0){ //every hour these will push to every hour to google sheets

    Particle.publish("googleDocs", "{\"ambTempF\":\"" + String(ambTempF) + "\",\"ambHumid\":\"" + String(ambHumid) +"\",\"waterTempF\":\"" + String(waterTempF) +"\"}", 60, PRIVATE);
}

I add each data entry using the above format, not sure if its the best way to cast the data to the string, but it works.

Once i confirmed the data was being published on the particle data feed, i went to construct the webhook.

using the webhook builder:

now you can test the webhook and see if its hitting the url and getting a response by using the test button in the webhook builder. The last step is to configure the google sheet.

first go to the sheet you want to use, and name the cell A1 " Timestamp" and then name the others to match what we put in the webhook builder (i kept them all the same as the variable name). Here is what my sheet looks like
35%20PM
Here is the code i used on the sheet, there are some instructions in the code as to how to set it up. I do not own the code, nor do i remember where it came from but someone deserves credit.

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

I hope this helps someone. Feel free to enlighten me with ways to improve it. Thanks for the help.