javascriptgoogle-sheetsgoogle-apps-scriptweb-applicationsgoogle-visualization

Draw and filter Charts in App Script Web app

I am trying to draw a chart on a Web App HTML page, fetching data from Google Sheets through my Apps 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.

Solution

  • Modification points:

    I guessed your expected result is as follows.

    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.

    enter image description here