0

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

};
3
  • Welcome to Web Applications. In '34:vr', what is vr? Also please add a brief description of your search efforts as is suggested in How to Ask. Nov 12, 2021 at 16:02
  • vr is the variable that defines the range "VestRows" (which is a single row). In the sheet I am working on, "VestRows" happens to be row 40. But on other sheets, this may change. I have searched for and tried many solutions. the above is the only code I was able to at least get semi working (ie: I can get it to work if I replace '34:vr' with '34:40' for example). The latest thing I have been searching for was for how to refer to a named range in Apps Script. That is how I got the idea to specify the named range (VestRows) as a variable. Hope this makes sense.
    – Matt Smith
    Nov 12, 2021 at 16:25
  • I have added an example of working code to my OP also.
    – Matt Smith
    Nov 12, 2021 at 16:35

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.