Pre-arrival email from a Google Sheet

Step 1: Make the Arrivals sheet

1. Go to sheets.google.com/create for a blank spreadsheet.

2. Rename the first tab "Arrivals".

3. In row 1, add these headers in this order: First name, Email, Arrival date, Sent. The script reads columns A to D in that order, so keep it.

4. Select the Arrival date column and set Format, Number, Date, so the script reads real dates and not text.

5. Add one row per booking. Two made-up examples:

   • Maya, maya@example.com, 29/10/2026, Sent left empty
   • Tom, tom@example.com, 30/10/2026, Sent left empty

6. Leave Sent empty for every booking. Add only guests who gave you their email for this booking, and share the sheet only with staff who add bookings.

7. Under File, Settings, set your time zone. The script uses it to work out each guest's arrival day.

Step 2: Paste the script

1. Open Extensions, then Apps Script. Delete the sample code and paste this:

const DAYS_BEFORE = 2;
const SENDER_NAME = 'Front desk, Your Hotel';
const SUBJECT = 'Before you arrive at Your Hotel';
const MESSAGE = [
  'Hi {name},',
  '',
  'We look forward to welcoming you on {date}.',
  '',
  'Check-in: from [time]. Check-out: by [time].',
  'Getting here: [address and a short line on directions or parking].',
  'Need anything before you arrive? Reply to this email.',
  '',
  'Front desk, Your Hotel'
].join('\n');

function sendPreArrivalEmails() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Arrivals');
  const timeZone = ss.getSpreadsheetTimeZone();
  const day = (date) => Utilities.formatDate(date, timeZone, 'yyyy-MM-dd');
  const today = day(new Date());
  const ahead = Date.now() + DAYS_BEFORE * 24 * 60 * 60 * 1000;
  const lastDay = day(new Date(ahead));
  const rows = sheet.getDataRange().getValues();
  for (let i = 1; i < rows.length; i++) {
    const [name, email, arrival, sent] = rows[i];
    if (!email || sent || !(arrival instanceof Date)) continue;
    const arrivalDay = day(arrival);
    if (arrivalDay < today || arrivalDay > lastDay) continue;
    const body = MESSAGE.replace('{name}', name || 'there').replace(
      '{date}',
      Utilities.formatDate(arrival, timeZone, 'EEEE d MMMM')
    );
    MailApp.sendEmail(email, SUBJECT, body, { name: SENDER_NAME });
    sheet.getRange(i + 1, 4).setValue(new Date());
  }
}

2. Edit SENDER_NAME and SUBJECT to your hotel's name.
3. In MESSAGE, replace the [placeholders] with your check-in time, check-out time, address and a line on directions or parking. Keep {name} and {date}; the script fills those in for each guest.
4. DAYS_BEFORE = 2 controls how many days ahead of arrival a guest gets the email. Change it if you want more or less notice.

Step 3: Run it once and test it

1. Add a test row with your own email address and tomorrow's date in Arrival date.
2. Click Run. Google warns that the app is unverified because you wrote it. Choose Advanced, then go to the project, to approve the permissions.
3. Check your inbox for the email.
4. Delete the test row, or leave its Sent date in place so it is not emailed again.
5. Replies to the email go to the Google account that ran the script, so run it from the front desk or reservations account, not a personal one.

Step 4: Send it every morning

1. In Apps Script, open Triggers (the clock icon) and click Add Trigger.
2. Choose sendPreArrivalEmails, event source Time-driven, type Day timer, and an hour window such as 8 to 9 AM.
3. Save. Google picks a time inside that hour and keeps it the same each day.

Step 5: Each guest gets it once

1. After the script emails a guest, it writes the send time into Sent on that row.
2. A row is skipped if the email is empty, the arrival date is in the past, or Sent already has a value.
3. A booking added late, arriving today or tomorrow, still gets the email on the next morning run.
4. To send the email again, clear the Sent cell on that row.

Details and limits

Checked on 2026-10-27. Google changes limits and menus, so re-check the linked pages.

What it does

Every arriving guest gets the same answers to the questions they ask most, before they ask. Nothing here needs a paid plan.

What it can't do

• It does not read your PMS or booking sites, so someone still adds each booking to the sheet.
• It does not answer replies. They land in the inbox of the account that runs the script.
• It sends plain text, in one language.
• It does not check that the address works. Test with your own email first.

Limits

• A consumer Google account can send 100 email recipients a day with MailApp; a Google Workspace account can send 1,500. See the quotas page. Quotas can change without notice.
• Each run has 6 minutes to finish.
• Clear old rows after check-out so the sheet stays short and holds less guest data.

When this stops being enough

When bookings should flow in from the PMS or booking sites by themselves, when the message should change by room type, language or booking source, or when replies need tracking, a sheet and a script can't do that.

Read how the no-build-fee model works.

Sources

1. Class MailApp (Apps Script)
2. Quotas for Google services (Apps Script)
3. Installable triggers (Apps Script)

Related guides

• Auto-acknowledge hotel enquiries in Gmail
• Auto-replies for hotel WhatsApp, set up free
• Hotel welcome and checkout message templates