Can you build a CSS file for Google Apps Script for Google Sheets from data from the sheet itself?

0
votes

I am building a sign-in form, which passes the sign-in data to a Google Spreadsheet. The form uses CSS and Jquery to build the UI and interactivity of the elements. The CSS file uses CSS variables, but those are still hard-coded into the style.html file itself. I'd like to pull the style information from a Settings sheet in the Google Spreadsheet itself.

To do this, I'm trying a file called customcss.html which, instead of a style file, is a script file that writes a <style> tag for jquery to append to the head element once the page is loaded. The customcss.html file looks like:

<script>
var sL = "<style>";
sL += ":root { --body-bg-color: " + google.script.run.withSuccessHandler(returnVal).getNamedRangeVal("body.bg.color") + "; ";
sL += "--num-text-color: " + google.script.run.withSuccessHandler(returnVal).getNamedRangeVal("num.text.color") + "; ";
sL += "--num-bg-color: " + google.script.run.withSuccessHandler(returnVal).getNamedRangeVal("num.bg.color") + "; ";
.
.
.
sL += "} </style>";
$("head").append(sL);
</script>

In order to pull data from the Sheet, the function getNamedRangeVal is called, which finds the defined name range passed to the function and reads the appropriate value in the cell:

function getNamedRangeVal(nR) {
  var ss = SpreadsheetApp.openByUrl(sheetURL);
  var ws = ss.getRangeByName(nR).getValue();
  return ws;
}

Since what is returned by getNamedRangeVal is the data I need, I need the success handler returnVal to be able to return a value (since a function called via google.script.run. returns void); returnVal is pretty basic - it simply turns around and passes back whatever it's passed:

function returnVal(v) {
  return v;
}

The problem is when the script runs, it does append the appropriate <style> tag, but all variable values are undefined, which from my initial investigation appears to be because of the async nature of the google.script.run call.

So what can I do to get the data from the sheet to the callback function to build the CSS script?

1
javascriptcssasynchronousgoogle-apps-scriptgoogle-sheets
You need to rewrite your script so you get all data needed from Sheets in one call of google.script.run, then on callback insert your styles with returned values. – Kos

1 Answers

0
votes

Issue and solution:

Calling google.script.run in the client-side (i.e., web browser) code starts the specified server-side Apps Script function. The client-side function continues execution immediately without waiting for the server-side function to complete. Often, the client-side function will simply end at this point.

When the server-side function eventually completes, the function specified by withSuccessHandler or withFailureHandler is started automatically, depending on whether the server-side function completed successfully or threw an error.

Therefore, as Kos said, you should move the CSS building to the function called by withSuccessHandler.

Alternatively, use templates:

Another way to dynamically build the CSS would be to use Templated HTML, which allows executing server-side code directly, without using google.script.run, but via scriptlets. That is a valid option if you want to bring a small amount of static data to the client-side when the page loads (see Load data asynchronously, not in templates).

See a simplified example in which the color for the div elements is brought from the server with the printing-scriplets (<?= ?>):

index.html:

<!DOCTYPE html>
<html>
  <head>
    <style>
      div {
        color: <?= myColor("color") ?>;
        background-color: <?= myColor("background-color") ?>;
      }
    </style>
  </head>
  <body>
    <div>THIS SHOULD BE COLORED</div>
  </body>
</html>

Code.gs:

function doGet() {
  const output = HtmlService.createTemplateFromFile("index").evaluate();
  return output;
}

function myColor(prop) {
  switch (prop) {
    case "color":
      return "blue";
    case "background-color":
      return "red";
  }
}