I am trying to draw chart in Google line chart in web app, functionality of the chart is
- On loading the page Chart should display with data “All”.
- On drop down selection chart have to be updated with selected values.
After test deployment, I am getting the error in the browser
First column must be a domain column
Not sure where I am messing around 🙁
Here is my App script function
function draw_chart(){
var ss = SpreadsheetApp.openById('XXXXXXX');
var metrics_sheet = ss.getSheetByName('sheet1');
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 chart_dt = JSON.stringify(values);
Logger.log(chart_dt);
return chart_dt;
}
Above function gives the output like
[["city","date","volume"],
["All","2022-04-30T18:30:00.000Z",492],
["All","2022-05-31T18:30:00.000Z",406],
["All","2022-06-30T18:30:00.000Z",924],
["All","2022-07-31T18:30:00.000Z",902],
["san fran","2023-04-30T18:30:00.000Z",613],
["san fran","2023-05-31T18:30:00.000Z",233],
["san fran","2023-06-30T18:30:00.000Z",593],
["san fran","2023-07-31T18:30:00.000Z",306],
["chicago","2022-04-30T18:30:00.000Z",989],
["chicago","2022-05-31T18:30:00.000Z",735],
["chicago","2022-06-30T18:30:00.000Z",497],
["chicago","2022-07-31T18:30:00.000Z",663]]
My Script for drawing and filtering the charts
<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(){
window.alert('Inside Draw Chart func');
google.script.run.withSuccessHandler(displaychart).chart_draw();
}
function get_filter_city(){
google.script.run.withSuccessHandler(filter_data).draw_linechart();
}
function filter_data(city_data){
let city_select = document.getElementById("city").value;
let f_city_data = city_data;
//Filter for regions
if(city_select==""){
city_select = city_data.slice(0);
}else{
filterbyregion = f_city_data.filter(city_data => city_data.city === city_select);
}
}
function displaychart(c_data1){
var datatable = new google.visualization.DataTable();
datatable.addColumn('string','date');
datatable.addColumn({type:'string',role:'annotation'});
datatable.addColumn('number',id:'pat');
var rows = [];
for(var i=0;i<c_data1.length;i++){
var curr_element = c_data1[i];
var e_date = new Date(curr_element[1]);
rows.push([curr_element[0],curr_element[1],curr_element[2]]);
}
datatable.addRows(rows);
//Setting Chart Options
var options={
title: 'Sales Volume'
};
// Draw our chart passing some options
var chart = new google.visualization.LineChart(document.getElementById('chart_container'));
chart.draw(datatable,options);
}
</script>
HTML code
<select id="city">
<option value="All">All</option>
<option value="san fran">san fran</option>
<option value="chicago">chicago</option>
</select>
<button id="srch_btn" onclick="get_filter_city()"> Search </button>
<div id="hell" class="chart_container"></div>