This content originally appeared on Level Up Coding - Medium and was authored by Dilip Kashyap
Generating invoices automatically can save businesses and freelancers countless hours of manual work. In this comprehensive guide, we’ll walk through how to generate invoices from a Google Sheet using Google Apps Script. This step-by-step tutorial covers everything from setting up your spreadsheet to writing and deploying the script that transforms tagged rows into PDF invoices. We’ve included complete code samples and detailed explanations so you can easily customize the solution for your business needs.
In this article, you’ll learn:
- How to set up your Google Sheet with data and an invoice template
- How to write a Google Apps Script that identifies rows marked for invoicing
- How to populate your invoice template with data and export it as a PDF
- Real-world applications and benefits of automated invoicing
- Strategies to optimize and share your solution with millions of readers
Read on for an in-depth tutorial packed with SEO-friendly tips and practical advice on automating your invoicing process!
Why Automate Invoice Generation?
Automated invoicing is a game changer for small businesses, freelancers, and accountants. By leveraging Google Apps Script with Google Sheets, you can:
- Save Time: Eliminate manual data entry and invoice formatting.
- Reduce Errors: Automatically populate invoices with accurate data.
- Improve Efficiency: Streamline repetitive tasks and focus on growing your business.
- Enhance Professionalism: Generate clean, consistent PDF invoices every time.
These benefits are especially valuable when handling high volumes of transactions or managing multiple clients.
Prerequisites and Setup
Before diving into the code, make sure you have the following:
- A Google Account: Access to Google Sheets and Google Drive.
- A Google Sheet with Two Key Tabs:
- InvoiceData: Contains your invoice details. Include columns such as:
- A: Tag (e.g., “Generate”)
- B: Invoice Number
- C: Customer Name
- D: Invoice Date
- E: Amount
- InvoiceTemplate: A formatted template sheet where you design your invoice. Use placeholder cells (for example, cell B2 for the invoice number, B3 for customer name, etc.) that the script will later fill with data.
- A Destination Folder in Google Drive: Create or choose a folder where the generated PDF invoices will be saved. Copy the folder’s ID to insert into the code.
Once your sheet is set up, open the Apps Script editor by navigating to Extensions > Apps Script in your Google Sheet.
Step-by-Step Code Walkthrough
Below is a detailed explanation of each part of our script.
1. Accessing the Spreadsheet and Sheets
The script begins by obtaining the active spreadsheet and the specific sheets containing your data and template:
var ss = SpreadsheetApp.getActiveSpreadsheet();
var dataSheet = ss.getSheetByName("InvoiceData");
var templateSheet = ss.getSheetByName("InvoiceTemplate");
This ensures that the script operates on the correct files.
2. Looping Through the Data Rows
Next, we read all the data from the InvoiceData sheet and iterate over each row. We assume the first row is a header row:
var dataRange = dataSheet.getDataRange();
var dataValues = dataRange.getValues();
for (var i = 1; i < dataValues.length; i++) {
var row = dataValues[i];
// Check if the first column (A) has the tag "Generate"
if (row[0].toString().toLowerCase() === "generate") {
// Process the row...
}
}
3. Extracting and Mapping Invoice Data
For each row tagged “Generate,” the script extracts relevant invoice details such as the invoice number, customer name, date, and amount:
var invoiceNumber = row[1];
var customerName = row[2];
var invoiceDate = row[3];
var amount = row[4];
Feel free to modify this mapping to match the columns in your sheet.
4. Creating and Populating the Invoice Template
A copy of the InvoiceTemplate sheet is created and renamed based on the invoice number. Then, specific cells in the template are updated with the extracted data:
var tempSheet = templateSheet.copyTo(ss);
tempSheet.setName("Invoice_" + invoiceNumber);
tempSheet.getRange("B2").setValue(invoiceNumber);
tempSheet.getRange("B3").setValue(customerName);
tempSheet.getRange("B4").setValue(invoiceDate);
tempSheet.getRange("B5").setValue(amount);
Customize the cell references ("B2", "B3", etc.) as needed to match your invoice layout.
5. Converting the Invoice to PDF
To generate a PDF, the script calls a helper function that builds a URL for the export API. This URL contains parameters for formatting (e.g., portrait orientation, no gridlines):
var pdfFile = createPDFfromSheet(ss, tempSheet);
6. Saving the PDF in Google Drive
Once the PDF blob is generated, the script saves it to your designated Google Drive folder using its folder ID:
var folder = DriveApp.getFolderById("YOUR_FOLDER_ID"); // Replace with your actual folder ID
folder.createFile(pdfFile);7. Updating the Data and Cleanup
After processing, the script updates the tag in the InvoiceData sheet to “Processed” to prevent duplicate invoicing and deletes the temporary sheet:
dataSheet.getRange(i + 1, 1).setValue("Processed");
ss.deleteSheet(tempSheet);Complete Code
Below is the entire code in one block. Copy and paste it into your Apps Script editor, replace "YOUR_FOLDER_ID" with your actual folder ID, and adjust the cell references as needed.
/**
* Main function to generate invoices from Google Sheet data.
*/
function generateInvoices() {
// Access the active spreadsheet and required sheets
var ss = SpreadsheetApp.getActiveSpreadsheet();
var dataSheet = ss.getSheetByName("InvoiceData");
if (!dataSheet) {
Logger.log("InvoiceData sheet not found!");
return;
}
var templateSheet = ss.getSheetByName("InvoiceTemplate");
if (!templateSheet) {
Logger.log("InvoiceTemplate sheet not found!");
return;
}
// Retrieve all data from the InvoiceData sheet
var dataRange = dataSheet.getDataRange();
var dataValues = dataRange.getValues();
// Loop through each row (skip header row)
for (var i = 1; i < dataValues.length; i++) {
var row = dataValues[i];
// Process only rows with the tag "Generate" (case-insensitive)
if (row[0].toString().toLowerCase() === "generate") {
// Extract invoice data (adjust column indices as necessary)
var invoiceNumber = row[1];
var customerName = row[2];
var invoiceDate = row[3];
var amount = row[4];
// Create a copy of the invoice template and rename it
var tempSheet = templateSheet.copyTo(ss);
tempSheet.setName("Invoice_" + invoiceNumber);
// Populate the template with invoice data
tempSheet.getRange("B2").setValue(invoiceNumber);
tempSheet.getRange("B3").setValue(customerName);
tempSheet.getRange("B4").setValue(invoiceDate);
tempSheet.getRange("B5").setValue(amount);
// Convert the populated invoice sheet into a PDF
var pdfFile = createPDFfromSheet(ss, tempSheet);
// Save the PDF to a specific folder in Google Drive (replace with your folder ID)
var folder = DriveApp.getFolderById("YOUR_FOLDER_ID");
folder.createFile(pdfFile);
// Update the tag in the data sheet to mark the invoice as processed
dataSheet.getRange(i + 1, 1).setValue("Processed");
// Remove the temporary invoice sheet to keep your spreadsheet clean
ss.deleteSheet(tempSheet);
}
}
}
/**
* Helper function to create a PDF file from a specific sheet.
* @param {Spreadsheet} ss - The active spreadsheet.
* @param {Sheet} sheet - The sheet to convert into a PDF.
* @return {Blob} The PDF file as a blob.
*/
function createPDFfromSheet(ss, sheet) {
// Build the export URL with desired parameters
var url_base = "https://docs.google.com/spreadsheets/d/" + ss.getId() + "/export?format=pdf" +
"&portrait=true" +
"&sheetnames=false" +
"&printtitle=false" +
"&pagenumbers=false" +
"&gridlines=false" +
"&fzr=false" +
"&gid=" + sheet.getSheetId();
// Fetch the PDF using URL Fetch and include the OAuth token
var token = ScriptApp.getOAuthToken();
var response = UrlFetchApp.fetch(url_base, {
headers: {
'Authorization': 'Bearer ' + token
}
});
// Return the PDF blob with a filename matching the sheet name
return response.getBlob().setName(sheet.getName() + ".pdf");
}
Use Cases and Applications
This solution is versatile and can be applied in various scenarios:
- Small Businesses: Automate monthly or weekly invoicing to streamline cash flow management.
- Freelancers: Generate professional invoices quickly, ensuring timely payments.
- Accounting Firms: Process bulk invoices with minimal manual intervention.
- Online Retailers: Automatically produce invoices for each transaction in an e-commerce setup.
- Educational Institutions: Create fee receipts and track payments efficiently.
By modifying the script or expanding it with additional features (like emailing invoices directly to customers), you can tailor the solution to fit diverse business needs.
Conclusion
Automating your invoicing process with Google Apps Script and Google Sheets improves efficiency, minimizes errors, and enhances professionalism. This guide has provided you with a detailed, step-by-step approach — from setting up your Google Sheet to writing a fully functional script that generates PDF invoices automatically.
Implement this solution today to save time and streamline your workflow. You can further expand its functionality with a few simple modifications to suit your unique business needs. Happy coding and invoicing!
I hope you find this article helpful. For the latest post intimation, you may follow, subscribe, and share this with your friends. Happy learning! 💻🥳🎉
Boost your Google Workspace potential with our e-book: Google Apps Script: A Beginner’s Guide. Streamline your workflow and automate tasks today. Get your copy now!
Please feel free to contact me via email at dilipkashyap.sd@gmail.com. Thank you :)
Effortless PDF Invoicing Automation with Google Sheets & Apps Script was originally published in Level Up Coding on Medium, where people are continuing the conversation by highlighting and responding to this story.
This content originally appeared on Level Up Coding - Medium and was authored by Dilip Kashyap
Dilip Kashyap | Sciencx (2025-02-13T22:25:19+00:00) Effortless PDF Invoicing Automation with Google Sheets & Apps Script. Retrieved from https://www.scien.cx/2025/02/13/effortless-pdf-invoicing-automation-with-google-sheets-apps-script/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.