Code snippet for query API

Hi all,

Happy new year everyone!

I have made a Javascript snippet (really quick & dirty) to access from a Google spreadsheet.

Here is the link: (snippet code and link to gootle spreadsheet template at comments)

Best regards,
PS: Who else has similar code (in any language) please, post here.

irisdc's picture

Hi Benito,

I've been trying your Google spreadsheet. Unfortunately without stable results. My consumption of a week ago e.g. has become 0. I can't figure out what's wrong with the javascript code (if anything) or what I did wrong myself in copying the file or anything else.
I've been using the getKWhperDay function (with var query = "&start=" + start +"&end=" + end +"&resolution=hour&unit=watt";) Start is e.g. 5/23/2013 and end is 5/24/2013. At each consultation the value displayed in the sheet is different.
Many thanks in advance.


jrbenito's picture

Hi Iris,

Sorry for late reply, I am in a business trip to South Korea and at work I have a very limited internet access due to company rules.

the issue you are experiencing maybe is related to language your spreadsheet is configured. Dates, numbers separators, currency are all changed and not every function handle this very well. Since my design to this function is really simple (quick and dirty) I dont believe it will work in different Google Spreadsheet configuration. This is a wilde guess but try to set your spreadsheet locale to english. Mine is set to United States (since I prefer the English interface).

Let me know if it does not work and I try to help you.

ah, one last thing, did you try same query URL with CURL or other utility capable of GET/POST http requests? Other thing that can help is the Logger class from google scripts, you can log the steps made in function and look it to see what happens in each step.

Best regards,

irisdc's picture

Thanks Benito for your feedback. My spreadsheet locale is United States as well.
When I try with curl, all queries to get the daily electricity consumption of more than a week ago do not work with 'resolution=hour'. The hourly results are nan. I am therefore now working with 'resolution=day' and I pass as result data[0][1]*24/1000 to the spreadsheet and this seems to work.