How to Use Google Docs for Data Logging

37,456

37

19

Posted

Introduction: How to Use Google Docs for Data Logging

I have a Netduino+ hooked up on the Internet with data logging  via Google docs.
Before i found this i was collecting the data via a SD card, on the Netduino.

I read almost every day Hackaday here i stumbled up at http://hackaday.com/2012/05/31/data-logging-directly-to-google-docs-google-drive/

But this didn't work for me, but i knew i was on the right trail.

After a lot of googleling  it worked for me, this (story) is how i did it.

I have tried to simply the whole thing in only text, with links to more relevant documents.
Please ask in the comments and i will try to answer these.

If something relevant is asked i maybe will change this instructable to clarify some streps, so please read first then ask.

This way of collecting is also useful with writing software with a database, you don't need a server with SQL or other language.

Step 1: Filling a Spreadsheet

Filling a spreadsheet in google docs goes always via a form:
After hitting the Submit button on this form the URL is send to Google docs:
https://docs.google.com/spreadsheet/formResponse?formkey=Here_goes_the_formkey&ifq&entry.0.single=opeens&entry.1.single=nu&pageNumber=0&backupCache=&submit=insturen

When you change the URL with the right values then you can send your data from a micro-controller or software to the spreadsheet.

Step 2: Use Google Docs to Collect Data of a Micro Controller.

To use Google docs you need an account, if you make your project for somebody else then make for that project a new account.
Login and go in  google docs to new-->form
Very importantvVia this form the data is collected and put in the spreadsheet.
No form, No spreadsheet.

For every parameter you will follow, make a field.
(# means a number
 : means "this will be" "in the URL")

Standard HTML:
Text field : entry.#.single
textarea    : entry.#.single
radio     : entry.#.group  // Same group number belongs to the same group.
checkbox   : entry.#.group  // Same group number belongs to the same group. entry.3.group=value1&entry.3.group=value2
option/select : entry.#.single

Special:
scale     : based on type radio on a row
grid     : based on type radio in a grid

The most used fields would be a text field for values and radio button for boolean.

Step 3: How to Implement This?

To try it out as a URL in the browser follow these steps:

In the form editor click in the bottom of the page on view in real, open here the source of the HTML.

Search for FORM action=""
In here is the link to the spreadsheet.

Something like:
https://docs.google.com/spreadsheet/formResponse?formkey=Your_formkey&ifq

After this text comes the parameters.
Search for name=" here you find as example:entry.1.single

use the & symbol with each entry.

example:
&entry.1.single=value

&entry.2.group=value1 // this way by checkbox
&entry.2.group=value2 // this way by checkbox

&entry.3.group= value of row 1 // this way by a grid every row is a separated entry.#.group=value
&entry.4.group= value of row 2 // A grid with four rows with 3 columns each row entry got the name of the column(There is only one possibility per row. )
&entry.5.group= value of row 3
&entry.6.group= value of row 4

so go on to the last parameter.
Close the string with :
&pageNumber=0&backupCache=&submit=Submit

Important:
For a space in a URL use %20, this is the symbol for space in HTML FORM.

Important:
If you need extra parameters then you must change the form with the google form editor.
You can't append more "entry.#....", google won't know these, it only knows the structure of the form you make the first time.

Step 4: The (simplified) Code

const string FormKey = "Here comes the form key"; // Important DON't use the spreadsheet key. this is different key
        static string GoogleIP = "spreadsheets.google.com";     // Google adress
        static Int32 GooglePort = 80;                   // Port Number 443=https 80=http
   
    Fill the parameters:
                formdata = "&entry.0.single=" + value.ToString();
                formdata += "&entry.1.single=" + value.ToString();
               
                formdata += "&entry.2.single=" + value.ToString();
                formdata += "&entry.3.single=" + value.ToString();
               
                formdata += "&pageNumber=0&backupCache=&submit=insturen"; // close with this these are hidden on the form
               
               
    Send parameters to Google Doc(formdata are your parameters)
   
      request = "POST /formResponse?formkey=";
            request +=  FormKey;
            request += "&ifq HTTP/1.1\r\n";
            request += "Host: ";
            request += GoogleIP+"\r\n";
            request += "Content-Type: application/x-www-form-urlencoded\r\n";
            request += "Connection: close\r\n";
            request += "Content-Length: " + formdata.Length + "\r\n";
           
            request += "\r\n";
            request += formdata;
           
            request += "\r\n";
           
   
