时间戳

首页 > 解决方案 > 在使用具有剪切和粘贴功能的 Apps 脚本时，如何处理来自 Google 表单的新数据？

google-apps-script - 在使用具有剪切和粘贴功能的 Apps 脚本时，如何处理来自 Google 表单的新数据？

问题描述

我在以下链接中的 Google 表格中有一个员工培训数据库：

链接到 Google 电子表格

Google 表单使用员工信息填充“添加员工表单响应”表的 A 到 J 列。该数据通过简单的 =cell 函数传输到“资格”表。问题是，我不知道为什么，当每个新表格完成并且表格答案填充“添加员工表格响应”表时，它会将“资格”表中下一个空行中的单元格编号更改为接下来的行号链接到“添加员工表单响应”表中的空白单元格。例如，在表格完成之前，资格表第 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);
  }

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

标签： google-apps-scriptgoogle-sheetsgoogle-forms

解决方案

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

在这个答案中，我建议您查看“资格”数据从表单响应表物理复制到“资格”表的场景。

出于开发目的，进行了以下步骤：

  • 创建了“资格”表的副本
  • 将重复的工作表命名为“员工”。
  • 在“员工”上显示所有行
  • 将一名现有员工复制（复制/粘贴值）到第 5 行
  • 从第 6 行到最后一行删除所有单元格的内容 - 到最后一列
  • 将脚本复制到绑定的项目中
  • 将该功能安装为可安装触发器 - 事件 = 电子表格/表单提交
  • 处理了一些新员工进行测试。
  • 确认新员工数据添加到最后一行加上一个“员工”
    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);

    }

推荐阅读