Sensor input to Google Docs Spreadsheet

Does anyone have a (simple) method for having a sensor store it's data in a Google docs file? I've got the temp sensor thing down, been logging it with hyper terminal and capture text. So, now I'd like to be able to put this information in the cloud, keeping a real time graph, something accessible from elsewhere. Eventually I want to lead this into automated control of HVAC and such, but that isn't an issue here. I can upload the .csv file of course, manually, but i'd like this to put the sensor reading directly up somehow. Suggestions?? Thanks.

Hi, Google have pretty extensive APIs to handle working with their apps and data. In the case of Docs you might want to look at the Data APIs: http://code.google.com/apis/gdata/samples.html I might be wrong about that but at least you can dig into http://code.google.com Depending of your language of choice there are ready samples how to do certain things. Sorry for not being more of help than pointing to an URL.

language of choice…too bad we can’t code in conversational English. I’l see if i can figure it out…but i’m open if anyone has suggestions (or knows already how to do this kinda interfacing) I’m still learning the basic ardu coding…and slowly.

Here I found exactly the docs for working with Google spreadsheets: http://code.google.com/apis/spreadsheets/docs/1.0/developers_guide_php.html#updateCell

Plenty of examples how to work with spreadsheets in PHP.

Are you deadset on a google document?

You could check out www.pachube.com - getting sensor data online is what the site is designed for. They even have tutorials specifically to for arduino.

that sounds liek a definite plan. I don't particularly care how/where, i use gdocs as example because I use it for so much other stuff. If this lets me see the data nearly realtime, i'm set.

Much obliged all.

Now I've just gotta get the invite thingy

Hi, I've the some request of Byron. Does anyone have a (simple) method for having a sensor store it's data in a Google docs file?

I'm using Pachube and ok, but I want upload data in my Google Spreadsheet.

There is a Get Method to upload the cell? I find the method to read a cell but I can't write....

Thank you

I have pachube available now, but I don't know enough to get my living data into it. My project aim is to help to track the causes of my migraines, and hopefully get some proactive information to get treatment started ahead of the misery. My workplace is far enough east of my home that I think if i measure and report certain atmospheric conditions at home, I will have 45 minutes to an hour forewarning that I can get some preventive medicine on a head start.

I'm trying Pachube. And it's working. But I want try Google Speedsheet. In Pachube I can't (I think) delete some data. I haven't access to single data but only the result.

any luck?

Hi all,

I found this on Asynclabs forum : http://asynclabs.com/forums/viewtopic.php?f=16&t=489

there is an Arduino code to send data to a google documents spreadsheet i will try it soon...

hope this helps

best

a.

Hi,
here is a code that sends some values to a Google Spredsheet

It is based on RobertMParker’s code found here : http://asynclabs.com/forums/viewtopic.php?f=16&t=489
It uses an Ethernet shield.

You just have to put your own form key in the buf char array.

/*
        * A simple sketch that uses Ethernet Shield to send some values (via POST) to GoogleDocs
        * Based on code by RobertMParker for the WiShield <http://asynclabs.com/forums/viewtopic.php?f=16&t=489>
 */

#include <Ethernet.h>
#include <SPI.h>

#define DEBUG_PRINT // comment to disable Serial.print

long unsigned int time =0;

