Google Sheets to WhatsApp: Automating Appointment Confirmations and Reminders

AI Automations  ·  2026-08-26  ·  10 min read  ·  By Devain Kapoor

No-shows are the quietest expense a service business carries. A clinic in Kelapa Gading, a detailing workshop in Surabaya, a property agent running site visits in BSD — all of them lose the same way: a slot is booked, nobody is reminded, and the hour goes unsold. The usual fix is an admin who types WhatsApp messages by hand every evening, which works until the day it doesn't.

This guide shows how to replace that person's evening with a Google Sheet and roughly eighty lines of Apps Script. It is the exact pattern we deploy for Indonesian clients, including the parts that only show up in production — phone number formats, template approval, and how to stop the script sending the same reminder twice.

What this automation actually does

In one sentence: when a customer books, Google Apps Script reads the new row in your Sheet and sends a WhatsApp confirmation within seconds, then sends a reminder the day before the appointment — with no human in the loop and no message sent twice.

The flow has four moving parts:

Nothing here needs a server, a subscription to a booking platform, or a developer on retainer. It needs a Google account and a WhatsApp sending channel.

Why WhatsApp rather than email or SMS

Because in Indonesia, WhatsApp is where messages are actually read. Email open rates for transactional reminders sit in the low tens of percent for most consumer businesses here, and a marketing email to a Gmail account often lands in Promotions. WhatsApp is the default channel for customer conversation across the market, and a utility message about an appointment the customer themselves booked is exactly the kind of message the platform is designed to carry.

There is a practical reason too: WhatsApp is two-way. A reminder that a customer can reply to — "can we move it to 3pm?" — turns a broadcast into a rescheduling conversation, which is what actually recovers the slot.

Choosing how you send: three options compared

Short answer: use the WhatsApp Business Cloud API directly if you have someone technical, a Business Solution Provider if you want the setup handled, and avoid unofficial gateways for anything you depend on.

The code in this guide targets the Cloud API. Most BSPs accept a near-identical request shape, so switching is a matter of changing the URL and the auth header.

Setting up the sheet

The sheet is the database, so its columns are the schema. Two of them exist purely to make the automation safe to re-run, and they are the ones people leave out.

Store your access token in Project Settings → Script Properties, never in the code. Anyone you share the Sheet with can read the script.

Normalising Indonesian phone numbers

This is the step that breaks most first attempts. The API requires E.164 — 628119… with no plus, no spaces, no leading zero. Customers will type 0811-9000-111, +62 811 9000 111, and 811 9000 111, all for the same number.

function toE164Id(raw) {
  var n = String(raw).replace(/[^0-9]/g, '');
  if (n.indexOf('62') === 0) return n;        // already 62...
  if (n.indexOf('0') === 0)  return '62' + n.slice(1);   // 0811... -> 62811...
  if (n.indexOf('8') === 0)  return '62' + n;            // 811...  -> 62811...
  return n;
}

Sending the confirmation

One reusable function sends any approved template. Everything else in this guide calls it.

var PHONE_NUMBER_ID = '1234567890';   // from Meta Business Manager
var API = 'https://graph.facebook.com/v21.0/';

function sendTemplate(toE164, templateName, bodyParams) {
  var token = PropertiesService.getScriptProperties().getProperty('WA_TOKEN');
  var payload = {
    messaging_product: 'whatsapp',
    to: toE164,
    type: 'template',
    template: {
      name: templateName,
      language: { code: 'id' },
      components: [{
        type: 'body',
        parameters: bodyParams.map(function (t) { return { type: 'text', text: String(t) }; })
      }]
    }
  };
  var res = UrlFetchApp.fetch(API + PHONE_NUMBER_ID + '/messages', {
    method: 'post',
    contentType: 'application/json',
    headers: { Authorization: 'Bearer ' + token },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  });
  return { code: res.getResponseCode(), body: res.getContentText() };
}

Now wire it to form submissions. In the Apps Script editor, add an installable trigger — Triggers → Add Trigger → onBooking → From spreadsheet → On form submit. The simple onFormSubmit(e) function will not work here, because simple triggers are not allowed to call external services.

function onBooking(e) {
  var sh  = e.range.getSheet();
  var row = e.range.getRow();
  var v   = sh.getRange(row, 1, 1, 7).getValues()[0];
  var name = v[1], phone = v[2], date = v[3], time = v[4];

  if (v[5]) return;                    // ConfirmSent already set — do nothing

  var r = sendTemplate(toE164Id(phone), 'appointment_confirm',
                       [name, formatDate_(date), time]);
  sh.getRange(row, 6).setValue(r.code === 200 ? 'YES' : 'ERR ' + r.code);
}

function formatDate_(d) {
  return Utilities.formatDate(new Date(d), Session.getScriptTimeZone(), 'd MMMM yyyy');
}

