CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Autar
Autar

Reputation: 1589

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

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.

Upvotes: 9

Views: 12173

Answers (3)

Joman68
Joman68

Reputation: 2850

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
  }
}

The following table shows how the Array.isArray() check works:

rangeVal data type Array.isArray(rangeVal[0]) Notes
2d array true rangeVal[0] is a one-dimensional array (as is rangeVal[1], rangeVal[2], etc...)
string false rangeVal[0] is a character
number/date/boolean/etc. false rangeVal[0] is undefined

Upvotes: 4

Şahan Şenvar
Şahan Şenvar

Reputation: 48

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

}

Upvotes: 0

Tim van Zonneveld
Tim van Zonneveld

Reputation: 518

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

Upvotes: 7

Related Questions