如何通过使用 Google App Scripts 查询相应 ID 来插入电子邮件地址? [英] How to insert the email address by querying corresponding ID using Google App Scripts?

问题描述

如果是我当前用于手动输入的代码，当我选择一个科目、一个日期并一次输入最多 4 个学生 ID(例如 1802001、1802002、1802004)然后选择一个选项时，它只是连接使用电子邮件模式(例如@icte.bdu.ac.bd)并将其插入到之前选择的目标主题表中.

In case of my current codes for doing MANUAL ENTRY, when I select a subject, a date and input maximum 4 student IDs at a time (e.g. 1802001, 1802002, 1802004 for example) and then select an option, it just concatenate with the email pattern (e.g @icte.bdu.ac.bd) and insert those to the destination subject sheet selected earlier.

我不想再使用电子邮件模式 (@icte.bdu.ac.bd).鉴于一批学生的所有电子邮件地址都相似(例如 [e-mail]、[e-mail])，我当前的代码工作正常.但是，许多机构不提供基于学生 ID 号的电子邮件地址，通常类似于 [e-mail] 或 [e-mail].

I do not want to use the Email Pattern (@icte.bdu.ac.bd) any more. My current codes work fine given that all email addresses of a batch of students are similar (e.g. [e-mail], [e-mail]). However, at many institutions does not provide email addresses based on student ID number, often it is like [e-mail] or [e-mail].

请注意，在向仪表板(见附图)和学生列表表中输入时，学生 ID 是相同的，但在目标表 (HUM4105) 中有所不同，例如 1802001、1802003、卷号:18.02".004注册号:2018.11.000027".但是，学生的电子邮件地址在所有地方都是相同的，应该用作基础.实际上它是一个学生考勤系统，其中输入通常来自Android应用程序.如果很少有学生错过报告出勤率，那么我们会进行人工录入.

Please note that student IDs are same when do inputs to the Dashboard (see attached image) and also in the StudentList sheet but it differs in the destination sheet (HUM4105) such as 1802001, 1802003, "Roll No.:18.02.004 Reg No.:2018.11.000027". However, email address of students are same at all places and it should be used as a basis.Actually it is a student attendance system where the inputs usually come from an android app. If few students miss to report attendance, then we do manual entry.

我想要做的是，当我输入学生 ID 时，最多一次输入 4 个(例如1802001, 1802003)它实际上会插入他们对应的电子邮件从 Google 表格 (StudentList) 到之前选择的主题表格 (HUM4105) 的地址.学生名单sheet contains SID in COLUMN D and Email in Column F. Please note that Edit trigger is already there and when a certain option is selected, the following code is executed.

What I am trying to do is, when I will input the student IDs, maximum 4 at time (e.g. 1802001, 1802003) it will actually insert their corresponding email address from a Google sheet (StudentList) to the subject sheet selected earlier (HUM4105). The StudentList sheet contains SID in COLUMN D and Email in Column F. Please note that Edit trigger is already there and when a certain option is selected, the following code is executed.

代码是:

