I am working on a side bar for a google sheet. I have a html page. The data that i am sending it is in two parts. I have accounts and each account has properties.
{
"name": "accountSummaries/223471136",
"account": "accounts/223471136",
"displayName": "G4MeasurmentTesting",
"propertySummaries": [
{
"property": "properties/307799514",
"displayName": "Ga4CsharpTesting",
"propertyType": "PROPERTY_TYPE_ORDINARY",
"parent": "accounts/223471136"
}
]
},
What i want to do is display two drop downs in my menu the first being accounts, the second being the properties under the account.
My issue is how do i reload the second select when the user selects the first one?
I have given the first select a name <select id="account" name="account"> then try to load the data for the second one using that accounts[account.selected].propertySummaries.length;
Its not working as i am seeing ReferenceError: account is not defined which implies to me that its not seeing the name of the select.
There must be a way to detect which item was selected in the first select right?
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<? var accounts = listAccounts(); ?>
<? const item = accounts[1];?>
<label>Account: </label>
<select id="account" name="account">
<option></option>
<? for (var i = 0; i < accounts.length; i ) { ?>
<option value="<?= i ?>"><?= accounts[i].displayName ?></option>
<? } ?>
</select>
<!-- Not working -->
<label>property: </label>
<select id="view" name="view">
<option></option>
<? for (var i = 0; i < accounts[account.selected].propertySummaries.length; i ) { ?>
<option><?= accounts[account.selected].propertySummaries[i].displayName ?></option>
<? } ?>
</select>
<!-- Not working -->
<?= accounts ?> <input type="button" value="Close" onclick="google.script.host.close()" />
</body>
</html>
The above code is giving an error ReferenceError: account is not defined
code.gs
function onOpen() {
var ui = SpreadsheetApp.getUi();
// Or DocumentApp or FormApp.
ui.createMenu('GA Menu')
.addItem('Alert Test', 'ShowMessage')
.addSeparator()
.addSubMenu(ui.createMenu('Sub-menu')
.addItem('Second item', 'ShowPrompt'))
.addItem('Show sidebar', 'showSidebar')
.addToUi();
}
// https://developers.google.com/apps-script/guides/dialogs?hl=en#code.gs_1
function showSidebar() {
var html = HtmlService
.createTemplateFromFile('Page')
.evaluate();
SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
.showSidebar(html);
}
It does work if I force it by specifying say 0 so the issue is not with it being able to read my data. So its not an issue with the data form at the issue is with linking the two selects.
<label>property: </label>
<select id="view" name="view">
<? for (var i = 0; i < accounts[0].propertySummaries.length; i ) { ?>
<option><?= accounts[0].propertySummaries[i].displayName ?></option>
<? } ?>
</select>
update attempt 2
Another idea was to try to grab it using document
<? const account = document.getElementById('account'); ?>
Which didnt work either it just gave me
ReferenceError: document is not definedDetails
CodePudding user response：
I see @Tanaike has beaten me to an answer but as promised I'm providing a mock up of how to do it through Templated HTML as well as through using google.script.run.
First the server side code in Code.gs. I use listAccounts() both in the template and in the client side <script>.
Then I show how I would modify the html code to include the event handler.
Code.gs
function showTest() {
var html = HtmlService.createTemplateFromFile("HTML_Test");
html.accounts = listAccounts();
SpreadsheetApp.getUi().showModalDialog(html.evaluate(),"Test");
}
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename)
.getContent();
}
// Mock up data
function listAccounts() {
let accounts = [ {account: 11111, displayName: "name11111", propertySummaries: [ {displayName: "prop11111"}, {displayName: "prop11112"} ]},
{account: 22222, displayName: "name22222", propertySummaries: [ {displayName: "prop22222"}, {displayName: "prop22223"} ]},
{account: 33333, displayName: "name33333", propertySummaries: [ {displayName: "prop33333"}, {displayName: "prop33334"} ]}];
return accounts;
}
HTML_Test.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<label>Account: </label>
<select id="account" name="account">
<? for (var i = 0; i < accounts.length; i ) { ?>
<option value="<?= i ?>"><?= accounts[i].displayName ?></option>
<? } ?>
</select>
<label>property: </label>
<select id="view" name="view">
</select>
<input type="button" value="Close" onclick="google.script.host.close()" />
<script>
var accounts = null;
function accountOnChange() {
let i = document.getElementById("account").selectedIndex;
let account = accounts[i];
let select = document.getElementById("view");
let options = select.options;
for( i=options.length; i>0; i-- ) {
options.remove(i-1);
}
account.propertySummaries.forEach( property => {
let option = document.createElement("option");
option.text = property.displayName;
select.options.add(option);
}
);
}
(function() {
try {
document.getElementById("account").addEventListener("change",accountOnChange);
google.script.run.withSuccessHandler(
function(value) {
accounts = value;
let select = document.getElementById("view");
accounts[0].propertySummaries.forEach( property => {
let option = document.createElement("option");
option.text = property.displayName;
select.options.add(option);
}
);
}
).listAccounts();
}
catch(err) {
alert(err);
}
})();
</script>
</body>
</html>
CodePudding user response：
Modification points:
- In order to set the values to the 2nd dropdown list by the 1st dropdown list, Javascript is used.
- In your HTML, the 2nd dropdown list is created using Javascript with the value of the 1st dropdown list.
- When the HTML template of Google Apps Script is used, when the calculation is run with
evaluate()method, the process cost becomes high. Ref - In the case of HTML template, when the HTML is loaded, the HTML created by
evaluate()is put as static. So,accountin your HTML cannot be used. I think that this is the reason for your current issue.
When these points are reflected in your script, how about the following modification? I think that in this case, there are several approaches. So, please think of this as one of them.
Modified script:
HTML side: Page.html
<label>Account: </label>
<select id="account" name="account" onchange="sample(this)"><?!= firstOptions ?></select>
<label>property: </label>
<select id="view" name="view"><?!= secondOptions ?></select>
<input type="button" value="Close" onclick="google.script.host.close()" />
<script>
const accounts = JSON.parse(<?= accounts ?>);
function sample(e) {
const select = document.getElementById("view");
select.length = 0;
const { propertySummaries } = accounts[e.value];
propertySummaries.forEach(({ displayName }, i) => {
const option = document.createElement("option");
option.text = displayName;
option.value = i;
select.appendChild(option);
});
}
</script>
- By loading the values of
accounts, after the HTML was loaded, this value can be used.
Google Apps Script side: Code.gs
function showSidebar() {
const accounts = listAccounts();
const html = HtmlService.createTemplateFromFile('Page');
html.accounts = JSON.stringify(accounts);
html.firstOptions = accounts.map(({ displayName }, i) => `<option value="${i}">${displayName}</option>`).join("");
html.secondOptions = accounts[0].propertySummaries.map(({ displayName }, i) => `<option value="${i}">${displayName}</option>`).join("");
SpreadsheetApp.getUi().showSidebar(html.evaluate());
}
In this case, it supposes that
listAccounts()returns the values like below.[{ "name": "accountSummaries/223471136", "account": "accounts/223471136", "displayName": "G4MeasurmentTesting", "propertySummaries": [ { "property": "properties/307799514", "displayName": "Ga4CsharpTesting", "propertyType": "PROPERTY_TYPE_ORDINARY", "parent": "accounts/223471136" } ] }, , , , ]When
showSidebar()is run, you can see the 1st dropdown list. When you change the 1st dropdown list, the 2nd dropdown list is changed.UPDATED: I noticed that when the sidebar is opened for the 1st time, the initial value of the 2nd dropdown list is not set, while the initial value of the 1st dropdown list is the 1st element. For this, I updated the script. If you are not required to set the initial value to the 2nd dropdown list, please use the previous version.
Note:
- This is a simple sample script for explaining the result. So, please modify this for your actual situation.