Google脚本根据列表重复并重命名工作表 [英] Google script duplicate and rename sheets based on list

问题描述

我有一个表，其中包含三列员工信息.第一列有员工姓名.我想编写一个Google Apps脚本，该脚本将复制预格式化的模板工作表，并使用员工姓名重命名.在脚本末尾，每位员工将有自己的工作表，以他们的名字命名.

I have a table that has three columns of employee info. The first column has the employe names. I want to write a google apps script that will duplicate a pre-formatted template sheet and re-name it with the employee name. At the end of the script each employee will have their own sheet named after them.

这是我到目前为止的代码，我正在使用Google脚本教程中的某些功能，但是我对如何继续进行不知所措.编辑，我已经走得更远了，这段代码可以工作一次，但是现在挂在setName上:

Here is the code I have so far, I am using some functions from the Google scripts tutorial, but I am at a loss on how to proceed further. EDITED, I have gotten a little further, this code worked once but now is getting hung on setName:

//Create new sheets for each employee in the list
function createEmployeeSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  // Get the range of cells that store employee data.
  var employeeDataRange = ss.getRangeByName("EmployeeRef");

 // For every row of employee data, generate an employee object.
 var employeeObjects = getRowsData(sheet, employeeDataRange);

 for (i=0; i < employeeObjects.length; i++) {
 var EmployeeName = employeeObjects[i].name;
 ss.setActiveSheet(ss.getSheetByName("Template"));
 SpreadsheetApp.getActiveSpreadsheet().duplicateActiveSheet();
 var first = ss.getSheetByName("Copy of Template 1");
 first.setName(EmployeeName);


 }

}   

推荐答案

您可以使用copyTo()函数来做更简单的事情. 另外，请确保您拥有唯一的EmployeeName.

You can do more simple by using the copyTo() function. Also make sure you have unique EmployeeNames.

所以您的代码如下:

function test() {
  var ss        = SpreadsheetApp.getActiveSpreadsheet();
  var employeeObjects = [
     {"name": "Peter" },
     {"name": "Alice" },
     {"name": "Frank" }
  ]
  var template = ss.getSheetByName('Template');
  for ( var i=0; i < employeeObjects.length; i++) {
    var EmployeeName = employeeObjects[i].name;

    // get the sheets to check you are not creating a duplicate sheet 
    var sheets = ss.getSheets();
    var ok = true;

    // loop through the sheets and check a duplicate exist 
    for ( var j=0; j<sheets.length;j++ ) {
      if ( sheets[j].getName() == EmployeeName ) {
        ok = false;
        Logger.log('duplicate');
      }
    }

    if ( ok ) {
      template.copyTo(ss).setName(EmployeeName);
    } else {

      // do whatever you need to do if employee name is duplicate

    }
  }
}

这篇关于Google脚本根据列表重复并重命名工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！