Search code examples
google-apps-scriptweb-applications

Open a HTML file with data using Google Script

I'm trying to use google script to display a bunch of data in a HTML file, however, my data doesn't seem to make it to the HTML file and I have no idea why. Can someone please tell me what I'm missing here?

Path: htmlList.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top" />
  </head>
  <body>
    My HTML page

    <? for(var i = 0; i <= (users.length -1); i++) { ?>

    <p><?= users[i].firstName ?></p>
    <? } ?>
  </body>
</html>

Path: Code.js

function doGet(users) {
  var html = HtmlService.createTemplateFromFile("htmlList");
  html.users = users;
  return html.evaluate().setTitle("Test my app");
}

function generateLinks() {
  var spreadSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var rr = spreadSheet.getLastRow();

  var users = [];
  for (var i = 3; i <= rr; i++) {
    var firstName = spreadSheet.getRange(i, 1).getValue();

    var user = {
      firstName: firstName
    };

    users.push(user);
  }

  doGet(users);
}
Solution
    • You want to open new tab for own browser using the created HTML data, when you run the function at the script editor.
    • You are using the container-bound script.

    If my understanding is correct, how about this modification?

    Modification points:

    In this modification, I used the following flow. Please think of this as just one of several answers.

    1. By running runScript(), a dialog is opened.
    2. The opened dialog runs a Javascript for opening new tab of the browser and open the URL of Web Apps.
      • At this time, generateLinks() is run from doGet(), and the values are retrieved and put to HTML data.
    3. Close the dialog.

    By this flow, when you run the function at the script editor, the created HTML is opened as new tab of your browser.

    Modified script:

    Please copy and paste the following script to the container-bound script of Spreadsheet. And then, please redeploy Web Apps as new version. At that time, as a test case, please set Execute the app as: and Who has access to the app: as Me and Anyone, even anonymous, respectively. In this case, you are not required to modify the script of HTML side.

    function doGet() {
  var html = HtmlService.createTemplateFromFile("htmlList");
  html.users = generateLinks(); // Modified
  return html.evaluate().setTitle("Test my app");
}

function generateLinks() {
  var spreadSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var rr = spreadSheet.getLastRow();

  var users = [];
  for (var i = 3; i <= rr; i++) {
    var firstName = spreadSheet.getRange(i, 1).getValue();

    var user = {
      firstName: firstName
    };

    users.push(user);
  }
  return users; // Modified
}

// I added the following function. Please run this function.
function runScript() {
  var url = ScriptApp.getService().getUrl();
  var script = "<script>window.open('" + url + "', '_blank').focus();google.script.host.close();</script>";
  var html = HtmlService.createHtmlOutput(script);
  SpreadsheetApp.getUi().showModalDialog(html, 'sample');
}
    • When var spreadSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); is used, the 1st sheet of Spreadsheet is used. So when you want to retrieve the values from the specific sheet, for example, please modify to SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheetName").

    Note:

    • If you modified the script of Web Apps, please redeploy Web Apps as new version. By this, the latest script is reflected to the Web Apps. Please be careful this.

    References: