For many small businesses, the process of creating invoices is both vital and time-consuming. Automating this process can save time, reduce errors, and ensure that payments are requested promptly. This blog post will guide you through setting up an automated system that creates invoices in Google Docs from sales data in a Google Sheet and then emails these invoices to customers.
Objective
The main goal is to automate the creation of invoices based on sales data from a Google Sheet, and then dispatch these invoices as PDFs to customers via email.
Tools Needed
Google Sheets (for sales data)
Google Docs (for invoice template)
Google Apps Script (to automate the process)
Step-by-Step Setup
Prepare Your DataBegin by organizing your sales data in Google Sheets. Here’s an example of how your data might look:
Customer Name Product Quantity Price Email
John Doe Widget Type A 3 19.99 johndoe@example.com
Jane Smith Widget Type B 5 15.49 janesmith@example.com
Alice Johnson Widget Type C 2 25.00 alicej@example.com
Create the Invoice Template in Google DocsYour invoice template should include placeholders that your script will replace with actual data. Here’s a simplified layout of what the invoice template might include:
Header: Your company’s logo, name, and contact information.
Body: Details of the customer and a table for the products with fields like Product, Quantity, Price, and Total.
Footer: Payment terms and other relevant information.
Example placeholders in the template:
INVOICE
Date: [Today’s Date]
Invoice #: [Invoice Number]
Bill To:
{{Customer Name}}
{{Product}}
{{Quantity}}
${{Price}}
${{Total}}
Subtotal: ${{Subtotal}}
Sales Tax (7%): ${{Tax}}
Total Due: ${{Total}}
Write the Apps ScriptOpen your Google Sheet, go to Extensions > Apps Script, and replace any existing code with the following:
function createAndSendInvoices() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const data = sheet.getDataRange().getValues();
const templateDocId = '1i1xoWQ'; // Replace with your template's ID
data.forEach((row, index) => {
if (index === 0) return; // Skip header
const quantity = Number(row[2]);
const price = Number(row[3]);
if (isNaN(quantity) || isNaN(price)) {
console.error('Invalid data in row ' + (index + 1));
return;
}
const totalPrice = quantity * price;
const docId = DriveApp.getFileById(templateDocId).makeCopy('Invoice for ' + row[0]).getId();
const doc = DocumentApp.openById(docId);
const body = doc.getBody();
body.replaceText('{{Customer Name}}', row[0]);
body.replaceText('{{Product}}', row[1]);
body.replaceText('{{Quantity}}', quantity.toString());
body.replaceText('{{Price}}', '$' + price.toFixed(2));
body.replaceText('{{Total}}', '$' + totalPrice.toFixed(2));const file = DriveApp.getFileById(docId);
const pdf = file.getAs('application/pdf');
const recipient = row[4];
MailApp.sendEmail({
to: recipient,
subject: 'Invoice from Your Company',
body: 'Dear ' + row[0] + ',\n\nPlease find attached your invoice.',
attachments: [pdf]
});
const folderId = 'FOLDERID'; // Replace with your folder's ID
const folder = DriveApp.getFolderById(folderId);
file.moveTo(folder);
});
}
Run Your ScriptAfter saving your project, you can run createAndSendInvoices to generate and send invoices based on the data in your sheet.
Conclusion
This automation streamlines the invoice generation process, reducing the administrative burden on your team and helping you ensure invoices are sent promptly and accurately. With Google Apps Script, small businesses can leverage powerful automation to improve efficiency and accuracy in their operations.
Automated Invoice Creation from Sheet Data
Create Sample Doc template
INVOICE
Date: [Today’s Date]
Invoice #: [Invoice Number]
Bill To:
{{Customer Name}}
Customer Address
Customer City, State, Zip Code
Customer Phone
Customer Email
Invoice Details
| Item Description | Quantity | Unit Price | Total |
| {{Product}} | {{Quantity}} | ${{Price}} | ${{Total}} |
Subtotal: ${{Subtotal}}
Sales Tax (7%): ${{Tax}}
Total Due: ${{Total}}
Sample Spreadsheet Structure
| Customer Name | Product | Quantity | Price | |
| John Doe | Widget Type A | 3 | 19.99 | johndoe@example.com |
| Jane Smith | Widget Type B | 5 | 15.49 | janesmith@example.com |
| Alice Johnson | Widget Type C | 2 | 25.00 | alicej@example.com |
Objective: Automate the creation of invoices in Google Docs based on sales data from a Google Sheet, and then email these invoices to customers.
Steps:
Prepare a Google Sheet with sales data including columns for Customer Name, Product, Quantity, Price, and Customer Email.
Open Extensions > Apps Script.
Replace the existing code with:
function createAndSendInvoices() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const data = sheet.getDataRange().getValues();
const templateDocId = ‘1i1xoWQ’; // Replace with the ID of a Google Docs invoice template
data.forEach((row, index) => {
if (index === 0) return; // Skip the header row
const quantity = Number(row[2]); // Convert to number, NaN if conversion fails
const price = Number(row[3]);
if (isNaN(quantity) || isNaN(price)) {
console.error(‘Invalid data in row ‘ + (index + 1));
return; // Skip this row
}
const totalPrice = quantity * price;
const docId = DriveApp.getFileById(templateDocId).makeCopy(‘Invoice for ‘ + row[0]).getId();
const doc = DocumentApp.openById(docId);
const body = doc.getBody();
body.replaceText(‘{{Customer Name}}’, row[0]);
body.replaceText(‘{{Product}}’, row[1]);
body.replaceText(‘{{Quantity}}’, quantity.toString());
body.replaceText(‘{{Price}}’, ‘$’ + price.toFixed(2));
body.replaceText(‘{{Total}}’, ‘$’ + totalPrice.toFixed(2));
const file = DriveApp.getFileById(docId);
const pdf = file.getAs(‘application/pdf’);
const recipient = row[4]; // Customer email
MailApp.sendEmail({
to: recipient,
subject: ‘Invoice from Your Company’,
body: ‘Dear ‘ + row[0] + ‘,\n\nPlease find attached your invoice.’,
attachments: [pdf]
});
// Move the invoice to a specific folder after sending (optional)
const folder = DriveApp.getFolderById(FOLDERID ); // Replace with your folder’s ID
file.moveTo(folder);
});
}
Save and name your project.
Run createAndSendInvoices when you need to generate and send invoices based on the sheet data.Explanation: This script takes each row from the Google Sheet, generates an invoice in Google Docs using a predefined template by replacing placeholders with actual data, and emails the invoice as a PDF attachment to the customer’s email address. Additionally, it moves the generated invoice file to a designated folder in Google Drive for organizational purposes.