//Wireless configuration parameters ----------------------------------------
byte mac[] = {  0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
byte ip[] = {
  192,168,0,3};   // IP address of Ethernet Shield
byte gateway[] = {
  192,168,0,1};   // router or gateway IP address
byte subnet[]    = {
  255,255,255,0}; // subnet mask for the local network

// IP Address for spreadsheets.google.com
byte ipGoogle[] = {
  74,125,67,102};
char hostname[] = "spreadsheets.google.com";
char url[] = "/formResponse?formkey=<ENTER_YOUR_KEY_HERE>";



// create a client that connects to Google
Client clientGoogle(ipGoogle,80);

void setup()
{   
  // Enable Serial output and ask WiServer to generate log messages (optional)
#ifdef DEBUG_PRINT
  Serial.begin(115200);
#endif //DEBUG_PRINT
  Ethernet.begin(mac, ip, gateway, subnet);

  // give the Ethernet shield a second to initialize
  delay(1000);
  int time = millis();
  clientGoogle.connect();
}


void loop()
{
  float temperature = (float) random(1000)/10.; // create some random values to test
  float humidity = 45 + (millis()%1000)/100.;
  if ( millis() > time + 10000 && clientGoogle.connected()){ // update sheet each 10 s
    String feedData = "entry.0.single=" + String((int)temperature) + "," + String(int(temperature*100)%100) + "&entry.1.single=" + String(int(humidity)) + "," + String(int(humidity*100)%100) + "&pageNumber=0&backupCache=&submit=Envoyer";
    //Serial.println(feedData);
    postRequest(clientGoogle, ipGoogle, 80, hostname, url, feedData);
    time = millis();
  }
  delay(10);
}

void postRequest(Client client, byte *ip, unsigned int port, char *hostName, char *url, String feedData){ 
  String buf = "POST " + String(url) + " HTTP/1.1";

#ifdef DEBUG_PRINT 
  Serial.println(buf);
  Serial.println("Host: " + String(hostName));
  Serial.println("Content-Type: application/x-www-form-urlencoded");
  Serial.println("Content-Length: " + String(feedData.length()));
  Serial.println("");
  Serial.println(feedData);
  Serial.println("");
  Serial.println(""); 
#endif

  client.println(buf);
  client.println("Host: " + String(hostName));
  client.println("Content-Type: application/x-www-form-urlencoded");
  client.println("Content-Length: " + String(feedData.length()));
  client.println("");
  client.println(feedData);
  client.println("");
  client.println(""); // POST request as GET ends with 2 line breaks and carriage returns (\n\r);
}

Hope this helps.

A.

Hi

I have used you code for uploading data to my google spreadsheet, and it work, but just after 5-10 uploads of data it stops but my arduino keep trying sending data to google, does someone hat the same problem.

If i resetting my Arduino it starts again sending data and they are also being received in my spreadsheet but fail again after 5-10 times.

The code i use is total the same as posted above i have only changed ip and the thing for my spreadsheet

Jesper

Hi,

Indeed I have the same problem. First I was thinking this is due to my internet connection but it seems not. I've changed the code to better understand what happens and it seems that the client is disconnected from the google spreadsheet server after 3 minutes or something like that and can't reconnect until reset. It doesn't matter how many data i'm sending. I'm looking for a better method through Google spreadsheet API but I can't find a raw HTTP request example which works...

I will post here what i will have found

best

a.

Hi.

I look forward to see what you can find :o). I have now tried to disconnect every time i send a message and connect again before i send a message again and it seams that the problem i solved for now :-/.

Jesper

Hi

So now i have done some test the last 2 days, and it still working with sending data to google.
My code

/*
        * A simple sketch that uses Ethernet Shield to send some values (via POST) to GoogleDocs
        * Based on code by RobertMParker for the WiShield <http://asynclabs.com/forums/viewtopic.php?f=16&t=489>
 */

