i have datatables + highcharts (in Child rows ) and i want change code and i want data from google sheets and Is it possible to change?
now data from json and i want data from google sheets and i want code replace https://caucasusoffline.com/data/1/country.json
https://codepen.io/intprotest/pen/YeOKBg?editors=1011
Google Spreadsheet
Highcharts.setOptions({
global: {
useUTC: true
}
});
var main_data = country_data;
var countryArray = null;
countryArray = new Array();
var table = "";
var chart_type = "";
var countryArray = [];
var sorted_countryArray = [];
var networkArray = [];
var sorted_networkArray = [];
var chart_container = "country_container";
var resetsearch = false
function drawtable(data, divid, tableid, type) {
$('#' + divid).html('<div><div class="tablebody"><table width="100%" cellpadding="0" cellspacing="0" border="0" class="display" id="' + tableid + '"><tr><td colspan="2"><div class="loading">Loading . .. . </div></td></tr></table></div></div>');
if (type == "country") {
var columns = [
{"width": "15%", "title": "Rank", "targets": 0, "visible": true, "searchable": false, "orderable": true},
{"width": "15%", "title": "IPv6 %", "targets": 1, "visible": true, "searchable": false, "orderable": true},
{"width": "15%", "title": "Country", "targets": 2, "visible": true, "searchable": true, "orderable": true},
//{"title": "groth rate", "targets": 3, "visible": true, "searchable": false, "orderable": false},
{"targets": 3, "visible": true, "searchable": false, "orderable": false}
];
var order = [1, "desc"];
}
if (type == "networks") {
var columns = [
{"width": "15%", "title": "Rank", "targets": 0, "visible": true, "searchable": false, "orderable": true},
{"width": "15%", "title": "IPv6 %", "targets": 1, "visible": true, "searchable": false, "orderable": true},
{"width": "15%", "title": "Network", "targets": 2, "visible": true, "searchable": true, "orderable": true},
//{"title": "groth rate", "targets": 3, "visible": true, "searchable": false, "orderable": false},
{"targets": 3, "visible": true, "searchable": false, "orderable": false}
];
var order = [0, "asc"];
}
var table = $('#' + tableid).dataTable({
"data": data,
'filter': true,
"order": [order],
language: {
searchPlaceholder: "Search"
},
"paging": false,
"autoWidth": false,
"info": false,
columns: columns,
initComplete: function(settings, json) {
$('div.loading').parent().remove();
},
rowCallback: function(row, data) {
$('td:eq(3)', row).hide();
},
drawCallback: function(settings) {
$('#country_data_table').find('*').removeClass('select trChart');
$('#asn_data_table').find('*').removeClass('select trChart');
$('#country_data_table tr').attr("title", "Click on the bar to view the graph");
}
});
$('#myInputTextField').keyup(function() {
table.fnFilter(this.value);
});
// $('.dataTables_scrollBody').jScrollPane({autoReinitialise: true});
return table;
}
function drawAreaChart(obj, options, main_data, index) {
options.chart.renderTo = 'country_container' + index;
$('#country_container' + index).empty();
var country = $(obj).children(':nth-child(3)').html().replace("&", "&");
options.series = [];
var series1 = new Array();
var chart_array = [];
for (i in main_data) {
if (i == country) {
series1[i] = {
showInLegend: '',
name: '',
type: '',
pointInterval: '',
data: []
};
series1[i].showInLegend = false;
series1[i].name = i;
series1[i].type = 'area';
series1[i].pointInterval = 24 * 3600 * 1000;
var json_data = main_data[i];
for (a in json_data) {
chart_array.push([parseInt(a), parseFloat(json_data[a])])
}
chart_array.sort(function(a, b) {
return a[0] - b[0]
});
for (var j in chart_array) {
series1[i].data.push(([(chart_array[j][0] * 1000), chart_array[j][1]]));
//series1[i].data.push(([chart_array[j][1]]));
}
options.series.push(series1[i]);
options.title.text = '';
}
}
if ($(window).width() <= 767) {
options.navigation.buttonOptions.enabled = false;
}
var chart = new Highcharts.Chart(options);
$('html, body').animate({scrollTop: $("#country_container" + index).offset().top}, 1000);
return chart;
}
function timeConverter(UNIX_timestamp, footer) {
var a = new Date(UNIX_timestamp);
var months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'];
var months = ['1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12'];
var year = a.getFullYear();
var month = months[a.getMonth()];
var date = a.getDate();
var hour = a.getHours();
var min = a.getMinutes();
var sec = a.getSeconds();
if (footer == "yes")
var time = month + '/' + date + '/' + year;
else
var time = month + '/' + date;
return time;
}
function maxKey(a) {
var max, k; // don't set max=0, because keys may have values < 0
for (var key in a) {
if (a.hasOwnProperty(key)) {
max = parseInt(key);
break;
}
} //get any key
for (var key in a) {
if (a.hasOwnProperty(key)) {
if ((k = parseInt(key)) > max)
max = k;
}
}
return max;
}
function maxValue(data) {
//var data = JSON.parse(a);
var maxProp = null
var maxValue = -1
for (var prop in data) {
if (data.hasOwnProperty(prop)) {
var value = data[prop]
if (value > maxValue) {
maxProp = prop
maxValue = value
}
}
}
return maxValue;
}
$(document).ready(function() {
//$("#download_network_data").attr("href","")
$('.btn').tooltip();
var lasttime = maxKey(country_data['Canada']);
$(".datafooter span").html("" + timeConverter((lasttime * 1000), "yes"));
/*
* Country array start
* @type Array
*/
for (i in country_data) {
var sorted_countries = [];
//sum = maxValue(country_data[i]);
sorted_countries.push(country_data[i][maxKey(country_data[i])]);
sorted_countries.push(i);
sorted_countryArray.push(sorted_countries);
}
//sort the array in desc order according to Ipv6 data
sorted_countryArray.sort(function(a, b) {
return b[0] - a[0];
});
//prepare the array for table chart
var index = 0;
for (i in sorted_countryArray) {
var countries = [];
index++;
countries.push(index);
countries.push(sorted_countryArray[i][0].toFixed(1) + "%");
countries.push(sorted_countryArray[i][1]);
//countries.push(index);
countries.push('<div id="country_container' + index + '" class="area-chart"></div>');
countryArray.push(countries);
}
/*
* Country array End
* @type Array
*/
/*
* Network array start
* @type Array
*/
for (i in asn_data) {
var sorted_networks = [];
// var sum = 0;
// for (j in asn_data[i]) {
// sum += asn_data[i][j];
// }
if (i > 0) {
//console.log(i);
} else {
sorted_networks.push(asn_data[i][maxKey(asn_data[i])]);
//sorted_networks.push(sum);
sorted_networks.push(i);
sorted_networkArray.push(sorted_networks);
}
}
//sort the array in desc order according to Ipv6 data
// sorted_networkArray.sort(function(a, b) {
// return b[0] - a[0];
// });
//prepare the array for table chart
var index = 0;
for (i in sorted_networkArray) {
var networks = [];
index++;
networks.push(index);
networks.push(sorted_networkArray[i][0].toFixed(1) + "%");
networks.push(sorted_networkArray[i][1]);
//networks.push(index);
networks.push('<div id="country_container' + index + '" class="area-chart"></div>');
networkArray.push(networks);
}
chart_type = document.location.toString().split("#")[1];
//hash = document.location.toString().split("#")[1].split("-");
//chart_type = hash[0];
if (chart_type == "networks") {
$(".ipv6btn").children().first().addClass("selected");
main_data = asn_data;
chart_container = "country_container";
options.plotOptions.area.fillColor = "#2c6f97";
options.plotOptions.area.lineColor = "#479fd3";
options.navigation.menuItemHoverStyle.background = "#2c6f97";
$(".boxshadow").removeClass("green");
drawtable(networkArray, 'country_data_div', 'country_data_table', "networks");
} else {
chart_type = "countries"
drawtable(countryArray, 'country_data_div', 'country_data_table', "country");
}
// if (hash[1] > 0) {
// var obj = $('#country_data_table tbody').children().eq(hash[1] - 1);
// $(obj).addClass("select trChart");
// $(obj).children().last().addClass("tdChart").show();
// drawAreaChart(obj, options, main_data, hash[1]);
// }
$(".ipv6btn a").click(function() {
$('#country_data_table').remove();
$('#country_data_table_wrapper').remove();
$(".ipv6btn a").removeClass("selected");
$(this).addClass("selected");
chart_type = this.href.split("#")[1];
if (chart_type == "networks") {
main_data = asn_data;
chart_container = "country_container";
options.plotOptions.area.fillColor = "#2c6f97";
options.plotOptions.area.lineColor = "#479fd3";
options.navigation.menuItemHoverStyle.background = "#2c6f97";
$(".boxshadow").removeClass("green");
drawtable(networkArray, 'country_data_div', 'country_data_table', "networks");
}
if (chart_type == "countries") {
main_data = country_data;
chart_container = "country_container";
options.plotOptions.area.fillColor = "#5d6d2e";
options.plotOptions.area.lineColor = "#97c409";
options.navigation.menuItemHoverStyle.background = "#5d6d2e";
$("#country_data_div").show();
$("#asn_data_div").hide();
$(".boxshadow").addClass("green");
drawtable(countryArray, 'country_data_div', 'country_data_table', "country");
}
//$('.dataTables_scrollBody').jScrollPane({autoReinitialise: true});
});
$('#country_data_table tbody tr').live('click', function(e) {
e.preventDefault();
var trIndex = $(this).children().first().html();
if ($(this).hasClass("select")) {
$(this).removeClass("select trChart");
$(this).children().last().removeClass("tdChart").hide();
$(this).attr("title", "Click on the bar to view the graph");
} else {
$(this).removeAttr("title");
$('#country_data_table').find('*').removeClass('select trChart');
$('#country_data_table').find('td:last-child').removeClass('tdChart').hide();
$(this).addClass("select trChart");
$(this).children().last().addClass("tdChart").show();
}
//window.location.hash = chart_type+'-'+trIndex;
options.chart.renderTo = 'country_container' + trIndex;
$('#country_container' + trIndex).empty();
var country = $(this).children(':nth-child(3)').html().replace("&", "&");
options.series = [];
var series1 = new Array();
var chart_array = [];
for (i in main_data) {
if (i == country) {
series1[i] = {
showInLegend: '',
name: '',
type: '',
pointInterval: '',
data: []
};
series1[i].showInLegend = false;
series1[i].name = i;
series1[i].type = 'area';
series1[i].pointInterval = 24 * 3600 * 1000;
var json_data = main_data[i];
for (a in json_data) {
chart_array.push([parseInt(a), parseFloat(json_data[a])])
}
chart_array.sort(function(a, b) {
return a[0] - b[0]
});
for (var j in chart_array) {
series1[i].data.push(([(chart_array[j][0] * 1000), chart_array[j][1]]));
//series1[i].data.push(([chart_array[j][1]]));
}
options.series.push(series1[i]);
options.title.text = i;
}
}
if ($(window).width() <= 767) {
options.navigation.buttonOptions.enabled = false;
}
var chart = new Highcharts.Chart(options);
});
$(document).on('keyup', "#country_data_table_filter input", function() {
if ($(this).val().length > 0) {
if (!resetsearch)
$("<a class='resetsearch'>X</a>").insertBefore(this);
resetsearch = true;
} else {
resetsearch = false;
$(".resetsearch").remove();
}
});
$(document).on('keyup', "#myInputTextField", function() {
if ($(this).val().length > 0) {
if (!resetsearch)
$("<a class='resetsearch'>X</a>").insertBefore(this);
resetsearch = true;
} else {
resetsearch = false;
$(".resetsearch").remove();
}
});
$(".resetsearch").live('click', function() {
$("#country_data_table_filter input").val("");
$("#myInputTextField").val("");
resetsearch = false;
$(".resetsearch").remove();
if (chart_type == "networks")
drawtable(networkArray, 'country_data_div', 'country_data_table', "networks");
else
drawtable(countryArray, 'country_data_div', 'country_data_table', "country");
});
$(window).bind('resize', function() {
// $('#country_data_table').remove();
// $('#country_data_table_wrapper').remove();
// if (chart_type == "networks")
// drawtable(networkArray, 'country_data_div', 'country_data_table', "networks");
// else
// drawtable(countryArray, 'country_data_div', 'country_data_table', "country");
}).trigger('resize');
});
I converted your 2D array
into Object
to match the input data in your script in HTML
also using AJAX XMLHttpRequest()
to fetch data from your sheets API.
Added script:
const url = "https://sheets.googleapis.com/v4/spreadsheets/1YNj6PtlnjbfoQqD6iu9bycre9Yok4k1oVN9lVNN4XWU/values/data!A2:Z?alt=json&key=AIzaSyB5FsZ-XKEpJVPSmTbQhJPNMOIPYO8VmhU";
let sheetData = {};
// Using XMLHttpRequest() Get request
function getData(url) {
var xhr = new XMLHttpRequest();
xhr.open("GET", url, false);
xhr.send(null);
if (xhr.status === 200) {
return JSON.parse(xhr.responseText).values;
}
}
// Converting Array into Object.
getData(url).forEach(([date, country, value]) => {
// Added this as if there is no data then it will return nothing.
if(!sheetData[country]){
sheetData[country] = {};
}
sheetData[country][date] = parseInt(value, 10);
});
Whole Modified HTML and Script
<html lang="en" style="overflow-x: hidden;">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<meta charset="utf-8">
<title> IPv6</title>
<!-- data
================================================== -->
<script src="http://caucasusoffline.com/1000/data2/country2.json" type="text/javascript"></script>
<!--css-->
<link rel="stylesheet" href="http://caucasusoffline.com/data/1/jquery.dataTables.css">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<link rel="stylesheet" type="text/css" href="http://caucasusoffline.com/data/1/style.css">
<!-- JavaScript
================================================== -->
<script src="https://code.jquery.com/jquery-3.7.1.js"></script>
<script src="https://code.jquery.com/jquery-migrate-1.4.1.js"></script>
<script src="https://cdn.datatables.net/1.13.11/js/jquery.dataTables.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/5.3.0/js/bootstrap.bundle.min.js"></script>
<script src="https://code.highcharts.com/highcharts.js"></script>
<script src="https://code.highcharts.com/modules/exporting.js"></script>
<script src="https://code.highcharts.com/modules/export-data.js"></script>
<script>
var options = {
chart: {
zoomType: 'xy',
type: 'area',
spacingTop: 30,
backgroundColor: '#323635', },
xAxis: {
type: 'datetime',
// title: { text: 'Select with your mouse to zoom-in on graph' },
labels: {
formatter: function() {
return Highcharts.dateFormat("%d-%m-%Y", this.value ); },
// minRange: 30 * 24 * 3600000 // fourteen days
}
},
yAxis: { title: { text: ' %' }, },
// legend: { enabled: false },
plotOptions: {
area: {
lineWidth: 2,
fillColor: "#5d6d2e",
lineColor: "#97c409",
marker: { enabled: true, symbol: 'circle', radius: 4,
states: { hover: { enabled: true, illColor: '#000000' } } }
} },
tooltip: {
backgroundColor: "#000000",
borderColor: '#000000',
xDateFormat: '%d/%m/%Y',
valueSuffix: ' %',
valueDecimals: 1,
style: {
color: '#ffffff',
padding: '8px'
}
},
// menuItemHoverStyle: { background: '#5d6d2e', },
// menuStyle: { background: '#424542', color: '#ffffff', border: '1px solid #282b2a', },
// menuItemStyle: { color: '#ffffff', background: 'none', } },
series: []
};
</script>
<!-- ?
================================================== -->
<!--
================================================== -->
<!--css-->
</head>
<script type="text/javascript">
const url = "https://sheets.googleapis.com/v4/spreadsheets/1YNj6PtlnjbfoQqD6iu9bycre9Yok4k1oVN9lVNN4XWU/values/data!A2:Z?alt=json&key=AIzaSyB5FsZ-XKEpJVPSmTbQhJPNMOIPYO8VmhU";
let sheetData = {};
function getData(url) {
var xhr = new XMLHttpRequest();
xhr.open("GET", url, false);
xhr.send(null);
if (xhr.status === 200) {
return JSON.parse(xhr.responseText).values;
}
}
getData(url).forEach(([date, country, value]) => {
if (!sheetData[country]) {
sheetData[country] = {};
}
const [day, month, year] = date.split('/');
const utcDate = Date.UTC(year, month - 1, day);
sheetData[country][utcDate] = parseInt(value, 10);
});
var main_data = sheetData;
console.log(sheetData);
var countryArray = null;
countryArray = new Array();
var table = "";
var chart_type = "";
var countryArray = [];
var sorted_countryArray = [];
var chart_container = "country_container";
function drawtable(data, divid, tableid, type) {
$('#' + divid).html('<div><div class="tablebody"><table width="100%" cellpadding="0" cellspacing="0" border="0" class="display" id="' + tableid + '"><tr><td colspan="2"><div class="loading">Loading . .. . </div></td></tr></table></div></div>');
if (type == "country") {
var columns = [
{"width": "15%", "title": "Rank", "targets": 0, "visible": true, "searchable": false, "orderable": true},
{"width": "15%", "title": "IPv6 %", "targets": 1, "visible": true, "searchable": false, "orderable": true},
{"width": "15%", "title": "Country", "targets": 2, "visible": true, "searchable": true, "orderable": true},
{"targets": 3, "visible": true, "searchable": false, "orderable": false}
];
var order = [1, "desc"];
}
var table = $('#' + tableid).dataTable({
"data": data,
'filter': true,
"order": [order],
"paging": false,
"autoWidth": false,
"info": true,
columns: columns,
initComplete: function(settings, json) {
$('div.loading').parent().remove();
},
rowCallback: function(row, data) {
$('td:eq(3)', row).hide();
},
drawCallback: function(settings) {
$('#country_data_table').find('*').removeClass('select trChart');
$('#country_data_table tr').attr("title", "Click on the bar to view the graph");
}
});
$('#myInputTextField').keyup(function() {
table.fnFilter(this.value);
});
return table;
}
function maxKey(a) {
var max, k; // don't set max=0, because keys may have values < 0
for (var key in a) {
if (a.hasOwnProperty(key)) {
max = (key);
break;
}
} //get any key
for (var key in a) {
if (a.hasOwnProperty(key)) {
if ((k = (key)) > max)
max = k;
}
}
return max;
}
function maxValue(data) {
//var data = JSON.parse(a);
var maxProp = null
var maxValue = -1
for (var prop in data) {
if (data.hasOwnProperty(prop)) {
var value = data[prop]
if (value > maxValue) {
maxProp = prop
maxValue = value
}
}
}
return maxValue;
}
$(document).ready(function() {
for (i in sheetData) {
var sorted_countries = [];
//sum = maxValue(country_data[i]);
sorted_countries.push(sheetData[i][maxKey(sheetData[i])]);
sorted_countries.push(i);
sorted_countryArray.push(sorted_countries);
}
sorted_countryArray.sort(function(a, b) {
return b[0] - a[0];
});
var index = 0;
for (i in sorted_countryArray) {
var countries = [];
index++;
countries.push(index);
countries.push(sorted_countryArray[i][0].toFixed(1) + "%");
countries.push(sorted_countryArray[i][1]);
countries.push('<div id="country_container' + index + '" class="area-chart"></div>');
countryArray.push(countries);
}
chart_type = document.location.toString().split("#")[1];
if (chart_type == "") {
} else {
chart_type = "countries"
drawtable(countryArray, 'country_data_div', 'country_data_table', "country");
}
$(".ipv6btn a").click(function() {
$('#country_data_table').remove();
$('#country_data_table_wrapper').remove();
$(this).addClass("selected");
chart_type = this.href.split("#")[1];
if (chart_type == "countries") {
main_data = sheetData;
chart_container = "country_container";
//options.plotOptions.area.fillColor = "red";
//options.plotOptions.area.lineColor = "#97c409";
//options.navigation.menuItemHoverStyle.background = "#5d6d2e";
$("#country_data_div").show();
drawtable(countryArray, 'country_data_div', 'country_data_table', "country");
}
});
$('#country_data_table tbody tr').live('click', function(e) {
e.preventDefault();
var trIndex = $(this).children().first().html();
if ($(this).hasClass("select")) {
$(this).removeClass("select trChart");
$(this).children().last().removeClass("tdChart").hide();
$(this).attr("title", "Click on the bar to view the graph");
} else {
$(this).removeAttr("title");
$('#country_data_table').find('*').removeClass('select trChart');
$('#country_data_table').find('td:last-child').removeClass('tdChart').hide();
$(this).addClass("select trChart");
$(this).children().last().addClass("tdChart").show();
}
//window.location.hash = chart_type+'-'+trIndex;
options.chart.renderTo = 'country_container' + trIndex;
$('#country_container' + trIndex).empty();
var country = $(this).children(':nth-child(3)').html().replace("&", "&");
options.series = [];
var series1 = new Array();
var chart_array = [];
for (i in sheetData) {
// console.log(sheetData[i]);
if (i == country) {
series1[i] = {
showInLegend: '',
name: '',
type: '',
pointInterval: '',
data: []
};
series1[i].showInLegend = false;
series1[i].name = i;
series1[i].type = 'area';
series1[i].pointInterval = 24 * 3600 * 1000;
var json_data = sheetData[i];
console.log(json_data);
for (a in json_data) {
console.log(timeStamp(parseFloat(a)));
chart_array.push([timeStamp(a), parseFloat(json_data[a])])
}
chart_array.sort(function(a, b) {
return a[0] - b[0]
});
for (var j in chart_array) {
series1[i].data.push(([(chart_array[j][0]), chart_array[j][1]]));
//series1[i].data.push(([chart_array[j][1]]));
}
options.series.push(series1[i]);
}
}
// if ($(window).width() <= 767) { options.navigation.buttonOptions.enabled = false; }
var chart = new Highcharts.Chart(options);
});
});
var timeStamp = function(str) {
var date = new Date(parseInt(str));
var day = ("0" + date.getUTCDate()).slice(-2);
var month = ("0" + (date.getUTCMonth() + 1)).slice(-2);
var year = date.getUTCFullYear();
return month + "/" + day + "/" + year;
};
</script>
<body class="bodybg visaulization_body customize-support" style="">
<!--<div style="overflow-x:hidden;">-->
<div class="container-fluid">
<div class="row">
<div class="col-md-12">
<div class="data-header">
<div class="row">
<div class="col-md-6 col-sm-6 col-xs-6">
<h2><font> Internet </font> <span>IPv6</span></h2> </div>
<div class="col-md-6 col-sm-6 col-xs-6">
<div class="ipv6btn">
<a class="btn btnNew red" href="#countries" title="">Countries</a></div>
</div>
</div>
</div>
<div class="data-body">
<div class="tablehead" style="padding-right:16px;">
<input type="text" id="myInputTextField" placeholder="Search" class="pull-right">
<div id="country_data_div">
<div>
<div class="tablebody">
<div id="country_data_table_wrapper" class="dataTables_wrapper no-footer">
<table width="100%" cellpadding="0" cellspacing="0" border="0" class="display dataTable no-footer" id="country_data_table" role="grid">
</div>
</div>
</div>
</div>
</div>
</section>
</div>
</body>
Codepen HTML Output
Reference: