代码之家 › 专栏 › 技术社区 › Yoko

试图用Javascript在表格中发送多个PDF块

blob pdf google-sheets email javascript

Yoko · 技术社区 · 1 年前

我想要的是:一个生成pdf的谷歌表格,将PDF保存到您的GDrive中,然后向您发送一封附有所有pdf的电子邮件。

我所能做的:

生成PDF
将PDF保存到GDrive
通过电子邮件发送个人PDF

我的(恶心的业余爱好者)代码在创建pdf的过程中循环得很好,但当我试图将它们保存/推送到数组中时,或者当我尝试将数组附加到电子邮件中时,它就会挂断。

(可能)有问题的代码行已被一行

  // ======================================== //
  //                                          //
  //      GENERATE MULTIPLE LOGS AT ONCE      //
  //                                          //
  // ======================================== //

function printMultiple() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ui = SpreadsheetApp.getUi();

  var lastRow = ss.getRange("AN:AN").getValues().filter(String).length;
  var targetRange = ss.getSheetByName("Show 3 Mileage Form").getRange(1,40,lastRow,1);
  var workerList = String(targetRange.getValues()).replaceAll(", ","+++").split(",");

  var printAllBox = ui.alert("PRINT ALL MILEAGE LOGS", "Attempting to generate pdfs for everybody who drove this week.\n\nThis might take a while, so don't touch anything.",ui.ButtonSet.OK_CANCEL);

    if (printAllBox === ui.Button.OK) {

      var timer = 0;
      var counter = 0;

      for (var i = 0; i < workerList.length; i++) {
        workerList[i] = workerList[i].replaceAll("+++",", ");
        ss.getRange("E2").setValue(workerList[i]);
    
        if (ss.getRange("PRINT!I10").getValue() == ""){  }

        else {
                    
          var sheet;
          var email = Session.getEffectiveUser().getEmail();
          var subject = "Multiple Mileage Logs";
          var body = "Mileage logs generated."

          // Look to see if it's a two pager or not

          if (ss.getRange("PRINT!G1").getValue() == "PAGE 1 of 1") { sheet = ss.getSheetByName("PRINT");}
          else { sheet = ss.getSheetByName("PRINT - 2 PAGE"); }

          // show the print sheet
          sheet.showSheet();

          const fileName = ss.getRange("PRINT!J2").getValue()+".pdf";
                  
          // Base URL
          var exportURL = "https://docs.google.com/spreadsheets/d/SS_ID/export?".replace("SS_ID", ss.getId());

          var url_ext = 'exportFormat=pdf&format=pdf' // export as pdf / csv / xls / xlsx
          + '&size=letter'                            // paper size legal / letter / A4
          + '&portrait=false'                         // orientation, false for landscape
          + '&fith=true&'                             // fit to page width, false for actual size  CHANGED TO HEIGHT
          + "&gridlines=false" 
          + "&top_margin=0.25" 
          + "&bottom_margin=0.25" 
          + "&left_margin=0.5" 
          + "&right_margin=0.5" 
          + '&sheetnames=false&printtitle=false'      // hide optional headers and footers
          + '&pagenumbers=false&gridlines=false'      // hide page numbers and gridlines
          + '&fzr=false'                              // do not repeat row headers (frozen rows) on each page
          + '&gid=';                                  // the sheet's Id

          var token = ScriptApp.getOAuthToken();

          //make an empty array to hold your fetched blobs  
          var pdf;
          var pdfCluster = [];

          // Convert your specific sheet to blob
          var response = UrlFetchApp.fetch(exportURL + url_ext + sheet.getSheetId(), {
          headers: { 'Authorization': 'Bearer ' +  token }
          });

          //  And then hide the printsheet
          sheet.hideSheet();

          //convert the response to a blob and store in our array
          pdf = response.getBlob().setName(fileName);

// =======================================================================================

          // I originally tried using the variable "pdf" as the array 
          // and figured that maybe having a completely separate 
          // variable might help

          pdfCluster.push(pdf);

          // I also tried going the pdfCluster[i] = pdf route 

// =======================================================================================

          // Check to see if they have the right folders in place, and if not, create them
          var folder1 = "gripPDFs"                          //  gripPDFs
          var folder2 = ss.getRange("PRINT!C5").getValue()  //  Job Name
          var folder3 = "mileage logs"                      //  mileage logs  
          var folder4 = ss.getRange("PRINT!K2").getValue()  //  date

          /* Find the first level folder, create if the folder does not exist */
          var folders = DriveApp.getFoldersByName(folder1);
          var firstLevelFolder = (folders.hasNext()) ? folders.next() : DriveApp.createFolder(folder1);
                                        
          /* Layer2 */
          folders = DriveApp.getFoldersByName(folder2);
          var secondlevelFolder = (folders.hasNext()) ? folders.next() : firstLevelFolder.createFolder(folder2); 

          /* Layer3 */
          folders = DriveApp.getFoldersByName(folder3);
          var thirdlevelFolder = (folders.hasNext()) ? folders.next() : secondlevelFolder.createFolder(folder3); 

          /* Layer4 */
          folders = DriveApp.getFoldersByName(folder4);
          var finalfolder = (folders.hasNext()) ? folders.next() : thirdlevelFolder.createFolder(folder4); 

          // And create that shit
          finalfolder.createFile(pdf);

          counter = (counter + 1);
          timer = timer + 1;
          if (timer > 3) { 
            // Sleep to appease the Google Lag Police
            SpreadsheetApp.flush();
            Utilities.sleep(5000);
            SpreadsheetApp.flush();
            timer = 0;
          }
        }
      }

  //And mail it!
  GmailApp.sendEmail(email, subject, body, {

// =======================================================================================

  attachments: [pdfCluster]

// =======================================================================================

  });

  var pdfResults = ui.alert("PROCESS COMPLETE",counter +" total logs printed.  The files have been emailed to you and can be found under "+folder1+"/"+folder2+"/"+folder3+"/"+folder4+" in your Google Drive.",ui.ButtonSet.OK);



  }
}

