Sheet.getRange throwing signature mismatch exception when not using A1 notation

Question

I'm writing a script on a Google Sheet that will be interacting with Google Map's Geocoding service.

The following code works correctly.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var header_v = ss.getRange("1:1").getValues();

However, later on in my function, I call Sheet.getRange() again using the Sheet.getRange(number, number, number, number) method:

var target_range = ss.getRange(1, ss.getLastColumn()+1, ss.getLastRow(), 10);

throws the following exception:

Exception: The parameters (String,number,number,number) don't match the method signature for Spreadsheet.getRange.

I've tried substituting the first parameter with a variable I know to be a number, to the same effect.

This is the function in its current state.

function geocode() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var header_v = ss.getRange("1:1").getValues();
  
  var col_indxs = {
    name : header_v[0].indexOf("restaurant_name"),
    address : header_v[0].indexOf("address")
  }

  var target_range = ss.getRange(1, ss.getLastColumn()+1, ss.getLastRow(), 10);
  Logger.log(target_range.getA1Notation());
}

score 45 · Accepted Answer · edited Mar 10 '21 at 06:30

45

Resolved this myself.

var ss = SpreadsheetApp.getActiveSpreadsheet();

Returns a Spreadsheet, however the getRange(number, number, number, number) is a member of the Sheet class.

var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

Solved my problem.

edited Mar 10 '21 at 06:30

Enkode

4,515
4
35
50

answered Aug 23 '19 at 21:54

Benjamin Didur

731
1
5
9

2

getRange is also a method of spreadsheet class but with a different signature. – TheMaster Aug 23 '19 at 22:43
6

thanks! this was such a gotcha! :O I wonder why on earth they would they keep the getRange() methods in both classes and not consistently keep them in sync with each other? – Magne Apr 28 '20 at 15:43
@Magne Calling `getRange()` from the File level compared to the Sheet level is a very different operation. You have to specify the Sheet when calling `getRange()` from the file level. It would be absurd if the application itself randomly chose a sheet to operate on. Hence the two functions cannot be kept in sync with eachother because they operate with different context. – Juuso Nykänen Apr 13 '21 at 10:23
2

An improvement I would like to see on Google's part is a pointer in the generic error message to help coders identify the problem: it is not a syntax problem, it is a problem with context. – Juuso Nykänen Apr 13 '21 at 10:25
You can directly do this, `var ss = SpreadsheetApp.getActiveSheet();` (no need to chain it with `getActiveSpreadsheet`) – Mujeeb Sep 02 '22 at 01:32

Sheet.getRange throwing signature mismatch exception when not using A1 notation

1 Answers1

Linked