Menu
You can use Google Apps Script to write a custom function, then use it in Google Sheets just like a built-in function.
At this time, Google Sheets doesn't have a feature to assign a name to a variable defined by a formula instead of a cell or range reference. To use a formula with these kinds of variables the alternative is to use custom functions.
Apps Script provides the Spreadsheet service which allows scripts to interact with your Google Sheet files and the data they contain. You can use this service to automate the following common spreadsheet tasks: Create or modify a spreadsheet. Read and update cell data, formulas, and formatting.
I'm creating a menu item in Google Sheets that will list a work directory and pull the email address for that user, to be later used to process employee records within Google Sheets. I'm trying to get it to work like this:
function onOpen() {
var ui = SpreadsheetApp.getUi();
// Or DocumentApp or FormApp.
var myapp = ui.createMenu('MyApp');
var pullemp = myapp.addSubMenu(ui.createMenu('Pull Employee')
.addItem('Nathaniel MacIver', menuItem2('[email protected]')));
myap.addToUi();
}
function menuItem2(email) {
SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
.alert('That email address is '+email);
}
Now, when I open the spreadsheet, The item triggers immediately, and I get the result, below, which is what I want:
But I want it to trigger when I click the button in my menu. As it is, when I click on my menu button I get the error:
I know the link mentions that the function name needs to be a string value, but why would it work onload as I need it to and then fail when I press a button?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With