Google Spreadsheets and Time Line Graphs

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

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
April 15, 2009 at 9:36 AM
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
April 15, 2009 at 2:37 PM
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
April 18, 2009 at 9:57 AM
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 :)
April 18, 2009 at 1:11 PM
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
April 18, 2009 at 1:42 PM
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
April 18, 2009 at 1:46 PM
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
April 21, 2009 at 12:39 PM
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
April 21, 2009 at 2:11 PM
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
April 21, 2009 at 2:47 PM
Desra said…
bizzare, google problem, this sites page works
http://sites.google.com/site/myhomeeco/dashboard
April 21, 2009 at 3:31 PM
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.
April 21, 2009 at 4:14 PM
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
June 2, 2009 at 3:42 PM
Post a Comment

Popular posts from this blog

Baileys liquor Chocolate Chip and Cream desert

-
Simple and very very very yummy. Prepare today for tomorrow 1-2 pks (14 biscuits approx but depends on size) of really good quality double chocolate chip cookies 100ml Baileys Cream Liqueur 250ml Cream, whipped Chocolate Shavings, Drops to decorate, or Cadburys Flake broken up, or sprinkle of cocoa Use a 9" spring form tin otherwise its tricky to remove the finished cake Pour the Bailey into a bowl One by one, dip or soak (depending on how strong you want it) the biscuits into the Baileys Once each on is soaked place in the tin until you have a layer Cover and spread evenly the freshly whipped cream Repeat the dipping processing and make another layer on top of the cream Cover and spread with cream again Sprinkle with the chocolate shavings or drops or flake etc to decorate Leave in the fridge overnight to allow the biscuits to absorb the cream mixture Remove from fridge just before serving and enjoy !!! (Increase quantities of Baileys and biscuits as required) Serve as is without...
Read more

using t1n1wall, opnsense or pfsense on Google Compute Engine GCE

-
I have been doing some work in GCP recently, both appengine , and GCE.  We wanted to make sure all our instances were on private ips and only the LBs had internet ips. This gave us the problem of how to allow our instances reach the internet for updates or for api calls outside. In AWS you had a NAT gateway, but in GCP this doesn't exist. So I set about looking at the easiest way to do NAT from a private IP subnet to a Public address.  I am very familiar with m0n0wall and t1n1wall and a tiny bit with pfsense and less with opnsense. The tl;dr is that all these distributions ship an image which within the distribution there is a disk image.  Taking this internal image, renaming it and re-compressing it is all you need to do to get it working in GCE. you can configure it via the serial port using these instructions  There are guides out there around doing things with Linux and stuff, but you can skip that step. I took the latest version ...
Read more

nginx decode base64 url for use with imgproxy

-
 i've been testing imgproxy, to handle our image serving needs, and it looks good. our existing servers are php based, and we sign and encode our urls for images.  To test out imgproxy , I wanted to simply drop it in as a replacement for our servers by sending a % of traffic.  There are many ways to do this, varnish was one, with custom code, but nginx is our go-to web server, so I had to find a way to have nginx sit in front of imgproxy and rewrite the decoded url. I settled on using njs, the cut down version of javascript that plugs into nginx as a loadable module.  Then use proxy_pass to pass the uri to javascript that will return the imgproxy compatable url, and proxy to it. a sample url would be http://foo.bar/images/c2lnbmF0dXJlZm9vaHR0cDovL3MzLWV1LXdlc3QtMS5hbWF6b25hd3MuY29tL215YnVja2V0b2ZwaG90b3MvcGhvdG9fb2ZfYV9jYXQ1fHx8MTIwMHgxMjAwfHx8fHx8fHw==.jpeg it has a sig, a bucket url, and parameters like image size. Getting nginx setup nginx.conf load_module modu...
Read more