CODEWITHSUNDEEP
javascriptarraysgoogle-sheets
Mark Stone
Mark Stone

Reputation: 1

Google Sheets row as array

I have a Google Sheets spreadsheet in which I'd like to pas a range of that row as an array to a custom function (for example, d37:m37). This works fine if you pass a range of a column (for example, d4:d37), but apparently rows behave differently than columns in this regard.

The only workaround I have found is to pass a two dimensional range (for example, d36:m37) and then within the custom function ignore all but the last row of this 2 dimensional "array".

Is there no way to get the same behavior from rows as from columns, and simply pass a range of a row in and have it treated as an array?

Upvotes: 0

Views: 2542

Answers (1)

user6655984
user6655984

Reputation:

When you call a custom function with a range as a parameter, the function gets a two-dimensional array. For example,

  • range A1:B2 is represented as [['a1', 'b1'], ['a2', 'b2']] (where the strings are placeholders for actual content of those cells)
  • range A1:B1 (a row) is represented as [['a1', 'b1']]
  • range A1:A2 (a column) is represented as [['a1'], ['a2']]
  • as an exception, a single-cell range, such as A1, passes the value in that cell directly, not an array.

As you can see, the array structure is different for row-ranges and column-ranges. A custom function can be written to handle all of the above cases appropriately. But if your custom function is such that it expects a column range, there is an easy workaround: use the transpose to turn a row into a column, e.g.,

=customfunction(transpose(D37:M37))

Upvotes: 2

Related Questions