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());
}

Billow

Answer 1

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.

Billow

getRange is also a method of spreadsheet class but with a different signature. – Appertain

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? – Fokker

@Fokker 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. – Julianjuliana

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. – Julianjuliana

You can directly do this, var ss = SpreadsheetApp.getActiveSheet(); (no need to chain it with getActiveSpreadsheet) – Preshrunk

Even still, why is it erroneously calling a number a string? – Lenette