1 回复 | 直到 1 年前

Zeros-N-Ones 1 年前

问题在于如何处理pdf数组和附件。尝试下面的代码,看看它是否修复了问题:

function printMultiple() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ui = SpreadsheetApp.getUi();

  var lastRow = ss.getRange("AN:AN").getValues().filter(String).length;
  var targetRange = ss.getSheetByName("Show 3 Mileage Form").getRange(1,40,lastRow,1);
  var workerList = String(targetRange.getValues()).replaceAll(", ","+++").split(",");

  var printAllBox = ui.alert("PRINT ALL MILEAGE LOGS", "Attempting to generate pdfs for everybody who drove this week.\n\nThis might take a while, so don't touch anything.",ui.ButtonSet.OK_CANCEL);

  if (printAllBox === ui.Button.OK) {
    var timer = 0;
    var counter = 0;
    // Initialize the array outside the loop
    var pdfBlobs = [];

    for (var i = 0; i < workerList.length; i++) {
      workerList[i] = workerList[i].replaceAll("+++",", ");
      ss.getRange("E2").setValue(workerList[i]);
  
      if (ss.getRange("PRINT!I10").getValue() == "") {
        continue;  // Skip empty entries
      }
              
      var sheet;
      var email = Session.getEffectiveUser().getEmail();
      var subject = "Multiple Mileage Logs";
      var body = "Mileage logs generated."

      // Look to see if it's a two pager or not
      if (ss.getRange("PRINT!G1").getValue() == "PAGE 1 of 1") {
        sheet = ss.getSheetByName("PRINT");
      } else {
        sheet = ss.getSheetByName("PRINT - 2 PAGE");
      }

      // show the print sheet
      sheet.showSheet();

      const fileName = ss.getRange("PRINT!J2").getValue() + ".pdf";
              
      // Base URL
      var exportURL = "https://docs.google.com/spreadsheets/d/SS_ID/export?".replace("SS_ID", ss.getId());

      var url_ext = 'exportFormat=pdf&format=pdf'
        + '&size=letter'
        + '&portrait=false'
        + '&fith=true&'
        + "&gridlines=false" 
        + "&top_margin=0.25" 
        + "&bottom_margin=0.25" 
        + "&left_margin=0.5" 
        + "&right_margin=0.5" 
        + '&sheetnames=false&printtitle=false'
        + '&pagenumbers=false&gridlines=false'
        + '&fzr=false'
        + '&gid=';

      var token = ScriptApp.getOAuthToken();

      // Convert your specific sheet to blob
      var response = UrlFetchApp.fetch(exportURL + url_ext + sheet.getSheetId(), {
        headers: { 'Authorization': 'Bearer ' + token }
      });

      //  And then hide the printsheet
      sheet.hideSheet();

      // Convert the response to a blob and store in our array
      var pdf = response.getBlob().setName(fileName);
      // Add the PDF blob to our array
      pdfBlobs.push(pdf);

      // Check to see if they have the right folders in place, and if not, create them
      var folder1 = "gripPDFs"
      var folder2 = ss.getRange("PRINT!C5").getValue()
      var folder3 = "mileage logs"
      var folder4 = ss.getRange("PRINT!K2").getValue()

      /* Find the first level folder, create if the folder does not exist */
      var folders = DriveApp.getFoldersByName(folder1);
      var firstLevelFolder = (folders.hasNext()) ? folders.next() : DriveApp.createFolder(folder1);
                                    
      /* Layer2 */
      folders = DriveApp.getFoldersByName(folder2);
      var secondlevelFolder = (folders.hasNext()) ? folders.next() : firstLevelFolder.createFolder(folder2); 

      /* Layer3 */
      folders = DriveApp.getFoldersByName(folder3);
      var thirdlevelFolder = (folders.hasNext()) ? folders.next() : secondlevelFolder.createFolder(folder3); 

      /* Layer4 */
      folders = DriveApp.getFoldersByName(folder4);
      var finalfolder = (folders.hasNext()) ? folders.next() : thirdlevelFolder.createFolder(folder4); 

      // Save to Drive
      finalfolder.createFile(pdf);

      counter = (counter + 1);
      timer = timer + 1;
      if (timer > 3) { 
        // Sleep to appease the Google Lag Police
        SpreadsheetApp.flush();
        Utilities.sleep(5000);
        SpreadsheetApp.flush();
        timer = 0;
      }
    }

    // Send email with all PDFs attached
    GmailApp.sendEmail(email, subject, body, {
      attachments: pdfBlobs  // Pass the array of blobs directly
    });

    var pdfResults = ui.alert("PROCESS COMPLETE", 
      counter + " total logs printed. The files have been emailed to you and can be found under " +
      folder1 + "/" + folder2 + "/" + folder3 + "/" + folder4 + " in your Google Drive.",
      ui.ButtonSet.OK);
  }
}