function manualEntry(e){ 
    var spreadsheet = SpreadsheetApp.getActive();
      var dashboard = spreadsheet.getSheetByName("Dashboard");
      var sheetName = dashboard.getRange("F5").getValue(); 
      var mDate = dashboard.getRange("G5").getDisplayValue();
      var sheetw = dashboard.getRange("A8");
      var sheetw2 = dashboard.getRange("G8");
           
        if (e.range.getA1Notation() === 'K6' && e.range.getValue() === "Report 1P") {
          sheetw2.setValue('Manual entry process is up and running. Please Wait!').setFontColor('Red');
          onePeriodm();       
          sheetw.setValue('You may take class attendance now. Thank You!').setFontColor('Green');
          sheetw2.setValue('You may do manual entry now. Thank You!').setFontColor('Green');
        }
   }          
    
    function onePeriodm(){
    //for one period manual entry
      const srcSheetName = "Dashboard";
    
      // 1. Retrieve values from the source sheet.
      const ss = SpreadsheetApp.getActiveSpreadsheet();
      const srcSheet = ss.getSheetByName(srcSheetName);
      const [
        [, , , , , emailPattern],
        ,
        [courseCatalogId, dateString, ...studentIds],
      ] = srcSheet.getRange("F3:K5").getDisplayValues();
    
      // 2. Retrieve current values
      const dstSheet = ss.getSheetByName(courseCatalogId);
      const dstCurrentValues = dstSheet
        .getRange(`A2:C${dstSheet.getLastRow()}`) // expanded to include email column
        .getDisplayValues(); // not flattening since we have multiple columns now
    
      // 3. Convert the values for putting to the destination sheet.
      //    Do not include if there is already an email for this date in current values
      const dstValues = studentIds.reduce((array, studentId) => {
        const existingEntry = dstCurrentValues.find(
          (row) => row[0] === dateString && row[2] === studentId + emailPattern
        );
        if (studentId != "" && !existingEntry)
          array.push([
            dateString,
            Number(studentId),
            studentId + emailPattern,
            ,
            ,
            courseCatalogId,
          ]);
        return array;
      }, []);
    
      // 4. Put the converted values to the destination sheet.
      const index = dstCurrentValues.map((row) => row[0]).lastIndexOf(dateString);
      const row = index + 2;
      if (dstValues.length > 0){
         dstSheet.insertRowsAfter(row, dstValues.length);
         dstSheet.getRange(row + 1, 1, dstValues.length, dstValues[0].length).setValues(dstValues);
      }

推荐答案

现在您的代码正在将 id 与电子邮件模式结合起来.因此，要使代码在这两种情况下都能正常工作，您需要进行检查以确定何时将 ID 与电子邮件模式连接起来，以及何时将 ID 替换为整个电子邮件地址.(我假设整个电子邮件地址都在 F 列的 StudentList 表中.)

Right now your code is combining the id with the email pattern. So for the code to work in both cases you'll need to have a check in place to identify when to concatenate the ID with the email pattern and when to substitute the ID with the whole email address. (I am assuming the whole email address is in the StudentList sheet in column F.)

如果始终列出整个电子邮件，即使它基于学生 ID，我也会完全取消 ID 和电子邮件模式的连接，而只使用 ID 替换整个电子邮件地址.

If the whole email is always listed, even when it is based on the student ID, I would do away with the concatenation of the ID and email pattern entirely and just use the substitution of ID with whole email address.

以下是需要在您的代码中实现的步骤:

Here are the steps that need to be implemented in your code:

1. 从 StudentList 列 D 到 F 获取范围.这将是一个数组(比如 studentListArray).
2. 获取学生 ID 数组.(如果您的仪表板结构不同，您可以简单地在行中列出任意数量的 ID，而不是一次只列出 4 个.)
3. 检查您的 ID 数组是否有重复.
4. 循环遍历 ID 数组并通过 studentListArray[0] 上的 ID 过滤 studentListArray，返回 studentListArray[2].
5. 检查过滤器结果不是未定义的.
6. 如果过滤结果未定义，则将结果设置为学生电子邮件.

编辑

这是更改代码的示例表:

Here is the example sheet with changes to the code:

https://docs.google.com/spreadsheets/d/146vRZ1QDGpnzhxnAuQ03qX4jTORZdj_piueW7ORfYDY/edit?usp=sharing

这是onePeriodm()的代码:

function onePeriodm(){
  //For protecting dashboard while scripts running
  var spreadsheet = SpreadsheetApp.getActive(); 
  
  //### new code ##########################################
  var studentListSheet = spreadsheet.getSheetByName("StudentList");
  var studentList = studentListSheet.getDataRange().getValues();
  var studentEmail = null;
  var tempEmail = null;
  //### end new code ######################################
  
  var dashboard = spreadsheet.getSheetByName("Dashboard");  
  var sheetw = dashboard.getRange("A8");
  var sheetw2 = dashboard.getRange("G8");  
  sheetw.setValue('Class Attendance is restricted now. Try Again Later!').setFontColor('Red');
   
  var rangem = dashboard.getRange("A4:K6");
  
  var timeZone = Session.getScriptTimeZone();
  var stringDate = Utilities.formatDate(new Date(), timeZone, 'dd/MM/yy HH:mm');
  //var me2 = Session.getEffectiveUser();
  var me2 = Session.getActiveUser().getEmail();
  var description = 'Scripts running on ' + stringDate + ' by ' + me2;
  
  //var me = Session.getEffectiveUser();
  //var description = 'Scripts are running by ' + me;
  var protectionm = rangem.protect().setDescription(description);  
  protectionm.removeEditors(protectionm.getEditors());
  protectionm.addEditor(me2);
  if (protectionm.canDomainEdit()) {
      protectionm.setDomainEdit(false);
  }  
  
  //Refreshing sheet with removing unprotectd data
  var spreadsheet = SpreadsheetApp.getActive();  
  var dashboard = spreadsheet.getSheetByName("Dashboard");  
  var sheetName = dashboard.getRange("F5").getValue(); 
  var sheet = spreadsheet.getSheetByName(sheetName);
  
  //For removing unprotected rows of data from a particular sheet  
  // 1. Retrieve data range.
    const dataRange = sheet.getDataRange();

    // 2. Create an object from the protected range. This is used for removing from the cleared rows.
    const protectedRanges = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE).map(e => {
      const r = e.getRange();
      const start = r.getRow();
      return {start: start, end: r.getNumRows() + start - 1};
    });

    // 3. Create range list for clearing rows using the object.
    let rangeList = [];
    for (let r = 2; r <= dataRange.getNumRows(); r++) {
      let bk = false;
      for (let e = 0; e < protectedRanges.length; e++) {
        if (protectedRanges[e].start == r) {
          r = protectedRanges[e].end;
          bk = true;
          break;
        }
      }
      if (!bk) rangeList.push(`A${r}:${r}`);
    }

    // 4. Delete the rows without the rows of the protected ranges.
    if (rangeList.length > 0) sheet.getRangeList(rangeList).getRanges().reverse().forEach(r => sheet.deleteRow(r.getRow()));
    //sheet.getRangeList(rangeList).getRanges().reverse().forEach(r => sheet.deleteRow(r.getRow()));
  

  //for one period manual entry
  const srcSheetName = "Dashboard";

  // 1. Retrieve values from the source sheet.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const srcSheet = ss.getSheetByName(srcSheetName);
  const [
    [, , , , , emailPattern],
    ,
    [courseCatalogId, dateString, ...studentIds],
  ] = srcSheet.getRange("F3:K5").getDisplayValues();

  // 2. Retrieve current values
  const dstSheet = ss.getSheetByName(courseCatalogId);
  const dstCurrentValues = dstSheet
    .getRange(`A2:C${dstSheet.getLastRow()}`) // expanded to include email column
    .getDisplayValues(); // not flattening since we have multiple columns now

  // 3. Convert the values for putting to the destination sheet.
  //    Do not include if there is already an email for this date in current values
  const dstValues = studentIds.reduce((array, studentId) => {
    const existingEntry = dstCurrentValues.find(
      (row) => row[0] === dateString && +row[1] === +studentId //(row) => row[0] === dateString && row[2] === studentId + emailPattern //############# code change
  );
//### new code ##########################################
tempEmail = studentList.filter(function(a){return (+a[3]) == (+studentId);})[0];
if (tempEmail != undefined && tempEmail != null &&tempEmail != ""){
  studentEmail = tempEmail[5];
}else{
  studentEmail = studentId + emailPattern;
}
//### end new code ######################################

    if (studentId != "" && !existingEntry)
      array.push([
        dateString,
        Number(studentId),
        studentEmail,//studentId + emailPattern //############# code change
        ,
        ,
        courseCatalogId,
      ]);
    return array;
  }, []);

  // 4. Put the converted values to the destination sheet.
  const index = dstCurrentValues.map((row) => row[0]).lastIndexOf(dateString);
  const row = index + 2;
  if (dstValues.length > 0){
     dstSheet.insertRowsAfter(row, dstValues.length);
     dstSheet.getRange(row + 1, 1, dstValues.length, dstValues[0].length).setValues(dstValues);
  }

  //dstSheet.insertRowsAfter(row, dstValues.length);
  //dstSheet.getRange(row + 1, 1, dstValues.length, dstValues[0].length).setValues(dstValues); 
  protectionm.remove();  
  SpreadsheetApp.flush();
  
  srcSheet.getRange("F5").setValue("All"); 
  srcSheet.getRange("K6").setValue("Please Select"); 
  srcSheet.getRange("G5:K5").clearContent();
  srcSheet.getRange('G5').clearDataValidations();
  //srcSheet.getRange('G5').setValue(new Date()).setNumberFormat("yyyy-mm-dd");    
} 

这篇关于如何通过使用 Google App Scripts 查询相应 ID 来插入电子邮件地址?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！