Using spreadsheet named functions in custom functions

Question

Alright, so I'm trying to repeat a function I have made in Google Sheets, called POINTS to calculate a point integer value from a cell parameter -- but sadly, there is no loop function for Sheets so far as I know.

I am now trying to code a function in Apps Script with a loop to run the POINTS function on a range of cells and sum them together; however, I cannot reference the POINTS function or any other named functions available in the actual spreadsheet like SUM or COUNTIF. Does anyone know how to import these functions over or allow the Apps Script to access them?

Here is the code I currently have for the apps script (with the POINTS function where I'd want it if it were possible):

function summing(row, startCol, endCol, function a) {
  
  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  var sum = 0;
  
  for (i = startCol; i <= endCol; i++) {
    sum += POINTS(s.getRange(row, i));
  }
  return sum;
}

You can not call Google Sheets builtin functions from App Script. Also you should look at Best Practices. Repeated calls to getRange in a loop is slow and inefficient. — TheWizEd
– TheWizEd, Commented May 11, 2024 at 19:56

Wicket · Accepted Answer · 2024-05-12 15:22:45Z

0

In the same way Google Sheets functions can't be used on Google Apps Script, Google Sheets named functions can't be used either.

It's worth noting that functions declared on Google Apps Script, in the Google Sheets context, are not "custom functions". While it's not mandatory, a custom function should contain JSDocs with the @customformula tag. Reference https://developers.google.com/apps-script/guides/sheets/functions.

Instead of creating a Google Sheets custom function, could you make a named function that does what you need? Not too long ago, Google added a large set of Google Sheets functions. For details, see

Another option is to write a JavaScript code to do what the POINTS named function does.

edited May 12, 2024 at 15:22

answered May 12, 2024 at 14:42

Wicket

39.4k9 gold badges81 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using spreadsheet named functions in custom functions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related