I've only been working in GAS for a little under 6 months. Everything thus far has been bound spreadsheet projects, but have created quite a few. I have a new need to create an add-on sidebar that people can use in any spreadsheet they want (in a domain). I created the code, published it so only the domain can see it, and can install it as another user in the same domain, all of that seems to work fine.
The issue has to do with the sidebar.html, and calling a function in my script using google.script.run to call a function that gets some external data and writes it to the current sheet. All the code to get the external data works just fine, it all goes into a 2 dimensional array. The part where it breaks is at SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(1,1,x,y).setValues(array). I get a "You do not have permissions to access the requested document".
I did some of the following during testing:
1) The script for the add-on started out as a bound script to an existing sheet for development. Thinking that might be the issue, I re-created the project as a stand alone GAS and published it as a web add-on for sheets. That didn't make a difference.
2) The developer can run the add-on just fine, no sidebar error.
3) I created another menu item for the add-on that, rather than opening a sidebar and letting the sidebar call the function via google.script.run, runs the function directly from the menu. Doing it that way, it 'works' for the other domain user (where it fails running it from the sidebar).
4) I 'shared' the underlying Google Sheet I was using to test the add-on with the domain test user with the developer, and the sidebar script will then work. If I use the add-on in another non-shared sheet, I get the permission error.
5) If I use it on a sheet in a big, shared Drive folder we all use and have access to (domain wide), it works fine, just as I would expect from the test in '3' above.
The Big Questions:
So it seems as though 'google.script.run' runs as the developer, and not as the current user (at the keyboard)? Is that right? Looking in documents like this, I couldn't find anything that would indicate that. Is there something I should be adding to make sure this works? Is it a side effect of the way I'm publishing it, for domain use only?
Update: Without identifying one in particular, I tested out someone's 'Find and Replace' add-on, and sure enough, it's able to write out from the sidebar directly to the sheet without any issues. The only thing I did was authorize the app when I installed it. So it's clearly possible. I'm just trying to figure out what I might be missing from my add-on that will allow me to do the same.
Here is a small, sample I put together just to test the 'write' of an array of 2 columns and 2 rows out to A1:B2 in the currently active sheet. It works from both the menu and the sidebar as the developer. It always works from the menu as a domain user, but will 'only' work from the sidebar if the developer has direct permissions to the file (either via a one-off share or via being created in a previously shared folder the developer has the appropriate permissions to).
// scopes
https://www.googleapis.com/auth/script.container.ui
https://www.googleapis.com/auth/spreadsheets
// code.gs
function onOpen(e) {
SpreadsheetApp.getUi().createAddonMenu()
.addItem('Sidebar Test', 'showSidebar')
.addItem('setValues Test', 'setValuesTest')
.addToUi();
}
function showSidebar() {
var htmlTemplate = HtmlService.createTemplateFromFile('sidebar');
var ui = htmlTemplate.evaluate()
.setTitle('Sidebar Test')
SpreadsheetApp.getUi().showSidebar(ui);
}
function onInstall(e) {
onOpen(e);
}
function setValuesTest() {
try {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var valArr = [];
valArr.push([1,2]);
valArr.push([3,4]);
sheet.getRange(1, 1, 2, 2).setValues(valArr);
} catch (e) {
throw new Error(errorMessage({e: e}));
}
}
function errorMessage(params) {
return params.e.message + '<br/><div class="main">file: '+
params.e.fileName+'<br/>line: '+ params.e.lineNumber + '</div>';
}
function getVersion() {
var scriptProps = PropertiesService.getScriptProperties();
return scriptProps.getProperty('Version');
}
// utility.gs
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename)
.getContent();
}
// sidebar.js.html
<script>
$(function() {
$('#run-query').click(runQueryButton);
});
function runQueryButton() {
runQuery(this);
}
function runQuery(element) {
element = (element === 'undefined') ? this : element;
element.disabled = true;
$('#error').remove();
google.script.run
.withSuccessHandler(
function(msg, element) {
element.disabled = false;
})
.withFailureHandler(
function(msg, element) {
showError(msg, $('#main'));
element.disabled = false;
})
.withUserObject(element)
.setValuesTest();
google.script.host.editor.focus();
}
function showError(msg, element) {
var div = $('<div id="error" class="error">' + msg + '</div>');
$(element).after(div);
}
</script>
// sidebar.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
<?!= include('sidebar.css'); ?>
</head>
<body>
<div class="sidebar branding-below">
<div id="main" >
<button class="blue cursor-pointer" id="run-query" data-toggle="modal">Execute</button>
</div>
</div>
<div class="sidebar bottom">
Version: <?!= getVersion(); ?>
</div>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<?!= include('sidebar.js'); ?>
</body>
</html>
// sidebar.css.html
<style>
.error {
padding: .25em .25em;
font-family: Arial,sans-serif;
}
</style>
Update 2 I changed the deployment type to 'Only Trusted Testers', and added my non-Domain self as that tester. I sent my(other)self the link to the app and installed it from there. Answered the authorization pop-up (though, since I'm not in the domain, I had to go the 'unsafe' route, which is fine). Ran the test add-on...and it worked like a champ! Now, both myself (non-domain) and other, developer self (domain) have paid $5. Is that the issue? Is the issue that the test user I created 'in' the domain hasn't paid any money, so it won't work?
That doesn't make much sense to me, as the test add-on installs, and the test user (in the domain) can call the underlying script from a 'menu', and it populates the spreadsheet just fine. It only breaks down for the domain user when they try to run the app from the side bar, which calls the code with a google.script.run call. This is really, really weird, and frustrating.
On another side note, I added a function to show the 'effective user' on the side bar, just in case there was something hinky happening on that front, and that seems all well and good, reporting the name of the test user in the domain and not the name of the developer in the domain.
I guess my next testing will be to:
Flip it back to domain, reinstall for the domain test user, see if something else didn't happen to fix it.
If still broken, flip it back to trusted testers, add the domain test user to that group, pay another $5, and see if they can install and run it in that fashion. If they can, then there is something either flawed with domain publishing of add-ons, or I'm missing some documentation (or something undocumented).
