In this article, we’ll be quickly reviewing the Google Apps Script project for converting data from Google Spreadsheets to PDF Invoice. This feature can be extremely useful, and it’s very easy to develop.

Without any delay, let’s analyze the output.

Step 1. Analyzing the Output

Following is what the output Invoice PDF looks like.

This is a sample Invoice, but it’s very close to a day-to-day invoice.

Now, Let’s see a small animation that demonstrates what Google Spreadsheet looks like. In addition to that, It also shows how PDF invoice gets generated.

In the next section, we’ll be reviewing the source code.

Step 2. Reviewing the Source Code

/**
 * Initial Declaration and References
 */

// Get the active spreadsheet and the active sheet
const ss = SpreadsheetApp.getActiveSpreadsheet();

// PDF.co API Key
const API_Key = "--ADD-YOUR-PDFco-API-KEY-HERE--";

/**
 * Add PDF.co Menus in Google Spreadsheet
 */
function onOpen() {
    var menuItems = [
        { name: 'Get PDF Invoice', functionName: 'getPDFInvoice' }
    ];
    ss.addMenu('PDF.co', menuItems);
}


/**
 * Function which gets Invoice Information using PDF.co
 */
function getPDFInvoice() {

    let invoiceData = JSON.stringify(generateInvoiceJson());

    // Prepare Payload
    var data = {
        "templateId": 3,
        "templateData": invoiceData
    };

    // Prepare Request Options
    var options = {
        'method': 'post',
        'contentType': 'application/json',
        'headers': {
            "x-api-key": API_Key
        },
        // 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/convert/from/html', options);

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

    let resultUrlCell = ss.getRange(`H1`);

    // Display Result
    if (!pdfCoRespJson.error) {
        resultUrlCell.setValue(pdfCoRespJson.url);
    }
    else {
        resultUrlCell.setValue(pdfCoRespJson.message);
    }
}

/**
 * Function to Generate Invoice JSON
 */
function generateInvoiceJson() {
    let oRet = {};

    oRet.paid = ss.getRange(`F1`).getValue();
    oRet.company_name = ss.getRange(`B1`).getValue();
    oRet.company_address = ss.getRange(`D1`).getValue();
    oRet.company_logo = "https://bytescout-com.s3.amazonaws.com/files/demo-files/cloud-api/pdf-edit/logo.png";
    oRet.barcode_value = ss.getRange(`B2`).getValue();
    oRet.ocr_scanline = ss.getRange(`B2`).getValue();
    oRet.order_id = ss.getRange(`B2`).getValue();
    oRet.order_date = ss.getRange(`D2`).getValue();
    oRet.customer_id = ss.getRange(`F2`).getValue();
    oRet.shipped_date = ss.getRange(`F3`).getValue();
    oRet.shipped_via = ss.getRange(`F4`).getValue();
    oRet.bill_to_name = ss.getRange(`B3`).getValue();
    oRet.bill_to_address = ss.getRange(`D3`).getValue();
    oRet.ship_to_name = ss.getRange(`B4`).getValue();
    oRet.ship_to_address = ss.getRange(`D4`).getValue();
    oRet.freight = ss.getRange(`B5`).getValue();
    oRet.notes = ss.getRange(`D5`).getValue();

    oRet.items = getInvoiceItemsJson();

    return oRet;
}

function getInvoiceItemsJson() {
    var oRet = [];

    let index = 9;
    let isDataAvailable = ss.getRange(`A${index}:B${index}`).getValue() !== "";

    while (isDataAvailable) {
        oRet.push({
            "name": ss.getRange(`A${index}:B${index}`).getValue(),
            "price": ss.getRange(`C${index}`).getValue(),
            "quantity": ss.getRange(`D${index}`).getValue()
        });

        index++;
        isDataAvailable = ss.getRange(`A${index}:B${index}`).getValue() !== "";
    }

    return oRet;
}

In the next section, we’ll be analyzing the main code snippets.

Step 3. Analysis

We can divide this source code into the following sections.

  1. Initial Declarations
  2. Creating PDF.co Menu
  3. Preparing Request Input Payload
  4. Executing Request and displaying the result

1. Initial Declarations

At the start of the program, we have useful declarations and references. For example, we’re storing the current spreadsheet reference into variable ss.

// Get the active spreadsheet and the active sheet
const ss = SpreadsheetApp.getActiveSpreadsheet();
 
// PDF.co API Key
const API_Key = "--ADD-YOUR-PDFco-API-KEY-HERE--";

Apart from that, PDF.co API key is stored in a variable named “API_Key”. This API key is being passed into PDF.co request headers. It’s useful for request authentication on the PDF.co side.

2. Creating PDF.co Menu

In this step, we’re adding a custom menu to Google Spreadsheet named “PDF.co”. We’re also adding a submenu under the “PDF.co” menu named “Get PDF Invoice”.

/**
 * Add PDF.co Menus in Google Spreadsheet
 */
function onOpen() {
  var menuItems = [
    {name: 'Get PDF Invoice', functionName: 'getPDFInvoice'} 
  ];
  ss.addMenu('PDF.co', menuItems);
}

On clicking the “Get PDF Invoice” menu, it’ll execute a function named “getPDFInvoice”.

3. Preparing PDF.co Request Input Payload

Next, we have functions that are preparing request payload which needs to be sent to PDF.co request.
For this task, we have the following functions defined.

  • generateInvoiceJson()
  • getInvoiceItemsJson()

These functions basically scan spreadsheet cells, read their data, and ultimately prepare a JSON object and return it.

4. Executing PDF.co Request and displaying the result

In the final step, we’re executing PDF.co API Endpoint /pdf/convert/from/html to get PDF Invoice generated.

Now, PDF.co has several built-in templates. Invoice template is one of that. Interestingly, These templates are customizable, and users can create their own custom templates too. All these features are available in PDF.co user area, and available to registered users only.

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

After PDF.co endpoint execution, its response is displayed in a predefined spreadsheet cell.

// Display Result
    if (!pdfCoRespJson.error) {
        resultUrlCell.setValue(pdfCoRespJson.url);
    }

Summary

Well, that’s how easy it is to generate PDF invoices using Google Apps Script and PDF.co. Please try this sample at your side for a better understanding. Thank you!