0

I'm sure I'm missing something very simple, but here is my problem. I have a spreadsheet with an apps script that has global variables that require authorization (due to openBy). I want to add a simple custom function to this spreadsheet, but it runs into an error because of the authorization required for the global variables. The custom function itself does not use the global variables, or do anything else that requires authorization.

4
  • Welcome to Web Applications SE. IIRC, this has been asked and answered before. Custom functions have several limitations. In short, don't use global variables on custom functions for Google Sheets. Nov 1, 2023 at 22:35
  • 1
    If you would like to reopen the question, you should add more details. Add a demo script include some representative global variables and ensure that it reproduces the problem, but basically custom function run without without access to the user authorization, it can't run call SpreadsheetApp.openById and other methods that require authorization. On the other hand, a custom function has 30-second execution limit. All of this is mentioned in the answers to the linked questions. Nov 1, 2023 at 22:40
  • My custom function doesn't use globals, and runs in under 5 seconds. However, the spreadsheet has other apps scripts attached that do require globals, so if I put the custom function in the same .gs file, the editor processes the globals and halts the operation (since it's been called by a cell). Nov 2, 2023 at 20:30
  • It doesn't matter that your custom function doesn't call globals inside the curly brackets of the function definition. The javascript engine executes globals before executing any function, including custom functions. I'm sorry for not being clear in my first comment. I should have said not to use globals on projects that will hold custom functions. Programming experts are better equipped to handle questions about globals and JavaScript engines, making Stack Overflow a more appropriate resource for such topics. Nov 2, 2023 at 20:53

0