How Do I Pass Values into HTML Template Sidebar?

Question

I've been tasked with replacing some of our standing Access databases, and I'm wondering if you all could help me out.

Currently, I've got a Google Sheet that will be accepting Google Form responses. I'm wanting to create some type of UI that will allow someone to review the line item details on the sidebar, one at a time. Then, I'd like them to be able to update the Google Sheet via the sidebar, without having them update the Google Sheet directly. Really, all I need them to do is mark that they have read the line with a button that updates the row with "Read", or or something similar. My first step though is to see if I can get the user's e-mail displayed at the top of the sidebar, and then work on passing data from the spreadsheet into it.

Now, my background is almost solely in SQL/VBA, so my JS is lacking. However, I was able to get the following working with one exception; the variable leaderEmail doesn't appear on the sidebar as expected.

I have validated that the Session.getActiveUser().getEmail(); does work with our GSuite domain e-mails. I would assume that declaring it as a variable should make it usable in HTML templating via Apps Script, right?

I would appreciate any/all help you all could provide.

Note: Trigger currently set to onOpen() event. Please feel free to make a copy of the Google Sheet for yourself, if you don't want to run it.

Thanks!

Code.gs

// Show Sidebar
function showUpdatesMenu(){
  // Start UI Menu
  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
        .createMenu('Updates Menu')
        .addItem('Leadership', 'openLeadership')
        .addToUi();

   // Log the email address of the person running the script.
    var sessionEmail = Session.getActiveUser().getEmail();
    var currentTime = Date.now();
   Logger.log(sessionEmail);
}

Sidebar.html

<!-- Use a templated HTML printing scriptlet to import common stylesheet. -->
<!-- ?!= HtmlService.createHtmlOutputFromFile('mtoStylesheet').getContent(); ? -->
<!-- This CSS package applies Google styling; it should always be included. -->
<!-- link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons.css" -->

<style>
label {
  font-weight: bold;
}

.branding-below {
  bottom: 54px;
  top: 0;
}

.branding-text {
  left: 7px;
  position: relative;
  top: 3px;
}

.logo {
  vertical-align: middle;
}

.width-100 {
  width: 100%;
  box-sizing: border-box;
  -webkit-box-sizing : border-box;‌
  -moz-box-sizing : border-box;
}

#sidebar-value-block,
#dialog-elements {
  background-color: #eee;
  border-color: #eee;
  border-width: 5px;
  border-style: solid;
}

#sidebar-button-bar,
#dialog-button-bar {
  margin-bottom: 30px;
}
</style>

<!-- Below is the HTML code that defines the sidebar element structure. -->
<!-- div class="sidebar branding-below" -->
  <p>
  <b>Logged in as <?= sessionEmail ?></b>
  </p>
  <hr />
  <p><b>CLEAR ALL LAPS AND STEPS:</b><br> This button clears all previous results.</p>

  <div class="block" id="sidebar-button-bar-clear">
    <button id="sidebar-clear-button1" style="width:100%" onclick="onClearClick();" value="1">Clear blocks</button>
  </div>
  <hr />
  <hr />




<!-- Use a templated HTML printing scriptlet to import JavaScript. -->
<!-- ?!= HtmlService.createHtmlOutputFromFile('sidebar-Javascript').getContent(); ? -->
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<script>

</script>

openLeadership.gs

function openLeadership() {
 //  var html = HtmlService.createHtmlOutputFromFile('Page')
//      .setTitle('My custom sidebar')
  var cache = CacheService.getUserCache();
  var leaderEmail = Session.getActiveUser().getEmail();
  var html = HtmlService.createHtmlOutputFromFile('Sidebar')
      .setSandboxMode(HtmlService.SandboxMode.IFRAME)
      .setTitle('Leadership')
      .setWidth(100)
      .setHeight(200);
  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
      .showSidebar(html);

var cache = CacheService.getUserCache();

}

Answer 1

To render Apps Script data in your HTML files you need to process the HTML as a template, and then explicitly pass the relevant data into your template before rendering it.

Here's what that looks like:

  var html = HtmlService.createTemplateFromFile('Sidebar');
  html.sessionEmail = Session.getActiveUser().getEmail();

  var rendered = html.evaluate().setSandboxMode(HtmlService.SandboxMode.IFRAME)
      .setTitle('Leadership')
      .setWidth(100)
      .setHeight(200);

  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
      .showSidebar(rendered);