1

I have the following script:

function setActiveCells() {

  var wb = SpreadsheetApp.getActiveSpreadsheet();

  var ws_names = wb.getRangeByName('an_sheets').getValues();
  var ws_cells = wb.getRangeByName('an_sheetsLastCells').getValues();
  var ws_count = ws_cells.length;

  for (var i=0;i<ws_count;i++) {
    var ws_cell_ref = ws_cells[i][0];
    if (ws_cell_ref.length > 0) {
      var ws_name = ws_names[i][0];
      var ws = wb.getSheetByName(ws_name);
      ws.setActiveRange(ws.getRange(ws_cell_ref));
      SpreadsheetApp.flush();
    }
  }

  var ws = wb.getSheetByName('Summary');
  ws.setActiveRange(ws.getRange(ws_cells[0][0]));

}
/* */

I am attempting to set the cursor to the indicated cell in each sheet. It does set the cursor in the correct cell on the first sheet, but the selected cells remain unchanged on the remaining sheets.

On further research I've found that adding SpreadsheetApp.flush(); after the ws.setActiveRange(...) (see code above) solves part of the issue. It does actually set the cursor in the correct cell, but it does not scroll to that cell.

I am trying to both select the cell and ensure that it is in the current viewport. Any assistance is appreciated.

  • I'm using the onOpen trigger to run the function. After some additional searching, I found that adding SpreadsheetApp.flush() after the setActiveRange causes Sheets to evaluate the instruction immediately and (almost) has the desired effect. Now I've found that, while the correct cell is selected in each sheet, it doesn't always scroll to that location - I'm still working on that issue. Any assistance would be appreciated. – Roger Grace Jan 20 at 2:30
0

The Apps Script Documentation says: Sets the specified range as the active range in the active sheet, with the top left cell in the range as the current cell.

Reference

  • thanks for the reference - I had found that documentation, but I appreciate the confirmation. – Roger Grace Jan 20 at 2:32
0

Use setActiveSelection(range) or setActiveSelection(a1Notation) methods of Class Sheet.

Reference

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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