1

I have a Google spreadsheet with multiple sheets. I would like the cursor to be set to a particular cell onOpen for all tabs. Each cell of the first row represents a week of the year, start to finish, for the whole year. I've written some code to have that particular column (week of the year) preselected on row 5 when opening.

Currently, only the last sheet has the cell selected that I want. This is because setActiveCell can only be used for one cell at a time I guess. I have also tried setActiveRange and setActiveSelection to no avail.

I found a question from years ago Here, but the solution does not work for me. Perhaps something has changed since then.

Here is my code:

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  Date.prototype.getWeek = function() {
    var onejan = new Date(this.getFullYear(),0,1);
    return Math.ceil((((this - onejan) / 86400000) + onejan.getDay()+1)/7);
  }
  var now = new Date();
  var weekNum = now.getWeek(); 
  var sheets = ss.getSheets();
  for(var i = 0; i < sheets.length; i++) {
    var newRange = sheets[i].getRange(5,weekNum + 1);      
    sheets[i].setActiveCell(newRange);
  }
}

Within the link I provided above, I've tried variances of all of the given solutions, but my code was never exactly the same because I wasn't looking for the exact same things as the OP. For example, using Jacob Jan Tuistra's latest code solution, I came up with:

function setCursor() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  Date.prototype.getWeek = function() {
    var onejan = new Date(this.getFullYear(),0,1);
    return Math.ceil((((this - onejan) / 86400000) + onejan.getDay()+1)/7);
  }
  var now = new Date();
  var weekNum = now.getWeek(); 
  ss.getSheets().forEach(function (sheet, weekNum) {
  Logger.log(weekNum);
      sheet.setActiveRange(sheet.getRange(5,weekNum+1));
    });
}

Which also only set the last sheet

Kurt Leadley
  • 513
  • 3
  • 20
  • Oof, [extending a builtin](https://stackoverflow.com/questions/14034180/why-is-extending-native-objects-a-bad-practice) AND using [for..in over an array](https://stackoverflow.com/questions/500504/javascript-for-in-with-arrays). Regarding the link, you should include here which of the many solutions are written in that link. My guess is you first need to call `activate()` on each sheet, then `setActiveRange()` – tehhowch Mar 16 '18 at 19:46
  • I updated the question and fixed the loop. Haven't had a chance to look at the extending a builtin part. – Kurt Leadley Mar 16 '18 at 20:23
  • Those links are more something to just be aware of - in this case of a bound worksheet script and not some exported library, collisions are rather unlikely. – tehhowch Mar 16 '18 at 20:34
  • I actually have an unexpected outcome with the 2nd block of code. The first block, weekNum is 11 (week of March 12th), but in the second block, weekNum iterates from 0 to 11. Is this possibly caused from any of that? I added flush() to the first block and it works fine btw. – Kurt Leadley Mar 16 '18 at 20:38
  • Sounds like something for a new question perhaps. It's not immediately clear to me what you're stating, and changing this question to ask a new one, or seek debugging help on a part of your code unrelated to setting the active cell on all sheets on open, is not recommended. – tehhowch Mar 16 '18 at 20:42

1 Answers1

2

Looks like you need to make a call to SpreadsheetApp.flush() when you loop over the sheets collection:

function onOpen() {
  SpreadsheetApp.getActive().getSheets().forEach(function (s) {
    s.setActiveSelection("B4");
    SpreadsheetApp.flush(); // Force this update to happen
  });
}

My guess is Apps Script's execution engine optimizes the writes to a single call, and in that single call only one active selection can be made. Calling flush forces 1 call per sheet to be updated.

tehhowch
  • 9,645
  • 4
  • 24
  • 42
  • Has to be done, unfortunately. With no other code there, Apps Script is correctly saving those "writes" until the end. You could use something like what is described [here](https://stackoverflow.com/questions/26506382/how-to-capture-change-tab-event-in-google-spreadsheet) to avoid needing to do the active cell changing until the specific sheet is activated. – tehhowch Mar 16 '18 at 20:32