如何使用 Google App Scripts 在学生出勤表的每一行最后一行添加色带？

Question

作为学生出勤系统的一部分，我想使用 App Scripts 在班级的每一行最后一行添加一个色带以进行出勤。我的 Google 表格列是：(i) 日期，(ii) 电子邮件，(iii) 纬度，(iv) 经度，和 (v) 主题代码。尝试了很多方法但没有找到解决方案。

  var sss = SpreadsheetApp.getActiveSpreadsheet();

  var ssID = sss.getId();

  var sheetName = sss.getName(); 

  var sheet = sss.getSheetByName("TempDataSet");

  var sheet1 = sss.insertSheet('TempDataSet_temp');

  sheet.getDataRange().copyTo(sheet1.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  sheet.getDataRange().copyTo(sheet1.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);

  

  var shID = sheet1.getSheetId().toString();

  sheet1.getRange(2, 1, sheet.getLastRow() -1, sheet.getLastColumn()).sort({column: 1, ascending: false}); 

  var columns_delete = [7,2]; //[7,5,4,2];

  columns_delete.forEach(col=>sheet1.deleteColumn(col));

  //const sss = SpreadsheetApp.getActiveSpreadsheet();

  //const sheet = sss.getSheetByName("TempDataSet");

  

  const subs = sheet.getRange('F2:F'+sheet.getLastRow()).getValues().flat();

  const usubs = subs.filter((value, index, self)=>self.indexOf(value) === index);

  const dts = sheet.getRange('A2:A'+sheet.getLastRow()).getDisplayValues().flat();

  const udts = dts.filter((value, index, self)=>self.indexOf(value) === index);

  

  if(usubs.length>1){

    subs.forEach((s,i)=>{

    if(i>1){

      if(subs[i]!=subs[i-1]){

        sheet.getRange(i+1,1,1,5).setBackground('yellow');

      }}});

  }

  else if (udts.length>1){

    dts.forEach((d,i)=>{

    if(i>1){

      if(dts[i]!=dts[i-1]){

        sheet.getRange(i+1,1,1,5).setBackground('yellow');

      }}});

  }

  }

Answer 1

