使用for循环将数据从一个Google表格映射到另一个 [英] Use for loop to map data from one Google Sheet to another one

问题描述

我对Google脚本还很陌生.我有一个学生名录Google表格，其中包含人口统计信息，包括ID号.我还有一个响应表，该表自动收集B列上的电子邮件地址(电子邮件地址由ID号加上"@ foo.org"创建).我试图让我的脚本通过自动收集的电子邮件进行解析，将电子邮件的ID部分映射到C列.然后，我使用for循环检查提取的ID号与我的主要花名册，并映射其余信息回复表上的同一个学生.

I'm still fairly new with Google Scripts. I have a student roster Google Sheet with demographic information, including ID numbers. I also have a responses Sheet that automatically collects email addresses on Column B (email addresses are created by the ID number plus "@foo.org"). I'm trying to get my script to parse through auto-collected emails, map the ID portion of the email onto column C. Then I'm using for loops to check the extracted ID number against my main roster, and map the remaining information for the same student on the responses sheet.

到目前为止，我只能正确映射响应表中的第一行；从第二行开始，我仅看到数字ID，但未映射其他相关列.这是我目前拥有的:

As of now, I'm only able to correctly map the first row in the responses sheet; As of the second row I only see the numeric IDs but it doesn't map the other relevant columns. This is what I currently have:

function extractId() {
  var responsesSheet = SpreadsheetApp.openById('XXXXXXX').getSheetByName('Sheet1');
  var rosterSheet = SpreadsheetApp.openById('XXXXXXX').getSheetByName('Sheet1');

  var responsesHighestEntry = responsesSheet.getLastRow();

  var email;
  var idNumber;

  var rosterLastRow = rosterSheet.getLastRow();
  var rosterArray = rosterSheet.getRange(2, 1, (rosterLastRow - 1)).getValues();
  var studentResponsesRow;

  var i;
  var x;
  var y = 2;
  var fullName;
  var grade;
  var advisoryTeacher;
  var advisoryRoom;


  for (i = 2; i < responsesHighestEntry; i++) {
    email = responsesSheet.getRange(i, 2).getValue();
    idNumber = email.replace(/\@(.*)/i,"");
    responsesSheet.getRange(i, 3).setValue(idNumber);

    for (x = 0; x < rosterLastRow; x++) { 
      if (rosterArray[x] == idNumber) {
        studentResponsesRow = y;

        fullName = rosterSheet.getRange(y, 2).getValue(); 
        responsesSheet.getRange(y, 4).setValue(fullName);

        grade = rosterSheet.getRange(x + 2, 6).getValue(); 
        responsesSheet.getRange(studentResponsesRow, 5).setValue(grade);

        advisoryTeacher = rosterSheet.getRange(x + 2, 7).getValue(); 
        responsesSheet.getRange(studentResponsesRow, 6).setValue(advisoryTeacher);

        advisoryRoom = rosterSheet.getRange(x + 2, 8).getValue(); 
        responsesSheet.getRange(studentResponsesRow, 7).setValue(advisoryRoom);
      } 

      y++;

    }
  }
}

包含人口统计数据的样本名册表.

Sample roster sheet with demographic data.

样本最终结果响应表.在这种情况下，将自动收集电子邮件地址.C-G列将是运行脚本的结果.

Sample end-result responses sheet. In this case, the email addresses are auto-collected. Columns C-G would be the result of running the script.

推荐答案

• 您要从问题图像中的名册表"的值中获得样本最终结果响应表".
  • 在您的情况下，响应表"仅包含带有电子邮件地址的"B"列.
  • 您要输入电子邮件地址ID与花名册"中的"A"列相同的值.

  如果我的理解是正确的，那么这个答案如何?请认为这只是几个可能的答案之一.

  If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

  此修改后的脚本的流程如下.

  The flow of this modified script is as follows.

  1. 从响应表"中检索"B"列的值.
  2. 从名册表"中检索值.
  3. 创建一个用于搜索ID的对象.
  4. 将结果值创建为数组.
  5. 将值放入响应表".

  修改后的脚本:

  function extractId() {
    var responsesSheet = SpreadsheetApp.openById('XXXXXXX').getSheetByName('Sheet1');
    var rosterSheet = SpreadsheetApp.openById('XXXXXXX').getSheetByName('Sheet1');
    var valuesOfresponsesSheet = responsesSheet.getRange(2, 2, responsesSheet.getLastRow() - 1, 1).getValues();
    var valuesOfrosterSheet = rosterSheet.getRange(2, 1, rosterSheet.getLastRow() - 1, 8).getValues();
    var obj = valuesOfrosterSheet.reduce(function(o, e) {
      o[e[0]] = [e[0], e[1], e[5], e[6], e[7]];
      return o;
    }, {});
  
    // var resultValues = valuesOfresponsesSheet.map(function(e) {return obj[e[0].replace(/\@(.*)/i,"")]});
    var resultValues = valuesOfresponsesSheet.map(function(e) {return obj[e[0].toString().replace(/\@(.*)/i,"")] || ["","","","",""]}); // Modified
  
    responsesSheet.getRange(2, 3, resultValues.length, resultValues[0].length).setValues(resultValues);
  }
  

  参考:

  • reduce()

  这篇关于使用for循环将数据从一个Google表格映射到另一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！