时间戳

首页 > 解决方案 > Google 表格将每张表格转换为 PDF，但不保留格式。我怎样才能使这项工作？

google-apps-script - Google 表格将每张表格转换为 PDF，但不保留格式。我怎样才能使这项工作？

问题描述

我们正在尝试使用以下代码将单个工作表转换为单个 PDF，并将 PDF 通过电子邮件发送到其中包含的电子邮件地址：

   function myFunction(){

    var ss = SpreadsheetApp.openById("YOUR_SPREADSHEET_ID");
    var sheets = ss.getSheets();

    var B2Values = [];
    sheets.forEach(function(elt,index){
    //Get all  your email adresses
    B2Values.push(sheets[index].getRange(2,2).getValue());
    });

    //For each sheet in your Spreadsheet, it create a temporary Spreadsheet
 //who got only one sheet, with your sheet values, 
// transform it as pdf, send the pdf by email to your B2 email adress and delete
//the temporary Spreadsheet

    sheets.forEach(function(elt, index){
    var temporarySS = SpreadsheetApp.create("NAME_OF_THE_FILE_WHO_WILL_BE_SENT");
    var temporaryId = temporarySS.getId();
    var dataToMove = sheets[index].getRange(1,1,sheets[index].getLastRow(),sheets[index].getLastColumn()).getValues();
    var openingTemporarySS = SpreadsheetApp.openById(temporaryId);
      dataToMove.forEach(function(elt){
      openingTemporarySS.appendRow(elt);
      })
     MailApp.sendEmail({
        to: B2Values[index],
        subject: "YOUR_SUBJECT",
        attachments: [openingTemporarySS.getAs('application/pdf')]
    })

    DriveApp.getFileById(temporaryId).setTrashed(true);

    });
    }

效果很好！但它不保留合并的单元格和一般格式。关于如何完成这项工作的任何想法或想法？

标签： google-apps-script

解决方案

  • 您希望将 Google 电子表格中的每张表格发送到从单元格“B2”作为 PDF 文件检索到的每封电子邮件。
  • 您想保留工作表的单元格格式。
  • 您想使用 Google Apps 脚本实现此目的。

如果我的理解是正确的，这个答案怎么样？请认为这只是几个可能的答案之一。

修改点：

  • In your script, new Spreadsheet is created and only the values of data range are copied to it. By this, the cell format is not copied.
  • In order to copy the values and format and create the sheet as PDF data, in your case, I think that hideSheet() can be used. For example, there are 2 sheets in a Spreadsheet, and when 1st tab is hidden by hideSheet() and the Spreadsheet is exported as the PDF data, only 2nd tab is exported as the PDF data. I think that this can be used for your situation. And also, this can also reduce the process cost.

When above points are reflected to your script, it becomes as follows.

Modified script:

function myFunction(){
  var ss = SpreadsheetApp.openById("YOUR_SPREADSHEET_ID");
  var sheets = ss.getSheets();
  var B2Values = [];
  sheets.forEach(function(elt,index){
    //Get all  your email adresses
    B2Values.push(sheets[index].getRange(2,2).getValue());  // You can also use B2Values.push(elt.getRange(2,2).getValue());
  });

  // I modified below script.
  B2Values.forEach(function(e, i) {
    sheets.forEach(function(sheet, j) {if (i != j) sheet.hideSheet()});
    // SpreadsheetApp.flush(); // By the situation, this line might be required.
    MailApp.sendEmail({
      to: e,
      subject: "YOUR_SUBJECT",
      attachments: [ss.getBlob()]
    });
    sheets.forEach(function(sheet) {sheet.showSheet()});
  });
}

References:

If I misunderstood your question and this was not the direction you want, I apologize.

推荐阅读