How to send your data is for each program language different.

Step 5: Now You Can Use the Spreadsheet As a Database

Document for using a spreadsheet as a database:
https://developers.google.com/chart/interactive/docs/querylanguage#Setting_the_Query_in_the_Data_Source_URL

Database examples:
https://spreadsheets.google.com/tq?tqx=out:html&tq=select*%20format%20A%20%27dd-MMMM-yyyy%20hh:mm%27&key=Spreadsheet key
here is:
    tqx=out:html (or csv|json) Maybe other outpu formats?
    tq=select*%20format%20A%20%27dd-MMMM-yyyy%20hh:mm%27
    (* means all)
           Select all columns but use a format on column A --> dd-MMMM-yyyy hh:mm somthing like 03-august-2012 12:23

https://spreadsheets.google.com/tq?tqx=out:html&tq=select+A,+B,+C,+D,+E&key=Spreadsheet key
here is:
    tq=select+A,+B,+C,+D,+E
      Select all data of column A,B,C,D,E,F
     
With select you can use calculations or other simple query's. See the document for examples.

With this URL you can get the data in your program, and manipulate it.

Step 6: Ready

I wrote this as is. So please ask and i will try to answer.
This step for step instructables i have made is for my self so i can not forget it,
but i have put it in the public because it is a nice way to collect your data and like to share with you.



Google docs will soon be Google drive, i hope this will work then to.
I have tried it 03-07-2013 and it still works:
spreadsheets.google.com/formResponse?formkey=Here comes your formkey&ifq&entry.0.single=10&entry.1.single=20&entry.2.single=30&entry.3.single=40&pageNumber=0&backupCache=&submit=insturen

Greetings,

Share

Recommendations

  • Epilog Challenge 9

    Epilog Challenge 9
  • Sew Warm Contest 2018

    Sew Warm Contest 2018
  • Gluten Free Challenge

    Gluten Free Challenge
user

We have a be nice policy.
Please be positive and constructive.

Tips

Questions

19 Comments

This won't work with plain old http anymore. Google requires TLS access now. Your board needs to support 'https' or use a middleman like Temboo, pushingbox (custom URL method) to make 'https' requests to google.

Hello,

I believe the format for the URL's for google's forms has changed. There is no longer a formkey, or it is not specified as it used to be. Does anyone know how to identify the formkey in the new URL format?

Thanks

Hi igagnon,

Did you find an answer? I am also after this.

Thanks

is it possible to create the link you created and run it in browser itself?

or can it be used in a html webpage as submittion button?

As in step 2 and 3 you see it is a conversion from HTML to C.
In step 6 you see the link itself created from de HTML Form tag.

is it possible to create the link you created and run it in browser itself?

or can it be used in a html webpage as submittion button?

Hi,

I am trying to send arduino data (of a sensor) to google spreadsheets using WiFi shield. But the data is not been saved in the spreadsheet. Here is my code:

#include <WiFi.h>

int SensorIn1 = 1;

int led_pin = 13;

char ssid[] = "**********";//Network name

char pass[] = "*********";// Network Password

int status = WL_IDLE_STATUS;

WiFiServer server(80);

void setup() {

Serial.begin (9600);

while ( status != WL_CONNECTED) {

Serial.print("Attempting to connect to WPA SSID: ");

Serial.println(ssid);

// Connect to WPA/WPA2 network:

status = WiFi.begin(ssid, pass);

// wait 10 seconds for connection:

delay(1000);

}

Serial.print("You're connected to the network");

server.begin(); // initialise the server

// print out your network data

printCurrentNet();

printWifiData();

}

