How to pass named range to custom function as cell and not as range?-CodePudding

In Google Sheets "Apps Script" i defined a custom function that receives a format, and some values, then replaces the values into the format's placeholders and return it.

function PRINT_DETAILS(format, productName, quantity) {
    /* format expects a string with placeholders like: "{productName} {quantity}"
       product: expects a string
       quantity: expects an integer
    */
}

In the Products "table" there are the following columns: name, quantity, price, etc. Each of those have their own named range.

I defined a "print" column where i call the function like this: = PRINT_DETAILS("{productName} {quantity}"; Products.Name; Products.Quantity)

The problem is that my function is receiving the whole named range, and not just the cell value of the same row. i.e. im receiving all the products names and all the products quantities...

I successfully used named ranges as cell values in lot of ways: adding them, in "IF" conditions, concat operator "&", etc. Even in standard functions calls.

Thats why i believe there would be a way to do the same in custom functions.

I wouldnt like to sacrifice readability using cell references like A1 or something.

So im looking for a way to constrain arguments, or get my desired values in a non too-hacky way. Also if we could get a look into Google Sheets source code, maybe those standard functions are also implemented in JavaScript and they have this already solved.

CodePudding user response：

Description

Excel has a feature where a value of a named range can be passed to a custom formula corresponding to the row the formula is in. Unfortunately Google Sheets does not have the feature. Instead you need to get the row number toof the formula.

Note that this assumes the named ranges start in row 1 and the formulas are on that same row in any sheet.

Thanks to Mike Steelson I have modified the script slightly to improve the script. But not the screeen shot.

=PRINT_DETAILS("{productName} {quantity}",Products.Name,Products.Quantity)

Screen shots

Script

function PRINT_DETAILS(format,productName,quantity) {
  try {
    var row = SpreadsheetApp.getActiveRange().getRow();
    return [productName[row-1],quantity[row-1]];
  }
  catch(err) {
    return err.message;
  }
}

Reference

https://support.google.com/docs/thread/92023000?authuser=0&hl=en