解释：根据你的问题，我理解了以下步骤：检查E列中是否至少有两个独特的主题。一种方法是找到唯一的主题列表。如果该列表的长度是或更多，则意味着您有不同的主题。在这种情况下，语句的第一个块的计算结果为，并且您在更改主题之前的行中添加了一条黄线。2iftrue如果您只有一个主题，即唯一主题列表的长度，则语句1的第一个块if将评估为false。在这种情况下，脚本将检查A列是否有2一个或多个唯一日期。如果是，if将执行语句的第二个块，脚本将在日期更改前的行中添加一条黄线。否则，它不会做任何事情。解决方案：color()您可以作为独立脚本执行。我建议您将此函数保存在一个新.gs文件中，然后在您当前的脚本中简单地调用它。即，在您提供的代码片段中放置您想要的color()&nbsp;任何位置。function color() {&nbsp; const sss = SpreadsheetApp.getActiveSpreadsheet();&nbsp; const sheet = sss.getSheetByName("TempDataSet");&nbsp;&nbsp;&nbsp; const subs = sheet.getRange('E2:E'+sheet.getLastRow()).getValues().flat();&nbsp; const usubs = subs.filter((value, index, self)=>self.indexOf(value) === index);&nbsp; const dts = sheet.getRange('A2:A'+sheet.getLastRow()).getDisplayValues().flat();&nbsp; const udts = dts.filter((value, index, self)=>self.indexOf(value) === index);&nbsp;&nbsp;&nbsp; if(usubs.length>1){&nbsp; &nbsp; subs.forEach((s,i)=>{&nbsp; &nbsp; if(i>1){&nbsp; &nbsp; &nbsp; if(subs[i]!=subs[i-1]){&nbsp; &nbsp; &nbsp; &nbsp; sheet.getRange(i+1,1,1,5).setBackground('yellow');&nbsp; &nbsp; &nbsp; }}});&nbsp; }&nbsp; else if (udts.length>1){&nbsp; &nbsp; dts.forEach((d,i)=>{&nbsp; &nbsp; if(i>1){&nbsp; &nbsp; &nbsp; if(dts[i]!=dts[i-1]){&nbsp; &nbsp; &nbsp; &nbsp; sheet.getRange(i+1,1,1,5).setBackground('yellow');&nbsp; &nbsp; &nbsp; }}});&nbsp; }&nbsp; }完整的解决方案：function sendEmails(){&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; var sss = SpreadsheetApp.getActiveSpreadsheet();&nbsp; var ssID = sss.getId();&nbsp; var sheetName = sss.getName();&nbsp;&nbsp; var sheet = sss.getSheetByName("TempDataSet");&nbsp; var sheet1 = sss.insertSheet('TempDataSet_temp');&nbsp; sheet.getDataRange().copyTo(sheet1.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);&nbsp; sheet.getDataRange().copyTo(sheet1.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);&nbsp; var shID = sheet1.getSheetId().toString();&nbsp; sheet1.getRange(2, 1, sheet.getLastRow() -1, sheet.getLastColumn()).sort({column: 1, ascending: true});&nbsp;&nbsp; var columns_delete = [7,2]; //[7,5,4,2];&nbsp; columns_delete.forEach(col=>sheet1.deleteColumn(col));&nbsp; SpreadsheetApp.flush();&nbsp;&nbsp;&nbsp;const subs = sheet1.getRange('E2:E'+sheet1.getLastRow()).getValues().flat();&nbsp; const usubs = subs.filter((value, index, self)=>self.indexOf(value) === index);&nbsp; const dts = sheet1.getRange('A2:A'+sheet1.getLastRow()).getDisplayValues().flat();&nbsp; const udts = dts.filter((value, index, self)=>self.indexOf(value) === index);&nbsp;&nbsp;&nbsp; if(usubs.length>1){&nbsp; &nbsp; subs.forEach((s,i)=>{&nbsp; &nbsp; if(i>1){&nbsp; &nbsp; &nbsp; if(subs[i]!=subs[i-1]){&nbsp; &nbsp; &nbsp; &nbsp; sheet1.getRange(i+1,1,1,5).setBackground('yellow');&nbsp; &nbsp; &nbsp; }}});&nbsp; }&nbsp; else if (udts.length>1){&nbsp; &nbsp; dts.forEach((d,i)=>{&nbsp; &nbsp; if(i>1){&nbsp; &nbsp; &nbsp; if(dts[i]!=dts[i-1]){&nbsp; &nbsp; &nbsp; &nbsp; sheet1.getRange(i+1,1,1,5).setBackground('yellow');&nbsp; &nbsp; &nbsp; }}});&nbsp; }&nbsp; SpreadsheetApp.flush();&nbsp; var from = Session.getActiveUser().getEmail();&nbsp; var subject = 'Batch Attendance Record for Your Reference';&nbsp; var body = 'Dear Student,'+ '\n\n' + 'Greetings! Please find the batch attendance record attached. Stay safe and blessed.' + '\n\n' + 'Thank you.';&nbsp;&nbsp;&nbsp; var requestData = {"method": "GET", "headers":{"Authorization":"Bearer "+ScriptApp.getOAuthToken()}};&nbsp;&nbsp;&nbsp; var url = "https://docs.google.com/spreadsheets/d/"+ ssID + "/export?format=xlsx&id="+ssID+"&gid="+shID;&nbsp; var result = UrlFetchApp.fetch(url , requestData);&nbsp;&nbsp;&nbsp; var contents = result.getContent();&nbsp; sss.deleteSheet(sss.getSheetByName('TempDataSet_temp'));&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; var sheet2 = sss.getSheetByName('StudentList');&nbsp;&nbsp;&nbsp; var data = sheet2.getLastRow();&nbsp; var students = [];&nbsp; var students = sheet2.getRange(2, 6, data).getValues();&nbsp;&nbsp; //MailApp.sendEmail(students.toString(), subject ,body, {attachments:[{fileName:sheetName+".xlsx", content:contents, mimeType:"MICROSOFT_EXCEL"}]});&nbsp; &nbsp;&nbsp;&nbsp; for (var i=0; i<students.length; i++){ // you are looping through rows and selecting the 1st and only column index&nbsp; &nbsp; if (students[i][0] !== ''){&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; MailApp.sendEmail(students[i][0].toString(), subject ,body, {attachments:[{fileName:sheetName+".xlsx", content:contents, mimeType:"MICROSOFT_EXCEL"}]});&nbsp;&nbsp; &nbsp; &nbsp; //MailApp.sendEmail(students[i][0].toString(), subject ,body, {from: from, attachments:[{fileName:"YourAttendaceRecord.xlsx", content:contents, mimeType:"MICROSOFT_EXCEL"}]});&nbsp; &nbsp; }&nbsp;&nbsp; }&nbsp; &nbsp; &nbsp;}