Google Web App Script Unknown Parameter Error on Load-CodePudding

On load my web app is producing this error:

DataTables warning: table id=data-table - Requested unknown parameter '9' for row 21, column 9. For more information about this error, please see http://datatables.net/tn/4

Code.gs

    function doGet() {
      return HtmlService.createTemplateFromFile('Index').evaluate();
    }
     
    //GET DATA FROM GOOGLE SHEET AND RETURN AS AN ARRAY
    function getData(){
      var spreadSheetId = "1VzHY8fTq8OsXhpHYHESSSPxeVNOnqxpjcsyWJpbuEOs"; //CHANGE
      var dataRange     = "Base Stats!A2:L"; //CHANGE
     
      var range   = Sheets.Spreadsheets.Values.get(spreadSheetId,dataRange);
      var values  = range.values;
     
      return values;
    }
     
    //INCLUDE JAVASCRIPT AND CSS FILES
    function include(filename) {
      return HtmlService.createHtmlOutputFromFile(filename)
          .getContent();
    }

Index.html

    <!DOCTYPE html>
    <html>
      <head>
        <base target="_top">
        <!--INCLUDE REQUIRED EXTERNAL JAVASCRIPT AND CSS LIBRARIES-->
        <script src="https://code.jquery.com/jquery-3.5.1.js"></script>
        <script src="https://cdn.datatables.net/1.10.23/js/jquery.dataTables.min.js"></script>
        <script src="https://cdn.datatables.net/1.10.23/js/dataTables.bootstrap4.min.js"></script>
        <link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.5.2/css/bootstrap.css">
        <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.23/css/dataTables.bootstrap4.min.css">
     
        <?!= include('JavaScript'); ?> <!--INCLUDE JavaScript.html FILE-->
      </head>
     
      <body>
        <div >
          <br>
          <div >
            <table id="data-table" >
              <!-- TABLE DATA IS ADDED BY THE showData() JAVASCRIPT FUNCTION ABOVE -->
            </table>
          </div>
        </div>  
      </body>
    </html>

JavaScript.html

    <script>
      /*
      *THIS FUNCTION CALLS THE getData() FUNCTION IN THE Code.gs FILE, 
      *AND PASS RETURNED DATA TO showData() FUNCTION
      */
      google.script.run.withSuccessHandler(showData).getData();
     
      //THIS FUNCTION GENERATE THE DATA TABLE FROM THE DATA ARRAY
      function showData(dataArray){
        $(document).ready(function(){
          $('#data-table').DataTable({
            data: dataArray,
            //CHANGE THE TABLE HEADINGS BELOW TO MATCH WITH YOUR SELECTED DATA RANGE
            columns: [
              {"title":"Date Added"},
              {"title":"SpotRacer"},
              {"title":"Brand"},
              {"title":"Model"},
              {"title":"Acceleration"},
              {"title":"Speed (MPH)"},
              {"title":"Speed (KPH)"},
              {"title":"Handling (%)"},
              {"title":"Star Rating"},
              {"title":"Comments"},
              {"title":"Score (Cumlative)"},
              {"title":"Score (Weighted)"}
            ]
          });
        });
      }
    </script>

I'm not sure what is causing the error with that specific row and column, but perhaps has something to do with the column not displaying plain text? Column 9 is 'Star Rating'.

Google Sheet: SpotRacers Fanbase Database

CodePudding user response：

In your script, Sheets.Spreadsheets.Values.get of Sheets API is used. In this case, the retrieved values are 2-dimensional array. But, for example, when all rows are not embedded by the cell values (for example, the 1st row has the values in the columns "A", "B", "C", and the 2nd row has the values in the columns "A" and "B".), the lengths of all rows are different. I'm worried about this situation. So, I thought that the reason for your issue might be due to this.

If my understanding of your current issue was correct, how about the following modification?

From:

function getData(){
  var spreadSheetId = "###"; //CHANGE
  var dataRange     = "Base Stats!A2:L"; //CHANGE
 
  var range   = Sheets.Spreadsheets.Values.get(spreadSheetId,dataRange);
  var values  = range.values;
 
  return values;
}

To:

function getData(){
  var spreadSheetId = "###"; // Please set your Spreadsheet ID.
  var sheet = SpreadsheetApp.openById(spreadSheetId).getSheetByName("Base Stats");
  var values = sheet.getRange("A2:L"   sheet.getLastRow()).getDisplayValues();
  return values;
}

In this modification, the values are retrieved using getDisplayValues(). And, the data is retrieved from the data range.

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".

Reference:

getDisplayValues()