Need Help Converting Chart Implementation to New Google Sheets API
I’m working on a project that combines data tables with interactive charts. The current setup was working fine with the older Google Sheets API but stopped functioning after the API changes. I need to migrate my existing code to use the newer version of the Google Sheets API.
The main issue is that my current implementation relies on the old API structure and I’m not sure how to properly adapt the data parsing logic for the new API format. The charts display monthly data in an expandable table format.
Here’s my current working code structure:
$(document).ready(function() {
var monthlyData = [];
var timeLabels = ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December'];
var apiEndpoint = "https://spreadsheets.google.com/feeds/list/SPREADSHEET_ID/od6/public/values?alt=json";
$.getJSON(apiEndpoint)
.done(function(response) {
var entries = response.feed.entry;
var recordIndex = 0;
var recordArray = [];
$(entries).each(function() {
if (recordIndex % 2 === 0) {
recordArray = [
this.gsx$_cn6ca.$t,
this.gsx$organization.$t,
this.gsx$january.$t,
this.gsx$february.$t,
this.gsx$march.$t,
this.gsx$april.$t,
this.gsx$may.$t,
this.gsx$june.$t,
this.gsx$july.$t,
this.gsx$august.$t,
this.gsx$september.$t,
this.gsx$october.$t,
this.gsx$november.$t,
this.gsx$december.$t
];
} else {
recordArray = recordArray.concat([
this.gsx$_cn6ca.$t,
this.gsx$organization.$t,
this.gsx$january.$t,
this.gsx$february.$t,
this.gsx$march.$t,
this.gsx$april.$t,
this.gsx$may.$t,
this.gsx$june.$t,
this.gsx$july.$t,
this.gsx$august.$t,
this.gsx$september.$t,
this.gsx$october.$t,
this.gsx$november.$t,
this.gsx$december.$t
]);
monthlyData.push(recordArray);
}
recordIndex++;
});
var dataGrid = $('#company-datatable').DataTable({
data: monthlyData,
deferRender: true,
"processing": true,
paging: false,
columnDefs: [{
className: "expand-control",
"targets": [0]
}]
});
function buildDetailView(rowInfo) {
return '<div class="detail-container">' +
'<table class="info-table" border="0">' +
'<thead>' +
'<td>' + [rowInfo[14]].join(', ') + '</td>' +
'<td>' + [rowInfo[15]].join(', ') + '</td>' +
'<td>' + [rowInfo[16]].join(', ') + '</td>' +
'<td>' + [rowInfo[17]].join(', ') + '</td>' +
'<td>' + [rowInfo[18]].join(', ') + '</td>' +
'<td>' + [rowInfo[19]].join(', ') + '</td>' +
'<td>' + [rowInfo[20]].join(', ') + '</td>' +
'<td>' + [rowInfo[21]].join(', ') + '</td>' +
'<td>' + [rowInfo[22]].join(', ') + '</td>' +
'<td>' + [rowInfo[23]].join(', ') + '</td>' +
'<td>' + [rowInfo[24]].join(', ') + '</td>' +
'<td>' + [rowInfo[25]].join(', ') + '</td>' +
'</thead>' +
'<div id="visualization' + rowInfo[14] + '"></div>' +
'</div>';
}
$('#company-datatable tbody').on('click', 'td.expand-control', function() {
var tableRow = $(this).closest('tr');
var currentRow = dataGrid.row(tableRow);
if (currentRow.child.isShown()) {
$('div.detail-container', currentRow.child()).slideUp(function() {
currentRow.child.hide();
tableRow.removeClass('expanded');
});
} else {
currentRow.child(buildDetailView(currentRow.data()), 'no-padding').show();
tableRow.addClass('expanded');
renderVisualization('visualization' + currentRow.data()[14], currentRow.data().slice(16));
$('div.detail-container', currentRow.child()).slideDown();
}
});
});
function renderVisualization(elementId, chartData) {
Highcharts.chart(elementId, {
series: [{
data: (function() {
chartData.forEach(function(value, index) {
chartData[index] = Number(value);
});
return chartData;
})()
}],
chart: {
zoomType: 'xy',
type: 'area',
backgroundColor: '#2a3f47'
},
plotOptions: {
series: {
lineWidth: 3,
fillColor: "#00aadd",
lineColor: "#ff6b5b",
marker: {
enabled: true,
symbol: 'circle',
radius: 5,
states: {
hover: {
enabled: true,
fillColor: '#333333'
}
}
}
}
},
title: {
style: {
color: '#E0E0E3',
fontSize: '18px'
}
},
yAxis: {
labels: {
style: {
color: 'white'
}
}
},
legend: {
itemStyle: {
color: 'white'
}
},
xAxis: {
categories: timeLabels,
labels: {
style: {
color: 'white'
}
}
}
});
}
});
How can I update this code to work with the current Google Sheets API? What changes do I need to make to the data parsing and API call structure?