Generate multiple pdfs at once and merge them after in one document (app script)

Question

In the google spreadsheet, I have a list called "ID_list_Google_doc", the list created from D3 to D (D3:D), which consists of a multitude of google doc ids. Each google doc id is unique and represents a different template for creating pdfs. What is the solution to go through the list, and identify if the cell contains text if yes, generate pdf, if not do nothing? Then combine the pdfs into a single document.

Foresight

I have to apologize for my poor English skill. Unfortunately, I cannot understand Each google doc id is unique and represents a different template for creating pdfs.. Do you want to convert the Google Document of the document IDs in column "D" to PDF format? Or, do you want to do another process? – Anthropology

@Anthropology Yes, I want to convert multiple google Document at once in PDF, from the document IDs list – Foresight

Thank you for replying. From your reply, I proposed 2 sample scripts as an answer. Please confirm it. If I misunderstood your question and that was not useful, I apologize. – Anthropology

Answer 1

I believe your goal is as follows.

• You want to retrieve the Google Document IDs from cells "D3:D" on Google Spreadsheet.
• You want to convert Google Documents to PDF format, and also you want to merge all PDF data as a PDF file.

In this case, how about the following sample script? In this case, in order to merge multiple PDF data as PDF data, I used pdf-lib.

Pattern 1:

In this pattern, a dialog is used on the Spreadsheet. Please set your sheet name and save the script. When you run main(), a dialog is opened on Spreadsheet, and the document IDs are retrieved from cells "D3:D" and the documents are converted to PDF format. And, the PDF data is merged as a single PDF file and created it as a file in the root folder.

function saveFile(data) {
  const blob = Utilities.newBlob(data, MimeType.PDF, "sample1.pdf");
  DriveApp.createFile(blob);
  return "Done.";
}

function getPDFdata() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Please set your sheet name.
  const ids = sheet.getRange("D3:D" + sheet.getLastRow()).getDisplayValues().filter(([d]) => d);
  return ids.map(([id]) => DriveApp.getFileById(id).getBlob().getBytes());
}

// Please run this script.
function main() {
  const html = `Now processing... <script src='https://cdn.jsdelivr.net/npm/pdf-lib/dist/pdf-lib.min.js'></script><script>google.script.run.withSuccessHandler(async (data)=>{const pdfDoc=await PDFLib.PDFDocument.create();for (let i=0;i < data.length;i++){const pdfData=await PDFLib.PDFDocument.load(new Uint8Array(data[i]));for (let j=0;j < pdfData.getPageCount();j++){const [page]=await pdfDoc.copyPages(pdfData,[j]);pdfDoc.addPage(page)}}const bytes=await pdfDoc.save();google.script.run.withSuccessHandler(google.script.host.close).saveFile([...new Int8Array(bytes)])}).getPDFdata();</script>`;
  SpreadsheetApp.getUi().showModalDialog(HtmlService.createHtmlOutput(html), "sample");
}

• In the above script, the following HTML & Javascript is included in the Google Apps Script.

  Now processing...
  <script src='https://cdn.jsdelivr.net/npm/pdf-lib/dist/pdf-lib.min.js'></script>
  <script>
  google.script.run.withSuccessHandler(async (data) => {
    const pdfDoc = await PDFLib.PDFDocument.create();
    for (let i = 0; i < data.length; i++) {
      const pdfData = await PDFLib.PDFDocument.load(new Uint8Array(data[i]));
      for (let j = 0; j < pdfData.getPageCount(); j++) {
        const [page] = await pdfDoc.copyPages(pdfData, [j]);
        pdfDoc.addPage(page);
      }
    }
    const bytes = await pdfDoc.save();
    google.script.run.withSuccessHandler(google.script.host.close).saveFile([...new Int8Array(bytes)]);
  }).getPDFdata();
  </script>
  

Pattern 2:

I noticed that in the current stage, pdf-lib.min.js can be directly used with Google Apps Script. So, as a pattern 2, I would like to also propose a sample script without using a dialog. In this sample script, when you run main(), the script is run. And, the same result with the above script is obtained without opening a dialog.

async function main() {
  // Retrieve PDF data.
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Please set your sheet name.
  const ids = sheet.getRange("D3:D" + sheet.getLastRow()).getDisplayValues().filter(([d]) => d);
  const data = ids.map(([id]) => new Uint8Array(DriveApp.getFileById(id).getBlob().getBytes()));

  // Merge PDFs.
  const cdnjs = "https://cdn.jsdelivr.net/npm/pdf-lib/dist/pdf-lib.min.js";
  eval(UrlFetchApp.fetch(cdnjs).getContentText()); // Load pdf-lib
  const pdfDoc = await PDFLib.PDFDocument.create();
  for (let i = 0; i < data.length; i++) {
    const pdfData = await PDFLib.PDFDocument.load(data[i]);
    for (let j = 0; j < pdfData.getPageCount(); j++) {
      const [page] = await pdfDoc.copyPages(pdfData, [j]);
      pdfDoc.addPage(page);
    }
  }
  const bytes = await pdfDoc.save();

  // Create a PDF file.
  DriveApp.createFile(Utilities.newBlob([...new Int8Array(bytes)], MimeType.PDF, "sample2.pdf"));
}

Note:

• I think that the above script works. But, in this case, when you directly copy and paste the Javascript retrieved from https://cdn.jsdelivr.net/npm/pdf-lib/dist/pdf-lib.min.js to your Google Apps Script project, the process cost for loading it can be reduced.

References:

• pdf-lib
• copyPages of pdf-lib
• addPage of pdf-lib

Anthropology

Thank you for your help, I really appreciate. I use pattern 2 and I get this error after I run the script ReferenceError: setTimeout is not defined eval X eval eval eval eval i e.computeBufferSize – Foresight

@Dornescu Lucian Thank you for replying. About I use pattern 2 and I get this error after I run the script ReferenceError: setTimeout is not defined eval X eval eval eval eval i e.computeBufferSize, unfortunately, setTimeout cannot be able to be used with Google Apps Script. I think that this is the reason of your current issue. In this case, in order to replace it, how about putting var setTimeout = function(f, t) { Utilities.sleep(t); return f(); } just after the line of eval(UrlFetchApp.fetch(cdnjs).getContentText());? By the way, how about pattern 1? – Anthropology