Send events to Google Sheets

A webhook tells another tool when something happens in WAFlow. This page connects a webhook to a Google Sheet, so every event you choose becomes a new row, by itself. You need nothing except a Google account. It takes about ten minutes, and you only copy and paste.

Here is how it works:

1. You add a small script to your Google Sheet.
2. Google turns it into a web address.
3. You give that address to a WAFlow webhook. From then on, every event you choose arrives as a row.

Each kind of event gets its own tab in the sheet, named after the event (message_received, chat_new, tag_added and so on), with the time first and one column for each piece of data.

1. Make a sheet and open Apps Script#

1. Open Google Sheets and start a new blank spreadsheet.
2. Click the title and give it a name you will recognise, for example WAFlow leads.

3. Open the Extensions menu and choose Apps Script.

   

4. A new tab opens: the Apps Script editor, where small programs for your sheet live. Because you opened it from this sheet, the script belongs to this sheet. Click the project name at the top and call it WAFlow webhook.
5. The editor starts with a sample function. Click inside the code, select everything with Ctrl + A, and press Delete.

2. Paste the script#

Click Copy on the box below, and paste the code into the editor. Then click Save (the disk icon) or press Ctrl + S.

// WAFlow to Google Sheets
// Paste this whole file into Extensions > Apps Script (replace what is there), then Deploy > New deployment > Web app.
// Every event WAFlow sends becomes a new row, in a tab named after the event (message_received, chat_new, ...).

/** @OnlyCurrentDoc */ // the script may only touch this one spreadsheet, nothing else in your Google account

// Optional. Type any word between the quotes, and add ?token=THAT-WORD at the end of the web address in WAFlow.
// Requests without it are refused.
const SECRET = '';

// Opening the web address in a browser shows this, so you can check that the deployment works.
function doGet() {
  return ContentService.createTextOutput('WAFlow webhook is ready. Paste this address into WAFlow.');
}

// WAFlow calls this for every event.
function doPost(e) {
  const lock = LockService.getScriptLock();
  lock.waitLock(20000); // one event at a time, so two rows never land on the same line
  try {
    if (SECRET && e.parameter.token !== SECRET) return reply({ ok: false, error: 'wrong token' });

    const payload = JSON.parse(e.postData.contents); // { event, at, data }
    const book = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = book.getSheetByName(payload.event) || book.insertSheet(payload.event);

    const row = Object.assign({ Time: stamp(payload.at), Event: payload.event }, flatten(payload.data));
    const headers = headersFor(sheet, Object.keys(row));
    const values = headers.map((h) => (row[h] == null ? '' : String(row[h])));

    // Everything is saved as plain text, so nothing a customer types can turn into a formula.
    const line = sheet.getRange(sheet.getLastRow() + 1, 1, 1, values.length);
    line.setNumberFormat('@').setValues([values]);
    return reply({ ok: true });
  } catch (err) {
    return reply({ ok: false, error: String(err) });
  } finally {
    lock.releaseLock();
  }
}

// The first row of a tab holds the column names. A field seen for the first time gets a new column.
function headersFor(sheet, keys) {
  const width = sheet.getLastColumn();
  const headers = width ? sheet.getRange(1, 1, 1, width).getValues()[0] : [];
  keys.forEach((k) => { if (headers.indexOf(k) === -1) headers.push(k); });
  if (headers.length !== width) sheet.getRange(1, 1, 1, headers.length).setNumberFormat('@').setValues([headers]).setFontWeight('bold');
  return headers;
}

// { chat: { name: 'Riya' } } becomes { 'chat.name': 'Riya' }, and a list becomes text.
function flatten(value, prefix, out) {
  out = out || {};
  if (value && typeof value === 'object' && !Array.isArray(value)) {
    Object.keys(value).forEach((k) => flatten(value[k], prefix ? prefix + '.' + k : k, out));
  } else if (prefix) {
    out[prefix] = Array.isArray(value) ? JSON.stringify(value) : value;
  }
  return out;
}

