How to track RSVPs in a Google Sheet

A sheet is the right tool for a guest list. It is shareable, it is easy to sort and filter, and everyone involved in the event already knows how to read one. Dedicated RSVP apps mostly lose to it on flexibility.

Where a sheet stops working is data entry. If replies are arriving as text messages, emails, and remarks at the school gate, somebody is retyping them, and that person is the bottleneck. The sheet is only ever as current as the last time they sat down with it.

So the goal is a sheet whose structure does the counting for you, fed by something that is not your own typing.

Six columns cover almost every private event. To start with all six and the formulas below, copy this template.

• ·Guest— the name as you would address them in a message. “The Okonkwo family” is a perfectly good value.
• ·Phone number — in international format, starting with a plus and the country code, no spaces or dashes.
• ·Relationship — how you know them. Useful for seating, and for deciding who gets chased personally.
• ·Attending — Yes, No, or blank for no answer yet. Keep it to those three values so formulas stay simple.
• ·Seats — how many people this row accounts for. This is the column your caterer cares about, and the one Avently reads when your message tells each guest how many seats they have.
• ·Dietary — free text, only if you are serving food you can actually change.

Put these beside the list, in columns of their own, so they are visible without scrolling. Assuming replies are in column D and seats in column E:

• ·Parties accepting: =COUNTIF(D:D,"Yes")
• ·Confirmed headcount: =SUMIF(D:D,"Yes",E:E)
• ·Declined: =COUNTIF(D:D,"No")
• ·Still to answer: =COUNTIFS(A:A,"<>",D:D,"")

The first two are different numbers and both matter. Twelve parties accepting might be twenty-six people. Quoting the wrong one to a venue is an expensive mistake.

Add conditional formatting on the reply column so accepted rows go green and declined rows grey. It turns the list into something you can read at a glance rather than something you have to filter.

Each template has the columns and formulas above, a Yes/No drop-down in Attending, and a few example rows to delete before you add your guests. Each name below makes your own copy in Google Sheets. The Excel link downloads the same file.

• ·Birthday party (Excel): the six columns above, plus Notes.
• ·Kids’ birthday party (Excel): the parent is the guest, with the child’s name, children coming, adults staying, allergies and pickup time.
• ·Dinner party (Excel): a Children column beside Seats, so you know how many child meals to order, and seating notes.
• ·Office holiday party (Excel): Email and Team columns, and a Plus-one column that fills in Seats for you.
• ·Club or association event (Excel): Member number, Member and Paid columns, with counts of who is coming but hasn’t paid.
• ·Potluck (Excel): Bringing (main, side, dessert or drinks), Dish and Contains, with a count of each kind of dish.

1. Step 01

   Set up the two required columns

   Your header row needs a column called Guest and one called Phone number. Capitalisation does not matter. Any other columns you keep are left alone.

2. Step 02

   Share the sheet with the service account

   Avently shows you an email address when you connect a sheet. Share the sheet with that address as an Editor, so replies can be written back. Without Editor access the connection will be refused with a permissions error.

3. Step 03

   Paste the sheet URL into Avently

   Avently reads the header row, checks the two required columns are there, and tells you immediately if anything is missing rather than failing at send time.

From then on, each guest’s answer is written to their row as it arrives, including the follow-up questions you set, such as additional guests and dietary requirements. The sheet stays yours. Avently uses it only to send your invitations and record replies.

A guest list with mobile numbers is personal data about people who did not choose to be in your spreadsheet. Two habits cover most of the risk. Share the sheet with named people rather than turning on link sharing, and delete the numbers once the event is over and you no longer need them.