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

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>

CodePudding user response：

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:

When you modified the Google Apps Script, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful this.
You can see the detail of this in the report of "Redeploying Web Apps without Changing URL of Web Apps for new IDE".

Google Apps Script side:

HTML & Javascript side:

From:

To:

Note:

References: