Converting DataTables and Highcharts data source from JSON to Google Sheets

Other Questions Google Sheets
1

I currently have a setup with DataTables and Highcharts working together where charts appear in expandable child rows. Right now my data comes from a JSON file but I need to switch the data source to Google Sheets instead. I’m wondering if this conversion is possible and how to modify my existing code to make it work.

The current implementation processes country and network data from JSON format. I need help updating the data fetching part to pull information directly from Google Spreadsheets while keeping the same table and chart functionality.

Charts.configure({
    global: {
        timezone: true
    }
});

var dataset = location_info;
var regionList = [];
var sorted_regions = [];
var providerList = [];
var sorted_providers = [];
var display_container = "region_display";
var searchReset = false;

function buildDataTable(info, containerDiv, tableId, dataType) {
    var columnConfig = [];
    if (dataType == "regions") {
        columnConfig = [
            {"width": "20%", "title": "Position", "targets": 0, "visible": true, "searchable": false, "orderable": true},
            {"width": "20%", "title": "Coverage %", "targets": 1, "visible": true, "searchable": false, "orderable": true},
            {"width": "20%", "title": "Location", "targets": 2, "visible": true, "searchable": true, "orderable": true},
            {"targets": 3, "visible": true, "searchable": false, "orderable": false}
        ];
    }
    
    var dataTable = $('#' + tableId).DataTable({
        "data": info,
        "ordering": [[1, "desc"]],
        "paging": false,
        "info": false,
        "columns": columnConfig
    });
    
    return dataTable;
}

function createChart(element, settings, sourceData, rowIndex) {
    settings.chart.renderTo = 'region_display' + rowIndex;
    var location = $(element).children(':nth-child(3)').text();
    settings.series = [];
    
    var chartData = [];
    for (region in sourceData) {
        if (region == location) {
            var seriesConfig = {
                name: region,
                type: 'line',
                data: []
            };
            
            var dataPoints = sourceData[region];
            for (timestamp in dataPoints) {
                chartData.push([parseInt(timestamp), parseFloat(dataPoints[timestamp])]);
            }
            
            seriesConfig.data = chartData;
            settings.series.push(seriesConfig);
        }
    }
    
    var newChart = new Charts.Chart(settings);
    return newChart;
}

How can I modify this code structure to fetch data from Google Sheets instead of the current JSON source?

2

Been using this setup for two years now. The Google Apps Script approach works, but just go with the client-side Sheets API since you’ve got JavaScript already working. Your biggest headache will be converting data between Sheets rows and your JSON structure. Write a helper function that maps columns to your object properties. For auth, use API keys for public sheets or OAuth2 for private ones. Watch out for this - the API returns Promises, so you’ll need to handle those properly in your DataTable setup. Probably means wrapping your buildDataTable calls in async functions. If your data’s across multiple tabs, batch the range requests for better performance. API response is usually quick, but throw in some loading states so users know what’s happening.

3

yeah, definitely doable. you’ll need google apps script or the sheets api though. easiest route is creating a published web app in apps script that spits out your data as json. then you barely touch your existing code - just swap the endpoint url.

4

I’ve done this exact migration from JSON to Google Sheets - totally doable but needs some tweaks. You’ll use the Google Sheets API v4 to grab your data, just gotta handle the async API calls. Start by setting up API credentials in Google Cloud Console and enabling the Sheets API. Then swap your data fetching to use gapi.client.sheets.spreadsheets.values.get(). The annoying part is Google Sheets spits out arrays instead of your JSON object structure. You’ll need preprocessing to convert sheet data back to what your buildDataTable and createChart functions want. I’d make a mapping function that turns the sheet rows into your current data format - saves you from rewriting all the chart logic. Also cache those API responses. Google Sheets has rate limits that’ll kill performance if you’re hitting it constantly.