Google Apps Script HtmlServices template variables and global variables

Question

I have a Google Apps Script that adds a side bar to an Sheets file. Before the side bar loads it creates an array of objects. The side bar has next/previous buttons. Then the goal is to show data from each row, one at a time, in the side bar.

function myFunction() {
  var ui = SpreadsheetApp.getUi();
  var testData = [{"name" : "bob", "age" : 32}, {"name" : "sue", "age" : 31}, {"name" : "dingo", "age" : 7}];

  var sideBar = HtmlService.createTemplateFromFile("sideBar");
  ui.showSidebar(sideBar.evaluate().setTitle("Selector"));
}

In the above example, testData is what I want to share with the side bar.

I am trying to figure out the best way to share the array of objects with the sideBar client script.

My first thought was to pass it as a template variable:

sideBar.data = testData;

And then assign it to a variable in sideBar.html:

var data = <?= data ?>;

But this does not work. It returns the testData as a string to data so data becomes a string of the testData.

So then I thought I would leave testData in the server side script and use google.script.run.withSuccessHandler(function).serverSideFunction(...) in the client side to get data, one row at a time, from testData on the server side.

The problem is, testData is a variable in myFunction which exists after the side bar is created. So when clientSideFunction(...) runs, the server side won't know what testData is anymore.

I could make testData a global variable in the server side script but I am not sure if that is the best approach.

Is there another, better way to do what I am after?

Update

I guess I cannot use a global variables. In myFunction, if I set a global variable, after myFunction finishes the variable data is lost. So when google.script.run.withSuccessHandler(function).serverSideFunction(...) is run, serverSideFunction runs in a new context so the global variable is gone.

Update 2

For my use-case, think of a sheet with rows and rows of tasks. The user would click a menu item that would check if any tasks need processing. If there are any rows that need processing it would show a side-bar that shows details for each task that needs processing. The side bar would show details for a task with an input box and a submit button. The user enters data in the input box then hits submit. After submission the script will go do things (complex things) and then show details for the next in-scope task.

Answer 1

You can absolutely template variables! The trick is that you are going to print a plain string, therefore just use JSON.stringify on the server-side and JSON.parse on the client side to pass anything other than a plain string value.

function myFunction() {
  var ui = SpreadsheetApp.getUi();
  var sideBar = HtmlService.createTemplateFromFile("sideBar");
  sideBar.testData = JSON.stringify([
    {"name" : "bob", "age" : 32},
    {"name" : "sue", "age" : 31},
    {"name" : "dingo", "age" : 7}
  ]);
  ui.showSidebar(sideBar.evaluate().setTitle("Selector"));
}

<script>
var test = JSON.parse(<?= testData ?>);
console.log(test); // => [{…}, {…}, {…}]
document.body.appendChild(document.createTextNode(test[0].name)); // => "bob"
</script>

Alternative: you can use force printing scriplets <?!= => to achieve the same without JSON:

function myFunction() {
  var ui = SpreadsheetApp.getUi();
  var sideBar = HtmlService.createTemplateFromFile("sideBar");
  sideBar.testData = 'var test = [{"name" : "bob", "age" : 32},{"name" : "sue", "age" : 31},{"name" : "dingo", "age" : 7}]';
  ui.showSidebar(sideBar.evaluate().setTitle("Selector"));
}

<script>
<?!= testData ?>
console.log(test); // => [{…}, {…}, {…}]
document.body.appendChild(document.createTextNode(test[0].name)); // => "bob"
</script>

Answer 2

Issue:

• Use of printing scriplets instead of standard scriplets to pass data. Printing scriplets print/output data to html page.

• Accessing locally scoped variables as if they're globally scoped.

Solution:

• Use standard scriplets: <? ?>
• Provide/Declare testData in clientSideFunction or use a global variable.

References:

• Scriplets

Answer 3

Why is it necessary to keep the testData variable in the sidebar function? It's best to decouple the view- and data-controlling logic. Create the 'showSidebar' function that ONLY shows the sidebar, then a separate data retrieval function.

function getData(rowNumber) {

//get row data

}

On the client-side, call the data retrieval function when the DOM has finished loading.

 var successCallback = function(data) {
      //manipulate DOM to display data
    }

 var failureCallback = function(err) {
     //handle errors
    }

 window.addEventListener('DOMContentLoaded', function() {
       google.script.run.withSuccessHandler(successCallback).withFailureHandler(failureCallback).getData(rowNumber);
    });

Answer 4

You can use the Properties Service [1] to save a counter variable while the script is running. You could also implement a menu button to show the sidebar for the first time when you click on it [2].

I coded a script that when you clicked on the button it will run the sidebar with the information of the first row (2nd row of the sheet). When you submit the sidebar Form you can use its information to process the tasks and then the function will call the sideBar.html again with the information of the next row, until it reaches an empty row and it'll close the sideBar.

Code.gs

function onOpen() {
  var scriptProperties = PropertiesService.getScriptProperties();
  scriptProperties.setProperty('i', 0);

  SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
  .createMenu('Test')
  .addItem('Run Sidebar', 'run')
  .addToUi();  
}

//Shows sidebar
function runSidebar(data) {
  var ui = SpreadsheetApp.getUi();
  var sideBar = HtmlService.createTemplateFromFile("sideBar");
  sideBar.data = data;

  ui.showSidebar(sideBar.evaluate().setTitle("Selector"));
}

//Process each task and show the sidebar
function runAgain(obj) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1"); 

  var scriptProperties = PropertiesService.getScriptProperties();
  var i =  Number(scriptProperties.getProperty('i'));
  scriptProperties.setProperty('i', i + 1);
  var data = sheet.getRange(3 + i, 1, 1, 3).getValues();

   //Obtain the data from obj variable which is a Form object (i.e: obj.valueX) and process the task
   //

  if (data[0][0] == "") {
    return "close";
  }
  runSidebar(data);
}

//This is to show the sidebar the first time
function run() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1"); 
  var data = sheet.getRange(2, 1, 1, 3).getValues();
  runSidebar(data)
}

sideBar.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
      <form id="myForm" >
    Value 1: <input type="text" name="valueX" value="<?= data[0][0] ?>" /><br><br>
    Value 2: <input type="text" name="valueY" value="<?= data[0][1] ?>" /><br><br>
    File name: <input type="text" name="fileName" /><br><br>
    Row position: <input type="number" name="position" /><br><br>
    <input type="button" value="Upload" onclick="runTask(this.parentNode);" />
  </form>
  <script>
   window.onload=func1;

  //To prevent the default behaviour of a form (action attibute)
  function func1() {
   document.getElementById('myForm').addEventListener('submit', function(event) {
            event.preventDefault();
          });  
  }

  //Receive Form object and runs runAgain() function
   function runTask(obj){
       google.script.run.withSuccessHandler(close).withFailureHandler(close).runAgain(obj);
   }   

   //Close script when it reaches an empty row
   function close(e) {
       google.script.host.close();
   }

  </script>
  </body>
</html>

[1] https://developers.google.com/apps-script/guides/properties

[2] https://developers.google.com/apps-script/guides/menus