将电子表格中的表格以pdf格式保存在相同的谷歌驱动器文件夹中

Question

我正在尝试制作一个代码，从谷歌驱动器文件夹中获取所有文件，然后将所有文件转换为excel并保存在同一文件夹中，还可以从每个电子表格的第二张"V2"中制作pdf文件并保存在相同文件夹中。

我成功地创建了excel文件夹，但pdf部分不起作用。我从许多不同的来源复制了代码。

function Generator(){
//Factors to change every month
var folderID = '14Bz9LLAiv2BgsT4bowxUtMfG2AsTxk91';


// Loop through all the files and save as excel and pdf.
var reportsFolder = DriveApp.getFolderById(folderID);
var files = reportsFolder.getFiles();
var TAB_NAME = 'V2' //For pdf
while(files.hasNext()) {
var file = files.next();
var bnkFile = SpreadsheetApp.getActiveSpreadsheet();
var name = file.getName();
//Creating Excel files
var url = "https://docs.google.com/spreadsheets/d/" + file.getId() + "/export?format=xlsx&access_token=" + ScriptApp.getOAuthToken();
var blob = UrlFetchApp.fetch(url).getBlob().setName(name + ".xlsx"); // Modified
reportsFolder.createFile(blob);
//Creating PDF files
var VName =  name.replace("REPORT", "V2");
var V2Sheet = bnkFile.getSheetByName(TAB_NAME);
var url2 = "https://docs.google.com/spreadsheets/d/" + V2Sheet.getId() + "/export?exportFormat=pdf&format=pdf" + ScriptApp.getOAuthToken();
var blob2 = UrlFetchApp.fetch(url2).getBlob().setName(VName + ".pdf"); // Modified
reportsFolder.createFile(blob2);

}
}

有没有生成PDF的简单解决方案？

Answer 1

说明：

您需要指定电子表格文件的id，也需要指定工作表本身的gid。

更换

var url2 = "https://docs.google.com/spreadsheets/d/" + V2Sheet.getId() + "/export?exportFormat=pdf&format=pdf" + ScriptApp.getOAuthToken();

带有

var url2 = "https://docs.google.com/spreadsheets/d/"+ file.getId() + "/export?format=pdf&id="+file.getId()+"&gid="+V2Sheet.getSheetId();

---

替换

var bnkFile = SpreadsheetApp.getActiveSpreadsheet();

带有

var bnkFile = SpreadsheetApp.openById(file.getId());

以得到CCD_ 4的CCD_。您当前的代码获得活动的spreadsheet，它是该脚本所附的电子表格。

在解决方案中定义requestData对象时，也要使用它。

---

解决方案：

function Generator(){
//Factors to change every month
var folderID = '14Bz9LLAiv2BgsT4bowxUtMfG2AsTxk91';

// Loop through all the files and save as excel and pdf.
var reportsFolder = DriveApp.getFolderById(folderID);
var files = reportsFolder.getFiles();
var TAB_NAME = 'V2' //For pdf
while(files.hasNext()) {
var file = files.next();

if(file.getMimeType()=='application/vnd.google-apps.spreadsheet'){

var bnkFile = SpreadsheetApp.openById(file.getId());
var name = file.getName();
//Creating Excel files
var url = "https://docs.google.com/spreadsheets/d/" + file.getId() + "/export?format=xlsx&access_token=" + ScriptApp.getOAuthToken();
var blob = UrlFetchApp.fetch(url).getBlob().setName(name + ".xlsx"); // Modified
reportsFolder.createFile(blob);
//Creating PDF files
var VName =  name.replace("REPORT", "V2");
var V2Sheet = bnkFile.getSheetByName(TAB_NAME);
var requestData = {"method": "GET", "headers":{"Authorization":"Bearer "+ScriptApp.getOAuthToken()}};
var url2 = "https://docs.google.com/spreadsheets/d/"+ file.getId() + "/export?format=pdf&id="+file.getId()+"&gid="+V2Sheet.getSheetId();
var blob2 = UrlFetchApp.fetch(url2,requestData).getBlob().setName(VName + ".pdf"); // Modified
var pdfFile = reportsFolder.createFile(blob2);
var pdfUrl = pdfFile.getUrl(); // <- pdfUrl will give you the url of the pdf file.
}
}
}