在使用具有剪切粘贴功能的应用程序脚本时，如何处理来自谷歌表单的新数据？

Question

我在Google Sheets中有一个员工培训数据库，链接如下：

链接到谷歌电子表格

谷歌表单用员工信息填充“添加员工表单回复”表的A到J列。该数据通过简单=单元格函数传输到“资格”表。问题是，我不知道为什么，当每个新表单完成并且表单答案填充“添加员工表单回复”表时，它会将资格表中下一个空行中的单元格号更改为下一个后续行号链接到“添加员工表单响应”表中的空白单元格。例如，在表格填写完成之前，资格表第4行中的所有单元格都是从“添加员工表格回复”表的第4行绘制的，但是在表格填充之后，资格表的第4行是从第5行“添加”绘制的员工表单响应”表仍然为空。

我曾经有另一张工作表，在两张工作表之间有一个查询功能，效果很好，但后来我添加了以下应用程序脚本（脚本编辑器的第1行到第24行），以在“资格”工作表的B列中输入日期时剪切该行，并将其粘贴到“资格-员工离开”工作表中。问题在于，查询只是再次从“添加员工表单响应”表中检索数据，然后重新填充“资格”表。换句话说，刚从“资格”表剪切并粘贴到“资格-员工离开”表中的数据通过查询返回到“添加员工表单响应”表中的“资格”表。

// Cut Employees Left from Qualifications sheet and 
// paste in Qualifications - Employees Left sheet
function onEdit(e) {
  var ss = e.source;
  var sheet = ss.getActiveSheet();
  var sheetName = "Qualifications"
  var range = e.range;
  var editedColumn = range.getColumn();
  var editedRow = range.getRow();
  var column = 2;
  var date = range.getValue();
  // Object.prototype.toString.call(date) === '[object Date]' --> checks if value is date
  // editedColumn == column && editedRow > 4 --> checks if edited cell is from 'Date Left'
  // sheet.getName() == sheetName --> checks if edited sheet is 'Qualifications'
  if(Object.prototype.toString.call(date) === '[object Date]' && editedColumn == column && editedRow > 4 && sheet.getName() == sheetName) {
    var numCols = sheet.getLastColumn();
    var row = sheet.getRange(editedRow, 1, 1, numCols).getValues();
    var destinationSheet = ss.getSheetByName("Qualifications - Employees Left");
    // Get first empty row:
    var emptyRow = destinationSheet.getLastRow() + 1;
    // Copy values from 'Qualifications'
    destinationSheet.getRange(emptyRow, 1, 1, numCols).setValues(row);
    sheet.deleteRow(editedRow);
    sheet.hideColumns(column);
  }

如果“资格”表和“添加员工表单响应”表之间的行编号问题有一个简单的解决方案，我会很高兴。否则，我认为唯一可以解决这个问题的方法是恢复查询功能，然后获取应用程序脚本以从“添加员工表单响应”表中删除数据。如果有任何建议，我将不胜感激。我真的希望一切都能自动化，因为会有很多不懂电脑的html" target="_blank">用户。

Answer 1

您正在寻找一种更好的方法来动态记录新员工，并在“添加员工表单响应”表和“资格”表中进行通知。目前，您使用的是简单的公式，但这些公式不一定会自动挑选新员工。

在这个回答中，我建议您考虑一个场景，其中“资格”数据从表单响应表物理复制到“资格”表。

出于开发目的，采取了以下步骤：

• 创建了资格表的副本
• 将重复的工作表命名为工作人员。
• 显示所有行的工作人员
• 复制（复制/粘贴值）一个现有的雇员到第5行
• 删除所有单元格的内容从第6行到最后一行-到最后一列
• 将脚本复制到绑定的Project中
• 将函数安装为可安装触发器-event=电子表格/在表单提交
• 处理了一些新员工进行测试。
• 确认新员工数据被添加到最后一行加上其中一个“员工”

    function so5882862602(e) {

      //58828626
      // setup spreadsheet and sheets
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var formsheetname = "Add Employee Form Responses";
      var form = ss.getSheetByName(formsheetname);
      var staffsheetname = "staff";
      var staff = ss.getSheetByName(staffsheetname);

      // get Form Response
      //Logger.log(JSON.stringify(e));
      var row = e.range.getRow();
      var range = form.getRange(row,1,1,10);
      //Logger.log("DEBUG: The range is "+range.getA1Notation());  
      var values = range.getValues();  
      var formSurname = values[0][1]
      var formFirstName = values[0][2];
      var formaka = values[0][3];
      var formType = values[0][4]
      var formBranch = values[0][5];
      var formDOB = values[0][6];
      var formGender = values[0][7]
      var formNSN = values[0][8];
      var formNZQA = values[0][9];
      //var formTimeStamp = values[0][0];
      Logger.log("DEBUG: Employee="+formSurname+", "+formFirstName+",aka="+formaka+", Type="+formType+", Branch="+formBranch+", DOB="+formDOB+", Gender="+formGender+", NSN="+formNSN+", NZQA="+formNZQA);


      //update the staff sheet
      var staffupdate = [];
      var staffinal=[];

      // check the value of formaka and adjust name accordingly
      var staffname = "";
      // check the value of formaka
      if (formaka != ""){
        staffname = formSurname.toUpperCase()+", "+formFirstName+" ("+formaka+")";// name
      }
      else{
        staffname = formSurname.toUpperCase()+", "+formFirstName;// name
      }

      // push the values to a blank array
      staffupdate.push(staffname); // name
      staffupdate.push(""); // left
      staffupdate.push(formType);// TYPE
      staffupdate.push(formBranch); // branch
      staffupdate.push(formDOB); // DOB
      staffupdate.push(formGender);// Gender
      staffupdate.push(formNSN);// National Student Number
      staffupdate.push(formNZQA); // NZQA Unit Standards

      //push the array to a second array to create 2D
      staffinal.push(staffupdate);

      // get staff data
      var staffLR = staff.getLastRow();
      var staffupdaterange = staff.getRange(staffLR+1,1,1,8);
      Logger.log("DEBUG: The staff range = "+staffupdaterange.getA1Notation());


        // update the form values to the staff sheet
      staffupdaterange.setValues(staffinal);

    }