Experimenting with Google Charts and Graphs API.

lardconcepts · **Posted:** Fri Apr 06, 2012 6:26 pm

nitrx wrote:

Thank you very much great job my first samplegraph http://apeldoorn.tk/weer/googlegraph.html

My pleasure - now I need to get my head into the advanced options and make some REALLY smart looking graphs

beteljuice · **Posted:** Fri Apr 06, 2012 9:38 pm

I don't know which 'route' you took to modify nitrx style data, but it seems to work. I didn't check early years data (they used to have a different line ending which can stuff parsers !)

Not totally sure exactly what format you need for the google graphs, but here is a beteljuice (php) take on things. In the google data call it should refer to eg. csv.php

csv.php

Code:

<?php
/*
   modify dayfile.txt for google graphs format
   beteljuice April 2012
   
   create a cache (csv) file no more than once every 6 hrs (don't know or care when you update your dayfile.txt)
   
*/

$dayfile = "http://www.apeldoorn.tk/weer/dayfile.txt"; // path or full url to dayfile

$then = time() - (6 * 60 * 60); // 6 hours ago

if(!file_exists("dayfile.csv") || (file_exists("dayfile.csv") && filemtime("dayfile.csv") < $then)) {
     $raw = file($dayfile);
     if($raw){
        $output = "";
        // cheat to see if . is dec delimiter
        $dec_check = substr($raw[0], 10, 4); // get a chunk which includes temp
        if (strpos($dec_check, ".") !==FALSE ){ // must be .
            $delim = 0;
        } else {
            $delim = 1; // dec delimiter needs replacing (assuming ",")
        }
        $date_check = substr($raw[0], 2, 1); // get date seperator
        // check date seperator is /
        if ($date_check == "/"){ // must be /
            $datelim = 0;
        } else {
            $datelim = 1; // needs replacing
        }
        // find field seperator
        $field_check = substr($raw[0], 8, 1);
        if ($field_check == ","){ // not a problem
            $fieldlim = 0;
        } else {
            $fieldlim = 1; // needs replacing
        }
        foreach($raw as $key) { // step through the raw data
            $key = rtrim($key); // get rid of last data field line returns prior to 1st Nov 2010
            if($delim){
                $key = str_replace(",", ".", $key); // make sure dec seperator is .
            }
            if($fieldlim){
                $key = str_replace($field_check, ",", $key); // make sure field seperator is ,
            }
            if($datelim){ // php4 work around
                $date_check = "/" .$date_check. "/";
                $key = preg_replace('.$date_check.', "/", $key, 2); // make sure date seperator is /
            }
            $output .= $key."\n";
        } // end foreach $raw

        // save the cache file
        $fh = fopen("dayfile.csv", "w");
        fwrite($fh, $output);
        fclose($fh);
    } // END if $raw
} // a valid dayfile.csv should exist ! (unless perhaps you are running in safe mode)
readfile("dayfile.csv");
?>

nitrx · **Posted:** Sat Apr 07, 2012 9:37 am

I think there must be something wrong with the spreadsheet the cells aren't calculated at the end when a new day was added, I updated the dayfile.txt on my server but after an hour the graph stayed stuck on the 5th of april last cells in the speadsheet seems to be out of range ?

BTW, Beteljuice I see the dayfile.csv is created on your server nice job maybe it can be a solution...

nitrx · **Posted:** Sat Apr 07, 2012 10:30 am

beteljuice wrote:

