Search code examples
google-apps-scriptgoogle-sheetsgoogle-sheets-formulatypeofcustom-function

What sort of objects do I get when I give range as an argument to my custom function in Google Sheets?

I'm writing function to use it like a formula in Google Sheets and give the range as argument, like '=myFormula (A1: A5)'. I can't figure out what type of object do I get, where I can read about its methods. For example, in A1 cell there is a date and in A2 there is a text. How can I check value types in these cells?

Solution

  • The argument you receive is a two dimensional array, if the argument is range of cells- the outer array ordered by rows and the inner array ordered by columns. If the argument is a single cell, you receive a single object.

    Argument Type: Object[][] or Object

    The JavaScript type of object inside the array is the corresponds to the type of object in the spreadsheet, i.e., Spreadsheet date objects are changed to corresponding JavaScript date objects, numbers to number etc. Spreadsheet type of cell A1 can be inferred from =TYPE(A1)

    //Run snippet to see A 3x2 argument structure example
var cols = ['A','B'];
var rows = [1,2,3]
console.log(rows.map(row=>cols.map(col=>col+row)))

    References: