i have datatables + highcharts and now don't working and i want replace sheets api v3 to v4
codepen.io/intprotest/pen/ewQJrK?editors=1010 !A2:Z?alt=json&key=AIzaSyB5FsZ-XKEpJVPSmTbQhJPNMOIPYO8VmhU
image screen
$(document).ready(function() {
var results = [];
var categories = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'];
var url = ";;
var jqxhr = $.getJSON(url, function() {
console.log("success");
})
.done(function(data) {
console.log("second success");
var entry = data.feed.entry;
var rowCount = 0; //row counter
var rowData = []; //data array for each row
console.log(data)
$(entry).each(function() {
if (rowCount % 2 === 0) {
// if rowCount mod 2 is 0 then start new row data
rowData = [
this.gsx$_cn6ca.$t,
this.gsx$company.$t,
this.gsx$jan.$t,
this.gsx$feb.$t,
this.gsx$mar.$t,
this.gsx$apr.$t,
this.gsx$may.$t,
this.gsx$jun.$t,
this.gsx$jul.$t,
this.gsx$aug.$t,
this.gsx$sep.$t,
this.gsx$oct.$t,
this.gsx$nov.$t,
this.gsx$dec.$t,
];
} else {
// Otherwise append second row to the first
rowData = rowData.concat([
this.gsx$_cn6ca.$t,
this.gsx$company.$t,
this.gsx$jan.$t,
this.gsx$feb.$t,
this.gsx$mar.$t,
this.gsx$apr.$t,
this.gsx$may.$t,
this.gsx$jun.$t,
this.gsx$jul.$t,
this.gsx$aug.$t,
this.gsx$sep.$t,
this.gsx$oct.$t,
this.gsx$nov.$t,
this.gsx$dec.$t
])
// and push onto the row data
results.push(rowData);
}
rowCount++;
});
var table = $('#selection-datatable').DataTable({
data: results,
deferRender: true,
"processing": true,
paging: false,
columnDefs: [{
className: "details-control",
"targets": [0]
}]
});
function format(title) {
return '<div class="slider" name>' +
'<table class=table table hover border="0" class="details-table">' +
'<thead> ' +
'<td>' + [title[14]].join(', ') + '</td>' +
'<td>' + [title[15]].join(', ') + '</td>' +
'<td>' + [title[16]].join(', ') + '</td>' +
'<td>' + [title[17]].join(', ') + '</td>' +
'<td>' + [title[18]].join(', ') + '</td>' +
'<td>' + [title[19]].join(', ') + '</td>' +
'<td>' + [title[20]].join(', ') + '</td>' +
'<td>' + [title[21]].join(', ') + '</td>' +
'<td>' + [title[22]].join(', ') + '</td>' +
'<td>' + [title[23]].join(', ') + '</td>' +
'<td>' + [title[24]].join(', ') + '</td>' +
'<td>' + [title[25]].join(', ') + '</td>' +
'<td>' + [title[26]].join(', ') + '</td>' +
'<td>' + [title[27]].join(', ') + '</td>' +
'</thead> ' +
'<div id="chart' + title[14] + '"></div>' +
'</div>'
}
$('#selection-datatable tbody').on('click', 'td.details-control', function() {
var tr = $(this).closest('tr');
var row = table.row(tr);
if (row.child.isShown()) {
// This row is already open - close it
$('div.slider', row.child()).slideUp(function() {
row.child.hide();
tr.removeClass('shown');
});
} else {
// Open this row
row.child(format(row.data()), 'no-padding').show();
tr.addClass('shown');
createChart('chart' + row.data()[14], row.data().slice(16));
$('div.slider', row.child()).slideDown();
}
});
});
function createChart(container, data) {
Highcharts.chart(container, {
series: [{
data: (function() {
data.forEach(function(el, i) {
data[i] = Number(el);
});
return data;
})()
}],
chart: {
zoomType: 'xy',
type: 'area',
backgroundColor: '#253138',
},
plotOptions: {
series: { neWidth: 2, fillColor: "#0099cc", lineColor: "#f7897b",
marker: { enabled: true, symbol: 'circle', radius: 4,
states: { hover: { enabled: true, illColor: '#000000' } } }
} },
title: { style: { color: '#E0E0E3', fontSize: '20px' } },
yAxis: { labels: { style: { color: 'white' } }},
legend: { itemStyle: { color: 'white' }, },
xAxis: {
categories: categories,
labels: { style: { color: 'white' } }
},
})
}
});
i have datatables + highcharts and now don't working and i want replace sheets api v3 to v4
codepen.io/intprotest/pen/ewQJrK?editors=1010 https://sheets.googleapis/v4/spreadsheets/1f8Ji80Qv3PX3mcUbHs5MeVHmTdC1lxBoPu72XInckaA/values/1!A2:Z?alt=json&key=AIzaSyB5FsZ-XKEpJVPSmTbQhJPNMOIPYO8VmhU
image screen
$(document).ready(function() {
var results = [];
var categories = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'];
var url = "https://spreadsheets.google/feeds/list/1f8Ji80Qv3PX3mcUbHs5MeVHmTdC1lxBoPu72XInckaA/od6/public/values?alt=json";
var jqxhr = $.getJSON(url, function() {
console.log("success");
})
.done(function(data) {
console.log("second success");
var entry = data.feed.entry;
var rowCount = 0; //row counter
var rowData = []; //data array for each row
console.log(data)
$(entry).each(function() {
if (rowCount % 2 === 0) {
// if rowCount mod 2 is 0 then start new row data
rowData = [
this.gsx$_cn6ca.$t,
this.gsx$company.$t,
this.gsx$jan.$t,
this.gsx$feb.$t,
this.gsx$mar.$t,
this.gsx$apr.$t,
this.gsx$may.$t,
this.gsx$jun.$t,
this.gsx$jul.$t,
this.gsx$aug.$t,
this.gsx$sep.$t,
this.gsx$oct.$t,
this.gsx$nov.$t,
this.gsx$dec.$t,
];
} else {
// Otherwise append second row to the first
rowData = rowData.concat([
this.gsx$_cn6ca.$t,
this.gsx$company.$t,
this.gsx$jan.$t,
this.gsx$feb.$t,
this.gsx$mar.$t,
this.gsx$apr.$t,
this.gsx$may.$t,
this.gsx$jun.$t,
this.gsx$jul.$t,
this.gsx$aug.$t,
this.gsx$sep.$t,
this.gsx$oct.$t,
this.gsx$nov.$t,
this.gsx$dec.$t
])
// and push onto the row data
results.push(rowData);
}
rowCount++;
});
var table = $('#selection-datatable').DataTable({
data: results,
deferRender: true,
"processing": true,
paging: false,
columnDefs: [{
className: "details-control",
"targets": [0]
}]
});
function format(title) {
return '<div class="slider" name>' +
'<table class=table table hover border="0" class="details-table">' +
'<thead> ' +
'<td>' + [title[14]].join(', ') + '</td>' +
'<td>' + [title[15]].join(', ') + '</td>' +
'<td>' + [title[16]].join(', ') + '</td>' +
'<td>' + [title[17]].join(', ') + '</td>' +
'<td>' + [title[18]].join(', ') + '</td>' +
'<td>' + [title[19]].join(', ') + '</td>' +
'<td>' + [title[20]].join(', ') + '</td>' +
'<td>' + [title[21]].join(', ') + '</td>' +
'<td>' + [title[22]].join(', ') + '</td>' +
'<td>' + [title[23]].join(', ') + '</td>' +
'<td>' + [title[24]].join(', ') + '</td>' +
'<td>' + [title[25]].join(', ') + '</td>' +
'<td>' + [title[26]].join(', ') + '</td>' +
'<td>' + [title[27]].join(', ') + '</td>' +
'</thead> ' +
'<div id="chart' + title[14] + '"></div>' +
'</div>'
}
$('#selection-datatable tbody').on('click', 'td.details-control', function() {
var tr = $(this).closest('tr');
var row = table.row(tr);
if (row.child.isShown()) {
// This row is already open - close it
$('div.slider', row.child()).slideUp(function() {
row.child.hide();
tr.removeClass('shown');
});
} else {
// Open this row
row.child(format(row.data()), 'no-padding').show();
tr.addClass('shown');
createChart('chart' + row.data()[14], row.data().slice(16));
$('div.slider', row.child()).slideDown();
}
});
});
function createChart(container, data) {
Highcharts.chart(container, {
series: [{
data: (function() {
data.forEach(function(el, i) {
data[i] = Number(el);
});
return data;
})()
}],
chart: {
zoomType: 'xy',
type: 'area',
backgroundColor: '#253138',
},
plotOptions: {
series: { neWidth: 2, fillColor: "#0099cc", lineColor: "#f7897b",
marker: { enabled: true, symbol: 'circle', radius: 4,
states: { hover: { enabled: true, illColor: '#000000' } } }
} },
title: { style: { color: '#E0E0E3', fontSize: '20px' } },
yAxis: { labels: { style: { color: 'white' } }},
legend: { itemStyle: { color: 'white' }, },
xAxis: {
categories: categories,
labels: { style: { color: 'white' } }
},
})
}
});
- You have used highcharts tag, but the problem doesn't seem to be about a chart - rather about data. If you'll have problems with Highcharts then please provide a demo with hard-coded, exemplary data. – magdalena Commented Mar 11 at 12:37
- @magdalena problem sheets api v3 and i want replace code and i want data from sheets sheets api v4 and i don't know replace code sheets.googleapis/v4/spreadsheets/… – IntProtest Commented Mar 11 at 13:52
1 Answer
Reset to default 0Changing URL and variable Values
I replace the current URL in the script and update the URL variable with the link provided above. I also modify your forEach in the script and alter the values of the entry variable to match the response of your URL JSON.
You can read more about Google sheets v3 to v4 answered by someone here in our community.
$(document).ready(function() {
var results = [];
var categories = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'];
// Change your previous url to your current url.
var url = "https://sheets.googleapis/v4/spreadsheets/1f8Ji80Qv3PX3mcUbHs5MeVHmTdC1lxBoPu72XInckaA/values/1!A2:Z?alt=json&key=AIzaSyB5FsZ-XKEpJVPSmTbQhJPNMOIPYO8VmhU"
var jqxhr = $.getJSON(url, function() {
console.log("success");
})
.done(function(data) {
console.log("second success");
console.log(data)
// Matching the Data response from your Json.
var entry = data.values;
var rowCount = 0; //row counter
var rowData = []; //data array for each row
console.log(entry)
$(entry).each(function() {
if (rowCount % 2 === 0) {
// if rowCount mod 2 is 0 then start new row data
rowData = [
// Edited your forEach to make things work.
entry[rowCount][0],
entry[rowCount][1],
entry[rowCount][2],
entry[rowCount][3],
entry[rowCount][4],
entry[rowCount][5],
entry[rowCount][6],
entry[rowCount][7],
entry[rowCount][8],
entry[rowCount][9],
entry[rowCount][10],
entry[rowCount][11],
entry[rowCount][12],
entry[rowCount][13]
];
} else {
// Otherwise append second row to the first
rowData = rowData.concat([
entry[rowCount][0],
entry[rowCount][1],
entry[rowCount][2],
entry[rowCount][3],
entry[rowCount][4],
entry[rowCount][5],
entry[rowCount][6],
entry[rowCount][7],
entry[rowCount][8],
entry[rowCount][9],
entry[rowCount][10],
entry[rowCount][11],
entry[rowCount][12],
entry[rowCount][13]
])
// and push onto the row data
results.push(rowData);
}
rowCount++;
});
console.log(results)
var table = $('#selection-datatable').DataTable({
data: results,
deferRender: true,
"processing": true,
paging: false,
columnDefs: [{
className: "details-control",
"targets": [0]
}]
});
function format(title) {
return '<div class="slider" name>' +
'<table class=table table hover border="0" class="details-table">' +
'<thead> ' +
'<td>' + [title[14]].join(', ') + '</td>' +
'<td>' + [title[15]].join(', ') + '</td>' +
'<td>' + [title[16]].join(', ') + '</td>' +
'<td>' + [title[17]].join(', ') + '</td>' +
'<td>' + [title[18]].join(', ') + '</td>' +
'<td>' + [title[19]].join(', ') + '</td>' +
'<td>' + [title[20]].join(', ') + '</td>' +
'<td>' + [title[21]].join(', ') + '</td>' +
'<td>' + [title[22]].join(', ') + '</td>' +
'<td>' + [title[23]].join(', ') + '</td>' +
'<td>' + [title[24]].join(', ') + '</td>' +
'<td>' + [title[25]].join(', ') + '</td>' +
'<td>' + [title[26]].join(', ') + '</td>' +
'<td>' + [title[27]].join(', ') + '</td>' +
'</thead> ' +
'<div id="chart' + title[14] + '"></div>' +
'</div>'
}
$('#selection-datatable tbody').on('click', 'td.details-control', function() {
var tr = $(this).closest('tr');
var row = table.row(tr);
if (row.child.isShown()) {
// This row is already open - close it
$('div.slider', row.child()).slideUp(function() {
row.child.hide();
tr.removeClass('shown');
});
} else {
// Open this row
row.child(format(row.data()), 'no-padding').show();
tr.addClass('shown');
createChart('chart' + row.data()[14], row.data().slice(16));
$('div.slider', row.child()).slideDown();
}
});
});
function createChart(container, data) {
Highcharts.chart(container, {
series: [{
data: (function() {
data.forEach(function(el, i) {
data[i] = Number(el);
});
return data;
})()
}],
chart: {
zoomType: 'xy',
type: 'area',
backgroundColor: '#253138',
},
plotOptions: {
series: { neWidth: 2, fillColor: "#0099cc", lineColor: "#f7897b",
marker: { enabled: true, symbol: 'circle', radius: 4,
states: { hover: { enabled: true, illColor: '#000000' } } }
} },
title: { style: { color: '#E0E0E3', fontSize: '20px' } },
yAxis: { labels: { style: { color: 'white' } }},
legend: { itemStyle: { color: 'white' }, },
xAxis: {
categories: categories,
labels: { style: { color: 'white' } }
},
})
}
});
HTML table output:
| # | Company | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 101 | 102 | 103 | 104 | 105 | 106 | 107 | 108 | 109 | 110 | 111 | 112 | |
| 3 | 301 | 302 | 303 | 304 | 305 | 306 | 307 | 308 | 309 | 310 | 311 | 312 | |
| 5 | 501 | 502 | 503 | 504 | 505 | 506 | 507 | 508 | 509 | 510 | 511 | 512 | |
| 7 | 701 | 702 | 703 | 704 | 705 | 706 | 707 | 708 | 709 | 710 | 711 | 712 | |
| 9 | 901 | 902 | 903 | 904 | 905 | 906 | 907 | 908 | 909 | 910 | 911 | 912 | |
| 11 | 1101 | 1102 | 1103 | 1104 | 1105 | 1106 | 1107 | 1108 | 1109 | 1110 | 1111 | 1112 | |
| 13 | 1401 | 1402 | 1403 | 1404 | 1405 | 1406 | 1407 | 1408 | 1409 | 1410 | 1411 | 1412 | |
| 15 | 1801 | 1802 | 1803 | 1804 | 1805 | 1806 | 1807 | 1808 | 1809 | 1810 | 1811 | 1812 | |
| 17 | 2201 | 2202 | 2203 | 2204 | 2205 | 2206 | 2207 | 2208 | 2209 | 2210 | 2211 | 2212 |
Codepen Output: