如何使用 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

解释：

根据你的问题，我理解了以下步骤：

1. 检查E列中是否至少有两个独特的主题。一种方法是找到唯一的主题列表。如果该列表的长度是或更多，则意味着您有不同的主题。在这种情况下，语句的第一个块的计算结果为，并且您在更改主题之前的行中添加了一条黄线。2iftrue

2. 如果您只有一个主题，即唯一主题列表的长度，则语句1的第一个块if将评估为false。在这种情况下，脚本将检查A列是否有2一个或多个唯一日期。如果是，if将执行语句的第二个块，脚本将在日期更改前的行中添加一条黄线。否则，它不会做任何事情。

---

解决方案：

color()您可以作为独立脚本执行。我建议您将此函数保存在一个新.gs文件中，然后在您当前的脚本中简单地调用它。即，在您提供的代码片段中放置您想要的color() 任何位置。

function color() {

  const sss = SpreadsheetApp.getActiveSpreadsheet();

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

  

  const subs = sheet.getRange('E2:E'+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');

      }}});

  }

  }

完整的解决方案：

function sendEmails(){  

  

  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: true}); 

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

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

  SpreadsheetApp.flush();

  

 const subs = sheet1.getRange('E2:E'+sheet1.getLastRow()).getValues().flat();

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

  const dts = sheet1.getRange('A2:A'+sheet1.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]){

        sheet1.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]){

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

      }}});

  }

  SpreadsheetApp.flush();

  var from = Session.getActiveUser().getEmail();

  var subject = 'Batch Attendance Record for Your Reference';

  var body = 'Dear Student,'+ '\n\n' + 'Greetings! Please find the batch attendance record attached. Stay safe and blessed.' + '\n\n' + 'Thank you.';

  

  var requestData = {"method": "GET", "headers":{"Authorization":"Bearer "+ScriptApp.getOAuthToken()}};  

  var url = "https://docs.google.com/spreadsheets/d/"+ ssID + "/export?format=xlsx&id="+ssID+"&gid="+shID;

  var result = UrlFetchApp.fetch(url , requestData);  

  var contents = result.getContent();

  sss.deleteSheet(sss.getSheetByName('TempDataSet_temp'));   

  

  var sheet2 = sss.getSheetByName('StudentList');  

  var data = sheet2.getLastRow();

  var students = [];

  var students = sheet2.getRange(2, 6, data).getValues(); 

  //MailApp.sendEmail(students.toString(), subject ,body, {attachments:[{fileName:sheetName+".xlsx", content:contents, mimeType:"MICROSOFT_EXCEL"}]});

    

  for (var i=0; i<students.length; i++){ // you are looping through rows and selecting the 1st and only column index

    if (students[i][0] !== ''){           

      MailApp.sendEmail(students[i][0].toString(), subject ,body, {attachments:[{fileName:sheetName+".xlsx", content:contents, mimeType:"MICROSOFT_EXCEL"}]}); 

      //MailApp.sendEmail(students[i][0].toString(), subject ,body, {from: from, attachments:[{fileName:"YourAttendaceRecord.xlsx", content:contents, mimeType:"MICROSOFT_EXCEL"}]});

    } 

  }

     

}