Passing URL Arguments and HTML Output Order of Execution

Question

This is directly related to the answer in Google Apps Script - possible charts types.

I am trying to extend the top answer by deploying it as a webapp instead of an add-on, and also to pass URL arguments to the app script.

Everything is exactly the same as the linked example above, except that I stripped out the addon code and put in the most basic webapp code by adding a doGet(e) function.

/*
//if I manually specify the values in the script, it works fine
var sheetRange = "A1:D20"; // standard range to gather data
var sheetTabName = "Sheet1"; //name of the tab in the spreadsheet to look for. must be unique
var spreadsheetId = '1CKQTQYXgt3YgnUXu0YHFeMcG5sMh99sj293oKRFVp4M'; //spreadsheet ID
*/

var sheetRange;
var sheetTabName;
var spreadsheetId;

function doGet(e) {
  //but if I try to load the arguments from the URL, it doesn't work
  //these values never get set here
  sheetRange = e.parameter.sheetRange;
  sheetTabName = e.parameter.sheetTabName;
  spreadsheetId = e.parameter.spreadsheetId;
  Logger.log("This never gets run %s %s %s",sheetRange,sheetTabName,spreadsheetId ); 

  //but this template gets made
  var template = HtmlService.createTemplateFromFile('BubbleEx')
      .evaluate()
      .setSandboxMode(HtmlService.SandboxMode.IFRAME)
      .setWidth(800)
      .setHeight(600);
  Logger.log("Why doesn't this get printed at least?");
  //and returned
  return template;
}

function getSpreadsheetData() {
  Logger.log("This does get run!\nSpreadsheetId is: %s\nSheetRange is: %s\nSheetTabName is: %s",spreadsheetId,sheetRange,sheetTabName);
  var sheet = SpreadsheetApp.openById(spreadsheetId);
  var data = sheet.getSheetByName(sheetTabName).getRange(sheetRange).getValues();
  return (data.length > 1) ? data : null;
}

Clearly I'm missing something fundamental about the order of execution here. Something about the way the HTML is interacting with the script is causing it to be completed before certain parts of the code.gs complete. I'm really new to using GAS as a deployed webapp, so any/all help is greatly appreciated. Thanks!

Here's the preformatted link (with the included arguments) I'm trying to use. The sheet is publicly viewable with a link:

https://script.google.com/a/macros/edmonton.ca/s/AKfycbxMbCG3p-zdoJReIS6jRHnLK3J-XsI1Zm_BFvfz_UQ/dev?spreadsheetId=1CKQTQYXgt3YgnUXu0YHFeMcG5sMh99sj293oKRFVp4M&sheetTabName=Sheet1&sheetRange=A1%3AD20

Answer 1

Visualization in a Web App

I expanded on the linked question in a blog post a while back, with a dashboard example as a web app. (The code for the dashboard is in the blog, I won't bother repeating it here.)

Logger usage

Comments you've left in your code imply that the conclusions you're making about what-has-run-when is based on whether or not logs have shown up. If only it was that easy!

Unfortunately, the Logger is an unreliable tool when used for debugging a web app or other asynchronous operations. Surprise! It's the subject of another blog post of mine.

The Logger can be extended by using the BetterLog library and a few simple utility functions, so that you can generate logs from the client side as well as from asynchronous server-side calls.

Why aren't those globals working?

Order of execution isn't the issue - rather it's about how global variables behave between execution instances.

When you've set spreadsheetId in your doGet() function, its content is available to the whole script, but only for the duration of that instance's execution. In the following diagram, I've illustrated the communication between a few of the pieces of your solution. Each asynchronous call to a Google Apps Script function creates a new, independent execution instance of your script. Each instance has its own copy of the script's global variables.

The upshot of this is that the spreadsheetId value you set in doGet() isn't available to getSpreadsheetData() when it is invoked by the google.script.run call in the client-side JavaScript. The variable exists as a symbol only - it isn't always the same piece of computer memory. (It might not even be on the same physical computer.)

If you want to "set" some "global" variables to survive between instances, you can use a persistent storage method such as the Properties Service. In your example, though, you would want to be careful with this; if two users were accessing the Web App at the same time, the last one in would over-write values previously set by the earlier user.

A more appropriate way to handle this would be to explicitly pass the "globals" via the html template. (If you create a new Google Apps Script using the demo "Web App" template, you'll see an example of this.)