Adding the day-before reminder

A second function, run once a day by a time-driven trigger, handles reminders. Set it for around 17:00 — late enough that tomorrow feels imminent, early enough that people can still reschedule.

function sendDayBeforeReminders() {
  var sh   = SpreadsheetApp.getActive().getSheetByName('Bookings');
  var rows = sh.getDataRange().getValues();
  var tz   = Session.getScriptTimeZone();
  var tomorrow = Utilities.formatDate(
    new Date(Date.now() + 24 * 60 * 60 * 1000), tz, 'yyyy-MM-dd');

  for (var i = 1; i < rows.length; i++) {
    var name = rows[i][1], phone = rows[i][2];
    var date = rows[i][3], time = rows[i][4], reminded = rows[i][6];

    if (reminded) continue;                       // guard column
    if (!date || !phone) continue;
    if (Utilities.formatDate(new Date(date), tz, 'yyyy-MM-dd') !== tomorrow) continue;

    var r = sendTemplate(toE164Id(phone), 'appointment_reminder', [name, time]);
    sh.getRange(i + 1, 7).setValue(r.code === 200 ? 'YES' : 'ERR ' + r.code);
    Utilities.sleep(300);                         // stay under rate limits
  }
}

The guard column is the whole trick. Apps Script triggers can fire twice — after a timeout, or when someone runs the function manually to test. Writing YES back to the row means the worst case is a message that doesn't send, rather than a customer who gets the same reminder four times.

Getting your templates approved

Any message you send outside an open 24-hour conversation window must use a template Meta has approved in advance. Approval usually lands within minutes to a few hours, and rejection is nearly always for the same reasons: promotional language in a utility template, or placeholders with nothing around them.

Submit these as Utility category, not Marketing. A working confirmation template:

Halo {{1}}, booking Anda sudah kami terima.

Tanggal: {{2}}
Jam: {{3}}

Balas pesan ini jika perlu mengubah jadwal.

Three rules that prevent most rejections:

1. Never start or end the body with a placeholder. {{1}}, booking Anda… is fine; a body that is only {{1}} is rejected automatically.
2. Keep utility templates transactional. "Diskon 20% untuk kunjungan berikutnya" turns a utility template into a marketing one, which is priced differently and held to stricter consent rules.
3. Match the language code you send. If the template is registered as id, sending language: { code: 'en' } fails with a mismatch error that reads, unhelpfully, as a missing template.

Handling replies, reschedules and cancellations

The moment a customer replies, a 24-hour service window opens in which you can send free-form messages without a template. That window is where rescheduling actually happens.

For most businesses the right answer is not to automate this. Route inbound replies to a shared WhatsApp inbox your staff already watch, and let a human move the booking. The automation's job is to start the conversation reliably; a person is better at the twenty seconds that follow.

If volume makes that impractical, the next step is a webhook that catches replies containing "batal" or "ubah", flags the row, and notifies staff — worth building once you are past roughly fifty appointments a week.

What it costs

Apps Script is free. The messages are not, but they are cheap relative to a no-show.

Meta changed WhatsApp Business pricing from per-conversation to per-message in 2025, and the rate card is revised periodically. Check Meta's current pricing page for your country before you budget — do not rely on figures quoted in any article, including this one.

Five things that break in production

1. The token expires. Temporary access tokens last 24 hours. Generate a permanent System User token in Business Manager, or your automation dies silently tomorrow.
2. Dates arrive as strings. If the Sheet column is formatted as text, new Date(date) returns Invalid Date and every comparison fails. Force the column to Date, or parse explicitly.
3. Nobody watches the error column. Add a weekly check, or a line that emails you when a row lands on ERR. An automation with no failure signal is not an automation.
4. The trigger's timezone is not yours. Apps Script projects can default to a US timezone. Set it under Project Settings, or the 17:00 reminder goes out at 05:00 WIB.
5. Someone edits the columns. The script reads by position. Add a column in the middle and every message goes out with the wrong time. Protect the header row.

When to graduate from Sheets

This pattern comfortably carries a few hundred appointments a month. Past that, the signals that you have outgrown it are consistent: more than one person editing the Sheet at once, reminders that need to fire at several intervals, appointments that belong to specific staff with their own calendars, or a need to report on no-show rates over time.

At that point the Sheet becomes the bottleneck rather than the shortcut, and the work moves to a proper booking system or a workflow tool with a real database behind it. The messaging logic you built here transfers almost unchanged — which is the argument for starting this way rather than buying a platform on day one.

Getting help with it

InReality Solutions builds these automations for businesses across Indonesia — WhatsApp booking and reminder flows, Google Sheets and Apps Script pipelines, CRM integrations and reporting dashboards. If you would rather have it built, tested and handed over working, see our AI automation services or talk to our team in Jakarta.