Create PDF Invoice From Google Sheet using PDF.co and Apps Script

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.

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 some images that demonstrates what Google Spreadsheet looks like. In addition to that, It also shows how PDF invoice gets generated.

Let's review 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.

We can divide this source code into the following sections.