CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
user11221377
user11221377

Reputation: 125

Sorting sheets by name when the names are dates

I have a google spreadsheet where some of the sheets names are text and others are dates in "dd/mm/yyyy/" format. I need a function that can put the sheet called "Tablero" first, then sort the sheets named with dates descendingly, and leave at the end the rest of the sheets.

This is my code so far:

function testOrdenar() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetsCount = ss.getNumSheets();
  var sheets = ss.getSheets();

  // I need the sheet "Tablero" to go first
  var tablero = ss.getSheetByName("Tablero");
  ss.setActiveSheet(tablero);
  ss.moveActiveSheet(1);

  var names = []; // This is where the sheets named with dates will go
  var j = 1; // I use this as a counter, but it is not absolutly necessary

  for (var i = 0; i<sheetsCount; i++){
    var sheetName = sheets[i].getName();
    var pattern = /(\d{2})\/(\d{2})\/(\d{4})/;
    var dt = new Date(sheetName.replace(pattern,'$3-$2-$1'));
    if (dt instanceof Date && !isNaN(dt.valueOf())) {names[j] = dt; j++;} // This is to distinguish the sheets that are dates
  }

  // Here I sort the sheets descendingly
  for (var m = 1;m<j;m++) {
    for (var n = 1;n<j;n++) {
      if (names[n] < names[m]) {
        var aux = names[n];
        names[n] = names[m];
        names[m] = aux;
      }
    }
  }

  var pos = 2;
  for (var a = 0;a<j;a++) {
    var sheetName = Utilities.formatDate(new Date(names[a]), "GMT-3", "dd/MM/yyyy");
    Logger.log(sheetName);
    var sheet = ss.getSheetByName(sheetName);
    ss.setActiveSheet(sheet);
    ss.moveActiveSheet(pos);
    pos++;
  }

The sorting is correct, but I don't know why each date ends up being a day less. I tried adding one to de variable but it comes out as "Invalid Object". And I also need those dates as strings because that is how i can then call the sheets.

My questions:

1) How can I get the correct dates? (not a day before each one). Could it have something to do with the timezone? I'm in "GMT-3".

(If the answer is adding one, please tell me how because I tried that and comes back as an error.)

2) How can I get the sorted dates as strings in "dd/MM/yyyy" format?

Here are the screenshots of my sheets and the logs I get:

enter image description here enter image description here

Upvotes: 2

Views: 137

Answers (1)

TheMaster
TheMaster

Reputation: 50594

Cause:

  • As written in the documentation, Date.parse(timestring), where timestring is a date only iso8601 compatible string, returns a date in UTC. When you format the date in GMT-3, the date is offset 3 hours to the previous day.

Solution:

  • Use GMT as timezone argument of Utilities.formatDate

  • Alternatively, You can avoid converting to date at all and sort them as plain strings.

Sample script:

function testOrdenar1() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  const pattern = /(?:Tablero)|(\d{2})\/(\d{2})\/(\d{4})/;

  const map = sheets.reduce( //Create a map of sheet object : sheetName(as yyyyMMdd)
    (m, sheet) =>
      m.set(
        sheet,
        sheet
          .getName()
          .replace(pattern, (m, p1, p2, p3) =>
            m === 'Tablero' ? '99999999' : p3 + p2 + p1//make Tablero highest 8 digit number string
          )
      ),
    new Map()
  );

  sheets.sort((a, b) => map.get(b) - map.get(a));//sort sheet objects by their name
  let pos = 1;
  sheets.forEach(sh => {
    ss.setActiveSheet(sh);
    ss.moveActiveSheet(pos++);
  });
}

Upvotes: 1

Related Questions