如何使用Google App脚本从数据透视表中以pdf或excel格式附加明细表? [英] How to attach a detail sheet as pdf or excel from a pivot table using Google App Scripts?

问题描述

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

I have a student attendance system. It has a count value of attendance per student in the COUNT column of a pivot table coming from TempDataSet tab. My expectation can be achieved through either of the two ways:

流程01:手动单击仪表板中数据透视表的计数"列的任何单元格时，它会产生出勤的详细信息 数据来自"TempDataSet"的那个学生，例如他/她参加哪一天的活动? 班级.工作表名称为[e-mail].然后我可以发送 pdf格式的详细信息表会手动发送到学生的电子邮件地址.现在，整个过程可以使用Google App脚本自动完成吗?

Process 01: Manually when I click on any cell of the Count column of a pivot table in the Dashboard, it produces a details of attendance of that student where data is coming from 'TempDataSet' like which days s/he attended a class. Then sheet name is the [e-mail]. Then I can send the details sheet as pdf to the student's email address manually. Now the whole process can be done automatically using Google App Scripts?

或过程02:根据学生的电子邮件ID拆分TempDataSet(与附件图像的样式相同)选项卡，并通过电子邮件分别作为附件发送给学生.但是，我班上有50至60名学生，因此所有操作都应通过从仪表板单击来完成，现在可以正常工作，但问题是它将整个TempDataSet选项卡发送给所有学生，而不是拆分TempDataSet信息并向每个学生发送特定信息.

OR Process 02: Split the TempDataSet (same pattern of the attached image) tab based on student email IDs and send to the students as attachment by email individually. However, I have 50-60 students in a class, so all should be done by a single click from the Dashboard, which is working fine now but the issues is it is sending the whole TempDataSet tab to all students instead of splitting the TempDataSet info and sending each student specific info.

以下代码适用于单个工作表标签下载并作为excel附件通过电子邮件发送(单个工作表中的所有学生出勤信息发送给所有学生:

The following codes are working fine for a single sheet tab download and email as excel attachment (all students attendance info in a single sheet to all students:

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"}]}); 
    } 
  }  

}

推荐答案

说明:

• 以下脚本遍历数据透视表(仪表板)，然后 对于每封学生电子邮件，它都会过滤相关数据 TempDataSet 工作表中的特定学生并将其复制到 名为temp_sh的临时工作表.最后，后者通过电子邮件发送为 给特定学生的Excel文件.

  Explanation:

  • The following script iterates over the pivot table (Dashboard) and for each student email it filters the relevant data, for that particular student, from the TempDataSet sheet and copies it to a temporary sheet named temp_sh. Finally, the latter gets emailed as an excel file to the particular student.

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

    It is important to mention that Spreadsheet.flush() is really necessary in this scenario because the script constantly creates and deletes a temporary sheet and therefore there are pending changes that need to be made in each iteration.

    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"}]});
    
    });
    
    }
    

    这篇关于如何使用Google App脚本从数据透视表中以pdf或excel格式附加明细表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！