Can I Create a Single HighCharts Graph from Multiple Data Sources (Multiple GoogleSheets in this case)

Question

I’m trying to use data from multiple GoogleSheets to produce a single HighChart graph. I’d like to do this without moving all the data into one area of a single spreadsheet, particularly as I want to use the drilldown option which would make it difficult to collect all the data together. I thought I could pass the columns as an array and then reference the array in the data property of my chart, but I’m struggling to do that with even one column from one sheet.

I have searched for answers online, but I have not found anything relating to highcharts getting data from multiple sources. Previous Research: Using GoogleSheets Data as an array before creating the chart: (Removed Link) - The problem is that I could only use one GoogleSheets reference here as the chart object sits inside the data object.

API documentation - (Removed Link) – tells me I can access the columns but that’s not the part I’m having problems with

Querying the Chart: (Removed Link) - I have actually considered making hidden charts, then interrogating the data and making new charts from those, but that seems like a very long way round and I’m still not sure I could grab all the data I need.

Using two GoogleSheets for separate charts on the same page: (Removed Link) I have done this.

Please could you help me to understand how I can access the properties and methods of this object outside of the object itself?

Thank you.

My Code:

        //Function to produce a data array ***Not Working - Cannot extract array from object method***
    function getData(){

    Highcharts.data({
        googleSpreadsheetKey: '12x66_QEkTKg_UzvpHEygdTmfnu7oH81pSQYn78Hxt80',
        googleSpreadsheetWorksheet: 4,
        startColumn: 16,
        endColumn: 22,
        startRow: 63,
        endRow: 76,

        parsed: function (columns) {
            var dataTest2 = [];
            var columnLength = columns[1].length;
            for (i = 0; i < columnLength; i = i + 1) { 
                dataTest2.push(columns[1][i]);

            }

            alert(dataTest2); //This works here, but not if I move it outside of the method, even if I return it.
            var testReturn = this.googleSpreadsheetKey; //trying to return any property using "this" - I've also tried this.googleSpreadsheetKey.value but still undefined
            return testReturn; //returns "Undefined" 
        }

    });


    }

Answer 1

You could use Google Sheets webQuery. Basically, this is a method to export the Spreadsheet's data into a given format such as csv, json, etc. In your case, the link should look like this:

• https://docs.google.com/spreadsheet/tq?key=12x66_QEkTKg_UzvpHEygdTmfnu7oH81pSQYn78Hxt80&gid=4&tq=select%20A,%20B&tqx=reqId:1;out:csv;%20responseHandler:webQuery

Please note that here "tg?key" is the key of your Google Sheet, and "&gid=" is NOT 4, this only tells Highcharts to selected Sheet 4, but for Google Sheets look at the source link and copy the numbers which go after "&gid=". Furthermore, "&tq=" is used to select the columns of the Google Sheet, which in the link above selects "Column A" and "Column B". To find out more on how to select columns and query the output refer to:

• https://developers.google.com/chart/interactive/docs/querylanguage?csw=1#Setting_the_Query_in_the_Data_Source_URL

Lastly, "&tqx=" is used to output your data in the format you want. The above link uses "out:csv" which will output the data as comma-separated values. This could as well be Json if you like. Have a look at this documentation:

• https://developers.google.com/chart/interactive/docs/dev/implementing_data_source#requestformat

In order to implement this output into your javascript code which you would then use to built your chart, you could use external Javascript libraries to handle these outputs. If you output your data as CSV, you can use "papaparse.js" to parse the CSV into Json which you can be then read by highcharts and allows you to built the chart. Refer to this documentation:

• http://papaparse.com/docs#remote-files

An alternative to this would be, to output your Google Sheets directly as Json, then use jquery to make an Ajax Call and load the JSON-encoded data into your Javascript code. Precisely, you could perhaps use jQuery.getJSON() to get the data. Look at this link for more details on how to get Json:

• http://api.jquery.com/jquery.getjson/

Finally, it is up to you on which format you choose to output the data, I prefer using Json as it saves you the extra step of having to convert the CSV into Json. Whatever suits you best and is easier for you to understand.

Once you have your data, you may have to parse your Json objects with Json.parse(), and then organize your data into an array with .push(). As @jlbriggs stated, organize your data first before you built the chart. Afterwards, you can make two, three or more Ajax calls to import data from different sources. I would not use many as this will impact in your loading and slow down data transfer.

NB: It is important to format the data accordingly for Highcharts to recognize the data, so use parseFloat() to convert strings into numbers, Date.UTC() to convert strings into date, etc.

Hope this helps you.