create a cache (csv) file no more than once every 6 hrs (don't know or care when you update your dayfile.txt)

*/

$dayfile = "http://www.apeldoorn.tk/weer/dayfile.txt"; // path or full url to dayfile

$then = time() - (6 * 60 * 60); // 6 hours ago

As I don't almost know nothing about php can this time-interval be increased something like

Code:

$then = time() - (0 * 0 * 0); // 0 seconds ago

I don't understand this formula ...

Second how does the script know to access the dayfile.txt without a command to process ?? :roll:

lardconcepts · **Posted:** Sat Apr 07, 2012 10:34 am

nitrx wrote:

I think there must be something wrong with the spreadsheet the cells aren't calculated at the end when a new day was added, I updated the dayfile.txt on my server but after an hour the graph stayed stuck on the 5th of april last cells in the speadsheet seems to be out of range ?

That is really weird; I found the culprit - in "merged data", the last row had "skipped" one, so that the formulas were:
=ArrayFormula(JOIN(",",'Raw import'!A838:V838))
=ArrayFormula(JOIN(",",'Raw import'!A839:V839))
=ArrayFormula(JOIN(",",'Raw import'!A841:V841))

Skipping
=ArrayFormula(JOIN(",",'Raw import'!A840:V840))

So it was looking for tomorrow's data, which wasn't there yet of course.

I've re-aligned it, not sure how that happened unless a row was deleted or I messed up.
I might try doing one from scratch, see if I can duplicate, and then post in Google Help if it does it again.

Anyway, you're fixed for now - fingers crossed for tomorrow!

PS - are you uploading it automatically through Cumulus? No need to edit anything manually, let Cumulus do the upload

beteljuice wrote:

I don't know which 'route' you took to modify nitrx style data, but it seems to work.

Click back a page. All is explained in this post

A php script is OK if you want to avoid Google Docs, but with my way, there's no need for any server side processing, and you get your graphs data in the right format.

Not saying your way is bad, wrong, or anything, but I don't see an immediate advantage.

That said, we've yet to see if "my way" is actually reliable - time will tell

nitrx · **Posted:** Sat Apr 07, 2012 10:58 am

Quote:

are you uploading it automatically through Cumulus? No need to edit anything manually, let Cumulus do the upload

The dayfile is allways uploaded at 00:15 automatically by cumulus toolbox so I don't edit manually, I've copied the new spreadsheet and will upload manualy some dayfiles with sampledata and see what happen. Thanx for investigating, any clue to insert a wait spinner in the html page :?:

nitrx · **Posted:** Sat Apr 07, 2012 12:11 pm

I've added a new (vitual) day to the dayfile.txt april 7th the dayfile is imported in the spreadsheet but gives errors in the worksheet I think this will not work

It seems your graph isn't updated either ? http://weather.talking-news.info/ggraphs/index.html

beteljuice · **Posted:** Sat Apr 07, 2012 1:04 pm

@ nitrx

nitrx wrote:

beteljuice wrote:

create a cache (csv) file no more than once every 6 hrs (don't know or care when you update your dayfile.txt)

*/

$dayfile = "http://www.apeldoorn.tk/weer/dayfile.txt"; // path or full url to dayfile

$then = time() - (6 * 60 * 60); // 6 hours ago

As I don't almost know nothing about php can this time-interval be increased something like

Code:

$then = time() - (0 * 0 * 0); // 0 seconds ago

I don't understand this formula ...

time() is now (since the 'epoch') in seconds.
So time() + is in the future, time() - is in the past.
so in seconds, 6 hours * 60 minutes * 60 seconds

nitrx wrote:

Second how does the script know to access the dayfile.txt without a command to process ?? :roll:

beteljuice wrote:

In the google data call it should refer to eg. csv.php

In otherwords

Code:

=ImportData("http://www.apeldoorn.tk/weer/csv.php")

As you know 'when' your .txt update 'should' occur, the time part could be rewritten to look for a specific (rolling) date / time.

@ lardconcepts

Sorry, I must have been coding or 'out to lunch' and missed your post.

Have I missed something else - does this google graphs api download everything to the browser ?
Suddenly it's not such an attractive proposition for people with limited bandwidths - pity.

My code is not nitrx specific and 'should' work in the majority of cases, if nothing else there's enough of an 'engine' there for people who may have been wondering how to convert their data so they can perform math / comparrisons

lardconcepts · **Posted:** Sat Apr 07, 2012 1:07 pm

nitrx wrote:

I've added a new (vitual) day to the dayfile.txt april 7th the dayfile is imported in the spreadsheet but gives errors in the worksheet I think this will not work

It seems your graph isn't updated either ? http://weather.talking-news.info/ggraphs/index.html

Ah, that was my error: I'd pasted the wrong graph embed; the only difference in that long URL is:

range=A%3AA%2CP%3AP&gid=2
(decoded: range=A1:A845,P1:P845&gid=2)

range=A%3AA%2CP%3AP&gid=2
(decoded: range=A:A,P:P&gid=2)

Now, this makes MY graph update, not sure why yours isn't when you add more data, but I'll have a poke around and an ask on Google Groups.

I'm glad someone mentioned Toolbox (which I hadn't spotted before). Looks like it might help here - it seems daft for Cumulus to be uploading dayfile.txt every 15 minutes when it only changes once a day! I also didn't know about http://cumulus.tnetweather.com/projects/jpgraphgraphs

Going to have a hack around and will come back later, hopefully with an improved model!

nitrx · **Posted:** Sat Apr 07, 2012 1:16 pm

beteljuice wrote:

@ nitrx

nitrx wrote:

beteljuice wrote:

create a cache (csv) file no more than once every 6 hrs (don't know or care when you update your dayfile.txt)

*/

$dayfile = "http://www.apeldoorn.tk/weer/dayfile.txt"; // path or full url to dayfile

$then = time() - (6 * 60 * 60); // 6 hours ago

As I don't almost know nothing about php can this time-interval be increased something like

Code:

$then = time() - (0 * 0 * 0); // 0 seconds ago

I don't understand this formula ...

time() is now (since the 'epoch') in seconds.
So time() + is in the future, time() - is in the past.
so in seconds, 6 hours * 60 minutes * 60 seconds

nitrx wrote:

Second how does the script know to access the dayfile.txt without a command to process ?? :roll:

beteljuice wrote:

In the google data call it should refer to eg. csv.php

In otherwords

Code:

=ImportData("http://www.apeldoorn.tk/weer/csv.php")

As you know 'when' your .txt update 'should' occur, the time part could be rewritten to look for a specific (rolling) date / time.

My code is not nitrx specific and 'should' work in the majority of cases, if nothing else there's enough of an 'engine' there for people who may have wondering how to convert their data so they can perform math / comparrisons

Thank Betel it's clear to me

nitrx · **Posted:** Sat Apr 07, 2012 1:21 pm

lardconcepts wrote:

nitrx wrote:

I've added a new (vitual) day to the dayfile.txt april 7th the dayfile is imported in the spreadsheet but gives errors in the worksheet I think this will not work

It seems your graph isn't updated either ? http://weather.talking-news.info/ggraphs/index.html

Ah, that was my error: I'd pasted the wrong graph embed; the only difference in that long URL is:

range=A%3AA%2CP%3AP&gid=2
(decoded: range=A1:A845,P1:P845&gid=2)

range=A%3AA%2CP%3AP&gid=2
(decoded: range=A:A,P:P&gid=2)

Now, this makes MY graph update, not sure why yours isn't when you add more data, but I'll have a poke around and an ask on Google Groups.

I'm glad someone mentioned Toolbox (which I hadn't spotted before). Looks like it might help here - it seems daft for Cumulus to be uploading dayfile.txt every 15 minutes when it only changes once a day! I also didn't know about http://cumulus.tnetweather.com/projects/jpgraphgraphs

Going to have a hack around and will come back later, hopefully with an improved model!

http://cumulus.tnetweather.com/projects/jpgraphgraphs is using very heavy serverattempt, and the graphs don't go back much in the past so I don't use it. Hopefully there's a solution for the spreadsheet, I even don't know how googledocs 'knows' when it has to update the sheet ...

steve · **Posted:** Sat Apr 07, 2012 1:48 pm

lardconcepts wrote:

I'm glad someone mentioned Toolbox (which I hadn't spotted before). Looks like it might help here - it seems daft for Cumulus to be uploading dayfile.txt every 15 minutes when it only changes once a day!

