In Google Sheets, can you trigger an add-on to be run via a button?

Question

I recently coded a Google Apps Script attached to a Google Sheet. The sheet is a form, and the script sends the form as a pdf attachment to my email, and then clears the data entered by the user so that it's ready to be used again. The script is run when a button on the sheet is clicked (the script is assigned to this button).

I want to share this sheet with my team members so that they can use it freely to submit the form whenever they need. However; I also don't want them to be able to freely edit the sheet and potentially break the functionality. I used the Protected Ranges ability that Google Sheets has to allow the user to only edit the cells required to complete the form. I then realized while testing that the user is still able to click into the Apps Script dropdown under extensions and directly edit the script attached to the spreadsheet. Unfortunately I have read that this is not really avoidable if the user has edit access to the sheet.

I was searching for workarounds and read that I could publish my script as an add-on, and then link the published add-on to the sheet. This way it would not be editable by the sheet user.

My question is if I publish my script as an add-on, will I still be able to link the add-on to a button on the sheet and have the add on triggered when the button is clicked? I find the buttons nice from a UI perspective so I am hoping this is possible, but the only option I can see to link a button when clicking into its options is "Assign Script". Is this possible?

score 0 · Answer 1 · answered Dec 22 '21 at 08:59

Issue:

Share sheets with script without giving editors edit access to script.

Solution:

You may use a webapp. But the web app must use it in a separate standalone script. Using it in a separate script means, you can't access the active row or the user interface of the sheet.

Flow:

Script on sheet:

function runOnButton(){
  const [id, access] = [ScriptApp.getIdentityToken(), ScriptApp.getAccessToken()]
  return UrlFetchApp.fetch(/*WEB APP PUBLISHED URL*/, {method:'post',payload:JSON.Stringify({id,access})})
}

Script standalone:

Run as "Me"
Access: "Anyone, even anonymous"

 function doPost(e){
    //TODO: Access params of e
    //TODO: Validate the id token
        @see https://developers.google.com/identity/protocols/oauth2/openid-connect#validatinganidtoken
    //TODO: [optional]Use editor access token to access any restricted resources 
    const ss = Spreadsheet.openById(/*Spreadsheet id*/);
    //Do stuff with ss
 }

In Google Sheets, can you trigger an add-on to be run via a button?

1 Answers1

Issue:

Solution:

Flow:

Note that anyone who knows the webapp url may post to your webapp. You should properly validate requests to avoid leaking sensitive information.

Related answers: