Search code examples
javascriptgoogle-apps-scriptgoogle-sheets

Apps Script : Bad value error for displaying google sheet as web app

I'm trying to display google sheet as a web app but returned a bad value error when trying to run it.

Here is the Code.gs:

function doGet() {
  return HtmlService.createTemplateFromFile('Index').evaluate();
}
 

function getData(){
  var spreadSheetId = "    "; //I have entered correct sheet id
  var dataRange     = "Sheet1!A2:H";
 
  var range   = Sheets.Spreadsheets.Values.get(spreadSheetId,dataRange);
  var values  = range.values;
 
  return values;
}
 

function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename)
      .getContent();
}

This is the Index.html code :

<!DOCTYPE html>
<html>
  
  <head>
    <base target="_top">
    
    <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'); ?> 
  </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 code:

<script>
  
  google.script.run.withSuccessHandler(showData).getData();
  
  function showData(dataArray){
    $(document).ready(function(){
      $('#data-table').DataTable({
        data: dataArray,
      
        columns: [
          {"title":"File Name"},
          {"title":"Description"},
          {"title":"File Type"},
          {"title":"Category"},
          {"title":"File Extension"},
          {"title":"File Link"}
          {"title":"Uploaded By"},
          {"title":"Upload Date"},
        ]
      });
    });
  }
</script>

The error is in line 18, when I tried to run the include function in Code.gs (showed in this screen shot) enter image description here)

I'm not sure what went wrong, anyone has an idea?

Solution

  • About your question of I'm trying to display google sheet as a web app but returned a bad value error when trying to run it. and I'm not sure what went wrong, anyone has an idea?, how about the following modification points?

    Modification points:

    • About your current error of Exception: Bad value, in this case, when the function include is directly run with the script editor, such an error occurs, because of no value of filename. I guessed that the reason for your current issue might be due to this.

    • In your script, it seems that you wanted to load "JavaScript" file when the HTML is loaded. So, in this case, please open your Web Apps with your browser. By this, the HTML template is loaded by replacing <?!= include('JavaScript'); ?> with your Javascript using return HtmlService.createTemplateFromFile('Index').evaluate();.

    Note:

    • When I put a sample "JavaScript" file and I tested your showing script by opening Web Apps with my browser, no error occurs.

    • I guess that the script calling getData() might be included in "JavaScript", and also, I suppose that you have already confirmed that <!-- TABLE DATA IS ADDED BY THE showData() JAVASCRIPT FUNCTION ABOVE --> worked. If you have an issue with this part, please provide the detail of it.

    • When you modified the Google Apps Script of Web Apps, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful about this.

    • You can see the detail of this in my report "Redeploying Web Apps without Changing URL of Web Apps for new IDE (Author: me)".

    Added:

    About your following reply,

    It's still doesn't work. I just added the JavaScript.html code in my original question, probably more clearer for you to know what cause the error

    When I saw your added Javascript, I think that your Javascript has a modification point. About the following part,

    columns: [
  {"title":"File Name"},
  {"title":"Description"},
  {"title":"File Type"},
  {"title":"Category"},
  {"title":"File Extension"},
  {"title":"File Link"} // <--- , is required to be added.
  {"title":"Uploaded By"},
  {"title":"Upload Date"},
]

    When this is reflected in your whole script, it becomes as follows.

    Google Apps Script side: Code.gs

    function doGet() {
  return HtmlService.createTemplateFromFile('Index').evaluate();
}

function getData() {
  var spreadSheetId = "###"; // Please set your Spreadsheet ID.
  var dataRange = "Sheet1!A2:H";
  var range = Sheets.Spreadsheets.Values.get(spreadSheetId, dataRange);
  var values = range.values;
  return values;
}

function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename).getContent();
}

    HTML: Index.html

    <!DOCTYPE html>
<html>
  
  <head>
    <base target="_top">
    
    <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'); ?> 
  </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: JavaScript.html

    <script>
  
google.script.run.withSuccessHandler(showData).getData();

function showData(dataArray) {
  $(document).ready(function () {
    $('#data-table').DataTable({
      data: dataArray,

      columns: [
        { "title": "File Name" },
        { "title": "Description" },
        { "title": "File Type" },
        { "title": "Category" },
        { "title": "File Extension" },
        { "title": "File Link" },
        { "title": "Uploaded By" },
        { "title": "Upload Date" },
      ]
    });
  });
}

</script>
    • When I tested the above Google Apps Script, HTML, and Javascript, no error occurs. I confirmed that a table is shown in the browser. So, please correctly copy and paste the above and test it.

    Note: