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

作为学生出勤系统的一部分,我想使用 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');

      }}});

  }


  }


慕田峪9158850
浏览 123回答 1
1回答

慕虎7371278

解释:根据你的问题,我理解了以下步骤:检查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;}
打开App,查看更多内容
随时随地看视频慕课网APP

相关分类

JavaScript