I am trying to draw a chart in Web App HTML page, fetching data from Google sheets through my App script function.
My GAS Code
// Data For Line Chart
function draw_chart(){
var ss = SpreadsheetApp.openById('XXXXXXX');
var metrics_sheet = ss.getSheetByName('data_s');
var lastrow = metrics_sheet.getLastRow();
var lastcolumn = metrics_sheet.getLastColumn();
var values = metrics_sheet.getRange("A1:X").getValues();
/* Find Last Index of the Non Blank cells */
const range = metrics_sheet.getRange("A1:X"+lastrow).getValues();
var index_values = lastrow - range.reverse().findIndex(c=>c[0]!='');
var temp = "A1:X"+index_values;
var values = metrics_sheet.getRange(temp).getValues();
var pat_data1 = JSON.stringfy(values);
return pat_data1;
}
Sample Output of the draw chart function
[["Region","date","volume"],
["All","2024-04-30T18:30:00.000Z",100],
["All","2024-05-30T18:30:00.000Z",403],
["All","2024-06-30T18:30:00.000Z",678],
["All","2024-07-30T18:30:00.000Z",700],
["North","2024-04-30T18:30:00.000Z",90],
["North","2024-05-31T18:30:00.000Z",200],
["North","2024-06-30T18:30:00.000Z",500],
["North","2024-07-31T18:30:00.000Z",300],
["South","2024-04-30T18:30:00.000Z",10],
["South","2024-05-31T18:30:00.000Z",203],
["South","2024-06-30T18:30:00.000Z",178],
["South","2024-07-31T18:30:00.000Z",400]]
Loading all my Libraries here
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<script src="https://www.gstatic.com/charts/loader.js"></script>
I understand arrayToDataTable accepts Array of Array,so I did this
<script type="text/javascript">
google.charts.load('current',{'packages':['corechart']});
google.charts.setOnLoadCallback(drawchart);
function drawchart(){
google.script.run.withSuccessHandler(displaychart).draw_linechart();
}
function displaychart(c_data1){
var chartdata = new google.visualization.arrayToDataTable(JSON.parse(c_data1),false);
console.log(chartdata);
//Setting Chart Options
var options={
title: 'Line Chart Example',
is3D: true
}
// Draw chart passing some options
var chart = new google.visualization.LineChart(document.getElementById('line_chart'));
chart.draw(chartdata,options);
}
</script>
<select id="city">
<option value="National">National</option>
<option value="North">North</option>
<option value="South">South</option>
</select>
<div id="line_chart" class="line_cont"></div>
I am getting the error
All series on a given axis must be of the same data type
For the above error, I took this answer as an reference
Google Chart All series on a given axis must be of the same data type confusing error
And I tried this
var data = new google.visualization.DataTable();
data.addColumn('string','region' );
data.addColumn('date','date');
data.addColumn('number','volume');
var arrMain=[];
for(i=0;i<c_data1.length;i++){
console.log(c_data1[i].region) // returns undefined
arrMain.push([c_data1[i].region,new Date(c_data1[i].date),c_data1[i].volume]);
}
data.addRows(arrMain);
Again same error “All series on a given axis must be of the same data type” console.log(c_data1[i].region) returns undefined.
What exactly I doing wrong here? I understand lot of down vote because lack of info I tired to convey my error again.
7
Modification points:
- In your Javascript, at
google.script.run.withSuccessHandler(displaychart).draw_linechart();,draw_linechartis called. But, in your Google Apps Script, the function name isdraw_chart(). Is thisdraw_linechart? - From your updated question, about
All series on a given axis must be of the same data type, when I saw yourSample Output of the draw chart function, the columns “A”, “B” and “C” are strings, strings, and numbers, respectively. I think that this is the reason for your current issue. In this case, the data type must be the same. But, I do not know your expected result. So, the following modification is just my guess.
I guessed your expected result is as follows.
- Your data has 3 columns
"Region","date","volume", and your HTML has a dropdown list. When you change the dropdown list, for example, you select “South”, you want to show the chart with the data of “South” in “Region”. When “National” is selected, you want to use the data of “All” in “Region”.
If my guess for your expected result is correct, how about the following modification?
Google Apps Script:
Please modify the function name from draw_chart() to draw_linechart.
HTML:
In this case, it supposes that the function draw_linechart works fine. And, the values Sample Output of the draw chart function correctly returned. Please be careful about this.
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<script src="https://www.gstatic.com/charts/loader.js"></script>
</head>
<body>
<script type="text/javascript">
google.charts.load('current', { 'packages': ['corechart'] });
google.charts.setOnLoadCallback(drawchart);
let obj;
function drawchart() {
google.script.run.withSuccessHandler(e => {
const [[, ...head], ...v] = JSON.parse(e);
obj = v.reduce((o, [a, b, c]) => (o[a] = o[a] ? [...o[a], [new Date(b), c]] : [head, [new Date(b), c]], o), {});
displaychart("All");
}).draw_linechart();
}
function displaychart(key) {
var chartdata = new google.visualization.arrayToDataTable(obj[key], false);
var options = {
title: 'Line Chart Example',
is3D: true
}
var chart = new google.visualization.LineChart(document.getElementById('line_chart'));
chart.draw(chartdata, options);
}
function selected(e) {
displaychart(e.value == "National" ? "All" : e.value);
}
</script>
<select id="city" onchange="selected(this)">
<option value="National">National</option>
<option value="North">North</option>
<option value="South">South</option>
</select>
<div id="line_chart" class="line_cont"></div>
</body>
</html>
Testing:
When the above-modified HTML and Javascript are used with your value “Sample Output of the draw chart function”, the following result is obtained.
8