0

My currently created menu is made as follows:

function onOpen(e) {
  SpreadsheetApp.getUi()
  .createMenu('Script Menu')
  .addItem('ClimaParaTestes', 'ClimaParaTestes')
  .addItem('JogosParaClimaParaTestes', 'JogosParaClimaParaTestes')
  .addToUi();
}

All functions in the file are:

function ClimaParaTestes()
function JogosParaClimaParaTestes()
function EnviarClimasParaSquads()
function ApagarHistóricoDoClima()
function DeleTeste()

I would like to know if there is any way for the menu to automatically update itself and add new functions without needing to create an addItem for each function.

Some way to read the file and find all existing functions and add to the menu.

I sometimes forget to add new functions and other people who have access to the spreadsheet don't know how to use Google App Script to run there.

Note: Menu items can have the same name as the function.

Digital Farmer
  • 1,705
  • 5
  • 17
  • 67
  • 1
    I think you could conceivably do that with Apps Script API. I'm guessing you may want to come up with a way of naming them for organizing the menu. But I'm sure it's possible. I actually have buttons in my sidebar that allow me to select which function I want each button to execute. – Cooper Jun 20 '21 at 03:05

1 Answers1

2

I believe your goal as follows.

  • When new function is added in the GAS project, you want to automatically add the created function to the custom menu.

In your situation, how about the following method? I referred this method from this answer, this answer and this post.

In this method, the custom menu is dynamically created by retrieving the current functions in the container-bound script. The sample script is as follows.

Sample script:

Please copy and paste the following script to the script editor of Google Spreadsheet and save the GAS project. And, please reopen the Google Spreadsheet. By this, the custom menu is created.

// This function dynamically creates the custom menu retrieving the functions in this GAS project.
function installFunctions() {
  const excludedFunctions = ["onOpen", "installFunctions"]; // Please set the execluded function names.

  const menu = SpreadsheetApp.getUi().createMenu('Sample custom menu');
  for (let i in this) {
    if (typeof this[i] == "function" && !excludedFunctions.includes(i)) {
      menu.addItem(i, i);
    }
  }
  menu.addToUi();
}

// This function is run when the Spreadsheet is opened.
function onOpen() {
  installFunctions();
}

// Following functions are from your question. The sample script is `Browser.msgBox("function name")`.
function ClimaParaTestes(){return Browser.msgBox("ClimaParaTestes()")}
function JogosParaClimaParaTestes(){return Browser.msgBox("JogosParaClimaParaTestes()")}
function EnviarClimasParaSquads(){return Browser.msgBox("EnviarClimasParaSquads()")}
function ApagarHistóricoDoClima(){return Browser.msgBox("ApagarHistóricoDoClima()")}
function DeleTeste(){return Browser.msgBox("DeleTeste()")}
  • In this sample script, when you create new function and reopen the Google Spreadsheet, the custom menu is created by automatically including the new function.
  • When you want to set the functions you don't want to include the custom menu, please set const excludedFunctions = ["onOpen", "installFunctions"];.

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165