创建谷歌脚本，将工作表保存为pdf到谷歌驱动器中的指定文件夹

Question

因此，多亏了其他成员，我能够拼凑出一个脚本，除了一个小问题之外，还能做我需要的事情。它不是将单个工作表保存为指定文件夹中的pdf格式，而是保存整个工作簿。我尝试了多种变体来保存我想要的表格(名为JSA(，但它要么给我一条错误消息，要么继续下载整个电子表格。我确信我缺少了一些简单的东西，但我对脚本的精通程度不如编写宏，所以我陷入了困境。代码的第一部分，将其下载为PDF就像一个魅力，这是我希望它将PDF保存到指定文件夹的最后一部分，这就是问题所在。如有任何协助，我们将不胜感激。

function downloadPdf() {
var ss = SpreadsheetApp.getActive(),
id = ss.getId(),
sht = ss.getActiveSheet(),
shtId = sht.getSheetId(),
url =
'https://docs.google.com/spreadsheets/d/' +
id +
'/export' +
'?format=pdf&gid=' +
shtId
+ '&size=letter' // paper size legal / letter / A4
+ '&portrait=false' // orientation, false for landscape
+ '&scale=2'  // 1= Normal 100% / 2= Fit to width / 3= Fit to height / 4= Fit to Page
+ '&fitw=true'                   // fit to width, false for actual size
+ '&top_margin=0.25'              // All four margins must be set!
+ '&bottom_margin=0.25'           // All four margins must be set!
+ '&left_margin=0.25'             // All four margins must be set!
+ '&right_margin=0.25'            // All four margins must be set!
+ '&sheetnames=false&printtitle=false' // hide optional headers and footers
+ '&pagenum=false&gridlines=false' // hide page numbers and gridlines
+ '&fzr=false' // do not repeat row headers (frozen rows) on each page
+ '&horizontal_alignment=CENTER' //LEFT/CENTER/RIGHT
+ '&vertical_alignment=TOP'; //TOP/MIDDLE/

var val = SpreadsheetApp.getActive().getRange('H2').getValues();//custom pdf name here 
var val2= Utilities.formatDate(SpreadsheetApp.getActive().getRange("N2").getValue(), ss.getSpreadsheetTimeZone(), "MM/dd/YY");
var val3= " - "
val += val3 += val2 += '.pdf';
//can't download with a different filename directly from server
//download and remove content-disposition header and serve as a dataURI
//Use anchor tag's download attribute to provide a custom filename
var res = UrlFetchApp.fetch(url, {
headers: { Authorization: 'Bearer ' + ScriptApp.getOAuthToken() },
});
SpreadsheetApp.getUi().showModelessDialog(
HtmlService.createHtmlOutput(
'<a target ="_blank" download="' +
val +
'" href = "data:application/pdf;base64,' +
Utilities.base64Encode(res.getContent()) +
'">Click here</a> to download, if download did not start automatically' +
'<script> 
var a = document.querySelector("a"); 
a.addEventListener("click",()=>{setTimeout(google.script.host.close,10)}); 
a.click(); 
</script>'
).setHeight(50),
'Downloading PDF..'
);
const folderName = `Test`;
const fileNamePrefix =  val += val3 += val2 += '.pdf';
var JSA = SpreadsheetApp.getActiveSpreadsheet();
var s = JSA.getSheetByName("JSA");
DriveApp.getFoldersByName(folderName)
.next()
.createFile(SpreadsheetApp.getActiveSpreadsheet()
.getBlob()
.getAs(`application/pdf`)
.setName(`${fileNamePrefix}`));                    
}

Answer 1

我相信您的目标如下。

• 您想要创建一个PDF文件，该文件使用脚本的第一部分下载到Google Drive上的特定文件夹中

在这种情况下，下面的修改如何？我认为res可能会被使用。

发件人：

var JSA = SpreadsheetApp.getActiveSpreadsheet();
var s = JSA.getSheetByName("JSA");
DriveApp.getFoldersByName(folderName)
.next()
.createFile(SpreadsheetApp.getActiveSpreadsheet()
.getBlob()
.getAs(`application/pdf`)
.setName(`${fileNamePrefix}`)); 

收件人：

DriveApp.getFoldersByName(folderName)
.next()
.createFile(res.getBlob().setName(`${fileNamePrefix}`));

在这个修改中，假定sht = ss.getActiveSheet(),是JSA片。如果活动工作表不是JSA工作表，请按如下方式进行修改。

• 收件人：

  const url2 =
  'https://docs.google.com/spreadsheets/d/' +
  id +
  '/export' +
  '?format=pdf&gid=' +
  ss.getSheetByName("JSA").getSheetId()
  + '&size=letter' // paper size legal / letter / A4
  + '&portrait=false' // orientation, false for landscape
  + '&scale=2'  // 1= Normal 100% / 2= Fit to width / 3= Fit to height / 4= Fit to Page
  + '&fitw=true'                   // fit to width, false for actual size
  + '&top_margin=0.25'              // All four margins must be set!
  + '&bottom_margin=0.25'           // All four margins must be set!
  + '&left_margin=0.25'             // All four margins must be set!
  + '&right_margin=0.25'            // All four margins must be set!
  + '&sheetnames=false&printtitle=false' // hide optional headers and footers
  + '&pagenum=false&gridlines=false' // hide page numbers and gridlines
  + '&fzr=false' // do not repeat row headers (frozen rows) on each page
  + '&horizontal_alignment=CENTER' //LEFT/CENTER/RIGHT
  + '&vertical_alignment=TOP'; //TOP/MIDDLE/
  const res2 = UrlFetchApp.fetch(url2, {
  headers: { Authorization: 'Bearer ' + ScriptApp.getOAuthToken() },
  });
  DriveApp.getFoldersByName(folderName)
  .next()
  .createFile(res2.getBlob().setName(`${fileNamePrefix}`));
  

参考：

• fetch(url，params(