How to send data from Arduino to Google Docs Spreadsheet

By on July 8, 2011

A very interesting feature of Arduino is the great avalaibility of library that make the developer work very simple and fast.
Make a Web Server, a Web Client or post a Tweet haven’t difficulty.
Try to do the same application with the Microchip’s Stack TCP/IP…. It’s not impossible but for beginner developpers is of course hard.

With the Ethernet shield or WiFi shield  you can connect your application to the internet world, get information, send email, post data…

Post data? But where?
Some site host your data with graphics like the famous Pachube, I also use this service for my projects.
But in some case could be useful have the data in a spreadsheet to do operations and evaluate numbers.

In this post I want explain the best way found by RobertMParker to send data on Google Spreadsheet. Many thanks to Andrea Fainozzi that has corrected the code.

The best way to access to Google Spreadsheet is not access to Google Spreadsheet…
I didn’t find a way to access directly on the sheet, but we can pass data to our sheet using a Form.
All the results of a Form are imported in the corresponding sheet…. so … the data are on Google Spreadsheet.

The method step by step to send data from Arduino to Google Spreadsheet

 

Update

As write Mathias in
http://michaelwalsh.org/blog/2013/02/05/work-around-for-google-forms-problem/

“Legacy Google Forms are still available IF you start creating the form by opening a SpreadSheet first. Once the spreadsheet is open, select “Create a legacy Form” from the “Tools” menu. The form editor and published URL will be the same as those that had worked prior to the Google Forms update.”

 

1° – We must create a Form from Google Documents page (you must be logged):

 2° – Select “Create new” -> “Form” from Google Docs Menu:

3° –  Create the form with TEXT type box. You can insert how many box you need. Give a name to the Form and to Question (the Questions names will be the columns names):

4° – Click “Done”. You created the Form. In the URL you can see the formkey. In my case is formkey=dDBMdUx3TmQ5Y2xvX2Z0V183UVp2U0E6MQ  Take note of this key. We will use it on the Arduino sketch.

5° – Normaly the TEXT box (Data 1 and Data 2 in this example) are named entry.0.single and entry.1.single …. etc. But the name can change if you modify the basic structure. So the best way is explore the HTML code to check this.

6° – I use Google Chrome to do this, but similar procedure is with Firefox or IE. The code show the BOX name. Take note of this name.

7° – In my case the name are entry.0.single for Data 1 and entry.2.single for Data 2. To send data to the Form we must use this sintax:
https://spreadsheets.google.com/formResponse?formkey=YOUR FORM KEY &ifq& YOUR ENTRY =THE VALUE TO STORE &submit=Submit
For Example:
https://spreadsheets.google.com/formResponse?formkey=dDBMdUx3TmQ5Y2xvX2Z0V183UVp2U0E6MQ &ifq&entry.0.single=Boris&entry.2.single=Landoni&submit=Submit

The result must be:

8° – The data are copied in the Google Spreadsheet. This is my sheet link https://spreadsheets.google.com/spreadsheet/ccc?key=0As0sZjJOmuxUdDBMdUx3TmQ5Y2xvX2Z0V183UVp2U0E&hl=en_US.

9° – Now we must upload data with Arduino. We use the POST method to send data to the Form. And the code become very simple.

/* Arduino to Google Docs
 created 2011

This example code is in the public domain.

http://www.open-electronics.org


http://www.futurashop.it

https://spreadsheets.google.com/formResponse?formkey=dDBMdUx3TmQ5Y2xvX2Z0V183UVp2U0E6MQ &ifq&entry.0.single=Boris&entry.2.single=Landoni&submit=Submit
Original from http://goodsite.cocolog-nifty.com/uessay/2010/07/arduinogoogle-d.html
Modified by John Missikos 11/6/11
Modified by Andrea Fainozzi 30/6/11
Modified by Boris Landoni 8/7/11

*/

#include 
#include 

