Google App Script根据列的值将行复制到动态创建的选项卡中

Question

首先，我是一名教师，自学应用程序脚本，所以请原谅我的草率代码。我所在地区经常进行的标准化考试的结果通常与所有考试一起提交到一张表格中。如果每个测试都在他们自己的表格中，他们将更容易管理。

我修改了在这个站点上找到的一个函数，为测试列（M）中的每个唯一值创建一个新选项卡。这就需要使用选项卡将行复制到其中。它使用数组，我真的不完全理解它。

function createNewSheets() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var masterSheet = spreadsheet.getSheetByName('Sheet1');
  var source_range = masterSheet.getRange("A1:AD1"); //header row

  // Retrieve 2d array for column M
  var colA = masterSheet.getRange('M2:M').getValues();

  // Create a 1d array of unique values
  var uniqueValues = {};
  colA.forEach(function(row) {
    row[0] ? uniqueValues[row[0]] = true : null;
  });
  var newSheetNames = Object.keys(uniqueValues);

  newSheetNames.forEach(function(sheetName) {
    // Check to see whether the sheet already exists
    var sheet = spreadsheet.getSheetByName(sheetName);
    if (!sheet) {
      spreadsheet.insertSheet(sheetName); //inserts new sheet
      source_range.copyTo(spreadsheet.getSheetByName(sheetName).getRange("A1:AD1")); //writes header row to newly created sheet
    }
  });
}

为了将正确的行放入相应的选项卡中，我最终使用了elseif语句。但是，这意味着我必须预测第一个函数将创建的所有不同选项卡，然后在运行它之前修改代码。它也很慢，但可以忍受。虽然这样做有效，但必须有更好的方法。如果有人有任何建议，我将非常感谢。

function copyRows() {
  var sSheet = SpreadsheetApp.getActiveSpreadsheet();
  var srcSheet = sSheet.getSheetByName("Sheet1"); 
  var lastRow = srcSheet.getLastRow();

  for (var i = 2; i <= lastRow; i++) {
    var srcRange = srcSheet.getRange("A" + i + ":AD" + i);
    var cell = srcSheet.getRange("M" + i);
    var val = cell.getValue();

  //sets the target sheet depending on the exam in column M

    if (val == "Growth: Language 2-12 KS 2017") {
      var tarSheet = sSheet.getSheetByName("Growth: Language 2-12 KS 2017");
    }

    else if (val == "Growth: Math 2-5 KS 2017") {
      var tarSheet = sSheet.getSheetByName("Growth: Math 2-5 KS 2017");
    }  

    else if (val == "Growth: Reading 2-5 KS 2017") {
      var tarSheet = sSheet.getSheetByName("Growth: Reading 2-5 KS 2017");
    } 

    else if (val == "Growth: Reading K-2 KS 2017") {
      var tarSheet = sSheet.getSheetByName("Growth: Reading 2-5 KS 2017");
    } 

    else if (val == "Growth: Math K-2 KS 2017") {
      var tarSheet = sSheet.getSheetByName("Growth: Reading 2-5 KS 2017");
    } 

    else {
    }
  //insets the row in the correct target worksheet  
      var tarRow = tarSheet.getLastRow()+1;
      tarSheet.insertRows(tarRow);
      var tarRange = tarSheet.getRange("A" + (tarRow) + ":AD" + (tarRow));
      srcRange.copyTo(tarRange);    
  }
};

下面是一个指向包含虚拟数据的工作表的链接。我的实际工作表最多可以有1500到2000行，需要进行8次或更多的测试。

Answer 1

此函数可以替换您的复制行脚本，并且应该运行得更快。

function copySheets() {
  var keyA=["Growth: Language 2-12 KS 2017", "Growth: Math 2-5 KS 2017", "Growth: Reading 2-5 KS 2017", "Growth: Reading K-2 KS 2017", "Growth: Math K-2 KS 2017"];
  var shtA=["Growth: Language 2-12 KS 2017", "Growth: Math 2-5 KS 2017", "Growth: Reading 2-5 KS 2017", "Growth: Reading K-2 KS 2017", "Growth: Math K-2 KS 2017"];
/*
I made the above arrays to different one in the event that you ever wish to change the mapping.

The code below gets all of the data on the source sheet from column1 to column 30 and row 2 to the last row.  Whenever you have a startrow that is not 1 you need to subtracting off the rows above the start row from the sheet.getLastRow() because the third parameter for get range is not the last row but the number of rows.

This code get all of the data in one two dimensional array and sticks it into the target range all at one time.  In my experience you should see a 10x increase in performance.
*/
  var ss=SpreadsheetApp.getActive();
  var srcsh=ss.getSheetByName("Sheet1"); 
  var vA=srcsh.getRange(2,1,srcsh.getLastRow()-1,30).getValues();
  var keyToSheet={};
  keyA.forEach(function(key,i){keyToSheet[key]=shtA[i];})
  var tarsh=ss.getSheetByName(keyToSheet(val));
  var tarrg=tarsh.getRange(2,1,vA.length,vA[0].length).setValues(vA);
}

Answer 2

如果我理解正确，您需要一个函数：

• 为每个不同的评估名称（列M来自Sheet1）
• 将每个评估名称对应的行追加到相应的创建表中

如果是这种情况，那么您可以遍历Sheet1中的所有数据，对于每一行，检查电子表格中是否有一个表的名称等于相应的评估名称。

如果存在这样的工作表，脚本会将当前行追加到目标工作表的末尾。

如果该工作表不存在，它将首先创建顶部带有标题的工作表，然后追加当前行。

这是通过使用appendRow（rowContents）实现的。

您的代码可以是以下内容：

function createNewSheets() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var masterSheet = spreadsheet.getSheetByName('Sheet1');
  // Get range with data from original sheet:
  var firstRow = 1;
  var firstCol = 1;
  var numRows = masterSheet.getLastRow();
  var numCols = masterSheet.getLastColumn();
  var data_range = masterSheet.getRange(firstRow, firstCol, numRows, numCols).getValues();
  // Get headers row (to be appended on top when new sheet is created):
  var headers = data_range[0];
  // Iterate through all rows of data from Sheet1:
  for (var i = 1; i < data_range.length; i++) {
    var row = data_range[i]; // Current row
    var assessmentName = row[12]; // Assessment name
    // Get sheet with current assessment name:
    var sheet = spreadsheet.getSheetByName(assessmentName);
    // Check if sheet with current assessment name exists. If it doesn't it creates it:
    if (!sheet) {
      sheet = spreadsheet.insertSheet(assessmentName);
      sheet.appendRow(headers);
    }
    // Appends current row of data to the new sheet:
    sheet.appendRow(row);
  }
}

此外，如果希望避免在每次脚本运行时重复追加的行，可以在主工作表的最后一列中设置一列，用于跟踪哪些行已复制到新工作表，如果已复制，则避免再次追加。例如，您可以在AC中复制一个名为的列标题。然后，当追加行时，该行将填充字符串“Done”。下次脚本运行时，它可以检查对应的单元格是否具有值“Done”。

例如，您可以通过以下方式更改函数的结尾：

    // Appends current row of data to the new sheet:
    sheet.appendRow(row);
  }
}

对此：

    // Appends current row of data to the new sheet if `AC` column value is not "Done":
    if (row[28] != "Done") {
      sheet.appendRow(row);
      masterSheet.getRange(i + 1, 29).setValue("Done");    
    }
  }
}

我希望这对你有帮助。