// The time of the event, in the time zone of this script (File > Project settings).
function stamp(iso) {
  return Utilities.formatDate(new Date(iso), Session.getScriptTimeZone(), 'yyyy-MM-dd HH:mm:ss');
}

function reply(result) {
  return ContentService.createTextOutput(JSON.stringify(result)).setMimeType(ContentService.MimeType.JSON);
}

What the script does#

Everything is saved as plain text, so nothing a customer types, for example a message that starts with =, can ever turn into a formula.

3. Publish it and copy the web address#

1. Click Deploy, then New deployment.

2. Click the gear next to Select type, and choose Web app.

   

3. Add a description if you like, for example WAFlow webhook.
4. Set Execute as to Me, so the script writes into your sheet with your own permission.
5. Set Who has access to Anyone, so WAFlow can reach it without signing in to Google. The address is long and secret, and only people who have it can use it.
6. Click Deploy.
7. The first time, Google asks for permission. Click Authorize access and choose your account. If Google says the app is not verified, that is normal: you wrote the script yourself. Click Advanced, then Go to WAFlow webhook (unsafe), and then Allow. The permission it asks for is only this spreadsheet.

8. Your Web app URL appears. It ends in /exec. Click Copy.

   

Open the address in a new browser tab. You should see WAFlow webhook is ready. If you do, the script is published.

4. Connect WAFlow#

1. In WAFlow, open Webhook (the sixth icon in the bar) and click Add webhook.
2. Name: for example Leads to Google Sheet.
3. URL: paste the web address you copied.
4. Chrome asks for permission to reach script.google.com. Click Allow and confirm in the small window. It will not ask again.
5. Send when: tick the events you want, for example Message received and New chat. Every event you pick gets its own tab.
6. Leave Signing secret and Extra headers empty. Google's script cannot read request headers, so WAFlow sends only the data to a script.google.com address. To protect the address, use the Secret word instead.
7. Click Save.

5. Test it#

1. Click the play icon on your webhook, to send a test. You should see Test delivered.
2. Go back to your sheet. A new tab called test has appeared, with one row: the time, the event and the test message.
3. Now a real one: ask a friend to write to you. A moment later a tab called message_received appears, with a row for the message: the time, who wrote, their number, and what they said.

You can filter, sort or chart the tabs like any other data, build a pivot table on them, or share the sheet with your team.

What each event writes#

The chatId is the contact's WhatsApp id, the phone number followed by @c.us.

Protect the address#

Anyone who has the web address can add rows to your sheet, so keep it private. To make it harder still:

1. In the script, type a word between the quotes of SECRET, for example const SECRET = 'blue-otter-42';.
2. Add ?token= and that word to the end of the address in WAFlow: https://script.google.com/macros/s/…/exec?token=blue-otter-42.
3. Save the script, then publish a new version.

Requests without the right word are refused, and nothing is written. Google still answers OK to a refused request, so a test can say Test delivered while no row appears. If that happens, check the word.

Change the script later#

After you edit the script, the published copy does not change by itself. Publish the new version:

1. Click Deploy, then Manage deployments.
2. Click the pencil on your deployment.
3. Under Version, choose New version, and click Deploy.

The web address stays the same, so you do not need to change anything in WAFlow.

If something is wrong#

Good to know#

• One webhook can send several events. Each kind gets its own tab, so the columns stay tidy.
• A busy account can fill a sheet fast. Choose the few events you really need, for example New chat and Tag added, instead of every message.
• To put everything in one tab, change book.getSheetByName(payload.event) || book.insertSheet(payload.event) to use one fixed name, such as 'WAFlow'. The columns then grow to cover every event.
• To send to another spreadsheet, make a copy of your sheet, open Apps Script in the copy, and publish it there. Each sheet has its own address.
• Requests to a script.google.com address carry only the data, without the X-WACRM-Event and X-WACRM-Signature headers. The event name and the time are in the data. See Webhooks.