Merge Google Drive PDF Files and Save Them Back using Google Apps Script and PDF.co

How to Merge PDF Files in Google Drive – Inputs

We will start with a Google Drive folder, which contains two PDF files and a Spreadsheet.

Google spreadsheet designed as shown in the below screenshot.

As we observe, In the first row we have a label for PDF.co API Key and in the adjacent cell, we’ll put PDF.co API key. You can obtain your PDF.co API key from signing up at PDF.co at this URL.

How to Combine PDF Files in Google Drive – Output Demo

Here, we have created a new menu named “PDF.co”. Inside that first sub-menu “Get all PDF from Current Folder” gets all PDF files from the same folder as the spreadsheet, and puts them in comma-separated format into a cell.

To merge PDF files we’ll be using PDF.co API. As PDF.co service requires publicly accessible PDF files, we’ll be also making PDF files publicly accessible, and get that download URL specifically.

The second Submenu “Merge PDF URLs Listed In Cell”, calls PDF.co and merges all PDF file URLs. It’ll update the result PDF URL to the output cell, and also store the actual file to drive location.

Combining PDF Files Google Drive – Source Code

/**
 * Initial Declaration and References
 */
// Get the active spreadsheet and the active sheet
ss = SpreadsheetApp.getActiveSpreadsheet();
ssid = ss.getId();
  
// Look in the same folder the sheet exists in. For example, if this template is in
// My Drive, it will return all of the files in My Drive.
var ssparents = DriveApp.getFileById(ssid).getParents();

// Loop through all the files and add the values to the spreadsheet.
var folder = ssparents.next();


/**
 * Add PDF.co Menus in Google Spreadsheet
 */
function onOpen() {
  var menuItems = [
    {name: 'Get All PDF From Current Folder', functionName: 'getPDFFilesFromCurFolder'},
    {name: 'Merge PDF URLs Listed In Cell', functionName: 'mergePDFDocuments'} 
  ];
  ss.addMenu('PDF.co', menuItems);
}


/**
 * Get all PDF files from current folder
 */
function getPDFFilesFromCurFolder() {
  var files = folder.getFiles();
  var pdfUrlCell = ss.getRange("A4"); 

  var allFileUrls = [];

  while (files.hasNext()) {
    var file = files.next();

    var fileName = file.getName();
    if(fileName.endsWith(".pdf")){
      // Make File Pulblic accessible with URL so that it can be accessible with external API
      var resource = {role: "reader", type: "anyone"};
      Drive.Permissions.insert(resource, file.getId());

      // Add Url
      allFileUrls.push(file.getDownloadUrl());
    }

    pdfUrlCell.setValue(allFileUrls.join(","));
  }   
}

function getPDFcoApiKey(){
  // Get PDF.co API Key Cell
  let pdfCoAPIKeyCell = ss.getRange("B1");

  return pdfCoAPIKeyCell.getValue();
}

/**
 * Function which merges documents using PDF.co
 */
function mergePDFDocuments() {

  // Get Cells for Input/Output
  let pdfUrlCell = ss.getRange("A4"); 
  let resultUrlCell =  ss.getRange("B4");

  let pdfUrl = pdfUrlCell.getValue();
  
  // Prepare Payload
  const data = {
    "async": true, // As we have large volumn of PDF files, Enabling async mode
    "name": "result",
    "url": pdfUrl
  };

  // Prepare Request Options
  const options = {
    'method' : 'post',
    'contentType': 'application/json',
    'headers': {
      "x-api-key": getPDFcoApiKey()
    },
    // Convert the JavaScript object to a JSON string.
    'payload' : JSON.stringify(data)
  };
  
  // Get Response
  // https://developers.google.com/apps-script/reference/url-fetch
  const resp = UrlFetchApp.fetch('https://api.pdf.co/v1/pdf/merge', options);

  // Response Json
  const respJson = JSON.parse(resp.getContentText());

  if(respJson.error){
    console.error(respJson.message);
  }
  else{

    // Job Success Callback
    const successCallbackFn = function(){
      // Upload file to Google Drive
      uploadFile(respJson.url);

      // Update Cell with result URL
      resultUrlCell.setValue(respJson.url);
    }

    // Check PDF.co Job Status
    checkPDFcoJobStatus(respJson.jobId, successCallbackFn);
  }
}

/**
 * Checks PDF.co Job Status
 */
function checkPDFcoJobStatus(jobId, successCallbackFn){
  // Prepare Payload
  const data = {
    "jobid": jobId
  };

 // Prepare Request Options
  const options = {
    'method' : 'post',
    'contentType': 'application/json',
    'headers': {
      "x-api-key": getPDFcoApiKey()
    },
    // Convert the JavaScript object to a JSON string.
    'payload' : JSON.stringify(data)
  };

  // Get Response
  // https://developers.google.com/apps-script/reference/url-fetch
  const resp = UrlFetchApp.fetch('https://api.pdf.co/v1/job/check', options);

  // Response Json
  const respJson = JSON.parse(resp.getContentText());

  
  if(respJson.status === "working"){
    // Pause for 3 seconds
    Utilities.sleep(3 * 1000);

    // And check Job again
    checkPDFcoJobStatus(jobId, successCallbackFn);
  }
  else if(respJson.status == "success"){
    // Invoke Success Callback Function 
    successCallbackFn();
  }
  else {
    console.error(`Job Failed with status ${respJson.status}`);
  }
}

/**
 * Save file URL to specific location
 */
function uploadFile(fileUrl) {
  var fileContent = UrlFetchApp.fetch(fileUrl).getBlob();
  folder.createFile(fileContent);
}

Source code is quite straightforward. Please note here that needed to add a Service Reference to “Drive” as shown in the following image, as it’s necessary to interact with Google Drive.

At the beginning of the source code, we’re declaring all necessary references such as the current spreadsheet, current Google Drive folder, etc.

Next, we’re writing code to add a menu on the onOpen event. This event is called every time Google Spreadsheet is opened.

Function getPDFFilesFromCurFolder() gets all PDF files from the current folder, makes them public, and writes back to the input data cell. Now to merge PDF URLs, we’re using login from the function mergePDFDocuments().

I hope this article is useful to you. Thank you!