google app script create custom menu that can be accessed by all sheets

Question

Simple problem that most likely has a simple solution.

Created a custom menu with 1 function. Now I want to be able to have this custom menu appear in all G sheets. I create a new spreadsheet everyday in Excel and move it to G Drive so that it can be viewed/shared with the whole team. When this new spreadsheet is added to the drive, I want the custom menu to show up so that the one function can be ran to clean up this daily rotating document.

How do I make this custom menu appear in all sheets: new, old, uploaded? I will note this App Script is tide to a

function onOpen(){
   var ui = SpreadsheetApp.getUi();
   ui.createMenu('Deluxe List Edit')
   .addItem('Prep deluxe list','deluxeFinish')
   .addToUi();
}
function deluxeFinish(){
   // code that edits the first sheet//

This App Script is tied to a specific sheet call 'Test'

By all sheets - do you mean all spreadsheets? If so - you need to deploy it as an Add-on. — ziganotschka
I'd publish a Workspace addon, as opposed to a "editor" addon. There is a quickstart at: https://developers.google.com/workspace/add-ons/cats-quickstart You'll need to make your own modifications, subtractions and additions to the sample code. So, you won't have a custom menu, you'll have your app icon in the sidebar on the right. But it will automatically show up in all Sheets files without doing anything in the Sheet that you open. Getting a custom menu to show up in every Sheet would be a lot of work actually. — Alan Wells
I would like to confirm my understanding for your question. In your situation, when the XLSX data is converted to Google Spreadsheet, you want to include the script shown in your question in the converted Spreadsheet. Is my understanding correct? — Tanaike
@ziganotschka that is the answer. I'm new to using app script and had no idea about deploying Add-ons. this will allow me to share this script with others and allow version control. Spot on and thank you! — Bruce Barry
@AlanWells just like Zifanotschka you hit the nail on the head! Thank you for this solution! — Bruce Barry

Alan Wells Alan Wells · Accepted Answer · 2021-06-13T16:53:29

I'd publish a Workspace addon, as opposed to a "editor" addon. There is a quickstart at:
https://developers.google.com/workspace/add-ons/cats-quickstart

You'll need to make your own modifications, subtractions and additions to the sample code. So, you won't have a custom menu, you'll have your app icon in the sidebar on the right. But it will automatically show up in all Sheets files without doing anything in the Sheet that you open. Getting a custom menu to show up in every Sheet would be a lot of work actually.

google app script create custom menu that can be accessed by all sheets

1 Answers