HTTPS Endpoint configuration with ESP8266 - Sensor to Google Script

Cloud Integration
1

I’m working on a garage door monitor. Part of this is to send the status of the door (an integer; 1=open, 2=closed, 3=operating)

I then want to pass this information to a google script.
https://script.google.com/macros/s/google_ID_KEY/exec?status=1

I’m using the Arduino IDE to program the ESP8266 (NODENCU).

#define DEVICE_ID "GDM2"  (the # sign bolds this line)

  thing["doorStatus"] >> [](pson & out) {
    out = doorStatus;
  };

 thing.call_endpoint("MakerTest1")

and 
  thing.handle();

This causes the MakerTest1 to operate and connect to a IFTTT that responds, but I’m not sure how to configure the Endpoint correctly for the Google Script application.

I know some documentation is coming, and I’ll volunteer to test it out!

Thanks
Peter

2

Hi! Yes, I am working on some documentation, but it wont cover your use case yet. So I can respond here.

Here I can recommend you to slightly modify your google script to accept incoming body in JSON format (sure it is possible), so you can make a POST request with the current state. For example, you could create a single endpoint to the following URL configured to pass the device data as JSON. Just create a HTTP request endpoint in the console, set ‘Send Device Data as JSON’ and set your script URL.

https://script.google.com/macros/s/_google_ID_KEY_/exec (call it DoorState)

Then your device could make something like the following code:

// call this only in every doorStatus change
pson data;
data["state"] = doorStatus;
thing.call_endpoint("DoorState", data);

So this way, your endpoint should be able to parse the incoming JSON document, and extract the integer value from the state key. With this method you could send even more information if you want, just appending more data to the data variable.

If you cannot modify your script code, just create three different endpoints (Door1, Door2, and Door3) only changing the url. Then your device could call one endpoint or other depending on the state. But this approach is uglier to me :slight_smile:

Hope it helps!

3

Hi. I’m realy struggling with the Google Script side of this. Can the Thinger.io HTTP endpoint handle HTTPS requests? That has been one of the problems I’ve seen in my earlier experiments.

I hardcoded my GoogleScript to not need any parameters and it’s still not reacting. Not sure not to see if the HTTP Endpoint is sending anything.

I’m using this to test:
if (doorStatus != oldDoorStatus) {
Serial.println(“Calling Endpoint now”);
//add a row to the change table
pson data;
data[“state”] = doorStatus;
thing.call_endpoint(“GDM1_test”,data);
thing.call_endpoint(“MakerTest1”);
}
and the MakerTest1 end point works great - fires off every time. So, I think it may be something with HTTPS…

4

Hi Peter, not sure if you can get me access to the Google Script code to check it. Anyway, I recommend you to test the HTTP call manually, by using a Chrome plugin like this:

This way you can be sure how to call your Google Script and check if it is working as you expect, or if it is providing a response error. Then you can configure the Endpoint request in a similar way you are doing in the plugin. HTTPS is not a problem here, as the IFTTT is also working over HTTPS.

In the other side, your sketch code seems to be perfect!

5

I saw your email! Let me check that!

6

That Plugin will definitely help. Lots of pieces to learn how to put together…

7

Yes! I have been submitting some data to your script using the plugin. Not sure if you have seen new inputs… the script returned me “You are a post”.

I think I have found the problem, thanks to the plugin I saw there is a 302 redirect response before doing the HTTP request to another page with different URL. The thinger.io server does not currently support redirect responses while calling endpoints. But I am working in a quick patch for it!

I will tell you if I get something :wink:

8

I checked my script and redeployed. You should see “Value 1 (open) was added to spreadsheet” when you Post.

9

Now I see different responses than before… Are you able to POST data with the Chrome Plugin? I get a html response now with some javascript…

10

Now I need to play with my Google/Javascript and the plugin. I need to extract the parameters from the JSON data…

11

Ok! let me know if you get it working, at least with the plugin, then I may need to update the server to support response redirects. I have a preliminary version working, but I should debug it in more detail.

12

Will do, now at least I can debug my extract process. Thanks

1 Like
13

Anyone interested in tracking this project, please check out www.heath-solutions.com as I’m blogging about all of the aspects there.

14

Very interesting Peter!! It is a nice project! I see that you still have problems with the Google Spreadsheet integration… Did you were able to post data by using raw HTTP requests?

15

Hi Alvaro
I’m able to post raw requests using REST plugin. Thinger.IO shows some IO. However, nothing makes it to the google script. I sent the POST string and key to you (via email) for testing if that helps. Let me know if there is anything else I can do to help.

16

I cannot see the email with the POST string and key, you can send it also from the forum in a private message, so I can take a look!

17

Hi Alvaro
I’m back. My door monitor has been working a champ and has been very reliable. It ran continually from January 9 until today when we had a power failure. When the power came back on, it rebooted just fine.

I’m now working on some enhancements and again running into a problem with the connection between the device and the endpoint and Google Script. I’m setting up a parallel environment for testing and trying to reproduce what is working so I can provide this as an example for others. I have some other projects that will need a spreadsheet record, so I’d like to make this more robust.

I can see on the dashboard that the Device is connecting and the values are showing up OK. My Google script is a clone of the original one, and works OK when I use ARC to test it.

Could you add a Dashboard widget that displayed the Data sent to Thinger.io when an endpoint is called?

For example when this code runs:
pson data;
data[“State”] = doorStatus;
data[“GDMtime”] = doorOpenTime;
thing.call_endpoint(“GDM”, data);

I can add Serial print lines into this code, but I don’t know what is happening with Thinger.

I’m putting the project on Github and will place on Instructables as well once I can get the Thinger.io connection to a Google script repeatable.

Any suggestions?? Maybe the Endpoint monitor can capture some of the return or result. For example, when I call the Google Script from ARC it returns:
{“result”:“success”,“row”:17}

It’s so close… Thanks for any help…
Peter

18

Hi Peter! Yes, it would be useful to be able to see the endpoint call result in the platform. I will add this to the big TODO list… :slightly_smiling:

At this moment, what is this you cannot get working? As I understand, you have a device that is successfully sending reports to your spreadsheet. And now you are replicating the setup in another device, and a different spreadsheet, and this is not working, right?. I suppose you created a new endpoint pointing to the new spreadsheet url, and your new code is pointing to the new endpoint. Is it?

19

Hi Alvaro
Yup… the original (working) system uses Program A --> Device GDM2 --> endpoint JSON_Testing --> Google Script “JSON_Test” --> Google Sheet “States”

I am trying to make a copy of that process to clean up the names and test some changes as:
Program A-prime --> Device GDM --> Endpoint GDM --> Google Script “GarageDoorMonitor.gs” --> Google Sheet “GarageDoorRecord”

There is probably some simple typo, but all I changed is the name of a column in the spreadsheet and Google Script (and I’ve tried changing those back). The ARC process works fine but since I cant see what is being sent to the Google Script, I can’t debug it.

Thanks for any help…