Advise on Google Chart: Data column(s) for axis #0 cannot be of type string

user_17332277 0
Viewed 37

I am trying to get a dynamic chart from google sheet data which is stored on the tab named 'Data' (range A1:C6, Headers- Date, Sales & Expense) to google WebApp. I have given the codes below, but the page is showing "Data column(s) for axis #0 cannot be of type string". I am not sure where am I doing it wrong, if anyone could please advise.

Code.gs:

function doGet(){
return HtmlService.createTemplateFromFile("chart").evaluate();
 
}

function getStats(){

const ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
return ws.getRange(2, 1, ws.getLastRow(),3).getDisplayValues();
    
}

Chart.html:

<html>
  <head>
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <script type="text/javascript">
      google.charts.load('current', {'packages':['corechart']});
      google.charts.setOnLoadCallback(getData);

      function getData(){

        google.script.run.withSuccessHandler(drawChart).getStats();

      }

      function drawChart(dataReturned) {
        var data = google.visualization.arrayToDataTable(dataReturned);

      
          var options = {
          title: 'Sales and Expenses',
          curveType: 'function',
          legend: { position: 'bottom' }
        };

        var chart = new google.visualization.LineChart(document.getElementById('chart'));

        chart.draw(data, options);
      }
    </script>
  </head>
  <body>
    <div id="chart" style="width: 900px; height: 500px"></div>
  </body>
</html>
google-apps-script google-sheets google-visualization charts
1 Answers

When I saw your script and your question, I thought that the reason for your issue might be due to that the values are retrieved by getDisplayValues(). In this case, all values are string type.

If your showing script is modified, how about the following modification?

Google Apps Script side:

function getStats() {
  const ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
  return ws.getRange(1, 1, ws.getLastRow(), 3).getValues().map(([a, b, c]) => [a instanceof Date ? a.toISOString() : a, b, c]); // Modified
}
  • Here, from your script and question, I guessed that the 1st row is the header row like "Date", "Sales" and "Expense". If your 1st row is not the header, please set them.

HTML & Javascript side:

From:

var data = google.visualization.arrayToDataTable(dataReturned);

To:

dataReturned = dataReturned.map(([a, b, c], i) => [i == 0 ? a : new Date(a), b, c]); // Added
var data = google.visualization.arrayToDataTable(dataReturned);

Note:

References:

user_71086530
Related