CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
vball
vball

Reputation: 389

What is the difference between a script and custom function in GAS?

This may be a stupid question, but I haven't been able to find the difference clearly outlined in the GAS documentation, and I was wondering what is qualified as a script vs a custom function. I have a Google Sheet that requires a function to run on an edit that checks whether the values entered already exist on another page (many line items are entered at once with a copy/paste and currently the script goes line by line to determine if a unique identifier from each line already exists on one of two other sheets, deleting any duplicates as it goes). I call this function from onEdit(), and on average it seems to take 30-40 seconds to run.

This is where I run into my problem, as the time limit for a custom function to run is 30 seconds, whereas scripts can run for 5 minutes. I should plan for my data set to grow, and therefore need this process to run as a script so that it doesn't cut off halfway through. I thought that onEdit() and anything you call from it qualified as a script and not a custom function, but clearly this is not the case. How do you make a script vs a function?

EDIT: Not sure why, but creating a manual trigger that calls onEdit on an edit seems to run it as a script, whereas the implicit onEdit call that is built in is called as a custom function. This fixed my immediate problem, but if anyone has an explanation of the difference I would still greatly appreciate it!

Upvotes: 6

Views: 1595

Answers (1)

James D
James D

Reputation: 3152

The main difference is that a custom function is called by entering =functionName() into a cell on the spreadsheet. Also, a custom function must return a value to be displayed in the cell. See the Google Documentation.

The example below from the same documentation I linked above is an example of a custom function. The custom function is called by entering (for example) =DOUBLE(5) in a cell on the spreadsheet. The function will run taking the input parameter (5) and multiplying it by 2, it will then return the answer (10) and display this value in the spreadsheet cell.

function DOUBLE(input) {
  return input * 2;
}

However, your onEdit() function is called by means of a trigger be it a simple trigger, installed trigger, custom menu button or the run button in the script editor and it doesn't require that a value be returned to the main function.

If you try to type =onEdit() into a cell on your spreadsheet the function won't run and you will receive an error in the cell.

Upvotes: 3

Related Questions