After performing a task the `SpreadsheetApp.getActive().getRange().activate();` doesn't execute in the sheet when I switch to another sheet

Thaddeus 0
Viewed 32

I have a simple function that delete rows in two sheets, Price and Quotation, that looks like this:

function DelRows() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActive();

  spreadsheet.getSheetByName('Price').activate(); 
  sheet.getRange('16:25').activate();
  sheet.getActiveSheet().deleteRows(sheet.getActiveRange().getRow(), sheet.getActiveRange().getNumRows());

  spreadsheet.getSheetByName('Quotation').activate(); 
  sheet.getRange('16:25').activate();
  sheet.getActiveSheet().deleteRows(sheet.getActiveRange().getRow(), sheet.getActiveRange().getNumRows());
  sheet.getRange('D12').activate();   // <== this don't work unless the function stops here**

  spreadsheet.getSheetByName('Price').activate(); 
  sheet.getRange('C12').activate();   // <== but this works!**
};

I can't get this line sheet.getRange('D12').activate(); to work in the Quotation sheet.

The range that was set by sheet.getRange('16:25').activate(); remains highlighted in the Quotation sheet.

I have tried adding utilities.sleep() to pause before switching to the Price sheet and rearranging the sequence of the sheet.getRange().activate(); statements but it all doesn't work.

It only works if the function ends there (i.e. stop at the Quotation sheet) and do not switch to the Price sheet.

In summary, I just want to set the active cell to C12 in the Price sheet and D12 in the Quotation sheet after deleting the rows in both sheets.

I'd be very appreciative to get some expert advice on what to do?

Thank you.

google-sheets google-apps-script
4 Answers

To make the activated cell to "stick", add SpreadsheetApp.flush(); after every call to Range.activate().

doubleunary0

Try replacing

var sheet = SpreadsheetApp.getActive();

by

var sheet = SpreadsheetApp.getActiveSheet();

The above because SpreadsheetApp.getActiveSpreadsheet() and SpreadsheetApp.getActive() returns a active spreadsheet object, to get the active sheet, use SpreasheetApp.getActiveSheet().

userH4H60

Deleting rows has nothing to do with activating

function DelRows() {
  SpreadsheetApp.getActive().getSheetByName("Price").deleteRows(16,10);
}
user30hu0

Following Cooper's suggestion, my previously long script is now just 3 lines like this:

function DelRows() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  spreadsheet.getSheetByName('Price').deleteRows(16,10); 
  spreadsheet.getSheetByName('Quotation').deleteRows(16,10); 
}

It's so much neater and the active range (where the cell pointer is in the respective sheets) remains where it was originally before running the script.

Thaddeus0
Related