React and Google Spreasheets | General | Forum

Avatar

Please consider registering
Guest

sp_LogInOut Log In sp_Registration Register

Register | Lost password?
Advanced Search

— Forum Scope —






— Match —





— Forum Options —





Minimum search word length is 3 characters - maximum search word length is 84 characters

sp_Feed sp_TopicIcon
React and Google Spreasheets
No permission to create posts
March 22, 2019
7:14 am
Avatar
Studvio

Silver
Members
Forum Posts: 8
Member Since:
July 25, 2017
sp_UserOfflineSmall Offline

Hi all, I need to create a new entry on a Google Spreadsheet every time a channel was not updated for some time. I created the REACT, and attached a ThingHTTP to it. The ThingHTTP has the URL in the form https://docs.google.com/forms/d/e/XXXXXXXXXXXXXXXXXXXXXXXXXX/formResponse, but not sure where to add the actual response (1 field, really, something like entry.XXXXXX and one word) in order for an entry to be added in the spreadsheet. Thanks in advance for your help.

LE. the REACT and ThingHTTP are working at the moment with IFTTT, but just got an email from Google saying that they will restrict their access to IFTTT, due to non-compliance with privacy stuff.

March 22, 2019
8:19 am
Avatar
cstapels
Moderator
Members


Moderators
Forum Posts: 861
Member Since:
March 7, 2017
sp_UserOfflineSmall Offline

I'm not sure about the command to write into a google sheet, but instead of writing into your google spreadsheet, you could have the thingHTTP write the logged event to another ThingSpeak channel using the REST call for write data.
You could still export the channel data to your google spreadsheet after much data has collected using the web interface on the import / export tab.

The write data format is here:
https://www.mathworks.com/help/thingspeak/writedata.html

March 22, 2019
8:29 am
Avatar
Studvio

Silver
Members
Forum Posts: 8
Member Since:
July 25, 2017
sp_UserOfflineSmall Offline

The idea is that once the response reaches the spreadsheet, I will get an instant notification by email. It is not for archiving purposes, but rather to learn and take action as soon as possible, when data has not been updated (i.e. no new data being received from the Arduino, which should update the channel every 20 sec.). I would use this therefore as a warning, in order to take action (i.e. go to location, and restart the Arduino, if required). Thanks.

March 22, 2019
8:37 am
Avatar
cstapels
Moderator
Members


Moderators
Forum Posts: 861
Member Since:
March 7, 2017
sp_UserOfflineSmall Offline

That seems to be a clever way to get a notification. Does google provide you an email every time the spreadsheet is updated?

My IFTTT based notifications are still working at of March 18. I did not get a discontinue note from them yet. If your application is not a commercial application, you could consider pushingbox for alert notifications. You can trigger them from thingHTTP or a MATLAB analysis the same way you trigger IFTTT notifications.

March 22, 2019
8:44 am
Avatar
Studvio

Silver
Members
Forum Posts: 8
Member Since:
July 25, 2017
sp_UserOfflineSmall Offline

Yes, you can opt to get an instant email notification once a new entry was added to a Google Spreadsheet using a Google Form.
********************
The email I got this morning:
"Hello,

Although you don’t need to take any action, we wanted to let you know that the following third-party apps will no longer be able to access some data in your Google Account, including your Gmail content. This change will go into effect starting 31 March 2019.

