Блог

В рамках системы посещаемости учащихся я хотел бы добавить цветную полосу в каждую последнюю строку класса для посещаемости с помощью скриптов приложений. Моими столбцами таблиц 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');
      }}});
  }
 
  var from = Session.getActiveUser().getEmail();
  var subject = 'Batch Attendance Record for Your Reference';
  var body = 'Dear Student,'  'nn'   'Greetings! Please find the batch attendance record attached. Stay safe and blessed.'   'nn'   'Thank you.';
  
  var requestData = {"method": "GET", "headers":{"Authorization":"Bearer " ScriptApp.getOAuthToken()}};  
  var url = "https://docs.google.com/spreadsheets/d/"  ssID   "/export?format=xlsxamp;id=" ssID "amp;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"}]});
    } 
  }
  

Объяснение:

Исходя из вашего вопроса, я понимаю следующие шаги:

1. Проверьте, есть ли у вас по крайней мере два уникальных предмета в столбце E. Один из способов сделать это — найти уникальный список предметов. Если длина этого списка равна 2 или больше, это означает, что у вас разные предметы. В этом случае первый блок if инструкции оценивается true как, и вы добавляете желтую строку в строку перед изменением темы.

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,'  'nn'   'Greetings! Please find the batch attendance record attached. Stay safe and blessed.'   'nn'   'Thank you.';
  
  var requestData = {"method": "GET", "headers":{"Authorization":"Bearer " ScriptApp.getOAuthToken()}};  
  var url = "https://docs.google.com/spreadsheets/d/"  ssID   "/export?format=xlsxamp;id=" ssID "amp;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"}]});
    } 
  }
     
}