I'm trying to make a menu app. I want to use a drawing as a button to add new blank ranges for new recipes.
I know I can use triggers like onEdit(e) and can use e.source.getActiveSheet() to return the sheet the edit was made on without having to reference the SpreadsheetApp class or give permissions to "access sensitive info in your Google Account".
But I can't find any guides on using a drawing with an assigned script to define the sheet on which the drawing was placed.
I know that using SpreadsheetApp requires user permission because that class allows Scripts to navigate, create, and edit entirely new spreadsheets among other things.
Is there a way to define a sheet in Apps Script based on where the drawing that triggered the script is located? Does left-clicking on a drawing count as a selectionChange for example? If so, I could use the event source for that instead of going through SpreadsheetApp.
Here's the code I've currently got that is requiring me to give permission to sell my soul just to click the button.
function addLine() {
var sheet = SpreadsheetApp.getSheetByName("Shopping List");
sheet.insertRowBefore(2);
}
I would like to use a function more like this, that wouldn't require giving extra permissions:
function addLine(e) {
var sheet = e.source.getActivesheet();
// This inserts a row before the 2nd row position
sheet.insertRowBefore(2);
}
This doesn't work as it is because the argument isn't a predefined event object like with onEdit(e). But if there is a way to get it to pass similar information to the Script when it's activated by clicking the drawing the script is assigned to, that is what I'm after.
Here's a sample spreadsheet. Menu Spreadsheet
I figured out an acceptable alternative. My biggest problem was with it showing the Unverified App warning, and being asked to allow the script to access sensitive user information when I went to execute the script.
I found out that when it tried to use the SpreadsheetApp.getSheetByName() method for the first time, Apps Script tried to automatically set the Authorization Scopes to allow that function to execute.
However, it tried setting the most permissive scopes that function could use, rather than the least required.
I found out how to manually set the Authorization Scopes I needed to only access the least data needed here.
In the Apps Script project settings, I enabled the "Show "appsscript.json" manifest file in editor" checkbox, then selected that file in the editor.
It looked like this:
{
"timeZone": "America/Mexico_City",
"dependencies": {
},
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8"
}
I edited it to only use the current spreadsheet, like this:
{
"oauthScopes": [
"https://www.googleapis.com/auth/spreadsheets.currentonly"
],
"timeZone": "America/Mexico_City",
"dependencies": {
},
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8"
}
The "currentonly" scope isn't considered a sensitive one, so it doesn't trigger the Unverified App warning, and the permissions it requests are much more reasonable.
In my script, I ended up using this for my addLine() function:
//Adds a new line item in the shopping list at the top of the
//sheet.
function addLine(){
//Ensures that it only adds a line to the shopping list
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Shopping List");
// This inserts a row before the 2nd row position
sheet.insertRowBefore(2);
}
If anyone can figure out how to send a Spreadsheet object as an argument to a Script assigned to a drawing, I would still love to learn how to do that, but for now this is a good compromise.