我移动了 pdfBlobs 循环外的数组初始化。还使用以下命令将每个pdf blob推送到数组中 pdfBlobs.push(pdf) 并在将附件数组传递给时删除了方括号 GmailApp.sendEmail() 。我添加了一个continue语句来跳过空条目,并清理了一些代码格式结构。

问题是,每次通过循环时都会创建一个新数组,并试图将该数组附加到另一个数组中 ([pdfCluster])

代码现在要做的是:

创建所有pdf
将它们存储在单个数组中
保存它们以供驾驶
发送一封附有所有pdf的电子邮件。

试试这个,看看它是否有效。

更新:

关于你在下面的评论中提到的新问题,我对代码做了一些更改,你可以在下面找到:

function printMultiple() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ui = SpreadsheetApp.getUi();

  var lastRow = ss.getRange("AN:AN").getValues().filter(String).length;
  var targetRange = ss.getSheetByName("Show 3 Mileage Form").getRange(1,40,lastRow,1);
  var workerList = String(targetRange.getValues()).replaceAll(", ","+++").split(",");

  var printAllBox = ui.alert("PRINT ALL MILEAGE LOGS", "Attempting to generate pdfs for everybody who drove this week.\n\nThis might take a while, so don't touch anything.",ui.ButtonSet.OK_CANCEL);

  if (printAllBox === ui.Button.OK) {
    var timer = 0;
    var counter = 0;
    // Initialize the array outside the loop
    var pdfAttachments = [];

    for (var i = 0; i < workerList.length; i++) {
      workerList[i] = workerList[i].replaceAll("+++",", ");
      ss.getRange("E2").setValue(workerList[i]);
  
      if (ss.getRange("PRINT!I10").getValue() == "") {
        continue;  // Skip empty entries
      }
              
      var sheet;
      var email = Session.getEffectiveUser().getEmail();
      var subject = "Multiple Mileage Logs";
      var body = "Mileage logs generated."

      // Look to see if it's a two pager or not
      if (ss.getRange("PRINT!G1").getValue() == "PAGE 1 of 1") {
        sheet = ss.getSheetByName("PRINT");
      } else {
        sheet = ss.getSheetByName("PRINT - 2 PAGE");
      }

      // show the print sheet
      sheet.showSheet();

      const fileName = ss.getRange("PRINT!J2").getValue() + ".pdf";
              
      // Base URL
      var exportURL = "https://docs.google.com/spreadsheets/d/SS_ID/export?".replace("SS_ID", ss.getId());

      var url_ext = 'exportFormat=pdf&format=pdf'
        + '&size=letter'
        + '&portrait=false'
        + '&fith=true&'
        + "&gridlines=false" 
        + "&top_margin=0.25" 
        + "&bottom_margin=0.25" 
        + "&left_margin=0.5" 
        + "&right_margin=0.5" 
        + '&sheetnames=false&printtitle=false'
        + '&pagenumbers=false&gridlines=false'
        + '&fzr=false'
        + '&gid=';

      var token = ScriptApp.getOAuthToken();

      // Convert your specific sheet to blob
      var response = UrlFetchApp.fetch(exportURL + url_ext + sheet.getSheetId(), {
        headers: { 'Authorization': 'Bearer ' + token }
      });

      //  And then hide the printsheet
      sheet.hideSheet();

      // Convert the response to a blob
      var pdf = response.getBlob().setName(fileName);

      // Create attachment object with all necessary properties
      var attachmentObj = {
        fileName: fileName,
        content: pdf.getBytes(),
        mimeType: "application/pdf"
      };

      // Add the attachment object to our array
      pdfAttachments.push(attachmentObj);

      // Check to see if they have the right folders in place, and if not, create them
      var folder1 = "gripPDFs"
      var folder2 = ss.getRange("PRINT!C5").getValue()
      var folder3 = "mileage logs"
      var folder4 = ss.getRange("PRINT!K2").getValue()

      /* Find the first level folder, create if the folder does not exist */
      var folders = DriveApp.getFoldersByName(folder1);
      var firstLevelFolder = (folders.hasNext()) ? folders.next() : DriveApp.createFolder(folder1);
                                    
      /* Layer2 */
      folders = DriveApp.getFoldersByName(folder2);
      var secondlevelFolder = (folders.hasNext()) ? folders.next() : firstLevelFolder.createFolder(folder2); 

      /* Layer3 */
      folders = DriveApp.getFoldersByName(folder3);
      var thirdlevelFolder = (folders.hasNext()) ? folders.next() : secondlevelFolder.createFolder(folder3); 

      /* Layer4 */
      folders = DriveApp.getFoldersByName(folder4);
      var finalfolder = (folders.hasNext()) ? folders.next() : thirdlevelFolder.createFolder(folder4); 

      // Save to Drive
      finalfolder.createFile(pdf);

      counter = (counter + 1);
      timer = timer + 1;
      if (timer > 3) { 
        // Sleep to appease the Google Lag Police
        SpreadsheetApp.flush();
        Utilities.sleep(5000);
        SpreadsheetApp.flush();
        timer = 0;
      }
    }

    // Send email with all PDFs attached using the attachment objects
    GmailApp.sendEmail(email, subject, body, {
      attachments: pdfAttachments
    });

    var pdfResults = ui.alert("PROCESS COMPLETE", 
      counter + " total logs printed. The files have been emailed to you and can be found under " +
      folder1 + "/" + folder2 + "/" + folder3 + "/" + folder4 + " in your Google Drive.",
      ui.ButtonSet.OK);
  }
}

我所做的更改是:

我创造并使用 pdfAttachments 而不是 pdf-blobs
对于每个pdf,我现在创建 fileName 这是pdf文件的名称; content 这是使用的pdf字节 pdf.getBytes() ;以及 mimeType 设置为application/pdf
将此附件对象推送到 pdf附件
通过 pdf附件 到 GmailApp.sendEmail()

这种方法应该保留您在单个附件方法中使用的附件属性,同时仍然允许多个附件。这个 文件名 , 内容 ,以及 mimeType 属性现在应该与之前的实现保持一致。

这有望解决附件问题。