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.

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
Leave a Reply

avatar
54 Comment threads
30 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
45 Comment authors
Google Docs Trying To Connect | chbinbinMadina NazarGoogle Container Data Center | onlinesystembackup.comkamesh dkr Recent comment authors
  Subscribe  
newest oldest most voted
Notify of
trackback

[…] to send data from Arduino to Google Docs Spreadsheet – [Link] Tags: Arduino, Google, Pachube, Spreadsheet, Wifi Filed in Mcu | 1 views No Comments […]

James
Guest
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
Guest
lamebananas

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

Bloink Kabbuhi
Guest
Bloink Kabbuhi

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

trackback

[…] quite a few ways to get data from your Arduino onto the internet, but I especially like this one. Open Electronics has a nice tutorial on using your Arduino to send data to be saved into a Google Spreadsheet. The trick is to use […]

kevin
Guest
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
Guest
Stairs

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

Stairs
Guest
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
Guest
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
Guest
nerdMuch

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

Thanks!

trackback

[…] How to send data from Arduino to Google Docs Spreadsheet 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. Source: http://www.open-electronics.org […]

trackback

[…] How to send data from Arduino to Google Docs Spreadsheet 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. Source: http://www.open-electronics.org […]

James W
Member
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
Guest
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?

Amey
Guest

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

trackback

[…] mi-au picat ochii pe un exemplu simpatic care demonstreaza cum poti sa folosesti un Google Form ca sa trimiti date din Arduino direct […]

Tony
Guest
Tony

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

Thanks
Tony

Dai anh Tai
Guest
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.

Thiru
Guest
Thiru

hey which wifi shield you used ….???

BorisLandoni
Guest
BorisLandoni
Dennis Schissler
Guest
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

trackback

[…] dettagliato e spiegato bene, anche se è una traduzione di un articolo pubblicato 3 mesi prima How to send data from Arduino to Google Docs Spreadsheet. Io allo sketch ho apportato alcune modifiche di miglioramento, ma quelli pubblicati vanno […]

Rabe
Guest
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
Guest
NoahDropkin

Is that ip unique to your spreadsheet?

Passive
Guest
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
Guest
BorisLandoni

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

mathias
Guest
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
Guest
Adri

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

Adri
Guest
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
Guest
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
Guest
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
Guest
BorisLandoni

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

Adri
Guest
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
Guest
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
Guest
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; //… Read more »

Igor Kovba
Guest
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
Guest
mohamed

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

BorisLandoni
Guest
BorisLandoni
Natthapong Watcharachaitalosot
Guest
Natthapong Watcharachaitalosot

URLS are changed again. How can i get formkey?

trackback

[…] dettagliato e spiegato bene, anche se è una traduzione di un articolo pubblicato 3 mesi prima How to send data from Arduino to Google Docs Spreadsheet. Io allo sketch ho apportato alcune modifiche di miglioramento, ma quelli pubblicati vanno […]

Harrie Bouwens
Guest
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

trackback

[…] 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.  […]

Morten Mo
Guest
Morten Mo

is this possible via pc serial arduino connection also?

BorisLandoni
Guest
BorisLandoni

What do you mean?

trackback

Recommeneded websites

Here you’ll find some sites that we think you’ll appreciate, just click the links over

jack
Guest
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
Guest
BorisLandoni

It use ethernet shield

jack
Guest
jack

where to get google IP?

BorisLandoni
Guest
BorisLandoni

use PING function

trackback

Check this out

Here are some of the sites we recommend for our visitors

Fahad Alrashid
Guest
Fahad Alrashid
Antonio Marcos
Guest
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
Guest
BorisLandoni

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

singha
Guest
singha

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

BorisLandoni
Guest
BorisLandoni

Yes but you have to change all the code….

Quixo
Guest
Quixo

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

Piyali Das
Guest
Piyali Das

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
Guest
BorisLandoni

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

Anjesh
Guest
Anjesh

Wonderful.. Thanks a lot

Swamygovinda
Guest
Swamygovinda

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

muundu
Guest
muundu

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.

Nathan Dudley
Guest
Nathan Dudley

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

Rishabh
Guest
Rishabh

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

Sin Iat Chi
Guest
Sin Iat Chi

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
Guest
BorisLandoni

We don’t try, but probably yes

Guy Chemla
Guest
Guy Chemla

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
Guest
Dtjuh

anny luck? I am looking at the same problem

Guy Chemla
Guest
Guy Chemla

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

john
Guest
john

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.

Denis Eduardo Donadon Torres
Guest
Denis Eduardo Donadon Torres

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

Brian
Guest
Brian

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