My Problem:
I have a problem in Drawing and filtering the Google charts in HTML page. My code works without an error checked it on the console,
but my charts displays blank on the browser.
My Intention:
I am trying to acheive draw a chart
1.On the page load Chart should be displayed with values "National" in the Data.
2.On the dropdown select and button click filter chart should be filterd based on the Dropdown selection.
(Note: I can upload the image of chart that results in the browser, but I don't know how to insert the image in the question)
What I have tried:
My App script function
function chart_draw(){
var ss = SpreadsheetApp.openById('XXXXXXX');
var metrics_sheet = ss.getSheetByName('notran');
var lastrow = metrics_sheet.getLastRow();
var lastcolumn = metrics_sheet.getLastColumn();
var values = metrics_sheet.getRange("A1:C").getValues();
const range = metrics_sheet.getRange("A1:C"+lastrow).getValues();
var index_values = lastrow - range.reverse().findIndex(c=>c[0]!='');
var temp = "A1:C"+index_values;
var values = metrics_sheet.getRange(temp).getValues();
var pat_data1 = JSON.stringify(values);
Logger.log(pat_data1);
return pat_data1;
}
Data Result from above function
[["Sales Div","date","volume"],
["National","2024-04-30T18:30:00.000Z",100]"
["National","2024-05-30T18:30:00.000Z",403],
["National","2024-06-30T18:30:00.000Z",678],
["National","2024-07-30T18:30:00.000Z",700],
["New City","2024-04-30T18:30:00.000Z",90],
["New City","2024-05-31T18:30:00.000Z",200],
["New City","2024-06-30T18:30:00.000Z",500],
["New City","2024-07-31T18:30:00.000Z",300],
["old City","2024-04-30T18:30:00.000Z",10],
["old City","2024-05-31T18:30:00.000Z",203],
["old City","2024-06-30T18:30:00.000Z",178],
["old City","2024-07-31T18:30:00.000Z",400]]
My JS Script
<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(displaychart).chart_draw();
}
let filter_selection =[];
function displaychart(c_data1){
var arrMain = new Array();
for(i=0; i<c_data1.length;i++){
var arr = new Array(c_data1[i].date,c_data1[i].volume,c_data1[i].SalesDiv);
arrMain.push(arr);
}
var datatable = new google.visualization.DataTable();
datatable.addColumn({type: 'date', label: 'x'});
datatable.addColumn({type: 'number', label: 'y'});
datatable.addColumn({type:'string',role:'annotation'});
datatable.addRows(arrMain);
console.log(datatable);
var chart = new google.visualization.LineChart(document.getElementById('line_cont'));
chart.draw(datatable);
}
function filtering(){
google.script.run.withSuccessHandler(filter_data).draw_linechart();
}
function filter_data(f_sales_div_data){
let selection = document.getElementById("sales_div").value;
let filter_sales_div = f_sales_div_data;
if(selection==""){
filter_selection = filter_sales_div.slice(0);
}else{
filter_selection = filter_sales_div.filter(f_sales_div_data => f_sales_div_data.salesDiv === selection) ;
}
}
</script>
<select id="sales_div">
<option value="National">National</option>
<option value="New City">New City</option>
<option value="old city">old city</option>
</select>
<button id="srch_btn" onclick="filtering()"> Search </button>
<div id="line_cont" class="d_c"></div>