I am following this Line Chart documentation to draw the charts. I have a problem in converting JSON Stringify data format to an Array. I have split my code as sections to pin point where my doubt and my wrong doing.
This is my App script function to bring the data
function collect_data(){
var ss = SpreadsheetApp.openById('XXXXXXX');
var my_sheet = ss.getSheetByName('proj_chart');
var values = metrics_sheet.getRange("A1:C50").getValues();
var strfy = JSON.stringfy(values);
return strfy;
}
Result from above function is this
[["Department","work_date","WorkHours"],
["All","2024-01-30T18:30:00.000Z",200],
["All","2024-02-30T18:30:00.000Z",100],
["All","2024-03-30T18:30:00.000Z",60],
["All","2024-04-30T18:30:00.000Z",70],
["HR","2024-01-30T18:30:00.000Z",100],
["HR","2024-02-31T18:30:00.000Z",50],
["HR","2024-03-30T18:30:00.000Z",30],
["HR","2024-04-31T18:30:00.000Z",40],
["New Hire","2024-01-30T18:30:00.000Z",100],
["New Hire","2024-02-31T18:30:00.000Z",50],
["New Hire","2024-03-30T18:30:00.000Z",30],
["New Hire","2024-04-31T18:30:00.000Z",30]]
Above data I am passing to my JS function to draw a chart like below
<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>
<script type="text/javascript">
google.charts.load('current',{'packages':['corechart']});
google.charts.setOnLoadCallback(drawchart);
function drawchart(){
google.script.run.withSuccessHandler(showchart).collect_data();
}
Then I am doin this to draw an chart
function showchart(dep_data){
var arrtopush = [];
Here I checked the Object of the return data from drawchart function
console.log(typeof(dep_data)); // Its String here
Once I tried to push to Array all the rows becomes ‘undefined’, I am sure I am doing something wrong here.
for(i=0; i<dep_data.length;i++){
var arr = new Array(dep_data[i].Department,dep_data[i].work_date,dep_data[i].WorkHours);
arrtopush.push(arr);
}
This is my data table to pass it to draw charts method
var datatable = new google.visualization.DataTable();
datatable.addColumn('string','Department');
datatable.addColumn('date','work_date');
datatable.addColumn('number','WorkHours');
datatable.addRows(arrtopush);
This Data table I using to draw the charts like below
var chart = new google.visualization.LineChart(document.getElementById('dept_chart'));
chart.draw(datatable);
It Give the error Uncaught Error: Row 0 is not null or an array.
I tried to pass the variable dep_data directly by using arrayToDataTable like this
var chartdata = new google.visualization.arrayToDataTable(JSON.parse(dep_data)); //return type is string so I used JSON.parse here
var chart = new google.visualization.LineChart(document.getElementById('dept_chart'));
chart.draw(chartdata);
Here also It Gives me the error Uncaught Error: Row 0 is not null or an array.
}
</script>
<select id="dept">
<option value="All">All</option>
<option value="HR">HR</option>
<option value="New Hire">New Hire</option>
</select>
<div id="dept_chart" class="cls"></div>
As I am in the learning phase seeking some advise and an working snippet.