CODEWITHSUNDEEP
javascriptfunctiongoogle-apps-scriptgoogle-sheets
Jensen010
Jensen010

Reputation: 445

Google apps script - Custom menu to run custom funtion

I've posted a bunch of times on this project, and am incredibly grateful for the help I've received :)

Something is eluding me: I have a Google sheet that contains 5 separate sheets, a Master and 4 individual sheets. I've written a "send emails" function for it that works as intended, and fires when a certain box within each of the 4 individual sheets contains "Yes."

My issues revolves around tying this script to a trigger. I received a previous suggestion to create a custom menu that fires the script upon selection. I was able to add the menu, tie it to a simple onOpen() trigger, and get it to appear in the sheet, but it would not run my script:

function onOpen() {
var ui = SpreadsheetApp.getUi();
// Or DocumentApp or FormApp.
ui.createMenu('Send Emails')
  .addSubMenu(ui.createMenu('Send Emails For:')
              .addItem('Miranda Sheet', 'menuItem1')
              .addItem('Piper Sheet', 'menuItem2')
              .addItem('Lowes Sheet', 'menuItem3')
              .addItem('Golden Sheet', 'menuItem4'))
.addToUi();
}
//actions for menu items are below...

The intent of the script is to send emails, so I know now that I to tie the menu to an installable onOpen() trigger. That's the part I can't seem to nail. If I change the function name and tie an onOpen() through resources>all your triggers, to that function, my menu dissapears: 

function addMenu() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Send Emails')
  .addSubMenu(ui.createMenu('Send Emails For:')
    .addItem('Miranda Sheet', 'menuItem1')
    .addItem('Piper Sheet', 'menuItem2')
    .addItem('Lowes Sheet', 'menuItem3')
    .addItem('Golden Sheet', 'menuItem4'))
.addToUi();
}

function menuItem1() {
    var ui = SpreadsheetApp.getUi();
    //Prompt user for confirmation to send emails
    var responseMir = ui.alert('Are you sure you want to send emails?', ui.ButtonSet.YES_NO);
    // Process the user's response.
    if (responseMir === ui.Button.YES) {
      //Test alert  
      //ui.alert('You clicked the first menu item!');
    }
}

So, my question is two fold:

  1. Where's my menu?

  2. After the function is tied to a trigger, how can I go about tying menu items to my script? It seems like I should be able to say "do stuff here" under the If statement above. Am I wrong in that?

Thank you for your thoughts! (I'm getting better I promise:)

Upvotes: 1

Views: 3452

Answers (1)

Vytautas
Vytautas

Reputation: 2286

Just to be clear, there should be no installable trigger selected from the menu in regards to the onOpen() function as it's a trigger already.

Once the function is written and you wish to test out the options, you first need it to run. You can do this 2 ways:

  1. Reload the spreadsheet (note that this will close your script tab!)
  2. Run the onOpen() function

Note that once you have run the function to add the menu, you will only need to re-run it if you change the menu itself. If you made changes to, let's say, menuItem1() then you do not need to re-run the onOpen() as the menu selection will still run the latest saved code in the script.

Reading the comments I will second Zig Mandel, if you go by this route and use a menu to start functions, then there is no need for an onEdit trigger as they serve the exact same purpose — initiate data gathering.

Upvotes: 2

Related Questions