How to use / refer to a named range when defining another named range in a Google Apps Script?

Question

I have the following script in a Google Sheet Apps Script macro. It is meant to identify & delete a range of rows on a sheet. This was created by originally recording a macro & then attempting to edit the code.

The the first row in the range will always be the same (ie: row 34), but the last row in the range will vary. I have given a named range of "VestRows" to the last row.

I am using the code below to attempt to achieve the above, but it seems to fail on getting the range to activate (and then delete). If I use the named range (ie: VestRows) in replacement of the actual row number (ie: 40), it fails. But using the row number works as expected.

For example, in the code below if I replace

spreadsheet.getRange('34:vr').activate();

with

spreadsheet.getRange('34:40').activate();

it works. Any help would be much appreciated.

My Non-Working Macro:

function DelteRowsTest() {

var spreadsheet = SpreadsheetApp.getActive();

  var vr = SpreadsheetApp.getActive().getRangeByName("VestRows");

  spreadsheet.getRange('34:vr').activate();

 spreadsheet.setCurrentCell(spreadsheet.getRange('A40'));

  spreadsheet.getActiveSheet().deleteRows(spreadsheet.getActiveRange().getRow(), spreadsheet.getActiveRange().getNumRows());

};

The code below is working, but not what I am after:

function DelteRowsTest() {

var spreadsheet = SpreadsheetApp.getActive();


  spreadsheet.getRange('34:40').activate();

 spreadsheet.setCurrentCell(spreadsheet.getRange('A40'));

  spreadsheet.getActiveSheet().deleteRows(spreadsheet.getActiveRange().getRow(), spreadsheet.getActiveRange().getNumRows());

};

Answer 1

Replace

spreadsheet.getRange('34:vr').activate();

by

spreadsheet.getRange(34,1,vr.getRow(),vr.getSheet().getMaxColumns()).activate();

---

It will be worthy that you spend some time learning about JavaScript as it's the programming language used by Google Apps Script as well about the Google Apps Script Spreadsheet services classes and methods.

1. The expression SpreadsheetApp.getActive().getRangeByName("VestRows"); returns a Class Range object.
2. '34:vr' is a literal string.
3. The code might not be working at if your spreadsheet hasn't a VR column, or work not as is expected because, as mentioned above, '34:vr' is a literal string that when used as getRange parameter it means starting reference row 34 ending column VR .

The suggested fix use getRange(startRow,startColumn, rowNum, columnNum) form instead getRange(a1Notation) form.

Resources

• https://developers.google.com/apps-script/guides/sheets
• https://developers.google.com/apps-script/reference/spreadsheet