Method “moveActiveSheet” doesn't work as expec

2019-05-10 12:58发布

问题:

I am writing a script in Google Apps Script.

The goal: allow users to sort the sheets/tabs in a Google spreadsheet by the date in the name of each sheet.

function sorter() {
  var sp = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = sp.getSheets();
  var sheet_metadata = get_and_sort_sheet_metadata(sheets)
  for( var j = 0; j < sheet_metadata.length; j++ ) {
    sp.setActiveSheet(sp.getSheetByName(sheet_metadata[j].name));
    sp.moveActiveSheet(j + 1);    
  }
}

This is an example of sheet_metadata's content:

[{date=false, name=A, index=1}, {date=false, name=B, index=2}, {date=1.391058E12, name=ARCHIVE: xname0 name: 1/30/2014 ID:145952, index=8}, {date=1.42173E12, name=ARCHIVE: zname1 name: 1/20/2015 ID:1459527232, index=7}, {date=1.4543892E12, name=ARCHIVE: wname3 name: 2/2/2016 ID:145952723299, index=6}, {date=1.4544756E12, name=ARCHIVE: dname4 name: 2/3/2016 ID:145952723266, index=5}, {date=1.454562E12, name=ARCHIVE: qname5 name: 2/4/2016 ID:1459500, index=4}, {date=1.4546484E12, name=ARCHIVE: qname6 name: 2/5/2016 ID:1459500, index=3}]

The date value is the result of .getTime(). The name is the sheet's name. The index is the original index of the sheet. The order of objects in this array is the order the sheets should be in.

The objects in sheet_metadata are always in the correct order. However, the for loop (see above) fails to place the sheets in the correct order.

In other words, the order the sheets end up in does not correspond to the order of the sheet data in sheet_metadata.

Why doesn't this work?

回答1:

I have had the same problem a few days ago and I found a way to get it working with this code :

in this example profSheets is an array of sheet names sorted the way I want.

  var pos = ss.getNumSheets();
  for(var s=0 ; s < profSheets.length ; s++){
    var sh = ss.setActiveSheet(ss.getSheetByName(profSheets[s]));
    SpreadsheetApp.flush();
    Utilities.sleep(200);
    ss.moveActiveSheet(pos);
    SpreadsheetApp.flush();
    Utilities.sleep(200);
  }

I place the sheet that should be in first position to the last position, then the next one at the last position and so on up to the last name in the list. At the end, the first one is actually in the first place. (I hope this is understandable ;-)