In this short and step-by-step guide, we’ll review how to merge Google Drive PDF documents and save them back to Google Drive using Google App Script and PDF.co.

Review our Inputs

The following image shows 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.

Output Demo

Before jumping to source code, let’s observe what our output looks like. The following recording demonstrates our output spreadsheet.

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.

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 which merges documents using PDF.co
 */
function mergePDFDocuments() {

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

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

  let pdfCoAPIKey = pdfCoAPIKeyCell.getValue();
  let pdfUrl = pdfUrlCell.getValue();
  
  // Prepare Payload
  var data = {
    "async": false,
    "encrypt": false,
    "inline": true,
    "name": "result",
    "url": pdfUrl
  };

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

  var pdfCoRespContent = pdfCoResponse.getContentText();
  var pdfCoRespJson = JSON.parse(pdfCoRespContent);

  // Display Result
  if(!pdfCoRespJson.error){
    // Upload file to Google Drive
    uploadFile(pdfCoRespJson.url);

    // Update Cell with result URL
    resultUrlCell.setValue(pdfCoRespJson.url);    
  }
  else{
    resultUrlCell.setValue(pdfCoRespJson.message);    
  }
}


/**
 * 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!