通过电子邮件仅发送一张或pdf格式的活动表格 [英] Sending only one sheet or active sheet as pdf via email

问题描述

您好，我一直在尝试编写我的第一个脚本，以便仅从我的一个google文档中发送一张表格，但是每次我最终都发送所有表格时，却没有这样做.该文档每天都有一个主页，其中有电子邮件列表，然后有7张纸，分别称为星期一，星期二等

Hello I have been trying to write my first script to send just one of the sheet from one of my google document but everytime I end up sending all the sheets instead. The document has a main page daily where i have the list of emails and then 7 sheets called monday, tuesday, etc

这是我使用的代码-您能帮忙吗?

This is the code i use - can you help?

function emailActivesheetAsPDF() {
  DocumentApp.getActiveDocument();
  DriveApp.getFiles();

  const ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1_3VpJaFliJVYt1JL3cLSKw2ftsmsLeDjznHJ66-wq9w/edit");

  const value = ss.getSheetByName("Daily").getRange("B5").getValue();
  const email = value.toString();

  const subject = 'Yesterday Handover';

  const body = "Please find attached yesterday handover";

  const url = 'https://docs.google.com/spreadsheets/d/1_3VpJaFliJVYt1JL3cLSKw2ftsmsLeDjznHJ66-wq9w/export?';

  const exportOptions =
    'exportFormat=pdf&format=pdf'
    '&size=legal'
    '&portrait=true'
    '&fitw=false'
    '&sheetnames=false&printtitle=false'
    '&pagenumbers=false&gridlines=false'
    '&fzr=false'
    '&gid=1263775066';
 
  var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};
  
  var response = UrlFetchApp.fetch(url+exportOptions, params).getBlob();
  
    GmailApp.sendEmail(email, subject, body, {
      htmlBody: body,
      attachments: [{
            fileName: "Handover" + ".pdf",
            content: response.getBytes(),
            mimeType: "application/pdf"
        }]
    });
}

推荐答案

说明/问题:

• 您忘记了连接 exportOptions 变量中的选项，因此 exportOptions 仅获得& size = legal 的值并忽略其余参数，包括您指定工作表ID的 gid .一种解决方案是通过 + 串联这些选项.

  Explanation / Issue:

  • You forgot to concatenate the options in the exportOptions variable and therefore exportOptions gets only the value of &size=legal and ignores the rest of the parameters including gid which you specify the sheet id. One solution is to concatenate the options via +.

    我还改进了您的代码，使其更加灵活.例如，您可以在变量 sheetToPrint 然后在 GAS 中通过其名称来定义它，而不必通过代码中的 gid 来定义它.将获取其 gid 并将其用作pdf的导出选项.

    I also improved your code to make it more flexible. For example, instead of defining a sheet by its gid in the code, you can define it by its name in the variable sheetToPrint and then GAS will get its gid and use it as an export option for the pdf.

    function emailActivesheetAsPDF() {
      const sheetToPrint = "Daily"; // name of the sheet to print
      const ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1_3VpJaFliJVYt1JL3cLSKw2ftsmsLeDjznHJ66-wq9w/edit");
      const ssID = ss.getId();
      const email = ss.getSheetByName("Daily").getRange("B5").getValue();
      const subject = 'Yesterday Handover';
      const body = "Please find attached yesterday handover";
      const shID = ss.getSheetByName(sheetToPrint).getSheetId();
      const url = "https://docs.google.com/spreadsheets/d/"+ ssID + "/export?format=pdf&id="+ssID;
      const exportOptions =
        '&size=legal'+
        '&portrait=true'+
        '&fitw=false'+
        '&sheetnames=false&printtitle=false'+
        '&pagenumbers=false&gridlines=false'+
        '&fzr=false'+
        '&gid='+shID; 
      var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};
      
      var response = UrlFetchApp.fetch(url+exportOptions, params).getBlob();
      
        GmailApp.sendEmail(email, subject, body, {
          htmlBody: body,
          attachments: [{
                fileName: "Handover" + ".pdf",
                content: response.getBytes(),
                mimeType: "application/pdf"
            }]
        });
    }
    

    这篇关于通过电子邮件仅发送一张或pdf格式的活动表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！