Home > OS >  google-app-script how to cocat all blob into one blob
google-app-script how to cocat all blob into one blob

Time:11-24

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!).

  • Related