Google App脚本可根据列的值将行复制到动态创建的标签中 [英] Google App Script to copy rows into dynamically created tabs based on the value of a column

问题描述

首先-我是一名老师，并且自学了自己的应用程序脚本，因此请原谅我的草率代码.我所在地区频繁进行的标准化测试的结果通常是将所有测试合并为一张表来提供的.如果每个测试都在自己的表中，它们将更容易管理.

First - I'm a teacher and self-teaching myself apps script, so please forgive my sloppy code. The results of my district's frequent standardized tests are often delivered with all the tests combined into one sheet. They would be much easier to manage if each test were in their own sheets.

我修改了在此站点上找到的函数，以为测试列(M)中的每个唯一值创建一个新标签.这样就可以使用制表符将行复制到其中.它使用一个数组，老实说我不完全理解它.

I modified a function that I found on this site to create one new tab for each unique value in the test column (M). That takes care of having tabs to copy the rows into. It uses an array and I honestly don't understand it completely.

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

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

In order to get the correct rows into the corresponding tabs I ended up using else if statements. However, it means that I have to anticipate all the different tabs that will be created by the first function and then modify my code before running it. It's also quite slow, but tolerable. While this works there has to be a better way. If anyone has any suggestions I'd really appreciate it.

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个或更多测试.

Here is a link to a sheet with dummy data. My actual sheets can have upwards of 1500 to 2000 rows with 8 or more tests.

推荐答案

如果我对您的理解正确，那么您想要的功能是:

If I understand you correctly, you want a function that:

• 为每个不同的评估名称(来自 Sheet1 的列 M )创建一个新工作表.
• 将与每个评估名称相对应的行追加到相应的已创建工作表中.

• Creates a new sheet for each different Assessment Name (column M from Sheet1).
• Appends the rows corresponding to each Assessment Name to the corresponding created sheet.

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

If that's the case, then you could iterate through all data in Sheet1, and for each row, check if there is a sheet in the spreadsheet whose name equals the corresponding assessment name.

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

If such a sheet it exists, the script appends the current row to the end of the target sheet.

如果该工作表不存在，它将首先创建该工作表并将其标题放在顶部，然后追加当前行.

If the sheet doesn't exist, it first creates that sheet with the headers on top, and then appends the current row.

这是通过使用 appendRow(rowContents) .

This is accomplished by the use of appendRow(rowContents).

您的代码可能类似于以下内容:

Your code could be something along the following lines:

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 中有一个称为 Copied 的列标题.然后，当附加该行时，该行将填充例如字符串"Done".脚本下次运行时，可以检查相应的单元格是否具有值完成".

Additionally, if you want to avoid duplicating the rows appended every time the script runs, you could have a column in the last column of your main sheet that keeps track of which rows have been copied to the new sheet, and if they have been copied, avoid appending them again. You could, for example, have a column header in AC called Copied. Then, when the row is appended, that row is populated with, for example, the string "Done". Next time the script runs, it can check whether the corresponding cell has the value "Done".

例如，您可以从此更改函数的结尾:

You could, for example, change the end of the function, from this:

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

我希望这会有所帮助.

这篇关于Google App脚本可根据列的值将行复制到动态创建的标签中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！