I’m trying to sort a Google Sheet (sample) by where one column’s data (‘Leads and Onboarding’!D7:D’, the column heading “STATUS”) falls within a custom array (“lead_status_sort!B5:B”). In this case, I want Partnerships first, then Onboardings, then Connectings, then Leads, then Abandoneds.

I’ve tried to write a bound Google App Script to do that. Here’s the entire function (question continues below).

function sortLeads() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); // gets bound spreadsheet
  var sheet = ss.getSheetById("547710831"); // gets leads sheet

  console.log("Getting List...");
  var list = ss.getSheetById("1696685410").getRange("B5:B").getValues().filter(String).flat(); // gets the list in lead_status_sort
  console.log(list); 
  list = list.reverse(); // We reverse the list, because we're going to iterate through the sheet moving matching rows to the top

  var firstRow = ss.getSheetById("1940886045").getRange("B6").getValue(); // This gets the row immediately following the row with "ORG" in the B column. 
  var rowCount = ss.getSheetById("1940886045").getRange("B7").getValue(); // This gets the number of rows with organizations in them
  var lastRow = firstRow + rowCount - 1; // This is the last row with an organization
  console.log("Sorting Leads Sheet Rows " + firstRow + " to " + lastRow)  
  var statusList = sheet.getRange("D" + firstRow + ":D" + lastRow).getValues().flat(); // gets the array of statuses

  for (let i = 0; i < list.length; i++) {
    console.log("Checking Array for Status = " + list[i]);
    for (let j = 0; j < rowCount; j++) {
      console.log("    Checking Row " + (firstRow + j));
      if (statusList[j] == list[i]) {
        console.log("    True");
        var rowSpec = sheet.getRange("A" + (firstRow + j) + ":" + (firstRow + j));
        try {
          console.log("    Moving Row");
          sheet.moveRows(rowSpec, firstRow);
          // LOCATION A
        } catch (error){
          
        }
      } else {
        console.log("    False");
      }
    }
  }
  console.log("pause");
  Utilities.sleep(5000);
}

It seems to make sense. In fact, if you manually change the i values, for example,

for (let i = 1; i < 2; i++) {

… and so on, it works great! The problem is, when I allow it to iterate through the entire for-loop, I’ve realized that it doesn’t actually move the rows when the script reaches LOCATION A (see the middle of the for-loop section). It moves the rows at the end of the script. (I wrote in a pause to be sure.)

So, the script is queuing actions that it then executes at script-end. And, it doesn’t seem to execute them in order (or, it tries to, but Google Sheets doesn’t move fast enough to do it in the order the script reads them).

How do I solve this problem? Am I going about it in entirely the wrong way?