1

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 class="container">
          <br>
          <div class="row">
            <table id="data-table" class="table table-striped table-sm table-hover table-bordered">
              <!-- 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

Tanaike
  • 181,128
  • 11
  • 97
  • 165
Chrizh
  • 27
  • 4
  • I'd guess that one of you titles does not match exactly – Cooper May 09 '22 at 23:28
  • "_Column 9 is 'Star Rating'._" - The DataTables error message refers to "column 9" - but DataTables columns are zero-indexed, so you should probably be looking at the "Comments" column, not the "Star Rating" column. – andrewJames May 10 '22 at 00:40
  • Same thing for "row index" - so you may be looking at the wrong cell in the wrong row. Also, bear in mind that row indexes are assigned by DataTables based on the order in which data is received - which could be different from the order in which it is being displayed to the user (e.g. due to sorting/filtering/paging). (I prefer to not open your Google Sheet, so I cannot see any data.) – andrewJames May 10 '22 at 00:42
  • Thank you. Something in the 'Comments' column in the Google Sheet must have been upsetting it. I've cleared some irrelevant comments and the issue went away. – Chrizh May 10 '22 at 06:12

1 Answers1

1

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:

Reference:

Tanaike
  • 181,128
  • 11
  • 97
  • 165