While Toolbox is an excellent and useful tool, if you're only using it to upload dayfile.txt, then you might want to consider using the 'external command' facility in Cumulus (or, indeed Task Scheduler), as mentioned here: http://wiki.sandaysoft.com/a/Upload_Dayfile

The advantage of getting Cumulus to do it via an external command is that it will always (attempt to) do it each time dayfile.txt is modified, even if you close Cumulus down overnight.

nitrx · **Posted:** Sat Apr 07, 2012 2:19 pm

' lardconcepts ' About the cell ranges for creating the graph are these also extended when a new day is added or do they need another syntax ?

Ofcourse I should have to mention the build in commands as Steve suggests wel thats done now

lardconcepts · **Posted:** Sat Apr 07, 2012 3:36 pm

beteljuice wrote:

Sorry, I must have been coding or 'out to lunch' and missed your post.

And I owe you an apology - I hadn't really figured out the power and usefulness of your script! It certainly overcomes the Google spreadsheet "importing csv" problem.

beteljuice wrote:

Have I missed something else - does this google graphs api download everything to the browser ?
Suddenly it's not such an attractive proposition for people with limited bandwidths - pity.

In this particular graph that I've chosen, yes, the very first time you visit the page, with nitrx's data, it's about 182kb; after assets have been cached, then subsequent visits are about 72k. Of course, the graph has to load a LOT of data to be able to zoom right from a 3+ year timespan, right down to an individual day.

