Sheet order not respecting index in google sheets in chrome

Question

This spreadsheet has a script that updates the tabs and labels them in response to the week ending date entered. It also rearranges them based on the selected last day of the week (Saturday or Sunday for example). The spreadsheet formulas calculate the order of the days and the day and day of the month labelling. These are read into the script from named ranges.

It works fine on my IOS devices and sometimes works properly in chrome desktop. But (in chrome desktop) it gets into a mode where one sheet is always out of position. In the example, I label the tabs with the sequence position (0 to 6) and the returned sheet index, so it's clear that the problem is alignment between the object model and the rendering. As you can see in the following pic, the Monday sheet is out of position, if I change the end of week day again, it will be the one out of position again.

How can I force google sheets to respect the sheet index? I tried flush but no joy. The only way I can get it to line up reliably is to close and re-open the sheet.

function onEdit(e) {
  var source = e.range;
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  if(!qualifiedSource(e.range, ["selectedDate", "lastDayOfWeek"].map(function(n) {
    return ss.getRangeByName(n);
  }))) return;

  var fmtedNames = WeekEndingDate2();

  // add indexing to the sheet names
    ss
    .getSheets()
    .filter(function(sht) { return fmtedNames.indexOf(sht.getName()) != -1 })
    .forEach(function (s, i) { s.setName(s.getName() + ":" + i + ":" + s.getIndex()); });  
}

function qualifiedSource (source /*range*/, target /*range | range[]*/) {
  if(!isArray(target)) target = [target];
  return target.some(function(t) { 
    return source.getSheet().getName() == t.getSheet().getName() && source.getA1Notation() == t.getA1Notation(); 
  });
}

function WeekEndingDate2() {
  var _wb = SpreadsheetApp.getActiveSpreadsheet();
  var _days = _wb.getRangeByName("daysOfWeek");
  var _daysFmt = _wb.getRangeByName("daysOfWeekFmt");

  return (function _update() {
    var daySheets = SheetsCollection();
    var days = _days.getValues()[0];
    var daysFmt = _daysFmt.getValues()[0];

    daySheets
    .Add(days.map(namesToSheets))
    .Sort(daysFmt);

    return daysFmt;

    function namesToSheets(d, i) {
      var allSheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
      var targetSheet;
      allSheets.some(function(sht) { 
        return targetSheet = sht.getName().indexOf(d) === 0 ? sht : null;
      });

      Logger.log(Utilities.formatString("%s\t%s", d, targetSheet.getName()));

      if (targetSheet == null)
        throw new Error("Error: Missing sheet for " + d);

      return targetSheet.setName(daysFmt[i]);
    }
  })();
}

function SheetsCollection () {
  var _sheets = {}; // hash of WrappedSheets
  var _maxIndex = 0;

  function _hash (n) {
    return n.replace(" ", "_");
  }

  function _addItem (s /*worksheet*/) {
    _sheets[_hash(s.getName())] = WrappedSheet(s);
    _maxIndex = Math.max(_maxIndex, s.getIndex())
  }

  function _add (s /*worksheet | worksheet[]*/) {
    if(Array.isArray(s))
      s.forEach(_addItem);
    else
      _addItem(s);
    return this;
  }

  function _sort ( sortOrder /*range | string[]*/, delay /* int milliseconds */) {
    var sortedNames = sortOrder.getValues ? sortOrder.getValues()[0] : sortOrder;
    var namesLength = sortedNames.length;
    var i, sht;

    for each (var name in sortedNames) {
      Logger.log(name);
      _sheets[_hash(name)].MoveTo(_maxIndex);
      if(delay) Utilities.sleep(delay);
    }
    return this;
  }

  return {
    Add: _add,
    Sort: _sort
  }

}

function WrappedSheet(sheet /*string || sheet*/) {
  var _wb = SpreadsheetApp.getActive();
  var _sheets = _wb.getSheets();
  var _shtName = typeof sheet == "string" ? sheet : sheet.getName();

  function _moveTo (to /*integer*/) {
    var insertAt = to;
    var actSht = _wb.getActiveSheet();
    var maxAttempts = 10;
    var attempt = 1;

    var before = this.Sheet.getIndex();
    Logger.log(listSheets(""));

    _wb.setActiveSheet(this.Sheet);
    _wb.moveActiveSheet(insertAt);
    _wb.setActiveSheet(actSht);

    Logger.log("%s -> %s after %s", this.Name, this.Sheet.getIndex(), attempt -1);

    Logger.log(listSheets(""));

  }

  return {
    MoveTo: _moveTo,
    get Sheet() { return _wb.getSheetByName(_shtName); },
    get Position() { return _wb.getSheetByName(_shtName).getIndex(); },
    Name: _shtName
  }
}

James D James D · Accepted Answer · 2017-02-06T15:21:21

I faced the exact same problem as you with a very similar functioning script. Unfortunately, there is no 'fix' for this issue, it seems to be a caching issue with the browser. The only way around it I found was to refresh the window twice or close and reopen the spreadsheet and then the tabs appear in the correct order.

Sheet order not respecting index in google sheets in chrome

1 Answers