IFTTT
We are making this change as part of ongoing efforts to make sure that your data is protected and private. These apps haven’t yet complied with our updated data privacy (https://cloud.google.com/blog/products/g-suite/elevating-user-trust-in-our-api-ecosystems) requirements announced on 8 October 2018.

You can always view, manage and remove apps that you’ve given access to your account by visiting your Google Account.

Thanks,
The Google Accounts team"
********************
"you could consider pushingbox for alert notifications. You can trigger them from thingHTTP..."
I would have no clue about how to do that. Any suggestion is highly appreciated. Thanks.

March 22, 2019
8:54 am
Avatar
cstapels
Moderator
Members


Moderators
Forum Posts: 861
Member Since:
March 7, 2017
sp_UserOfflineSmall Offline

Thanks for the info on Google spreadsheets and update on IFTTT. I remember creating a MATLAB script that would write to a spreadsheet once in the past, so I think it is possible. If I find that work, ill post sometime later.

As far as pushingbox goes, just go to the website, create an account and then use your api key to create a REST call. It has the form:
http://api.pushingbox.com/pushingbox','devid=XXXXXXXXXXXXXXX

You can use thingHTTP or a MATLAB analysis (with webwrite('url'); to trigger the REST call. As I said, pushingbox TOS does not allow commercial use though.

Can you tell us anything more about your application? We are always looking for interesting projects.

March 22, 2019
9:05 am
Avatar
Studvio

Silver
Members
Forum Posts: 8
Member Since:
July 25, 2017
sp_UserOfflineSmall Offline

Thanks for the details, I will look into it.
About my project... I am doing my PhD, and I have six automated enclosures at six different locations across Dublin, Ireland. They are based on Arduino, and can be monitored and interacted with remotely via a custom built mobile phone app. Participants are growing vegetables in those enclosures, and I am studying their engagement with technology. But in order for me to be able to do that, the technology must be up and running Laugh. Therefore, when any location is down, I'd like to be informed as soon as possible, so that I can go there and fix it quickly. At the moment I am using IFTTT, but according to the email, it will stop working soon.
You can find more information about the project here http://eyeduinoproject.online/

On Google I created a form and a spreadsheet. Filling the form and submitting it will add a new entry in the spreadsheet. Once that happens, a script attached to the spreadsheet will automatically parse the text, format it, and email it to me instantly. I might actually be able to create a ThingHTTP with some URL that will silently and automatically fill in the attached form and submit it.

March 22, 2019
9:40 am
Avatar
Studvio

Silver
Members
Forum Posts: 8
Member Since:
July 25, 2017
sp_UserOfflineSmall Offline

Got it!
A React linked to a ThingHTTP with an URL like this
https://docs.google.com/forms/d/e/XXXXXXXXXXXX/formResponse?entry.XXXXXXXXXXX=TEXT+TO+BE+POSTED&submit=Submit
will do the job. The spreadsheet needs to be shared in such a way that "Anyone who has the link can edit"

March 22, 2019
9:41 am
Avatar
cstapels
Moderator
Members


Moderators
Forum Posts: 861
Member Since:
March 7, 2017
sp_UserOfflineSmall Offline

Great project, thanks for the details!

I would recommend working with a MATLAB analysis over thingHTTP, you will get more flexibility. You can use a time control to set a MATLAB analysis to read the channel on regular intervals and look for missing data. (i.e use api call to read channel with a set number of minutes, return the data in a timetable and then count the entries.)
You can use react on no data, but the parameters are less controllable that what you can write in a MATLAB analysis.

In your workflow, are users manually filling in the form, or you expect that to happen only when react calls the thingHTTP?
"Filling the form and submitting it will add a new entry in the spreadsheet."

March 22, 2019
9:57 am
Avatar
Studvio

Silver
Members
Forum Posts: 8
Member Since:
July 25, 2017
sp_UserOfflineSmall Offline

cstapels said
In your workflow, are users manually filling in the form, or you expect that to happen only when react calls the thingHTTP?
"Filling the form and submitting it will add a new entry in the spreadsheet."  

The form gets filled automatically when the React / ThingHTTP "detect" the system being down. The users have no access to the form, as it is only for my internal use.

March 28, 2019
8:58 am
Avatar
Studvio

Silver
Members
Forum Posts: 8
Member Since:
July 25, 2017
sp_UserOfflineSmall Offline
March 28, 2019
1:47 pm
Avatar
cstapels
Moderator
Members


Moderators
Forum Posts: 861
Member Since:
March 7, 2017
sp_UserOfflineSmall Offline

Excellent! Thanks for sharing.

Forum Timezone: America/New_York

Most Users Ever Online: 166

Currently Online:
6 Guest(s)

Currently Browsing this Page:
1 Guest(s)

Top Posters:

rw950431: 272

Vinod: 240

piajola: 95

turgo: 70

vespapierre: 63

Adarsh_Murthy: 62

Member Stats:

Guest Posters: 1

Members: 8665

Moderators: 1

Admins: 2

Forum Stats:

Groups: 3

Forums: 14

Topics: 1600

Posts: 5760

Newest Members:

khanhNguyen_CT, susyburr, quijano__, meosuckhoe, briskpanda, Dejw0089

Moderators: cstapels: 460

Administrators: Hans: 405, lee: 457