void loop() {

WiFiClient client = server.available();

int val;

val = analogRead(SensorIn1);

Serial.println(val);

delay (100);

if (client) {

Serial.println("new client");

boolean currentLineIsBlank = true;

while (client.connected()) {

if (client.available()) {

char c = client.read();

Serial.write(c);

//if (client.connected()) {

Serial.println("connected");

// send a standard http response header

String data = "&entry.1515954247=";

data = data + val;

data = data + "&submit=Submit";

client.print("POST /forms/d/1tUMKnf1HYY5tCuG_lG93N9kJhd4voQ1elRmTXYzLm/formResponse?");

client.println("ifq HTTP/1.1\r\n");

client.println("Host: docs.google.com");

client.println("User-Agent: Arduino/1.0");

client.println("Content-Type: application/x-www-form-urlencoded");

client.println("Connection: close");

client.println("Refresh: 1");

client.print(data);

//client.print("Content-Length: ");

//client.println(data.length());

//client.println();

//client.print(val);

client.println();

}

}

delay (100);

client.stop();

Serial.println("client disconnected");

}

}

void printWifiData() {

// print your WiFi shield's IP address:

IPAddress ip = WiFi.localIP();

Serial.print("IP Address: ");

Serial.println(ip);

// print your MAC address:

byte mac[6];

WiFi.macAddress(mac);

Serial.print("MAC address: ");

Serial.print(mac[5],HEX);

Serial.print(":");

Serial.print(mac[4],HEX);

Serial.print(":");

Serial.print(mac[3],HEX);

Serial.print(":");

Serial.print(mac[2],HEX);

Serial.print(":");

Serial.print(mac[1],HEX);

Serial.print(":");

Serial.println(mac[0],HEX);

}

void printCurrentNet() {

// print the SSID of the network you're attached to:

Serial.print("SSID: ");

Serial.println(WiFi.SSID());

// print the received signal strength:

long rssi = WiFi.RSSI();

Serial.print("signal strength (RSSI):");

Serial.println(rssi);

// print the encryption type:

byte encryption = WiFi.encryptionType();

Serial.print("Encryption Type:");

Serial.println(encryption,HEX);

Serial.println();

}

Can anyone help me in debugging this. I have seen many references from google but nothing is working out.

You send your values to docs.google.com instead of spreadsheets.google.com

maybe this is what goes wrong.

I tried with docs.google.com as well but the problem is still the same. I can give you the spreadsheet key and the form key separately if that helps.

per 04 januari 2015:

spreadsheets.google.com/formResponse?formkey=

Here comes the form key

&ifq&entry.0.single=10&entry.1.single=20&entry.2.single=30&entry.3.single=40&pageNumber=0&backupCache=&submit=insturen

This still works

It is important that you replicate the exact structure in your code like in Step 4 there are a few variable you missed.

Write out your code as a URL and put it in your browser navigation then you can see what goes wrong.

Step 4:

const string FormKey = "Here comes the form key"; // Important DON't use the spreadsheet key. this is a different key

static string GoogleIP = "spreadsheets.google.com"; // Google adress

static Int32 GooglePort = 80; // Port Number 443=https 80=http



Fill the parameters:

formdata = "&entry.0.single=" + value.ToString();

formdata += "&entry.1.single=" + value.ToString();



formdata += "&entry.2.single=" + value.ToString();

formdata += "&entry.3.single=" + value.ToString();



formdata +=
"&pageNumber=0&backupCache=&submit=insturen"; // close with
this these are hidden on the form

Also important is last line with the pagenumber=0 variable it is for me a few years ago it worked for me on the moment i'am not using it anymore maybe googledocs has changed dramatic. the trick is to replicate the HTML form data in your C code.