performancegoogle-sheetsgoogle-apps-script

Is there a faster way to sort sheets with AppScripts?

My current code is below. Is there some other way of doing this that might be quicker?

 var tabs = ["A","B","C","D","E","F","G","H"] 

function sortie() {
  for (var i = 0; i < tabs.length; i++) {
    sortETF(tabs[i])
  };
};

function sortETF(tabs) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(tabs);
  var lr = sheet.getLastRow();
  var lc = sheet.getLastColumn();
  var srtrg = sheet.getRange(2, 1, lr-1, lc);
  
  srtrg.sort(6);
  
};
Solution

  • When Sheets API is used, the process cost can be reduced. Ref (Author: me) I think that TheMaster's comment will be correct. When your script is modified by using Sheets API, how about the following modification?

    Modified script:

    Before you use this script, please enable Sheets API at Advanced Google services.

    function myFunction() {
  const tabs = ["A", "B", "C", "D", "E", "F", "G", "H"]; // This is from your script.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const requests = tabs.map(tab => {
    const sheet = ss.getSheetByName(tab);
    return {
      sortRange: {
        range: {
          sheetId: sheet.getSheetId(),
          startRowIndex: 1,
          endRowIndex: sheet.getLastRow(),
          startColumnIndex: 0,
          endColumnIndex: sheet.getLastColumn()
        },
        sortSpecs: [{ sortOrder: "ASCENDING", dimensionIndex: 5 }]
      }
    }
  });
  Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());
}

    References: