Creating charts from an external Spreadsheet and update data every hour/day(or at a particular time)

[Alex Reds]

Hello,

I'm new to the chart API and for the life of me I can't figure out how to make my charts by pulling data from my spreadsheets. I'm missing something.

The documentation is very scarce, probably oriented for experienced developers.

I figured out how to generate charts with something like that

function drawRegionsMap() {

var data = google.visualization.arrayToDataTable([

['County', 'Cases'],

['Carlow', 0],

['Cavan', 67],

['Clare', 54],

...

]);

But no I need to pull the same data from a dynamically updated spreadsheet. The spreadsheets updates a few times per day, so I need to make my charts update along as well.

Could someone give me a small tutorial or a working example of it? preferably with a geo chart and an area one. The rest I hope I will able to figure out myself. 

I particularly struggle with

a) figuring out how to generate a proper spreadsheet link(to a particular sheet in it and a particular range). The gives GID example, but I could make it work. 

b) and how to wrap it all in a full function for it.

Thanks

[Ray Thomas]

First, do you mean a Google Sheet or Excel? Google Sheets are much easier to work with and the charts will update as soon as the sheet is, and the web page refreshed. 

Excel is going to need a bit more help to work properly and there's very little help around for doing it. There's some at the following, but I haven't used them...

Tushar Mehta at http://www.tushar-mehta.com/publish_train/xl_vba_cases/excel_google_chart_api/ used VBA in Excel to do it.
yuriarfil on GitHub also uses VBA - https://github.com/yuriarfil/google-visualization-vba

SheetJS on GitHub uses JavaScript to create the JSON data

As far as I know there's just one example of using a Google Sheet in the Google Visualizations help and that's at https://developers.google.com/chart/interactive/docs/spreadsheets#creating-a-chart-from-a-separate-spreadsheet 

You may also need to look at the Query Language help at https://developers.google.com/chart/interactive/docs/querylanguage 

If you're updating the spreadsheet you may be better off just specifying the columns from it you want to import. For example, my table at http://hmsgambia.org/crewlist.htm is drawn from the sheet at https://docs.google.com/spreadsheets/d/1kjOTQMAlWc-j6G_XfFUJIzAxuvmyxygQh0q1Dpn4oRU/edit#gid=0 and uses:

function drawChart() {

      var queryString = encodeURIComponent('SELECT A, B, C, D, E order by A');

      var query = new google.visualization.Query(

'https://docs.google.com/spreadsheets/d/1kjOTQMAlWc-j6G_XfFUJIzAxuvmyxygQh0q1Dpn4oRU/gviz/tq?gid=0&headers=1&tq=' + queryString);

      query.send(handleQueryResponse);

    }

function handleQueryResponse(response) {

if (response.isError()) {

        alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());

        return;

}

var crewDataTable = response.getDataTable();

        ...

        ...

       }

or you can omit the querystring altogether and simply use

     var query = new google.visualization.Query(

'https://docs.google.com/spreadsheets/d/1kjOTQMAlWc-j6G_XfFUJIzAxuvmyxygQh0q1Dpn4oRU/gviz/tq?gid=0&headers=1');

in which case Google will try and import the entire sheet to work on.

Unless you are using the authorization method at https://developers.google.com/chart/interactive/docs/spreadsheets#authorization the only thing to remember is to make the Google Sheet public or "viewable to anyone with the link". I don't use authorization because I don't keep anything in the Sheets that is not going to be put onto a web page in one form or another anyway.

Other than where the data comes from to create the datatable there's no difference in the code to be used to create your charts, and all the methods and events for your particular chart types will work as they should.

I hope this helps.

[Mathew Trickel]

Ray Thomas, 

This link really helped me find a solution, while it did not tell me how to, but it gave me some ideas on how I could make it work. It took a couple of days of trial and error. But I got it!!

Thanks for posting this link!

Mat

You may also need to look at the Query Language help at https://developers.google.com/chart/interactive/docs/querylanguage   

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualizati...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/b3829fc4-9d24-41a7-94cb-ccb1ba0acd53%40googlegroups.com.

--

Mathew Trickel

Facility Planner

Facilities/ University Housekeeping 

Administration Building 3, 225E

2701 Sullivan Drive
Raleigh, NC 27695

Office: 513-7277 Cell: 801-9286

matr...@ncsu.edu

http://go.ncsu.edu/housekeeping