Use variable value from .gs file in custom HTML sidebar

Question

I have a google sheet where I have scripted a custom sidebar.

Within the sidebar, I want to display the value of column I within the active row.

I've managed to work out the script to store the variable 'I' when the sidebar is opened:

var hotelName = ""

function openSidebar() {
var html = HtmlService.createHtmlOutputFromFile('index');
SpreadsheetApp.getUi().showSidebar(html);
var ss=SpreadsheetApp.getActiveSpreadsheet();
var s=ss.getSheetByName("Contract Registry Document");
var row=s.getActiveCell().getRow();
var column=9;
hotelName = s.getRange(row, column).getValue();
Logger.log(hotelName);
}

However I now need this to appear in an empty DIV element in my sidebar. How do I export this variable?

UPDATE:

That works great. I've now added as well a button to the sidebar to refresh and what I want that to do is to update the script so that if the user has changed rows, the new hotel name will show. At the moment I've added this HTML:

 <div id="footer">

 <div><?= hotel ?></div>

 <p><i class="fa fa-refresh" aria-hidden="true" id="testing"></i></p>

 </div>

And this JQuery to the index file:

$("#testing").on('click', function buttonClick() {
google.script.run.buttonClick();
});

And for the function I just copied the original function:

function buttonClick() {
var ss=SpreadsheetApp.getActiveSpreadsheet();
var s=ss.getSheetByName("Contract Registry Document");
var row=s.getActiveCell().getRow();
var column=9;
var hotelName = s.getRange(row, column).getValue();

// get the html template after retrieving the values
var html_template = HtmlService.createTemplateFromFile('index');

// set the value for the index.html `hotel` placeholder/scriptlet
html_template.hotel = hotelName;

// evaluate the template
var sidebar_html = html_template.evaluate();

SpreadsheetApp.getUi().showSidebar(sidebar_html);

Logger.log(hotelName);
}

However, really I want the value of the cell to update asynchronously so that the sidebar doesn't re-load, just the hotel name...

Would this be possible??

Possible duplicate of [How do I make a Sidebar display values from cells?](https://stackoverflow.com/questions/30628894/how-do-i-make-a-sidebar-display-values-from-cells) — Rubén, Nov 15 '17 at 15:44

score 1 · Answer 1 · answered Nov 15 '17 at 14:36

Use Scriptlets with createTemplateFromFile() and evaluate(). Then use the printing scriptlets in the html file and set their value in your openSidebar()

function openSidebar() {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var s=ss.getSheetByName("Contract Registry Document");
  var row=s.getActiveCell().getRow();
  var column=9;
  var hotelName = s.getRange(row, column).getValue();

  // get the html template after retrieving the values
  var html_template = HtmlService.createTemplateFromFile('index');

  // set the value for the index.html `hotel` placeholder/scriptlet
  html_template.hotel = hotelName;

  // evaluate the template
  var sidebar_html = html_template.evaluate();

  SpreadsheetApp.getUi().showSidebar(sidebar_html);

  Logger.log(hotelName);
}

index.html

<html>
  <body>
    <p>The hotel name is: <?= hotel ?></p>
  </body>
</html>

Hi and thanks for this - I implemented and it works great!! How could I add a refresh function so that the div containing the hotel name is updated, not the rest of the sidebar? — DevB1, Nov 15 '17 at 16:27
for that, you want to use @Copper's jQuery solution..this one was just for your original question about exporting a variable to an html sidebar — random-parts, Nov 15 '17 at 16:39

Cooper · Accepted Answer · 2017-11-15T17:19:46.593

Getting data from a spreadsheet on loading a sidebar

Don't forget to select the row first before displaying the sidebar.

code.gs

function openThisSideBar()
{
  var html = HtmlService.createHtmlOutputFromFile('hotel').setTitle('MySideBar');
  SpreadsheetApp.getUi().showSidebar(html);
}

function getHotel()
{
  var s=SpreadsheetApp.getActive().getSheetByName("Contract Registry Document");
  var row=s.getActiveCell().getRow();
  var n=Utilities.formatString('Your Hotel is named: %s',s.getRange(row, 9).getValue());
  return n;
}

function onOpen()
{
  SpreadsheetApp.getUi().createMenu('MyTools')
    .addItem('Open Sidebar', 'openThisSideBar')
    .addToUi();
}

hotel.html:

<!DOCTYPE html>
<html>
  <head>
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
    <script>
    $(function()
    {
       google.script.run
           .withSuccessHandler(updateHotel)
           .getHotel();
    });
    function updateHotel(name)
    {
       document.getElementById('hotel').innerHTML=name;
    }
    console.log('MyCode');
    </script>
  </head>
  <body>
   <div id="hotel"></div> 
  </body>
</html>

You can add your refresh like this:

<script>
function refresh()
    {
       google.script.run
          .withSuccessHandler(updateHotel)
          .getHotel();
    }
</script>

And the new html:

<input type="button" value="Refresh" onClick="refresh();" />

That way you needn't refresh the entire page but just the div.

Cheers for this. The main code is working great. But I cannot get the refresh function up and running. Can I check where you are inserting the refresh function? I've tried it in the .gs file as well as within a script tag in the HTML and the refresh doesn't seem to be working. Thanks — DevB1, Nov 15 '17 at 17:07
I had a semicolon after the withSuccessHandler. I think it will work now. — Cooper, Nov 15 '17 at 17:20

Use variable value from .gs file in custom HTML sidebar

2 Answers2

Getting data from a spreadsheet on loading a sidebar

Linked