如何将我的谷歌工作表的工作pdf附加到电子邮件草稿中

Question

我有一个脚本，用来打开、重命名谷歌工作表，并将其导出为pdf。然后，该表单将被添加到电子邮件草稿中。我已经使用这个脚本两年了，但由于某种原因，pdf附件将不再打开。

我得到的错误消息是"；Adobe Acrobat Reader无法打开[文件名.pdf]，因为它不是受支持的文件类型，或者文件已损坏"我直接转到谷歌工作表并手动导出为pdf，我可以毫无问题地打开它，所以它肯定与脚本有关。

请你复习一下下面的剧本，看看是否有我可以使用的作品？

// Note that the spreadsheet is NOT physically opened on the client side.
// It is opened on the server only (for modification by the script).
var ss = SpreadsheetApp.openByUrl(Logger.getLog());
var first = ss.getSheetByName("BHC AP Vendor Payment");
var vendorname = first.getRange(2, 1).getValue();
var paymentdate = first.getRange(2, 22).getDisplayValue();
// renames the google sheet to the data that is in 2nd Row, 1st Column
ss.rename('BHC '+vendorname+' Payment ['+paymentdate+']');


var sheet = ss.getSheetByName('BHC AP Vendor Payment'); //returns the sheet named 'BHC AP Vendor Payment'
sheet.setName('BHC '+vendorname+' Payment ['+paymentdate+']');
var lastRow = sheet.getLastRow(); //returns integer last row

var url = ss.getUrl();
//remove the trailing 'edit' from the url
url = url.replace(/edit$/, '');
//additional parameters for exporting the sheet as a pdf
var url_ext = 'export?exportFormat=pdf&format=pdf' + //export as pdf
//below parameters are optional...
'&size=letter' + //paper size
'&portrait=false' + //orientation, false for landscape
'&fitw=true' + //fit to width, false for actual size
'&sheetnames=false&printtitle=false&pagenumbers=false' + //hide optional headers and footers
'&gridlines=false' + //hide gridlines
'&fzr=false' + //do not repeat row headers (frozen rows) on each page
'&gid=' + sheet.getSheetId(); //the sheet's Id
var token = ScriptApp.getOAuthToken();
var response = UrlFetchApp.fetch(url + url_ext, {
headers: {
'Authorization': 'Bearer ' + token
}
});
var blob = response.getBlob().setName(ss.getName() + '.pdf');

// Create a draft email with a file from Google Drive attached as a PDF.
var doc = DocumentApp.openById(*sample document*);
var body = doc.getBody();
var bodytext = body.getText();
var apEmail = *sample email address*;
var subject = 'Payment ['+vendorname+']';


GmailApp.createDraft('', subject, bodytext, {
cc: apEmail,
from: apEmail,
attachments: [blob
]

谢谢！

Answer 1

修改：

你能试一下以下两个修改点吗：

• 代替：

  url = url.replace(/edit$/, '');
  

  用途：

  url = url.replace(//edit.*$/, '');
  

---

• 而不是：

  var url_ext = 'export?...'
  

  用途：

  var url_ext = '/export?...'
  

---

完整代码：

var ss = SpreadsheetApp.openByUrl(Logger.getLog());
var first = ss.getSheetByName("BHC AP Vendor Payment");
var vendorname = first.getRange(2, 1).getValue();
var paymentdate = first.getRange(2, 22).getDisplayValue();
// renames the google sheet to the data that is in 2nd Row, 1st Column
ss.rename('BHC '+vendorname+' Payment ['+paymentdate+']');


var sheet = ss.getSheetByName('BHC AP Vendor Payment'); //returns the sheet named 'BHC AP Vendor Payment'
sheet.setName('BHC '+vendorname+' Payment ['+paymentdate+']');
var lastRow = sheet.getLastRow(); //returns integer last row


//remove the trailing 'edit' from the url
url = url.replace(//edit.*$/, '');

//additional parameters for exporting the sheet as a pdf
var url_ext = '/export?exportFormat=pdf&format=pdf' + //export as pdf
//below parameters are optional...
'&size=letter' + //paper size
'&portrait=false' + //orientation, false for landscape
'&fitw=true' + //fit to width, false for actual size
'&sheetnames=false&printtitle=false&pagenumbers=false' + //hide optional headers and footers
'&gridlines=false' + //hide gridlines
'&fzr=false' + //do not repeat row headers (frozen rows) on each page
'&gid=' + sheet.getSheetId(); //the sheet's Id
var token = ScriptApp.getOAuthToken();
var response = UrlFetchApp.fetch(url + url_ext, {
headers: {
'Authorization': 'Bearer ' + token
}
});
var blob = response.getBlob().getAs('application/pdf').setName(ss.getName() + '.pdf');

// Create a draft email with a file from Google Drive attached as a PDF.
var doc = DocumentApp.openById(*sample document*);
var body = doc.getBody();
var bodytext = body.getText();
var apEmail = *sample email address*;
var subject = 'Payment ['+vendorname+']';


GmailApp.createDraft('', subject, bodytext, {
cc: apEmail,
from: apEmail,
attachments: [blob
]})