var res1 = UrlFetchApp.fetch(url1,para);
var res2 = UrlFetchApp.fetch(url2,para)
The above url1&url2 is the link to export a pdf from google sheet. How to generate a blob contain res1 and res2 and then I could generate a pdf file with res1 and res blob using
DriveApp.getFoldersByName("a").next().createFile(blobs);
I tried to use the following and it failed.
var blobs = res1.getBlob() res2.getBlob
const url1 = "https://docs.google.com/spreadsheets/d/SHEETID/export?format=pdf&gid=788671078"
const url2= "https://docs.google.com/spreadsheets/d/SHEETID/export?format=pdf&gid=788671070"
var res1 = UrlFetchApp.fetch(url1,para); var res2 = UrlFetchApp.fetch(url2,para)
DriveApp.getFoldersByName("a").next().createFile(res1.getBlob());
DriveApp.getFoldersByName("a").next().createFile(res2.getBlob());
The above code can generate 2 pdf files. I want to generate a pdf including these 2 pdf file pages.
CodePudding user response:
Issue and workaround:
In your situation, I think that merging those blobs cannot be directly achieved. For example, when you can use the external API, I think that it can be achieved. If you cannot use the external API, I would like to consider whether the Spreadsheets of url1 and url2 can be merged. By this, I asked your current script in my comment.
From your current script, I thought that in this case, 2 sheets might be able to be exported as one PDF file. In this answer, I would like to propose the following sample script.
Sample script 1:
From your script of const url1 = "https://docs.google.com/spreadsheets/d/SHEETID/export?format=pdf&gid=788671078"
and const url2= "https://docs.google.com/spreadsheets/d/SHEETID/export?format=pdf&gid=788671070"
, it seems that SHEETID
is the same for 2 sheets. In this case, how about the following script? This script shows only the 2 sheets you want and the Spreadsheet is exported as a PDF file. By this, the PDF file includes only the 2 sheets.
function myFunction() {
const gids = ["788671078", "788671070"]; // These are from your URLs.
const spreadsheetId = "SHEETID"; // This is from your URLs.
const sheets = SpreadsheetApp.openById(spreadsheetId).getSheets();
sheets.forEach(s => {
s[!gids.includes(s.getSheetId().toString()) ? "hideSheet" : "showSheet"]();
});
const url = `https://docs.google.com/spreadsheets/d/${spreadsheetId}/export?format=pdf`;
const res = UrlFetchApp.fetch(url, {headers: {authorization: "Bearer " ScriptApp.getOAuthToken()}});
DriveApp.getFoldersByName("a").next().createFile(res.getBlob());
sheets.forEach(s => s.showSheet());
}
Sample script 2:
In this sample script, it supposes that your 2 sheets are put in different 2 Spreadsheet. 2 sheets are put to a temporal Google Spreadsheet and the Spreadsheet is exported as a PDF file. By this, the PDF file includes only the 2 sheets.
function myFunction() {
const gids = [{spreadsheetId: "SHEETID1", gid: "788671078"}, {spreadsheetId: "SHEETID2", gid: "788671070"}]; // These are from your URLs.
const temp = SpreadsheetApp.create("temp");
const ssId = temp.getId();
gids.forEach(({spreadsheetId, gid}) => {
const ss = SpreadsheetApp.openById(spreadsheetId)
ss.getSheets().forEach(s => {
if (s.getSheetId() == gid) {
const ts = s.copyTo(ss);
const r = ts.getDataRange();
r.copyTo(r, {contentsOnly: true});
ts.copyTo(temp);
ss.deleteSheet(ts);
}
});
});
temp.deleteSheet(temp.getSheets()[0]);
SpreadsheetApp.flush();
const url = `https://docs.google.com/spreadsheets/d/${ssId}/export?format=pdf`;
const res = UrlFetchApp.fetch(url, {headers: {authorization: "Bearer " ScriptApp.getOAuthToken()}});
DriveApp.getFoldersByName("a").next().createFile(res.getBlob());
DriveApp.getFileById(ssId).setTrashed(true);
}
References:
CodePudding user response:
This can not be done natively.
The Blob object is a native Apps Script data interchange object and are not addable/concatenatable with the
operator in the way you are using it.
You will therefore have to use either a third party API which completes the merge, or try to refactor an existing PDF merge library written for a different JavaScript environment (not recommended).
There are many APIs which offer PDF merging services. I can't recommend any one specifically but a Google search gives many results of some examples (some are even well documented!).