Experimenting with Google Charts and Graphs API.

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.

lardconcepts · **Posted:** Sun Apr 08, 2012 8:49 am

Well, what's happened this morning is: The 'external command' facility in Cumulus worked a treat this morning.
Shortly afterwards, my spreadsheet had updated OK, as has the graph.

It also seems that Nitrx's spreadsheet has updated the first page, but it's the "messing about and joining" sheet in the middle which appears to go wrong.

Getting there - I just wish Google Spreadsheets had a lovely formula builder like LibreOffice does - makes things so much easier!

Of course, beteljuice's script would be a solution here, but I'm also trying to find a "no-php" solution too.

beteljuice · **Posted:** Sun Apr 08, 2012 10:59 am

The beteljuice was guilty of fluffy thinking, or to put it another way he is used to nicking other peoples data (for testing purposes).

A simple way to only update the cache file when older than dayfile.txt is to 'forget' about the time() and change the file check line to:

Code:

if(!file_exists("dayfile.csv") || (file_exists("dayfile.csv") && filemtime("dayfile.csv") < filemtime($dayfile))) {

NB:This will only work if the files are on the same server !
(That's why I forgot about it and can't show you an example)

beteljuice · **Posted:** Sun Apr 08, 2012 11:09 am

steve wrote:

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.

But how would you 'append' the new constructed data line to the local file and then upload - some sort of .bat file would be required ?

steve · **Posted:** Sun Apr 08, 2012 11:17 am

beteljuice wrote:

But how would you 'append' the new constructed data line to the local file and then upload - some sort of .bat file would be required ?

Yes; that's why I admitted that it wasn't quite the same thing. It would need to work like Kevin's JpGraphs that use an appended realtime.txt file?

nitrx · **Posted:** Sun Apr 08, 2012 4:13 pm

I use Betels php file it works fine for me ((i will try later the php file without the time issue have to eat some Easter-eggs

), about the webtags , they keep also comma's in my parsed data this will not work for creating a dayfile I think.

	It is currently Tue Jun 18, 2013 11:18 pm
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