I would like the Google Sheets sidebar to open with a color set in cell Sheet1:A1. My current code works (I suspect there may be a more efficient way to do this), but the CSS steps through each theme in root until it lands on the correct theme.
For example, if A1 is set to 'Orange', calling the sidebar will load with the body first as 'Default' and then switch to 'Orange'. Is there a way to load the correct root theme on the initial page load instead of stepping through the themes in root?
Google Apps Script
function onOpen(e) {
SpreadsheetApp.getUi()
.createMenu("Sidebar")
.addItem("Show sidebar", "showSidebar")
.addToUi();
}
function showSidebar() {
var htmlWidget = HtmlService.createTemplateFromFile('Test').evaluate()
.setTitle("Theme Test");
SpreadsheetApp.getUi().showSidebar(htmlWidget);
}
function getColorTheme() {
colorTheme = SpreadsheetApp.getActive().getRange("Sheet1!A1").getDisplayValue();
return colorTheme;
}
HTML for Sidebar
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<style>
:root,
:root.Default {
--bg-color: #45818e;
}
:root.Orange {
--bg-color: #e69138;
}
body {
background-color: var(--bg-color);
}
</style>
<script>
function setTheme(colorTheme) {
document.documentElement.className = colorTheme;
}
</script>
</head>
<body>
<p>Hello world</p>
<script>
google.script.run.withSuccessHandler(setTheme).getColorTheme();
</script>
</body>
</html>
CodePudding user response：
From your situation, how about the following patterns?
Pattern 1:
In this pattern, HTML is modified using Google Apps Script and the modified HTML is used with HtmlService.createHtmlOutput().
Google Apps Script side:
function showSidebar() {
var colorTheme = SpreadsheetApp.getActive().getRange("Sheet1!A1").getDisplayValue();
var html = HtmlService.createHtmlOutputFromFile('Test').getContent().replace("{{colorTheme}}", colorTheme);
var htmlWidget = HtmlService.createHtmlOutput(html).setTitle("Theme Test");
SpreadsheetApp.getUi().showSidebar(htmlWidget);
}
HTML side:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<style>
:root,
:root.Default {
--bg-color: #45818e;
}
:root.Orange {
--bg-color: #e69138;
}
body {
background-color: var(--bg-color);
}
</style>
<script>
document.documentElement.className = "{{colorTheme}}";
</script>
</head>
<body>
<p>Hello world</p>
</body>
</html>
Pattern 2:
In this pattern, HTML is modified using HTMl template and the modified HTML is used with HtmlService.createHtmlOutput().
Google Apps Script side:
function ashowSidebar() {
var colorTheme = SpreadsheetApp.getActive().getRange("Sheet1!A1").getDisplayValue();
var htmlWidget = HtmlService.createTemplateFromFile('Test')
htmlWidget.colorTheme = colorTheme;
SpreadsheetApp.getUi().showSidebar(htmlWidget.evaluate().setTitle("Theme Test"));
}
HTML side:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<style>
:root,
:root.Default {
--bg-color: #45818e;
}
:root.Orange {
--bg-color: #e69138;
}
body {
background-color: var(--bg-color);
}
</style>
<script>
document.documentElement.className = "<?= colorTheme ?>";
</script>
</head>
<body>
<p>Hello world</p>
</body>
</html>
Note:
From the recent benchmark of the HTML template, it seems that in the current stage, the process cost of
evaluate()is a bit high. Ref So, I proposed the above 2 patterns with and without an HTML template.In this case,
<html >and<html >might be able to be used instead of Javascript. But, I'm not sure about your actual situation. So, in this answer, Javascript is used as a sample modification.