From Zapier to Scripts: How I Automated Monthly Invoice Emails for Free!

send email using google apps scripts

In the world of business, automating repetitive tasks can save time, reduce errors, and improve efficiency. One such task is sending monthly invoices to customers. Today, I’ll guide you through setting up an automation that sends personalized invoice emails directly from Google Sheets using Google Apps Script, with invoices stored in Google Drive. Here’s how you can achieve this and stay ahead of your administrative game.

Why Automate Invoice Emails?

Sending invoices manually can be time-consuming and prone to mistakes. Automating this process ensures:

1. Consistency: Invoices are sent on time, every time.
2. Accuracy: Reduces the risk of missing a client or attaching the wrong file.
3. Efficiency: Frees up valuable time for you to focus on growing your business.

My Use Case: Transitioning from Zapier to Google Apps Script

Initially, I used Zapier to automate my invoice email process. While Zapier is a fantastic tool for automation, I encountered a limitation: the free plan only allows two steps per Zap. This meant I couldn’t include additional steps for fetching and attaching invoices without upgrading to a paid plan.

To overcome this, I decided to switch to Google Sheets and Apps Script. This solution provided greater flexibility, cost-effectiveness, and the ability to fully customize the automation to suit my needs.

What We’ll Use

• Google Sheets: To manage customer data like names and email addresses.
• Google Drive: To store invoice PDFs.
• Google Apps Script: To write and execute the automation logic.

Step-by-Step Guide to Automation

Step 1: Prepare Your Google Sheet

Create a Google Sheet with the following columns:

• Column A: Customer Name
• Column B: Email Address

Fill in the customer details as rows beneath these headers.

Step 2: Organize Invoice PDFs in Google Drive

(This is optional if you want to attached documents)

Create a folder in Google Drive with any name you want in here i use (Infortronix Invoice PDFs). Store your invoices in this folder, and name them in the following format: INFORTRONIX-INVOICE-APRIL-2025.docx.pdf(this name is used for me you can use any name)

Step 3: Write the Apps Script

Step 3: Write the Apps Script

1. Open your Google Sheet.
2. Click on Extensions > Apps Script.
3. Replace the contents of Code.gs with the following script:

app script

apps script page

Simple Explanation of the Code

Here’s a brief and easy-to-understand explanation of the script:

1. Fetch Google Sheet Data: The script reads customer names and email addresses from the Google Sheet.
2. Check the Date: The script only runs on the 25th of the month to match the schedule.
3. Access the Folder: It locates a specific folder in Google Drive (Infortronix Invoice PDFs) where all the invoices are stored.
4. Generate the Invoice Name: Based on the current month and year, the script generates the invoice file name (e.g., INFORTRONIX-INVOICE-APRIL-2025.docx.pdf).
5. Find the File: It searches for the invoice file in the folder.
6. Personalize the Email: The email body is customized with the customer’s name, the month, and the year.
7. Send the Email: Using the GmailApp service, the script sends an email to the customer with the invoice attached.
8. Logs for Debugging: The script logs success messages (or errors) for each email sent, helping you track and debug the process.

function sendMonthlyInvoices() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();
  const today = new Date();

  // Check if today is the 25th
  if (today.getDate() !== 25) return;

  const folderName = "Infortronix Invoice PDFs"; // Folder name where invoices are stored
  const folder = DriveApp.getFoldersByName(folderName).next();
  if (!folder) {
    console.error(`Folder "${folderName}" not found.`);
    return;
  }

  const month = today.toLocaleString("default", { month: "long" }).toUpperCase(); // e.g., "APRIL"
  const year = today.getFullYear(); // e.g., 2025
  const subject = "Your Monthly Invoice";
  const emailBodyTemplate = "Dear {{name}},\n\nPlease find your invoice for {{month}} {{year}} attached.\n\nThank you!";

  for (let i = 1; i < data.length; i++) { // Start from row 2 (skip headers)
    const [customerName, email] = data[i];
    if (!email || !customerName) continue; // Skip rows with missing data

    // Build the expected invoice name
    const invoiceName = `INFORTRONIX-INVOICE-${month}-${year}.docx.pdf`;

    try {
      // Search for the file in the folder
      const files = folder.getFilesByName(invoiceName);

      if (!files.hasNext()) {
        console.warn(`Invoice "${invoiceName}" not found for ${customerName}.`);
        continue;
      }

      const file = files.next(); // Get the first file matching the name
      const blob = file.getBlob();

      // Personalize email body
      const body = emailBodyTemplate
        .replace("{{name}}", customerName)
        .replace("{{month}}", month)
        .replace("{{year}}", year);

      // Send email with the invoice attached
      GmailApp.sendEmail(email, subject, body, {
        attachments: [blob],
        name: "Infortronix"
      });

      console.log(`Invoice sent to ${email} for ${customerName}.`);
    } catch (e) {
      console.error(`Failed to send invoice to ${email}: ${e.message}`);
    }
  }
}

Step 4: Set Up a Monthly Trigger

1. In the Apps Script editor, click on the clock icon (Triggers).
2. Click + Add Trigger.
3. Configure the trigger as follows:
4. Save the trigger.

trigger screen

Step 5: Test the Automation

1. Add sample data to your Google Sheet.
2. Upload a sample invoice to your Google Drive folder with the correct naming convention.
3. Run the script manually by selecting sendMonthlyInvoices or your function name in the Apps Script editor and clicking the ▶️ Run button.
4. Check your Gmail and logs for confirmation.

The Result

Once set up, this automation will:

• Retrieve customer details from your Google Sheet.
• Fetch the correct invoice from your Google Drive.
• Send a personalized email with the invoice attached to each customer on the 25th of every month.

Final Thoughts

This solution demonstrates how simple tools like Google Sheets, Drive, and Apps Script can work together to automate tasks and enhance productivity. Switching from Zapier to this custom solution allowed me to overcome the limitations of the free plan while maintaining full control over the process. Whether you’re a small business owner or a freelancer, automating your invoice process can save you time and streamline your operations.

Are you ready to simplify your invoicing process? Let me know your thoughts or if you have any questions!