Friday, April 10, 2009

Google Spreadsheets and Time Line Graphs

Below Should be a nice graph of my solar usage instead of an image

12 comments:

Jean-Marc said...

Hi,

How are you doing to automatically feed google document with you data to create this up to date graph?

Thanks,

JM

Desra said...

Hi Jean-Marc,

i use rrdtool to store my data with rrdcollect. i have a 5 min cron job that runs rrdexport to give 5 min interval data for 2 weeks of my solar to a csv.

my google spreadsheet uses the importdata function to pull in the csv via http. this sheet is then the source for the graph gadget.

everytime the graph is drawn, it pulls from the sheet, which polls from my csv. there is some caching of data in the gadget and the sheet so it's not polling my csv everytime. i'm not sure what the caching is yet, but its built into the sheet. not configured by me.

i not there is now a pachube app using the google viz api, so it should be possible to do it that way also.

i did try a sheet using pachube as a source, but found it unreliable

Jean-Marc said...

Hi Desra,

Thanks for your reply. I still have some troubles to reproduce the graphique with my own data.

I'm fine with the rrd capture and the rrd xport.

I tried to convert the XML to CSV using xml2 and 2csv with no success, so I'm now using cat, grep and replace instead.

My CSV is here: https://ssl.spaggiari.org/export.csv

Then I'm able to upload it to Google Docs through my http server, and I'm also able to generate the gadget. I'm then able to publish the gadget to an HTML page (at the bottom of https://ssl.spaggiari.org/eau.html) but...

How do you put your CSV file into Google Doc with a script? Because with what I did, everything is static. Even if the xport is on the cron. Can you share some hints for the "automatic upload" to google docs?

Thanks,

JM

Desra said...

Hi Jean-Marc,

use rrdfetch, it's easier

rrdtool fetch -s -100h /tmp/RESOL.rrd AVERAGE | grep "^1" | tr " " "," | tr ":" "," | cut -d"," -f1,3-6 > /tmp/solartemps.csv

makes a neat csv that i serve using mini_httpd

then my sheat has a first line of
time,collector,bottomhwc,tophwc,return
in cells a1-a5
then in b1 this formula, =arrayformula({ImportData("http://my.home.com:21171/solartemps.csv")} / {86400,1,1,1,1} + {25569,0,0,0,0})

this formula will fill the columns and make sense of the date format. I then format col. A as time 2009/4/4 10:55:55 , type format
and lastly i make an entry in a1301 so that the range a1:e1300 is always valid as a source range for the graph.

i hope this makes sense and will help :)

Jean-Marc said...

Oh, yes, it's going to help a lot! Thanks!

I was not aware of the import formula.

However, I tried your formula and I get an error. Seems that I don't have the "arrayformula" formula.

What this formula is supposed to do? Is it supposed to adjust the timespamp? Can it be because I don't have enought values yet?

Thanks,

JM

Jean-Marc said...

Ok, I found the issue.

{86400,1,1,1,1} is beause you have 5 column. But so far I have only 1 for my tests. To I removed some and it's now working fine!

thanks a lot for your help!

JM

Jean-Marc said...

Hi Desra,

Is the graphic still working for you? I'm not able to see it anymore since the last 2 days.

Also, with google gadget, seems that you can have your http file as the graphic source without going through Google Docs, but you will have to update the file content to have the right date format.

JM

Desra said...

mmm, weird, not sure why it's not working ... the same code in anyother html page works, looks like a blogger acl to google sheets, need to look at it more

Jean-Marc said...

Yes, wierd. Mine is not working any more. I also tried by using a direct http file, from the google gadget page, and it's not working either.

http://tbaoebshgeq225lhq2bam0m0a5mf6u0b.open.gmodules.com/ig/creator?synd=open&url=http://www.google.com/ig/modules/time-series-line.xml&lang=en

Maybe google desktop is down? But I will be surprised.

I checked the logs, and it'S querying the file correctly. But tt's not rendering it...

JM

Desra said...

bizzare, google problem, this sites page works
http://sites.google.com/site/myhomeeco/dashboard

Jean-Marc said...

I'm able to see your chart. So I tried to create mine under google sites, but it's not working for me. I'm getting he same error message.

http://sites.google.com/a/spaggiari.org/dashboard/

I tried with an HTTP source for the data with no sucess.

Desra said...

This happened again , so I had a closer look, it happens when rrdtool outputs a NaN, this gadget breaks if you pass a non number.

I added

awk '{gsub(/nan/,"0");print}'

to my command line, to swap it to a zero

load testing