Menu
I tried to implement a simple "Multi selector sidebar" extension based on THIS SHEET which I found in this Google support thread
When I copy the sheet it works fine but when I try to put the exact same code in my real sheet, it doesn't work anymore. It throws an error when I try to access the GA function from within the template.
I have created a simplified test project which also fails to work for me.
To reproduce the error:
In the "code.gs" enter
function doGet() {
var html = HtmlService.createHtmlOutputFromFile('Index')
.setTitle('Multiple selector')
.setWidth(300);
SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
.showSidebar(html);
}
function onOpen(e) {
SpreadsheetApp.getUi().createMenu('Index')
.addItem('Show Sidebar', 'doGet')
.addToUi();
doGet();
}
function getOptions() {
var validation = {
sheet: 'CATEGORIES',
range: 'A2:A'
}
Logger.log("running getOptions");
Logger.log(SpreadsheetApp.getActive().getSheetByName(validation.sheet).getRange(validation.range).getDisplayValues());
return SpreadsheetApp.getActive().getSheetByName(validation.sheet).getRange(validation.range).getDisplayValues()
.filter(String)
.reduce(function(a, b) {
return a.concat(b)
})
}
And create a second file (HTML file) called Index.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script>
function onFailure(error) {
var div = document.getElementById('output');
div.innerHTML = "ERROR: " + error.message;
}
google.script.run.withFailureHandler(onFailure)
.getOptions();
</script>
</head>
<body>
<div id="output"></div>
</body>
</html>
Now in the sheet there should be a sidebar which opens with an error for me PERMISSION_DENIED
Even when I select a project at Resources -> Cloud platform project it won't work.
Oddly enough if I use the original linked (working) spreadsheet and change something in the code, it won't work anymore for me.
Things I know by now: - It doesn't work with my gmail or google apps account - For other people using the same document it works - Still doesn't work if I disable Adblocker - Doesn't work if I access the sheet from incognito mode - It does work if I use Firefox instead of Chrome
What am I missing?
392
I have the same issue with the "permission denied" error message, and I found this
https://github.com/burnnat/page-sizer/issues/3
I think the issue is that I'm logged into multiple google accounts when I am working on this. I logged out of all google accounts, then only logged into the one account that I was trying to use formMule and it worked.
so I tried the exactly same code with incognito mode in the chrome, with only one account logged in, and it works !
I hope this can help you with your issue.
121
I had the same issue. Problem is indeed caused by two or more Google accounts that are logged in into one session.
The problem is that the frontend is loaded and executed by logged in user X and the backend (Code.gs-file) is executed by logged in user Y.
So a workaround for this is to check if the user executing the backend code is the same user that is looking at the frondend code. (In this case: the frontend is your sidebar)
I found this workaround working for one of my add-ons:
Add this function to your Code.gs file. It will check if the "front end user" (initiator), that is viewing the html sidebar" is the same as the "backend user" (userEmailAddress). If it is not the same user, it will throw an error. If it is the same user, no error is thrown.
function checkMultipleAccountIssue(initiator) {
var userEmailAddress = Session.getEffectiveUser().getEmail();
if (initiator) {
// check if effective user matches the initiator (the account who
triggered the display of the UI)
// Due to a Google bug, if user is connected with multiple accounts
inside the same browser session
// google.script.run can be executed by another account than the
initiator
if (initiator != userEmailAddress) {
console.error({
message: "Client side calls initiated from wrong account",
initiator:initiator, // user in frontend (sidebar)
effectiveUser: userEmailAddress // user in backend (code.gs)
});
var errorMessage = "You are logged in with multiple accounts.<br>";
errorMessage+= "This causes errors. Log out with account " +
userEmailAddress;
errorMessage+= " if you want to continue with the account: " +
initiator;
throw new Error(errorMessage);
}
else{
return 'No conflicts found. Good to go.'
}
}
}
In the frontend (html sidebar) add this bit of javascript that runs when the sidebar is loaded:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script>
var initiator = '<?!= Session.getEffectiveUser().getEmail() ?>'
console.log('FRONTEND USER: ' + initiator)
google.script.run.
withFailureHandler(function(error){
alert(error);
// or prompt the user to logout!
//(HINT: let them click this link: https://google.com/accounts/Logout and login with the correct user.)
})
.withSuccessHandler(function(ret){
console.log(ret)
})
.checkMultipleAccountIssue(initiator)
function onFailure(error) {
var div = document.getElementById('output');
div.innerHTML = "ERROR: " + error.message;
}
google.script.run.withFailureHandler(onFailure)
.getOptions();
</script>
</head>
<body>
<div id="output"></div>
</body>
</html>
The part
var initiator = '<?!= Session.getEffectiveUser().getEmail() ?>'
is an apps script scriptlet that gets "injected" when the HTML is being prepared before it goes to the end user. If you want to use such scriptlets, you need to load your HTML-sidebar as a Template. To load your HTML-sidebar as templated HTML, change your doGet() function as follows:
function doGet() {
var html = HtmlService.createTemplateFromFile('Index').evaluate()
.setTitle('Multiple selector')
.setWidth(300);
SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
.showSidebar(html);
}
More about scriptlets and templated HTML can be found here: https://developers.google.com/apps-script/guides/html/templates#code.gs
This is where I found the workaround: https://sites.google.com/site/scriptsexamples/home/announcements/multiple-accounts-issue-with-google-apps-script
You can star this issue here https://issuetracker.google.com/issues/69270374?pli=1 in order to get it resolved "faster". :)
34
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
