Google App脚本-Google Spreadsheets根据单元格值有效地移动行 [英] Google App Script - Google Spreadsheets Move Row based on cell value efficiently

问题描述

我正在尝试根据单元格值(我编写的函数)移动行.但是，当要移动的行超过24行时，脚本将超时.有什么方法可以使循环更快或更有效地运行，因为它总是会超时.

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.

该脚本读取第7(G)列的单元格值，如果它与某个销售人员姓名匹配，则会将其复制到具有其名称的工作表中.

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表格链接

谢谢

推荐答案

我已经优化了以下代码.现在，总运行时间为0.864秒.

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);
}

这篇关于Google App脚本-Google Spreadsheets根据单元格值有效地移动行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！