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.
![Output PDF Invoice](/_next/image?url=https%3A%2F%2Fcdn.sanity.io%2Fimages%2F2niroq9z%2Fproduction%2F2fbefed95c0b3c02fae75a1334631ce8652b92f2-583x817.png&w=1200&q=75)
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.
![Google Sheet](/_next/image?url=https%3A%2F%2Fcdn.sanity.io%2Fimages%2F2niroq9z%2Fproduction%2F106ddec5b97d2efdca89973163362fce6aff7a8c-2608x1352.png&w=3840&q=75)
![Generated PDF](/_next/image?url=https%3A%2F%2Fcdn.sanity.io%2Fimages%2F2niroq9z%2Fproduction%2F80956e5fd2cedf063000e552a2dc805aff335d74-2608x1352.png&w=3840&q=75)
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.
Step 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, the 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.
Step 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”.
Step 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.
Step 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);
}
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!