sodcalculator

10 Tech How-To

Google Sheets Bill Split: Automatic Expense Reminders

Guide 10-53942 · Updated · Step by step

To send automatic expense reminders from a Google Sheets bill split, you use Google Apps Script to trigger email notifications based on a schedule or a cell value. This article explains how to set up that script, connect it to your sheet, and automate the entire reminder process.

Why Automate Reminders in Google Sheets

Manual reminders are easy to forget, especially when you split bills with roommates, friends, or family every month. A shared spreadsheet tracks who owes what, but it does nothing until someone checks it. Automating reminders saves you the awkward “just a friendly nudge” message and ensures everyone pays on time. Google Sheets is free, collaborative, and its built-in Apps Script editor lets you send emails directly from Gmail without any paid add-on.

What You Need Before You Start

  1. A Google Sheet with columns for: name, amount owed, due date, and email address.
  2. A Gmail account (the same one that owns the sheet) to send the emails.
  3. Basic familiarity with the Sheets menu: Extensions > Apps Script.
  4. Permission to run a script that sends emails – Google will ask you to authorize it once.

You do not need any coding experience beyond copying and pasting the script below, then adjusting the column letters.

How the Script Works

The script reads each row of your sheet, checks the due date and the payment status column, and sends an email to the person if the bill is unpaid and the date is near or past due. You can run it manually from the script editor, but the real power comes from a time-driven trigger that runs it daily or weekly.

Here is a basic script that sends a reminder email for every unpaid row where the due date is today or earlier:

function sendReminders() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var data = sheet.getDataRange().getValues();
  var today = new Date();
  today.setHours(0,0,0,0);
  
  for (var i = 1; i < data.length; i++) {
    var name = data[i][0];
    var email = data[i][1];
    var amount = data[i][2];
    var dueDate = new Date(data[i][3]);
    var paid = data[i][4];
    
    if (paid != "yes" && dueDate <= today) {
      var subject = "Bill split reminder for " + name;
      var body = "Hi " + name + ",\n\nThis is a reminder that you owe $" + amount + " for the shared bill. Please pay by " + dueDate.toDateString() + ".\n\nThanks!";
      MailApp.sendEmail(email, subject, body);
    }
  }
}

This assumes your columns are: A = name, B = email, C = amount, D = due date, E = paid status (write “yes” when paid). Adjust the array indexes (0 = A, 1 = B, etc.) to match your sheet.

Setting Up the Automatic Trigger

After pasting the script, click the clock icon in the Apps Script editor (or go to Triggers in the left menu). Click Add Trigger, choose sendReminders as the function, select Time-driven as the event source, then pick how often you want it to run – for example, every day between 8am and 9am. Save the trigger and authorize the script when prompted. The script will now run automatically on that schedule.

You can also add a custom menu to run it manually:

function onOpen() {
  SpreadsheetApp.getUi().createMenu("Reminders")
    .addItem("Send now", "sendReminders")
    .addToUi();
}

This adds a “Reminders” menu to your sheet so you or a group member can trigger emails on demand.

Common Problems and Fixes

  • Script does not send emails: Check that the email column contains valid addresses and that the due date column is formatted as a date, not text. Also check the Apps Script execution log (View > Logs) for errors.
  • Emails go to spam: Ask recipients to add your Gmail address to their contacts. Gmail’s spam filter is aggressive for bulk sends, so keep the daily volume low.
  • Trigger stops working: Google may disable triggers if the script throws repeated errors. Open the script editor, run the function once manually, and fix any issues before relying on the trigger.
  • Date comparison fails: Due dates stored as text like “Jan 5” will not compare correctly. Use the DATE function in Sheets or ensure the cell is formatted as a date.

Alternatives to Apps Script

If you prefer not to code, you have a few options. Google Forms can collect payment confirmations, but they do not send reminders. Add-ons like Yet Another Mail Merge or FormMule can send personalized emails from spreadsheet data, but they often require a paid plan for automatic scheduling. Another simple alternative is to set a recurring calendar event with a link to the sheet, though that does not target individual debtors.

For a purely manual approach, you can use conditional formatting to highlight overdue rows in red, then copy-paste the list into an email. That is faster than typing each name, but still requires a human to hit send.

Costs and Limitations

Apps Script and Gmail are free for personal use, but there are quotas. Consumer Gmail accounts can send about 100 emails per day via MailApp. If you have more than that, you will hit a limit and the script will stop. Google Workspace accounts have higher limits, but still cap out around 1,500 per day. For a typical bill split among friends, you will never come close to these numbers.

Another limitation: the script only sends to addresses in your sheet. If someone changes their email, update the sheet. Also, the script does not track whether the email was opened – it only confirms that Gmail accepted it.

Safety and Privacy

When you authorize the script, you grant it access to your Gmail and Sheets. Only use scripts you understand, and never share the script editor with untrusted people. Avoid putting sensitive information like full bank account numbers in the body of the email – just the amount owed and the due date. If you share the sheet with others, use the Protect ranges feature to lock the script and the email column so only you can edit them.

Frequently Asked Questions

Can I send reminders only to people who haven’t paid?

Yes, the script above checks a “paid” column and skips rows where the value is “yes”. You can change the condition to match your own status labels, such as “done” or “paid”.

How do I change the email subject or body?

Edit the subject and body variables in the script. You can include any text and pull in other cell values by adding more array indexes. For example, to include the bill description, add a column and reference it in the body.

What if the due date is in the future?

The script as written only sends reminders for due dates today or earlier. To send a “coming due” reminder a few days before, change the condition to dueDate <= new Date(today.getTime() + 3*24*60*60*1000) to include the next three days.

Can I send reminders via SMS instead of email?

Not directly. Apps Script can only send emails through Gmail. For SMS, you would need to use a third-party service like Twilio, which requires a paid account and additional setup.

Will the script run if my computer is off?

Yes, because the trigger runs on Google’s servers, not your local machine. As long as the script is saved and the trigger is active, it will run even if you close your laptop.

How do I stop the automatic reminders?

Go to the Apps Script editor, click Triggers, find the active trigger, and click the trash icon to delete it. The script itself remains but will no longer run automatically.

Related

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →