Draw and filter Charts in App Script Web app

  Kiến thức lập trình

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_linechart is called. But, in your Google Apps Script, the function name is draw_chart(). Is this draw_linechart?
  • From your updated question, about All series on a given axis must be of the same data type, when I saw your Sample 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

Theme wordpress giá rẻ Theme wordpress giá rẻ Thiết kế website Kho Theme wordpress Kho Theme WP Theme WP

LEAVE A COMMENT