char formkey[] = "dDBMdUx3TmQ5Y2xvX2Z0V183UVp2U0E6MQ"; //Replace with your Key
byte mac[] = { 0x90,0xA2,0xDA,0x00,0x55,0x8D};  //Replace with your Ethernet shield MAC
byte ip[] = { 192,168,0,109};  //The Arduino device IP address
byte subnet[] = { 255,255,255,0};
byte gateway[] = { 192,168,0,254};
byte server[] = { 209,85,229,101 }; // Google IP
Client client(server, 80);

void setup()
{
  Serial.begin(9600);
  Ethernet.begin(mac, ip , gateway , subnet);
  delay(1000);
  Serial.println("connecting...");
}

void loop(){
  String data;
  data+="";
  data+="entry.0.single=";
  data+=analogRead(A0);
  data+="&entry.2.single=";
  data+=analogRead(A1);
  data+="&submit=Submit";

  if (client.connect()) {
    Serial.println("connected");

    client.print("POST /formResponse?formkey=");
    client.print(formkey);
    client.println("&ifq HTTP/1.1");
    client.println("Host: spreadsheets.google.com");
    client.println("Content-Type: application/x-www-form-urlencoded");
    client.println("Connection: close");
    client.print("Content-Length: ");
    client.println(data.length());
    client.println();
    client.print(data);
    client.println();

    Serial.print("POST /formResponse?formkey=");
    Serial.print(formkey);
    Serial.println("&ifq HTTP/1.1");
    Serial.println("Host: spreadsheets.google.com");
    Serial.println("Content-Type: application/x-www-form-urlencoded");
    Serial.println("Connection: close");
    Serial.print("Content-Length: ");
    Serial.println(data.length());
    Serial.println();
    Serial.print(data);
    Serial.println();

  }
  delay(1000);
  if (!client.connected()) {
    Serial.println();
    Serial.println("disconnecting.");
    client.stop();
  }

  delay(10000);

}

This code publishes on the Google Spreadsheet the value of analog input A0 and A1.

The application could be very different. If you have the data on Google you can draw grafics, download data on you PC, share this data with your friends…

If you have some idea…. Send me your projects and I’ll try to satisfy your requirement.

About Boris Landoni

