Question 221819 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – 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 I used:

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>

The error is in line 18, the include function showed in this screen shot enter image description here)

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

Tags:

2

Answers


  1. 0

    This works:

    GS:

    function getMyData(){
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet1");
  const vs = Sheets.Spreadsheets.Values.get(ss.getId(),"Sheet1!A1:H" + sh.getLastRow()).values
  //Logger.log(JSON.stringify(vs));
  return vs;
}

function launchMyDialog() {
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createTemplateFromFile('ah1').evaluate(),'Dialog')
}

    html ah1.html:

    <!DOCTYPE html>
<html>
<head>
  <base target="_top">
</head>
<body>
  <div id="tabledata">
       <? var vs = getMyData(); ?>
       <table>
         <? vs.forEach((r,i)=>{ ?>
           <tr>
           <? r.forEach((c,j)=>{ ?>
             <? if(i == 0) { ?>
            <th style="padding:2px 5px;font-weight:bold;border:1px solid black;"><?= c ?> </th>           
           <? } else { ?>
             <td style="padding:2px 5px;border:1px solid black;"><?= vs[i][j] ?> </td>
           <? } ?>
         <?  }); ?>
           </tr>
         <? }); ?>
       </table>
     </div>
</body>
</html>

    enter image description here

    Login or Signup to reply.
  2. 0

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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search