Send Google sheet Invoices via html mail

In this post, I'll show you how to automate the process of sending invoices directly from a Google Sheet using Google Apps Script. This script generates an HTML table from your invoice data and sends it as an email. Follow the steps below to implement this in your own Google Sheet.

(Tap the image to see clearly)

This script consists of two main functions:

1. generateInvoiceHTMLTable(): This function creates an HTML table from the data in your "Invoice" sheet.
2. sendInvoiceEmail(): This function sends the generated HTML table via email.

The Script

Copy and paste the following code into the Google Apps Script editor:

function generateInvoiceHTMLTable() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var tab = ss.getSheetByName("Invoice"); var lr = tab.getRange("H25").getNextDataCell(SpreadsheetApp.Direction.UP).getRow(); // Specifying the range from C1 to I (columns 3 to 9) var datavalues = tab.getRange(1, 3, lr, 7).getValues(); var html = "Invoice <br><br><table>"; if (datavalues.length > 0) { for (var i = 0; i < datavalues.length; i++) { html += '<tr>'; for (var j = 0; j < datavalues[i].length; j++) { // Check if the row is header of the table or normal row if (i == 0) { html += Utilities.formatString('<td style="border:1px solid black"><th>%s</th></td>', datavalues[i][j]); } else { html += Utilities.formatString('<td style="border:1px solid black">%s</td>', datavalues[i][j]); } } html += '</tr>'; } html += '</table>'; } return html; } function sendInvoiceEmail() { MailApp.sendEmail({ to: "muralyjio@gmail.com", subject: "New mail", htmlBody: generateInvoiceHTMLTable() }); }

1. Generate Invoice HTML Table: The generateInvoiceHTMLTable() function retrieves the data from the "Invoice" sheet, formats it into an HTML table, and returns the HTML as a string.
2. Send Email: The sendInvoiceEmail() function uses the MailApp service to send an email with the generated HTML table as the body.

Setting Up the Script

1. Open Google Apps Script Editor: In your Google Sheet, go to Extensions > Apps Script.
2. Create a New Project: Click on the + icon to create a new project.
3. Copy and Paste the Script: Copy the script above and paste it into the script editor.
4. Save the Project: Give your project a name and save it.
5. Run the Script: You can manually run the sendInvoiceEmail() function from the script editor to test it.

---