This is also about the only graph that uses flash, some are static PNGs about 6k max, and most of the interactive javascript ones are about 20k and use SVG/VML.
I'm learning loads about Google Graphs all the time; my first example wasn't supposed to be definitive, hence the "experimenting" part of the title

nitrx wrote:

I even don't know how googledocs 'knows' when it has to update the sheet ...

It's about once an hour, apparently, but if you wanted it to pull your data much more frequently (up to every 2 minutes) if you were making graphs from the regular 5(?) minute updates, someone's found a clever workaround here:
http://groups.google.com/a/googleproduc ... DZnVj-mFnA

nitrx wrote:

' lardconcepts ' About the cell ranges for creating the graph are these also extended when a new day is added or do they need another syntax ?

When you're creating a graph to embed in a website, you want to make sure that in the URL you get rid of the row range;
Annoyingly, in this particular chart, when I select

Code:

'Working sheet'!A:A, 'Working sheet'!P:P

it "corrects" it to (in nitrx's case)

Code:

'Working sheet'!A1:A944, 'Working sheet'!P1:P944

so you need to take account of this in the exported URL.

BUT WAIT! Before anyone does much more with my spreadsheet, I've just started working on much better plan. It's going to take a cup of Earl Grey and at least 2 macaroons, and don't expect anything today, or even anything reliable or good, but it might be interesting.

steve wrote:

While Toolbox is an excellent and useful tool, if you're only using it to upload dayfile.txt, then you might want to consider using the 'external command' facility in Cumulus (or, indeed Task Scheduler), as mentioned here: http://wiki.sandaysoft.com/a/Upload_Dayfile

Ah, even better! Yes, I was only using Toolbox for that one function, thanks for pointing that out. I REALLY should spend more time in the docs

Steve, while you're here, as you can see, a large amount of this thread is dealing with the fact that when the system locale is non-UK, the csv file generated is not a csv, it's in the local format, giving problems for using it externally, as nitrx has found. If it's not too much extra effort, is it possible to "override" the system local and generate, say, a second dayfile.txt, but as dayfile.csv in a "normal" csv format?

I know you don't say it's csv, and we've made a workaround or two above, and there's possibly a setting that I've missed (there usually is!) but what do you think?

I'm going to lie down in a dark room for a couple of hours then have another go at this!

steve · **Posted:** Sat Apr 07, 2012 4:14 pm

lardconcepts wrote:

is it possible to "override" the system local and generate, say, a second dayfile.txt, but as dayfile.csv in a "normal" csv format?

I haven't checked, but all of the components of dayfile.txt, should be available as web tags, so it ought to be possible to create dayfile.txt entries using commas. I know that's not quite the same thing, but it would be a quicker solution than waiting for me to get around to this particular addition.

	It is currently Tue Jun 18, 2013 7:11 am
Please click here before posting. Help me to help you!
Useful Links: Cumulus FAQ • Enhancement requests • Wiki (documentation)
Please put your approximate location into your profile
Add your web site to the Cumulus user map
Vantage Pro2 users with firmware 3.00 should upgrade to fw 3.12 and Cumulus 1.9.4
Latest Cumulus release v1.9.3 (build 1059) - Feb 09 2013 Latest Cumulus beta v1.9.4 build 1080 - Jun 03 2013 --> Latest Cumulus Realtime v1.0.0.5 - Sep 05 2009

Sandaysoft

Experimenting with Google Charts and Graphs API.

Who is online