Google App Script - Google Spreadsheets Move Row b

问题:

I am trying to move rows based on cell values, the function I have written works. However when there are more than 24 rows to move the script times out. Is there any way to make the loop run faster or more efficiently, as it will always time out.

The script reads the cell value of column 7(G) and if it matches one of the sales persons names it copies it over to the sheet that has their name.

function CopyDataToNewFile() {
    
  // How Many Columns over to copy
  var columsCopyCount  = 11; // A=1 B=2 C=3 ....
  
  // What Column to Monitor
  var columnsToMonitor  = 7; // A=1 B=2 C=3 ....MONITORS SALES PERSON NAME
  
   //TARGET SPREAD SHEETS
  var salesPerson1  = "Lorna";
  var salesPerson2  = "Sarah";
  var salesPerson3  = "Mark";
 
  //SOURCE SPREAD SHEET
  var ss = SpreadsheetApp.openById('1S3F0Dekyda4g77j_a150Obz0IDNKtWMU2WlGDSXdcD4');
  var sourceSpreadSheetSheetID = ss.getSheetByName("importdata");
  var sourceSpreadSheetSheetID1 = ss.getSheetByName(salesPerson1);
  var sourceSpreadSheetSheetID2 = ss.getSheetByName(salesPerson2);
  var sourceSpreadSheetSheetID3 = ss.getSheetByName(salesPerson3);
  
  var numRows = sourceSpreadSheetSheetID.getLastRow()+1;
    
  
  for(var i = 2; i < numRows; i++)
  {
  var r = sourceSpreadSheetSheetID.getRange(i, columnsToMonitor);

  var rValue = r.getValue();
    
  if(rValue == salesPerson1) {
      var targetRange = sourceSpreadSheetSheetID1.getRange(sourceSpreadSheetSheetID1.getLastRow() + 1, 1);
      sourceSpreadSheetSheetID.getRange(i, 1, 1, columsCopyCount).copyTo(targetRange), {contentsOnly: true};
      //sourceSpreadSheetSheetID.deleteRow(i);
    
    }else if (rValue == salesPerson2) {
        var targetRange = sourceSpreadSheetSheetID2.getRange(sourceSpreadSheetSheetID2.getLastRow() + 1, 1);
      sourceSpreadSheetSheetID.getRange(i, 1, 1, columsCopyCount).copyTo(targetRange), {contentsOnly: true};
      //sourceSpreadSheetSheetID.deleteRow(i);
      
    }else if (rValue == salesPerson3) {
       var targetRange = sourceSpreadSheetSheetID3.getRange(sourceSpreadSheetSheetID3.getLastRow() + 1, 1);
      sourceSpreadSheetSheetID.getRange(i, 1, 1, columsCopyCount).copyTo(targetRange), {contentsOnly: true};
      //sourceSpreadSheetSheetID.deleteRow(i);
    
    }else {//Fail Safe
      var targetRange = sourceSpreadSheetSheetID1.getRange(sourceSpreadSheetSheetID1.getLastRow() + 1, 1);
      sourceSpreadSheetSheetID.getRange(i, 1, 1, columsCopyCount).copyTo(targetRange), {contentsOnly: true};
      //sourceSpreadSheetSheetID.deleteRow(i);
    }
  
  }
  
}

Here is a copy of the sheet with the script. The script can be run from the script editor.

Google Sheet Link

Thank you

回答1:

I have optimized the below code. Now, the total runtime 0.864 seconds.

function CopyDataToNewFile() {

    // How Many Columns over to copy
    var columsCopyCount = 11; // A=1 B=2 C=3 ....

    // What Column to Monitor
    var columnsToMonitor = 7; // A=1 B=2 C=3 ....MONITORS SALES PERSON NAME

    //TARGET SPREAD SHEETS
    var salesPerson1 = "Lorna";
    var salesPerson2 = "Sarah";
    var salesPerson3 = "Mark";

    //SOURCE SPREAD SHEET
    var ss = SpreadsheetApp.openById('164nb8HbOPX8204KFlrF0BZeuZ-rCjoxojYT5jvEIuNU');
    var sourceSpreadSheetSheetID = ss.getSheetByName("importdata");
    var sourceSpreadSheetSheetID1 = ss.getSheetByName(salesPerson1);
    var sourceSpreadSheetSheetID2 = ss.getSheetByName(salesPerson2);
    var sourceSpreadSheetSheetID3 = ss.getSheetByName(salesPerson3);

    var data = sourceSpreadSheetSheetID.getRange(2, 1, sourceSpreadSheetSheetID.getLastRow() - 1, sourceSpreadSheetSheetID.getLastColumn()).getValues();

    var lorna = [];
    var sarah=[];
    var mark=[];


    for (var i = 0; i < data.length; i++) {

        var rValue = data[i][6];

        if (rValue == salesPerson1) {
            lorna.push(data[i]);
        } else if (rValue == salesPerson2) {
            sarah.push(data[i]);
        } else if (rValue == salesPerson3) {
            mark.push(data[i]);
        } else { //Fail Safe
            lorna.push(data[i]);
        }
    }

    if(lorna.length > 0){
      sourceSpreadSheetSheetID1.getRange(sourceSpreadSheetSheetID1.getLastRow() + 1, 1, lorna.length, lorna[0].length).setValues(lorna);
    }

    if(sarah.length > 0){
       sourceSpreadSheetSheetID2.getRange(sourceSpreadSheetSheetID2.getLastRow() + 1, 1, sarah.length, sarah[0].length).setValues(sarah);
    }

    if(mark.length > 0){
      sourceSpreadSheetSheetID3.getRange(sourceSpreadSheetSheetID3.getLastRow() + 1, 1, mark.length, mark[0].length).setValues(mark);
    }

    //Will delete the rows of importdata once the data is copided to other sheets
   sourceSpreadSheetSheetID.deleteRows(2, sourceSpreadSheetSheetID.getLastRow() - 1);
}