Ads script (*.gs) is missing apps script getRange(int, int, int, int) method?

Question

When I run an ads-script to call the app-script::spreadsheet API, I get the following error:

var sheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
var rangeValues = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();

==> Cannot find method getRange(number,number,number,number)

How can it be? Only a subset of the app-script sheet api is available from ads-script?

Answer 1

In your code, the variable sheet is an object/instance of the spreadsheet class. However, getRange is a method of the sheet object and can't be applied to the sheet variable.

You need to define a sheet object first. This can be done in many ways, one of them is to define it by using the name of the sheet. Here is the spreadsheet object:

var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);

Then you can define the sheet object:

var sheet = spreadsheet.getSheetByName("Sheet1");

And now you can get the values of a range :

var rangeValues = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();

Adjust Sheet1 to the sheet of your choice. Make sure that a sheet with that name exists.