Boris Landoni is the technical manager of Open-Electronics.org. Skilled in the GSM field, embraces the Open Source philosophy and its projects are available to the community.
  • Pingback: Electronics-Lab.com Blog » Blog Archive » How to send data from Arduino to Google Docs Spreadsheet

  • James

    Hacking URLs like this is a pretty bad idea – they are likely to change at some point. Why not use the Spreadsheets API?

    http://code.google.com/apis/spreadsheets/

    • lamebananas

      James. Be great if you could develop a tutorial similarly with Spreadsheets API.

  • Bloink Kabbuhi

    Now this method, Sir, is really awesome!
    I hope I can find time to try it out soon.

  • Pingback: How-To: Save Arduino Output to Google Docs Spreadsheets | dev.SquareCows.com

  • http://www.open-electronics.org Boris Landoni

    @ James. Use the API with Arduino it’s not simple. You have to be logged and, if I not mistaken, is required a secure connection. And with Arduino is not so simple do it.
    I hope that someone can me contradict.
    @Bloink Thank you :-) give me a feedback when you try it.

  • kevin

    Nice tutorial. @James if there was currently a way to use DNS with the Arduino Ethernet stack then it would be easier to not hack the URL, but for now it looks like this is the best option.

  • Stairs

    How would the code differ if instead of using an ethernet shield, you send the data through the serial (usb) port?

  • Stairs

    Nevermind, I got it. I have the Arduino read the data from via OneWire and then have Processing read the data from the serial port and post directly to a google spreadsheet without the form.

    Thanks though for getting me started!!!!

  • Shea

    I am just getting started with Arduino and have minimal programming experience.
    This has been a great start and is almost exaclty what I need for my project. I am having a hard time finding exactly what I need on the google spreadsheets API info pages.
    I’m trying make each entry overwrite the same cells everytime rather then add new lines.
    How do i accomplish this. Any help would be greatly appreciated.

  • nerdMuch

    How would I need to change this if I am using a WIFI shield instead of an ethernet shield?

    Thanks!

  • http://www.open-electronics.org Boris Landoni

    Hi, the system is the same. We developed a new WiFi shield like the Asynclab Shield. ASAP I’ll public a example

  • Pingback: How to send data from Arduino to Google Docs Spreadsheet | Future ot Internet | Scoop.it

  • Pingback: How to send data from Arduino to Google Docs Spreadsheet | Open Data Development with Joomla | Scoop.it

  • James W

    Cool hack indeed. Google’s API is tough, even provided client python/java libraries are so. I’ve used this one, called gspread for my python needs. I know it’s not an option for Arduino for now, but if you glance at the code it’s pretty easy to figure out how the auth process on and all data update methods are working.

  • PizzaEater

    Half of what I was looking for, I need to be able to read data out of a google doc back to the Arduino too, How do you do that?

  • http://No.com Amey

    Print.h:83: error: ‘const class Printable’ has no member named ‘printTo
    Some one help

  • Pingback: Cum sa trimiti loghezi pe Google Docs temperatura, presiunea atmosferica si umiditatea direct din Arduino | Robofun Blog

  • Tony

    Hi,
    Is there a way to achieve this using a GSM shield?

    Thanks
    Tony

  • http://www.open-electronics.org Boris Landoni

    Hi, you can use the code for GPRS function http://code.google.com/p/gsm-shield-arduino/downloads/list
    ASAP I publish a project with this function.

  • Dai anh Tai

    Hi.
    I posted data from wifi sheild to google spreadsheet successful.
    But i don’t know ,how to get data from google spreadsheet to arduino wifi.
    Everybody can help me!!!
    Thanks.

  • Dennis Schissler

    Do you know how I might sent a non-integer value? Like “68.6”? I suppose I can just send the integer and fractional values separately and then combine in the spreadsheet

  • http://www.open-electronics.org Boris Landoni

    The problem is that you can’t send the dot point with get method.

  • Pingback: Arduino & Google Drive: monitor dei sensori con trigger e invio notifiche

  • Rabe

    Thank you for the great tutorial which helped me to upload weather data to Google drive. Yesterday I noticed the upload stopped. :-(
    Obviously Google changed the ip-adress, replacing it with 173.194.66.113 got the scetch working again. :-)

    • NoahDropkin

      Is that ip unique to your spreadsheet?

  • Passive

    Hello,

    Great guide found it a while ago, but I have just battled with my WiFly to get it connected, and now i came to use this to send information to google docs and they have changed google docs so it no longer works. Have you or anyone reading this had success with it since the change?

    • BorisLandoni

      I found that they changed the system, but I have not studied the new method ..

      • mathias

        But If you can find a new method, that would be brilliant :) as I am not too sure if Google will keep this format much longer..

        • Adri

          Do you know if the old forms will delete or keep them in parallel?

        • Adri

          I am a newbie in this battles, but as I have seen for some IoT servers, for data sending use a PUT request instead of POST. Given that PUT is safe, and that the new forms are added security, there may be relationship. If so, can serve as an example: http://cosm.com/docs/v2/feed/update.html

      • mathias

        http://puravidaapps.com/taifunGS.php#new
        was trying something with the above link for the new form format.. with no success.. someone else give it a shot?

    • mathias

      http://michaelwalsh.org/blog/2013/02/05/work-around-for-google-forms-problem/
      found this, it will do for the while… cheers to the guy who discovered it!

      Thanks for this wonderful guide again..

      • BorisLandoni

        Thank you Mathias.
        Good work, thanks to share!!

  • Adri

    Thanks for this article, I being a big help on a project I have in mind, but with the new forms of google, I can not send data, only with direct URL method. Do you know if you still can do this with the new forms, or have blocked this option?

    • BorisLandoni

      As write Mathias in
      http://michaelwalsh.org/blog/2013/02/05/work-around-for-google-forms-problem/

      “Legacy Google Forms are still available IF you start creating the form by opening a SpreadSheet first. Once the spreadsheet is open, select “Create a legacy Form” from the “Tools” menu. The form editor and published URL will be the same as those that had worked prior to the Google Forms update.”

  • Igor Kovba

    Hi Boris.

    I’m using IDE 1.0.3 and seems Ethernet.h updated and I have to change a bit your sketch to compile it. If you ineresting… see my comments after // ——>
    may-be it can help others.

    #include // ——> for some reason in published scetch missing
    #include // ——> for some reason in scetch missing

    char formkey[] = “dFA3aGdKUmtoaGtQYmtXTskippedMQ”;

    byte mac[] = { 0x90,0xA2,0xDA,0x00,0x55,0x8D};

    byte ip[] = { 192,168,1,99};
    byte subnet[] = { 255,255,255,0};
    byte gateway[] = { 192,168,1,1};
    byte dnserv[] = { 192,168,1,1}; // ——> have to add this line
    byte server[] = { 173,194,41,136 };

    EthernetClient client; // ——> have to remove (server, 80)

    void setup()
    {
    Serial.begin(9600);

    Ethernet.begin(mac, ip ,dnserv, gateway , subnet); // ——> add “dnserv,”
    // otherwise arguments order is wrong
    delay(1000);

    Serial.println( Ethernet.localIP()); // ——> just to check
    Serial.println( Ethernet.subnetMask()); // ——> just to check
    Serial.println( Ethernet.gatewayIP()); // ——> just to check
    Serial.println( Ethernet.dnsServerIP()); // ——> just to check

    Serial.println(“connecting…”);
    }

    void loop(){

    String data;
    data+=””;
    data+=”entry.0.single=”;
    data+=10;
    data+=”&entry.1.single=”;
    data+=20;
    data+=”&entry.2.single=”;
    data+=-5;
    data+=”&submit=Submit”;

    if (client.connect(server, 80)) { // ——> have to add “server, 80″

    Serial.println(“connected”);

    …..

    Thanks.

    • Igor Kovba

      Strange….when i type it was ok….but in my post it look wrong….probably som bug in post comments script….
      #include // SPI should be capital
      #include // E should be capital

  • mohamed

    the whole URLs are changed, i can’t even find the formkey :/

  • Natthapong Watcharachaitalosot

    URLS are changed again. How can i get formkey?

  • Pingback: Arduino & Google Drive: monitor dei sensori con trigger e invio notifiche

  • Harrie Bouwens

    Hi, the sketch presented doesn’t work with me. I get an error on

    Client client(server, 80);

    “The class Client is now named EthernetClient”

    What libraries should be included, I only used Ethernet.h

  • Pingback: How to send data from Arduino to Google Docs Sp...

  • Morten Mo

    is this possible via pc serial arduino connection also?

    • BorisLandoni

      What do you mean?

  • Pingback: styczen

  • jack

    i got one question .

    is it it work with arduino by connecting cable to laptop and use laptop upload data

    or

    can use ethernetshield as gateway to upload the data through router?

    • BorisLandoni

      It use ethernet shield

  • jack

    where to get google IP?

    • BorisLandoni

      use PING function

  • Pingback: countydrugcard.org

  • Fahad Alrashid
  • Antonio Marcos

    Good day friend, I like your program but I need to read data from the table and make the Arduino make decisions for these data, for example an LED light

    you know how to do this?

    • BorisLandoni

      Hi, interesting project. At the moment we haven’t a solution, but I’ll study it ;-)

  • singha

    Hi Boris
    Can I use Arduino yun instead in the project?

    • BorisLandoni

      Yes but you have to change all the code….