Get Sheet By Name

Question

I'm trying to get the following formula to work:

function setDataValid(range, sourceRange) {
  var rule = SpreadsheetApp.newDataValidation().requireValueInRange(sourceRange, true).build();
  range.setDataValidation(rule);
}

function onEdit() {
  var aCell = SpreadsheetApp.getActiveSheet().getActiveCell();
  var aColumn = aCell.getColumn();

  if (aColumn == 2 && SpreadsheetApp.getActiveSheet().getName() == 'Local' ) {
    var range = SpreadsheetApp.getActiveSheet().getRange(aCell.getRow(), aColumn + 1);
    var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
    setDataValid(range, sourceRange)
  }
}

When debugging onEdit() it shows that sourceRange in setDataValid(range, sourceRange) is null. As the range is in my sheet 'Local' I'm trying to change the getActiveSpreadsheet() to a get spreadsheet by name. Anyone who can help ?

score 151 · Accepted Answer · edited Sep 19 '19 at 01:20

151

Use get sheet by name on the spreadsheet:

var sheet = SpreadsheetApp.getActive().getSheetByName('Local')

edited Sep 19 '19 at 01:20

Soviut

88,194
49
192
260

answered Feb 19 '18 at 14:10

Hink

2,271
1
11
8

score 11 · Answer 2 · answered Mar 13 '19 at 09:42

11

Its very simple just get the sheet by name, the syntax is as follows as per the documentation.

    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Expenses");

answered Mar 13 '19 at 09:42

Naved Ahmad

783
8
7

1

`getActive` and `getActiveSpreadsheet` are synonymous. Prefer the former as the latter is too similar to `getActiveSheet`, which does not return a `Spreadsheet` object. – tehhowch Mar 14 '19 at 11:09
I dont believe this is true any longer. `getActiveSpreadsheet().getSheetByName(xxx)` returns an error currently. `getActive().getSheetByName()` works. – crthompson Jul 23 '21 at 15:58
Where exactly is getSheetByName documented? It's used in several places in the [Sheet Class doc page](https://developers.google.com/apps-script/reference/spreadsheet/sheet) but never explicitly documented. – Dan Dascalescu May 15 '23 at 13:54

score 1 · Answer 3 · edited Sep 19 '19 at 01:20

1

Please check the statement

var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());

This should be the one returning null. For the cause look at the official documentation here.

var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName('Local!TaxRates')

should get the range.

edited Sep 19 '19 at 01:20

Soviut

88,194
49
192
260

answered Feb 19 '18 at 15:30

Suhail Ansari

92
5

Thanks. But I need the getRangeByName value to depend on the value in the cell I have just edited – Michiel van Dijk Feb 20 '18 at 07:47
Use the parameter of the getRangeByName to include both your sheet name and the cell value, which has the named range name. `var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName('Local!'+aCell.getValue())` – Suhail Ansari Feb 20 '18 at 08:05
Still comes back like this Argument cannot be null: range (line 2, file "Code") – Michiel van Dijk Feb 20 '18 at 08:13

Get Sheet By Name

3 Answers3

Linked

Related