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.
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
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 <Ethernet.h>
#include <SPI.h>
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.
Related posts:
(3 votes, average: 5.00 out of 5)
Loading ...
[...] to send data from Arduino to Google Docs Spreadsheet – [Link] Tags: Arduino, Google, Pachube, Spreadsheet, Wifi Filed in Mcu | 1 views No Comments [...]
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/
Now this method, Sir, is really awesome!
I hope I can find time to try it out soon.
[...] 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 [...]
@ 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.
give me a feedback when you try it.
I hope that someone can me contradict.
@Bloink Thank you
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.
How would the code differ if instead of using an ethernet shield, you send the data through the serial (usb) port?
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!!!!
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.
How would I need to change this if I am using a WIFI shield instead of an ethernet shield?
Thanks!
Hi, the system is the same. We developed a new WiFi shield like the Asynclab Shield. ASAP I’ll public a example
[...] 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 [...]
[...] 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 [...]
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.
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?
Print.h:83: error: ‘const class Printable’ has no member named ‘printTo
Some one help
[...] mi-au picat ochii pe un exemplu simpatic care demonstreaza cum poti sa folosesti un Google Form ca sa trimiti date din Arduino direct [...]
Hi,
Is there a way to achieve this using a GSM shield?
Thanks
Tony
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.
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.
Leave your response!
Most Viewed
You can free download all our projects from issue n.1 to n. 123!
www.elettronicain.it
Tags
LabVIEW World - Current Issue
LabVIEW World is a magazine intended for the Italian community of NI LabVIEW users. Inside this magazine you will find articles containing news of the LabVIEW world, tutorials on the best way to approach the most difficult applications, tricks and secrets from other developers.
www.labviewworld.it