javascript - 如何使用 Google App Scripts 从数据透视表中将明细表附加为 pdf 或 excel？

Question

我有一个学生考勤系统。它在来自 TempDataSet 选项卡的数据透视表的 COUNT 列中具有每个学生的出勤计数值。我的期望可以通过以下两种方式之一来实现：

过程 01：当我手动单击仪表板中数据透视表的“计数”列的任何单元格时，它会生成该学生的出勤详细信息，其中数据来自“TempDataSet”，例如她/他参加课程的日期。然后工作表名称是 [e-mail]。然后我可以手动将详细信息表作为 pdf 发送到学生的电子邮件地址。现在整个过程可以使用 Google App Scripts 自动完成吗？

或流程 02：根据学生电子邮件 ID 拆分 TempDataSet（与附件图像相同的模式）选项卡，并通过电子邮件单独作为附件发送给学生。但是，我一个班有 50-60 名学生，所以所有都应该通过从仪表板单击来完成，现在工作正常，但问题是它将整个 TempDataSet 选项卡发送给所有学生，而不是拆分 TempDataSet 信息并发送每个学生的具体信息。

以下代码适用于单个工作表选项卡下载和电子邮件作为 excel 附件（所有学生的出勤信息在单个工作表中发送给所有学生：

function autoEmailing(){
  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: 3, ascending: true}); 
  var columns_delete = [7,5,4,2];
  columns_delete.forEach(col=>sheet1.deleteColumn(col));
 
  var subject = 'Your Attendance Record at BDU';
  var body = 'Dear Student,'+ '\n\n' + 'Greetings! Please find the attendance record attached for your reference.' + '\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"}]}); 
    } 
  }  

}

标签： javascriptgoogle-apps-scriptgoogle-sheets

Answer 1

解释：

• 以下脚本遍历数据透视表 ( Dashboard )，并针对每个学生电子邮件从TempDataSet 工作表中过滤该特定学生的相关数据，并将其复制到名为 的临时工作表temp_sh中。最后，后者以 excel 文件的形式通过电子邮件发送给特定的学生。

• 值得一提的Spreadsheet.flush()是，在这种情况下确实有必要这样做，因为脚本会不断创建和删除临时工作表，因此每次迭代都需要进行未决更改。

---

解决方案：

function emailSender(){

const ss = SpreadsheetApp.getActive();
const sh_db = ss.getSheetByName('Dashboard');
const sh_tds = ss.getSheetByName('TempDataSet');

const u_emails = sh_db.getRange('A13:A57').getValues().flat(); // adjust this to your specific range
const data = sh_tds.getRange('A1:G'+sh_tds.getLastRow()).getValues();

const subject = 'Your Attendance Record at BDU';
const body = 'Dear Student,'+ '\n\n' + 'Greetings! Please find the attendance record attached for your reference.' + '\n\n' + 'Thank you.';
const from = Session.getActiveUser().getEmail();
const requestData = {"method": "GET", "headers":{"Authorization":"Bearer "+ScriptApp.getOAuthToken()}}; 
const ssID = ss.getId();

u_emails.forEach(e=>{

  var temp_data = data.filter( row => {
    return (row[2] == e || row[2] == 'Umail'); 
  });
  
  var temp_sh = ss.insertSheet('temp_sheet');
  temp_sh.getRange(1,1,temp_data.length,temp_data[0].length).setValues(temp_data);
  SpreadsheetApp.flush();
  var shID = temp_sh.getSheetId();
  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();
  ss.deleteSheet(temp_sh);
  
  GmailApp.sendEmail(e, subject ,body, {from: from, attachments:[{fileName:"YourAttendaceRecord.xlsx", content:contents, mimeType:"MICROSOFT_EXCEL"}]});

});

}