How to test type of range parameter in Google Spreadsheet script?

Question

I'm just starting tinkering with scripts for Google Spreadsheet and I have a problem :

How to test if the type of a function's parameter is a range of cells ?

I'd like to do something like this :

if(typeof intput != "range") {
  throw "input must be a range";
}

From Google's examples here (middle of the page) :

if (typeof inNum != "number") {  // check to make sure input is a number
  throw "input must be a number";  // throw an exception with the error message
} 

So this seems to be the right way to test the type of a variable. But I don't know how to test if the type is a range of cells.

It'd be even better if I could specify if the range is one or two dimensions.

Valona

Answer 1

A range of cells is just a Array (multidimensional array) Javascript has a problem in that way. Array's are seen as an object. So first check if you have the "object" type and then you could test like this.

if(typeof intput=="object"&&intput.length!=undefined) {
  //input is a array
}else{
  //Not a array
}

By testing a default property you can determine for certain that you have a array

Musca

Thank you it works. I got it to check for one-dimensional range like this : if(input.length != undefined && input[0].length == undefined) and it seems to work. – Valona

Answer 2

A range may represent a single cell (e.g. 'A1') or group of cells (e.g. 'A1:A2').

A range is converted into a range value when it is passed as a custom function parameter (e.g. =processRangeVal(A1:A2)).

If the range is a single cell then the range value is simply the data in that cell.

If the range is a group of cells then the range value is a 2-dimensional array. The first dimension is the rows and the second dimension the columns in each row.

To test for the range value representing a cell vs a group of cells:

function processRangeVal(rangeVal) {
  if (Array.isArray(rangeVal[0])) {
    // do 2d-array handling
  } else {
    // do cell data handling
  }
} 

rangeVal[0] resolves to undefined if the range is a single cell and the cell data does not support indexing. In this case Array.isArray(undefined) resolves to false which is what we want.

Walton

Answer 3

I know its really old post but I have a solution.

Firtly you need to know those:

• getCell() is a range object method
• isSheetHidden() is a sheet method
• getSpreadsheetTheme() is a spreadsheet method

now we will take advantage of it:

first write extention method for all spesific object type:

Object.prototype.isRange = function () { return this.getCell !== undefined }
Object.prototype.isSheet = function () { return this.isSheetHidden != undefined }
Object.prototype.isSpreadsheet = function () { return this.getSpreadsheetTheme != undefined }

now you can test:

function myFunction() {
  let ss = SpreadsheetApp.getActiveSpreadsheet()
  let sheet = ss.getSheetByName("FOOD_DB")
  let range = sheet.getActiveRange()

  Logger.log("range.isRange(): " + range.isRange())             //TRUE
  Logger.log("range.isSheet(): " + range.isSheet())             //false
  Logger.log("range.isSpreadsheet(): " + range.isSpreadsheet()) //false

  Logger.log("sheet.isRange(): " + sheet.isRange())             //false
  Logger.log("sheet.isSheet(): " + sheet.isSheet())             //TRUE
  Logger.log("sheet.isSpreadsheet(): " + sheet.isSpreadsheet()) //false

  Logger.log("ss.isRange(): " + ss.isRange())                   //false
  Logger.log("ss.isSheet(): " + ss.isSheet())                   //false
  Logger.log("ss.isSpreadsheet(): " + ss.isSpreadsheet())       //TRUE

}

Uralaltaic