How to send data from Arduino to Google Docs Spreadsheet

By on July 8, 2011
Pin It

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.







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.



jlcpcb.com

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.

84 Comments

  1. Pingback: Electronics-Lab.com Blog » Blog Archive » How to send data from Arduino to Google Docs Spreadsheet

  2. James

    July 16, 2011 at 3:34 PM

    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

      September 11, 2014 at 4:05 AM

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

  3. Bloink Kabbuhi

    July 17, 2011 at 10:26 AM

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

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

  5. Boris Landoni

    July 17, 2011 at 4:49 PM

    @ 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.

  6. kevin

    July 18, 2011 at 4:54 PM

    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.

  7. Stairs

    August 6, 2011 at 8:53 PM

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

  8. Stairs

    August 8, 2011 at 12:28 AM

    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!!!!

  9. Shea

    October 1, 2011 at 1:00 AM

    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.

  10. nerdMuch

    October 8, 2011 at 8:01 PM

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

    Thanks!

  11. Boris Landoni

    October 11, 2011 at 7:44 AM

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

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

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

  14. James W

    December 13, 2011 at 1:31 PM

    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.

  15. PizzaEater

    January 23, 2012 at 6:49 PM

    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?

  16. Amey

    February 1, 2012 at 6:22 PM

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

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

  18. Tony

    February 3, 2012 at 5:47 AM

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

    Thanks
    Tony

  19. Boris Landoni

    February 3, 2012 at 9:20 AM

    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.

  20. Dai anh Tai

    May 19, 2012 at 3:10 AM

    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.

  21. Dennis Schissler

    August 28, 2012 at 5:44 PM

    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

  22. Boris Landoni

    August 29, 2012 at 9:17 AM

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

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

  24. Rabe

    January 12, 2013 at 8:24 PM

    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

      February 2, 2013 at 4:16 AM

      Is that ip unique to your spreadsheet?

  25. Passive

    February 23, 2013 at 8:22 PM

    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

      February 28, 2013 at 11:10 AM

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

      • mathias

        March 5, 2013 at 9:23 AM

        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

          March 6, 2013 at 3:53 PM

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

        • Adri

          March 12, 2013 at 6:47 PM

          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

        March 16, 2013 at 7:45 AM

        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

      March 5, 2013 at 8:54 AM

      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

        March 8, 2013 at 9:16 AM

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

  26. Adri

    March 6, 2013 at 3:47 PM

    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

      March 8, 2013 at 9:18 AM

      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.”

  27. Igor Kovba

    March 12, 2013 at 10:51 PM

    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

      March 12, 2013 at 11:04 PM

      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

  28. mohamed

    April 28, 2013 at 11:19 AM

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

  29. Natthapong Watcharachaitalosot

    September 6, 2013 at 11:22 AM

    URLS are changed again. How can i get formkey?

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

  31. Harrie Bouwens

    October 8, 2013 at 9:02 PM

    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

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

  33. Morten Mo

    November 8, 2013 at 4:07 PM

    is this possible via pc serial arduino connection also?

    • BorisLandoni

      November 30, 2013 at 10:02 AM

      What do you mean?

  34. Pingback: styczen

  35. jack

    January 18, 2014 at 12:51 PM

    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

      January 29, 2014 at 12:31 PM

      It use ethernet shield

  36. jack

    January 21, 2014 at 6:58 AM

    where to get google IP?

    • BorisLandoni

      January 29, 2014 at 12:31 PM

      use PING function

  37. Pingback: countydrugcard.org

  38. Fahad Alrashid

    March 8, 2014 at 9:24 PM

  39. Antonio Marcos

    March 14, 2014 at 1:30 PM

    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

      March 20, 2014 at 6:04 PM

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

  40. singha

    March 25, 2014 at 3:16 AM

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

    • BorisLandoni

      April 19, 2014 at 11:32 AM

      Yes but you have to change all the code….

      • Quixo

        December 16, 2014 at 12:19 PM

        Hi there, I’m also trying to use this code with the Yun and running into problems, have either of you been able to adapt the code?

        would be great if you could let me know. thanks a lot
        Ben

  41. Piyali Das

    December 22, 2014 at 1:36 AM

    I am trying to do do project with Arduino uno, sensor, LED and WiFi shield. I am taking analog input of sensor data in Arduino uno. The basic program lit up an LED if the sensor value exceeds a threshold level. And this program is working fine. Now I want simultaneously to upload the input data to Google spreadsheet with the help of WiFi shield. After mounting the WiFi shield the basic functionality is even not getting executed. Hence I am wondering whether accessing two hardware level components simultaneously is supported by Arduino micro-controller or not?

    • BorisLandoni

      December 29, 2014 at 6:23 PM

      If the two shield are using the same pins probably doesn’t work.

  42. Anjesh

    February 5, 2015 at 5:15 AM

    Wonderful.. Thanks a lot

  43. Swamygovinda

    April 6, 2015 at 3:56 PM

    Can any one explain me how to upload data to new google spreadsheet (which is different from the one explained in this section)

    • muundu

      October 1, 2015 at 4:47 PM

      Hey, i am doing a project and i want to send data logged from my Gboard pro to the google spreadsheet. i am wondering if you managed to solve the problem and can help me out. I am currently sending data to my google spreadsheet via pushingbox.com from my board but i am only limited to 1000 requests a day and for analysis purpose i need to send more then that.

  44. Nathan Dudley

    April 9, 2015 at 7:31 PM

    Thanks! This was great! I was able to build a charging dashboard for my electric vehicle. http://www.rainorshine.bike/tessa

  45. Rishabh

    April 14, 2015 at 9:46 PM

    Mine is not connecting. It keeps on giving disconnecting on the serial monitor.

  46. Sin Iat Chi

    April 23, 2015 at 11:32 PM

    Does it work with Arduino Yun too? I was trying to modify the code but I just don’t know where to start……..Please help

    • BorisLandoni

      May 27, 2015 at 10:31 PM

      We don’t try, but probably yes

  47. Guy Chemla

    May 19, 2015 at 9:44 AM

    Hello Boris and thanks for sharing this useful approach for logging. I am blocked at getting the form key: Could you please explain how exactly i can find the key and if the url you gave (below) is the good one

    https://spreadsheets.google.com/formResponse?formkey= &ifq&=&=&submit=Submit

    Thanks in advance for your time

    • Dtjuh

      May 27, 2015 at 1:07 PM

      anny luck? I am looking at the same problem

      • Guy Chemla

        May 28, 2015 at 6:45 AM

        Not so far, another approach would be sparkfun logger. Didn’t try it yet

  48. john

    August 8, 2015 at 3:02 PM

    In my project i have to send data of ultrasonic sensor output to spreadsheet using arduino uno and wifi.how this can be done.if any one have code for this then pleasr share it.

  49. Denis Eduardo Donadon Torres

    November 17, 2015 at 7:02 PM

    It doesn’t works, could anyone help me please?

  50. Brian

    November 18, 2015 at 5:57 PM

    Any chance this will ever be updated with fresh information? Everything here is outdated. It is hard to follow and I have had no luck making it work.
    Thanks

  51. kamesh dkr

    December 15, 2015 at 6:52 AM

    BorisLandoni
    Hi, I have connected Arduino with Ethernet shield. As per your instruction I have created the form and implemented the key to it into the code. Everything seems connected successful. But I couldn’t see the updated data in google sheet. Please help in this.

    • BorisLandoni

      January 7, 2016 at 5:56 PM

      This is a very old post.
      In the comments some people solve the problems

    • Madina Nazar

      October 10, 2016 at 11:16 AM

      Hi! I know this is very old, but did you solve the problem? I have the same problem here.

      • kamesh dkr

        October 11, 2016 at 6:47 AM

        @madinanazar:disqus : Thanks for getting back Madina, I have solved this issues.

        • Madina Nazar

          October 11, 2016 at 8:48 AM

          Can you help me with it? What was the problem?

          • kamesh dkr

            October 11, 2016 at 9:03 AM

            I used SFG software to integrate the data from the file. Instead of using the above method.

  52. Pingback: Google Container Data Center | onlinesystembackup.com

  53. binbin

    October 27, 2016 at 11:04 AM

    ôi 15:50

    i want to creat 1 google form

    to send data from sim 900 to google

    but im not look formkey

    this is my https://docs.google.com/forms/d/e/1FAIpQLSe7iAyEnx5QcFw0ylepEiZ5AtJ4ACl-rFioqnPU48TE7w5gJA/viewform

    can u help me?

  54. Pingback: Google Docs Trying To Connect | ch

Leave a Reply