延续我之前的question,我想把Google Sheet的两个标签合并成一个pdf.@Tanaike提出了一个解决方案,根据需要在这个sheet上发挥了作用.以下是建议的javascript library代码:
function myFunction(e) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form responses 1");
SpreadsheetApp.flush();
const output = [
{sheetName: "Quan", range: "A2:Q28"},
{sheetName: "Qual", range: "A1:I27"},
];
var ss = SpreadsheetApp.getActiveSpreadsheet();
const url_base = ss.getUrl().replace(/edit$/,'');
const headers = {authorization: "Bearer " + ScriptApp.getOAuthToken()};
const data = output.map(({sheetName, range}) => {
const s = ss.getSheetByName(sheetName);
const gid = s.getSheetId();
const r = s.getRange(range);
const r1 = r.getRow() - 1;
const c1 = r.getColumn() - 1;
const r2 = r1 + r.getNumRows();
const c2 = c1 + r.getNumColumns();
const url = `${url_base}export?exportFormat=pdf&format=pdf`
+ '&size=letter'
+ '&portrait=true'
+ '&fitw=true'
+ '&sheetnames=false&printtitle=false&pagenumbers=false'
+ '&gridlines=false'
+ '&fzr=false'
+ `&gid=${gid}&r1=${r1}&c1=${c1}&r2=${r2}&c2=${c2}`;
const res = UrlFetchApp.fetch(url, {headers, muteHttpExceptions: true});
if (res.getResponseCode() != 200) {
console.log(res.getContentText())
}
return new Uint8Array(res.getContent());
});
const setTimeout = function(f, t) {
Utilities.sleep(t);
return f(); }
const pdfDoc = await PDFLib.PDFDocument.create();
for (let i = 0; i < data.length; i++) {
const pdfData = await PDFLib.PDFDocument.load(data[i]);
const pages = await pdfDoc.copyPages(pdfData, [...Array(pdfData.getPageCount())].map((_, i) => i));
pages.forEach(page => pdfDoc.addPage(page));
}
const bytes = await pdfDoc.save();
var folder = DriveApp.getFolderById('1t6ns346GNX_gP-uGkwE43egGhTCaWgKO');
var pdf = folder.createFile(Utilities.newBlob([...new Int8Array(bytes)], MimeType.PDF, "sample1.pdf"));
console.log(pdf.getUrl())
sheet.getRange("AW7").setValue(pdf.getUrl());
}
问题是这个脚本只适用于这Spreadsheet.如果我复制它或使用url访问另一个Spreadsheet,则它会显示以下错误消息:
ReferenceError:未定义setTimeout(匿名)@JS Library.gs:15 X@JS Library.gs:15(匿名)@JS Library.gs:15 (匿名)@JS Library.gs:15(匿名)@JS Library.gs:15 a@js Library.gs:15
在代码的第6行,语句是SpreadsheetApp.getActiveSpreadsheet()
,我想使用SpreadsheetApp.openByUrl()
.任何指导意见都将不胜感激.