Calling a script from a formula-CodePudding

I am hoping you can help me with this question, so I created an alert message in google script, that works just fine, but I can't figure out how to "call the script" from a formula, so what I need is to use it as the TRUE value of an IF, my script is called AlertMessage, so I'm hoping to do something like this - IF(B3="Test", AlertMessage(),"") - Any assistance is appreciated, thank you!

CodePudding user response：

Google Sheets formulas could call scripts, more specifically, there is a special type of Google Apps Script function than can be called this way, they are named custom functions. They are great to take advantage of JavaScript capabilities but please bear in mind that custom functions have several limitations, i.e. they should be included in a bounded script, their execution time limit is 30 seconds, they can't execute methods that require authorization, they can't open ui elements like alerts and prompts.

You might use JSDoc to show a on the function emerging help a description of the function as well of the parameters.

/**
 * Returns an alert message
 *
 * @customfunction
 */
function AlertMessage(){
   return 'Alert!'
}

CodePudding user response：

I believe if you try it you will get the Error: Cannot call SpreadsheetApp.getUi() from this context.

CodePudding user response：

According to the documentation all you need to do is to open the spreadsheet that you want to use, then open Apps Script from the settings of the Spreadsheet to "link" that script with the spreadsheet and use the function as if it was any other regular Google Sheets function.

If you created a separate script I think it will not work because the script needs to be opened from the spreadsheet.

CodePudding user response：

The custom function will return the value inside the cell and can be the neighbor. But you can't return a message as Browser.msgbox or toast.