Save data to a cloud server

For logging certain data such as my home alarm system I just dump the data into a Google Drive spreadsheet. I found the easiest way to do it is to:

  • Set up a google form
  • Use cURL or Node-RED or similar to write to the fields of the form
  • Google will mind this for you

Hope this helps
Garry

I think we need a bit more info then that Garry … any links to a ‘how to ?’

1 Like

I found a tutorial which I must go dig out to share but not at my own PC today, you’ll have to wait.

Essentially when you use a google form you submit field data which Google then appends to a spreadsheet. By hijacking the URL that the Google form populates with the field data and then doing a HTTP GET request, then to Google Drive it looks like a form response coming in so it stores it.
I have a Node-Red function node that contains this code where the input message is the variable I want to store. Wired to it’s output is an empty HTTP GET node which submits this any time a new message payload arrives.

msg.url = “https://docs.google.com/forms/#a_long_string_of_characters_pointing_to_my_form_data/formResponse?entry.#thisI_is_the_field_ID=”+msg.payload;
return msg;

#is where your identifiers will be in the msg.url etc.
Date and timestamp are added as below by Google:
|22/10/2018 06:03:14|Disarmed OK
|22/10/2018 09:38:34|Armed OK
|22/10/2018 10:43:46|Disarmed OK
|22/10/2018 10:56:07|Armed OK
|22/10/2018 14:59:42|Disarmed OK
|22/10/2018 15:23:42|Armed OK
|22/10/2018 16:16:09|Disarmed OK
|23/10/2018 01:29:21|Armed OK
|23/10/2018 06:23:58|Disarmed OK
|23/10/2018 11:18:31|Armed OK
|23/10/2018 16:48:44|Disarmed OK

HTH

G

1 Like

Garry are you thinking of this SQuix/Eichorn blog post - logging to Google Spreadsheets?

Main problem I believe is it is insecure as stated:-

One word of warning: the current script is not really save {safe- sic}, anyone knowing the URL can send data to your spread sheet. We could improve it by adding an authentication header (in the currently) empty TTN form field and check it in our App script code.

Not this way @Jeff-UK

The way I did this is as follows:

  1. Open Google Drive

  2. Using the “NEW” button, drill down until you find “New Form” and create and name a new form

  3. Now design your form - The text of each question(eg “Sensor 1”) will be the header of each data column in your table

question

  1. Select answer type - Your answers should be of the type “Short Answer” (formats a text cell in sheets)

  2. For each extra data field click the + button for to add a question and repeat these two steps

  3. When done click on the top/right of the page, click the three dots to drop down a contextual menu

getLink

  1. Select “Get pre-filled link” from the list. This will display a sample form with a “Get Link”

  2. Put some identifiable text in each answer space like “Data1”, “Data2” etc

  3. Click on the “Get Link” button to copy the pre-filled link to your clipboard

  4. This is your URL complete with the key to point to your sheets spreadsheet that will collect your results

  5. You need to edit this as below. (I have taken my key out and added some text)

https://docs.google.com/forms/d/e/your_key_between_these-slashes/viewform?usp=pp_url&entry.1138990543=Data1

changes to

https://docs.google.com/forms/d/e/your_key_between_these-slashes/formResponse?entry.1138990543=Data1

You can test this by pasting the edited URL into a browser and submitting, you should get an acknowledgement screen if your link is formatted correctly and you should have populated the target spreadsheet with the text “Data1” in the column with the header “Sensor 1”.

Try it and see how you get on? The data will be stored in your Google Drive on a Google Sheets spreadsheet which will have the same filename as you gave to your form in step 2

G

1 Like

:thinking: I may give that a try at the weekend :wink: thanks

Forumites - if anyone else has success with this and evolves further can they please update post here…

Thank you very much for the answers guys :slight_smile: I’ll look into them :slight_smile:

hey there,
I am a bit of a rookie in the IOT my self. I have been reading a lot of materials that helped me understand the concept of it but I cant quite figure out how I will be able to deploy it nor find any material that could help me. can you point me in the right direction regarding

if there are any simulation software and tutorials I could practice programming on
if you could help a beginner in any way please

Hi,

A possible direction would be to start with Arduino. ( 136.000.000 results on google)
starter books (arduino starter sensor book)

If you have a breadboard, some wire / switches / leds and an Arduino and you have installed the arduino software on your computer you can start programming / experimenting.
There are starter kits.

This is not the site for seeking help on starting with arduino/programming and learning some basics.

Later you can connect a specific lorawan module and program this in a way that you can transmit sensor data to the things network or use a wifi connection.
next step would be to do somethimg with that data.

example:

thank you for your quick response , I am not entirely new to Arduino programming. I am interested on the point you put as connecting a specific lorawan module and program this in a way you can transmit sensor data to the things network or use wifi connection.
questions
are there any simulator software that could replace the module , cos I don’t know where I can find those , I leave in Ethiopia.

and if there are any links you could share that could teach me how to transmit data from the sensors to the things network

I don’t know a simulator software that simulates arduino connected to a lora module and simulates also how a network reacts to a received transmission.

If you are familiar with arduino I suggest build yourself a (single channel) gateway and a node

see TTN LABS or use search

I will do as you suggested, thank you for all the help

Hi @GryKyo, I was hoping to try your google forms suggestion but the two links provided above are broken. Do you mind re-posting them. Thanks.

https://docs.google.com/forms/d/e/your_key_between_these-slashes/viewform?usp=pp_url&entry.1138990543=Data1
changes to
https://docs.google.com/forms/d/e/your_key_between_these-slashes/formResponse?entry.1138990543=Data1

I fixed the broken code of squix with a lot of Squix inspiration:


Maybe it helps other.
Still usnecure but handy for fast development

1 Like

Hi GryKyo and you all, and many thanks for your detailed instructions!
I’m Roberto from Italy and I would like to save back data from Arduino MKR WAN 1300, to a G-spreadsheet for educational porpose, through the TNN platform and HTTP integration.

Therefore, I followed successfully your guidelines and I’m able so far, to submit 2 variable names (AirRH and AirT) through the http-integration and save back them into the google-spreadsheet.

https://docs.google.com/forms/d/e/my_key/formResponse?entry.1557039956=AirRH&entry.1427718550=AirT
AirRH (relative humidity) and AirT (temperature) are also the names of the decoded variables.

Now, my question: how should I configure the http-integration post, in order to send the values and not the variable names AirRH and AirT?

I tested the http integration with pipedream and it works fine:

hardware_serial:A8610A3432476C18
metadata {1}
payload_fields {2}
AirRH:48.9
AirT:20.2
payload_raw:MjAuMjB8NDguOTA=

I wonder if I have to use something like payload_fields{AirRH} in the http integration?
https://docs.google.com/forms/d/e/my_key/formResponse?entry.1557039956=payload_fields{AirRH}&entry.1427718550=payload_fields{AirT}

Any help would be greatly appreciated!

Many thanks

Roberto

You can’t; see How add data field to URL in HTTP Integration?

To use Google Sheets with the HTTP Integration, without any intermediate server, you’ll need to add some Google Apps Script code to your spreadsheet, and then enable that as a web app.

Thank you so much for your quick reply!
I’ll take a look and try the suggested approach.

Finally, I was implementing squix78 instructions and script, to get Google Sheets working with the TTN HTTP Integration!


I still have to perform some minor changes to the script, to fit it to my payload, but it works fine.
For educational purposes, it’s good enough :wink:
Many thanks for your tips!

That example is a bit outdated (no setup is needed when creating the Apps Script from Google Sheets) and does not inform you about errors. But indeed it should work just fine otherwise.