#include <Ethernet.h>
#include <SPI.h>
#include <OneWire.h>
#include <DallasTemperature.h>

  // Start Define variables ----------------------------------------
  
  #define DEBUG_PRINT // comment to disable Serial.print
  #define ONE_WIRE_BUS 9 // Data wire is plugged into port 9 on the Arduino
  long unsigned int time =0;
  long unsigned int Cntdevices;
  String SensorAdr1;
  String SensorVal;
  // End Define variables   ----------------------------------------

  // Start Ethernet configuration parameters ----------------------------------------
  byte mac[] = {  0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
  byte ip[] = {192,168,137,217};   // IP address of Ethernet Shield
  byte gateway[] = {192,168,137,1};   // router or gateway IP address
  byte subnet[]    = {255,255,255,0}; // subnet mask for the local network
  // End Ethernet configuration parameters   ----------------------------------------

  // Start configuration for spreadsheets.google.com ----------------------------------------
  byte ipGoogle[] = {74,125,67,102};
  char hostname[] = "spreadsheets.google.com";
  char url[] = "/formResponse?formkey=dFfkjrfrkjfrkjfhrkjfhEE6MQ";
  Client clientGoogle(ipGoogle,80); // create a client that connects to Google
  // End configuration for spreadsheets.google.com   ----------------------------------------

  // Start Temperatur Sensor OneWire ----------------------------------------
  // Pass our oneWire reference to Dallas Temperature. 
   OneWire oneWire(ONE_WIRE_BUS); // Setup a oneWire instance to communicate with any OneWire devices (not just Maxim/Dallas temperature ICs)
  DallasTemperature sensors(&oneWire);  // Pass our oneWire reference to Dallas Temperature. 
  // arrays to hold device addresses
  //DeviceAddress, insideThermometer, outsideThermometer;
  //Device 0 Address: 28D46688010000E5
  //Device 1 Address: 28 74 9F 88 01 00 00 EC
  uint8_t insideThermometer[8] = {0x28, 0xD4, 0x66, 0x88, 0x1, 0x0, 0x0, 0xE5};
  uint8_t outsideThermometer[8] = {0x28, 0x74, 0x9F, 0x88, 0x1, 0x0, 0x0, 0xEC};
  // End Temperatur Sensor OneWire   ----------------------------------------
 
void setup()
{  
  // Start setup   ----------------------------------------
  #ifdef DEBUG_PRINT // Enable Serial output and ask WiServer to generate log messages (optional)
  Serial.begin(115200);

  #endif //DEBUG_PRINT
  Ethernet.begin(mac, ip, gateway, subnet);
  delay(1000); // give the Ethernet shield a second to initialize
  int time = millis();
  //clientGoogle.connect();
  
  sensors.begin(); // Start up the library
  Serial.print("Locating devices...");
  Serial.print("Found ");
  Cntdevices = sensors.getDeviceCount(), DEC;
  Serial.print(Cntdevices);
  Serial.println(" devices.");
   
  Serial.print("Parasite power is: "); // report parasite power requirements
  if (sensors.isParasitePowerMode()) Serial.println("ON");
  else Serial.println("OFF");
 
  //if (!sensors.getAddress(insideThermometer, 0)) Serial.println("Unable to find address for Device 0"); 
  //if (!sensors.getAddress(outsideThermometer, 1)) Serial.println("Unable to find address for Device 1"); 
  
  Serial.print("Device 0 Address: "); // show the addresses we found on the bus
  printAddress(insideThermometer);
  Serial.println();
  
  Serial.print("Device 1 Address: "); // show the addresses we found on the bus
  printAddress(outsideThermometer);
  Serial.println();
    
  sensors.setResolution(insideThermometer, 12);  // set the resolution to 12 bit (Each Dallas/Maxim device is capable of several different resolutions)
  sensors.setResolution(outsideThermometer, 12);  // set the resolution to 12 bit (Each Dallas/Maxim device is capable of several different resolutions)
  
  Serial.print("Device 0 Resolution: ");
  Serial.print(sensors.getResolution(insideThermometer), DEC); 
  Serial.println();
  
  Serial.print("Device 1 Resolution: ");
  Serial.print(sensors.getResolution(outsideThermometer), DEC); 
  Serial.println();
  // End setup     ----------------------------------------
}
 // Start Function to print a device address ----------------------------------------
void printAddress(DeviceAddress deviceAddress)
{
  for (uint8_t i = 0; i < 8; i++)
  {
    if (deviceAddress[i] < 16) Serial.print("0");
    Serial.print(deviceAddress[i], HEX);
  }
}
  // End function to print a device address ----------------------------------------

  // Start nction to print the temperature for a device  ----------------------------------------
String getTemperature(uint8_t* deviceAddress)
{
  char buffer[20];
  return dtostrf((sensors.getTempC(deviceAddress)), 2, 3, buffer); 
}
  // End nction to print the temperature for a device    ----------------------------------------
  
  // Start loop for sending data to google  ----------------------------------------
void loop()
{ 
    sensors.requestTemperatures();  // Send the command to get temperatures
 if ( millis() > time + 8000){ // update sheet each 8 s
    clientGoogle.connect();
    delay(1000);
    String feedData = "entry.0.single=" + String(getTemperature(outsideThermometer)) + "&entry.1.single=" + String(getTemperature(insideThermometer)) + "&pageNumber=0&backupCache=&submit=Envoyer";
    postRequest(clientGoogle, ipGoogle, 80, hostname, url, feedData);
    time = millis();
  }
  delay(10);
  clientGoogle.stop();
}
  // End loop for sending data to google     ----------------------------------------
void postRequest(Client client, byte *ip, unsigned int port, char *hostName, char *url, String feedData){
  String buf = "POST " + String(url) + " HTTP/1.1";

#ifdef DEBUG_PRINT
  Serial.println(buf);
  Serial.println("Host: " + String(hostName));
  Serial.println("Content-Type: application/x-www-form-urlencoded");
  Serial.println("Content-Length: " + String(feedData.length()));
  Serial.println("");
  Serial.println(feedData);
  Serial.println("");
  Serial.println("");
#endif

  client.println(buf);
  client.println("Host: " + String(hostName));
  client.println("Content-Type: application/x-www-form-urlencoded");
  client.println("Content-Length: " + String(feedData.length()));
  client.println("");
  client.println(feedData);
  client.println("");
  client.println(""); // POST request as GET ends with 2 line breaks and carriage returns (\n\r);
}

Thanks Jesper,

it also works for me but I have to flush the client before disconnecting to avoid something like a buffer overflow

and I'm still looking for another way to better control the spreadsheet through the Google's API but for now, I can't build a POST request that works...

a/

My workplace is far enough east of my home that I think if i measure and report certain atmospheric conditions at home, I will have 45 minutes to an hour forewarning that I can get some preventive medicine on a head start.

I have a solution for you... but not an Arduino solution.

At....

http://mon277rr.dyndns.org/

... you can see various things that might affect migraines.. temperature, humidity, etc. The system if flecible, you can add various sensors. The vertical gray bars are midnights, the graph shows the data for about the past nine days. It is produced by a system called FarWatch

To run a FarWatch system, you need....

A Windows PC, always on, connected to an always on internet connection. (The FarWatch page covers a bunch of things, many of which are intelligible by a Linux user... but the FarWatch page is written in Windows terms.)

Several pieces of free software.

A program running in the computer to create a graphic file... that graphic file is where the graph comes from. The basic FarWatch program doesn't care how that file is created. The FarWaatch pages tell you ONE way of creating the graphic file... my program DS025.

You can run a demo version of DS025 for free, just to be sure you can manage it. To create graphs as complex as the one in the link given above, you have to purchase a DS025 license. I'll cut $10 off the price of any DS025 license purchased with reference to this post until April 2011.

To run DS025, you need to equip your PC with a 1-Wire network. That involves an adapter, which you will use for any 1-Wire setup on your PC, and sensors.

Much, much more at....

http://sheepdogsoftware.co.uk/ssds025.htm

... which explains the DS025 display in detail, and has links to the FarWatch pages.

The basic FarWatch, remember, costs nothing beyond the computer and your time setting things up.

You COULD create the graphic with a program reading data from an Arduino connected to the PC, but I don't have such a program available for you.

As most of the above is not Arduino related, I hope that it won't get much discussion here in the Arduino forum.... although an Arduino program to "plug in" to FarWatch would certainly be valid. But I hope you agree that it was relevant to what the OP wanted to know?