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?
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.
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";
}
}