Google Sheets

Integrate your Google Sheets with Mercuri to easily reach your customers or clients through WhatsApp, without needing to open the Mercuri Dashboard.

Follow the steps below to set up the integration instantly.

Step 1: Open Google Apps Script

Open your Google Sheet
Go to Extensions → Apps Script

Step 2: Create Required Files

You need to create the following two files:

Sidebar.html
Code.gs

A. To create the Sidebar.html file:

Click the Plus (+) icon next to Files
Select HTML
Rename the file to Sidebar.html

⚠️ Make sure the file name matches exactly. Otherwise, the script may throw an error.

Copy and paste the following HTML code into the file.

Sidebar.html

Sidebar.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <style>
      :root {
        --border: #d9d9d9;
        --bg-soft: #f7f7f7;
        --text-muted: #666;
        --accent: #0f766e;
        --danger: #b42318;
      }

      body {
        font-family: Arial, sans-serif;
        padding: 12px;
        margin: 0;
        color: #222;
      }

      h3 {
        margin: 0 0 12px;
      }

      label {
        display: block;
        margin-top: 10px;
        font-weight: 600;
      }

      select,
      input,
      textarea,
      button {
        width: 100%;
        margin-top: 4px;
        padding: 8px;
        box-sizing: border-box;
        border: 1px solid var(--border);
        border-radius: 8px;
      }

      button {
        cursor: pointer;
        background: var(--accent);
        color: #fff;
        border: none;
      }

      button.secondary {
        background: #fff;
        color: #222;
        border: 1px solid var(--border);
      }

      .muted {
        color: var(--text-muted);
        font-size: 12px;
        margin-top: 4px;
      }

      .selection-box {
        margin-top: 6px;
        padding: 10px;
        border: 1px solid var(--border);
        border-radius: 10px;
        background: var(--bg-soft);
        font-size: 13px;
      }

      .selection-actions {
        margin-top: 8px;
      }

      .selection-actions button {
        background: #fff;
        color: #222;
        border: 1px solid var(--border);
      }

      .section {
        margin-top: 16px;
        padding-top: 12px;
        border-top: 1px solid #ececec;
      }

      .param-row {
        margin-top: 10px;
        padding: 10px;
        background: var(--bg-soft);
        border: 1px solid var(--border);
        border-radius: 10px;
      }

      .param-grid {
        display: grid;
        gap: 8px;
      }

      .param-label {
        font-size: 12px;
        color: var(--text-muted);
        margin-top: 2px;
      }

      .param-actions {
        margin-top: 8px;
        text-align: right;
      }

      .param-actions button {
        width: auto;
        padding: 6px 10px;
        background: #fff;
        color: var(--danger);
        border: 1px solid var(--border);
      }

      #status {
        margin-top: 12px;
        white-space: pre-wrap;
        font-size: 12px;
      }
    </style>
  </head>
  <body>
    <h3>Mercuri Sender</h3>

    <label>Recipient column</label>
    <select id="recipient"></select>

    <label>Country column (optional)</label>
    <select id="country"></select>

    <label>Default Country (optional)</label>
    <input id="defaultCountry" placeholder="IN / US / etc">

    <div class="section">
      <label>Phone Number ID</label>
      <input id="phoneNumberId" placeholder="Paste from Mercuri">

      <label>Template ID</label>
      <input id="templateId" placeholder="Paste from Mercuri">
    </div>

    <div class="section">
      <label>Template parameters</label>
      <div class="muted">Add each Mercuri variable and map it directly to a Google Sheet column.</div>
      <div id="paramsList"></div>
      <button type="button" class="secondary" onclick="addParamRow()">Add Parameter</button>
    </div>

    <div class="section">
      <label>Selected rows to send</label>
      <div id="selectedRowsBox" class="selection-box">Loading selection...</div>
      <div class="selection-actions">
        <button type="button" class="secondary" onclick="refreshSelection()">Refresh Selected Rows</button>
      </div>
    </div>

    <button type="button" onclick="sendNow()">Send Messages</button>

    <div id="status"></div>

    <script>
      let headers = [];
      let templateVariables = {};
      let flatVariables = [];
      let selectedRows = [];

      function fillSelect(id, allowBlank) {
        const el = document.getElementById(id);
        el.innerHTML = '';

        if (allowBlank) {
          const blank = document.createElement('option');
          blank.value = '';
          blank.textContent = '-- None --';
          el.appendChild(blank);
        }

        headers.forEach(function(header) {
          const opt = document.createElement('option');
          opt.value = header;
          opt.textContent = header;
          el.appendChild(opt);
        });
      }

      function buildVariableList(variableMap) {
        const list = [];
        Object.keys(variableMap).forEach(function(groupName) {
          variableMap[groupName].forEach(function(item) {
            list.push({
              group: groupName,
              text: item.text,
              value: item.value,
              isMediaVariable: item.isMediaVariable
            });
          });
        });
        return list;
      }

      function createHeaderOptions(select, allowBlank) {
        select.innerHTML = '';

        if (allowBlank) {
          const blank = document.createElement('option');
          blank.value = '';
          blank.textContent = '-- Select Column --';
          select.appendChild(blank);
        }

        headers.forEach(function(header) {
          const opt = document.createElement('option');
          opt.value = header;
          opt.textContent = header;
          select.appendChild(opt);
        });
      }

      function createVariableOptions(select) {
        select.innerHTML = '';

        Object.keys(templateVariables).forEach(function(groupName) {
          const group = document.createElement('optgroup');
          group.label = groupName;

          templateVariables[groupName].forEach(function(item) {
            const opt = document.createElement('option');
            opt.value = item.value;
            opt.textContent = item.text + ' (' + item.value + ')';
            group.appendChild(opt);
          });

          select.appendChild(group);
        });
      }

      function addParamRow(initialValue) {
        const wrap = document.createElement('div');
        wrap.className = 'param-row';

        const keyLabel = document.createElement('div');
        keyLabel.className = 'param-label';
        keyLabel.textContent = 'Mercuri variable';

        const variableSelect = document.createElement('select');
        variableSelect.className = 'param-key';
        createVariableOptions(variableSelect);

        const columnLabel = document.createElement('div');
        columnLabel.className = 'param-label';
        columnLabel.textContent = 'Google Sheet column';

        const columnSelect = document.createElement('select');
        columnSelect.className = 'param-column';
        createHeaderOptions(columnSelect, true);

        const actions = document.createElement('div');
        actions.className = 'param-actions';
        const removeButton = document.createElement('button');
        removeButton.type = 'button';
        removeButton.textContent = 'Remove';
        removeButton.onclick = function() {
          wrap.remove();
        };
        actions.appendChild(removeButton);

        const grid = document.createElement('div');
        grid.className = 'param-grid';
        grid.appendChild(keyLabel);
        grid.appendChild(variableSelect);
        grid.appendChild(columnLabel);
        grid.appendChild(columnSelect);
        wrap.appendChild(grid);
        wrap.appendChild(actions);

        document.getElementById('paramsList').appendChild(wrap);

        if (initialValue && initialValue.key) {
          variableSelect.value = initialValue.key;
        }
        if (initialValue && initialValue.columnName) {
          columnSelect.value = initialValue.columnName;
        }
      }

      function collectParams() {
        return Array.from(document.querySelectorAll('.param-row')).map(function(rowEl) {
          const columnInput = rowEl.querySelector('.param-column');

          return {
            key: rowEl.querySelector('.param-key').value,
            valueType: 'column',
            columnName: columnInput ? columnInput.value : ''
          };
        }).filter(function(item) {
          return item.key && item.columnName;
        });
      }

      function updateSelectionUI(selection) {
        selectedRows = selection && selection.rowNumbers ? selection.rowNumbers : [];
        const label = selection && selection.label ? selection.label : 'No data rows selected';
        const count = selection && selection.count ? selection.count : 0;
        const box = document.getElementById('selectedRowsBox');

        if (!count) {
          box.textContent = 'No data rows selected. Select rows in the sheet, then click "Refresh Selected Rows".';
          return;
        }

        box.textContent = 'Rows selected (' + count + '): ' + label;
      }

      function refreshSelection() {
        document.getElementById('selectedRowsBox').textContent = 'Refreshing selection...';
        google.script.run.withSuccessHandler(function(selection) {
          updateSelectionUI(selection);
        }).getSelectedRowsData();
      }

      function sendNow() {
        const config = {
          mappings: {
            recipient: document.getElementById('recipient').value,
            country: document.getElementById('country').value
          },
          defaults: {
            country: document.getElementById('defaultCountry').value.trim()
          },
          phoneNumberId: document.getElementById('phoneNumberId').value.trim(),
          templateId: document.getElementById('templateId').value.trim(),
          parameterMappings: collectParams(),
          selectedRows: selectedRows
        };

        if (!selectedRows.length) {
          document.getElementById('status').textContent =
            'Please select one or more data rows in the sheet, then click "Refresh Selected Rows".';
          return;
        }

        document.getElementById('status').textContent = 'Sending...';

        google.script.run
          .withSuccessHandler(function(result) {
            const ok = result.filter(function(r) { return r.success; }).length;
            document.getElementById('status').textContent =
              'Done. Success: ' + ok + ' / ' + result.length + '\n\n' +
              JSON.stringify(result, null, 2);
          })
          .withFailureHandler(function(err) {
            document.getElementById('status').textContent = 'Error: ' + err.message;
          })
          .sendMappedMessages(config);
      }

      function init() {
        google.script.run.withSuccessHandler(function(data) {
          headers = data.headers || [];
          templateVariables = data.templateVariables || {};
          flatVariables = buildVariableList(templateVariables);
          updateSelectionUI(data.selection || {});

          fillSelect('recipient', false);
          ['country'].forEach(function(id) {
            fillSelect(id, true);
          });

          if (!document.querySelector('.param-row')) {
            addParamRow();
          }
        }).getSidebarData();
      }

      init();
    </script>
  </body>
</html>

B. To Create the Code.gs File

Click the Plus (+) icon next to Files
Select Script
Rename the file to Code.gs

⚠️ Ensure the file name is exactly Code.gs to avoid script errors.

Copy and paste the following code into the editor.

Code.gs

Code.gs

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Mercuri')
    .addItem('Open Sender', 'showMercuriSidebar')
    .addToUi();
}

function showMercuriSidebar() {
  const html = HtmlService.createHtmlOutputFromFile('Sidebar')
    .setTitle('Mercuri WhatsApp Sender');
  SpreadsheetApp.getUi().showSidebar(html);
}

function getSheetHeaders() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  return headers;
}

function getSheetPreview() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const data = sheet.getDataRange().getValues();
  return {
    headers: data[0],
    rows: data.slice(1, Math.min(data.length, 6))
  };
}

function sendMappedMessages(config) {
  const apiKey = PropertiesService.getScriptProperties().getProperty('MERCURI_API_KEY');
  if (!apiKey) throw new Error('Missing MERCURI_API_KEY in Script Properties');

  const sheet = SpreadsheetApp.getActiveSheet();
  const data = sheet.getDataRange().getValues();
  const headers = data[0];

  const headerMap = {};
  headers.forEach((h, i) => headerMap[String(h).trim()] = i);

  const results = [];
  const selectedRows = config.selectedRows || [];

  selectedRows.forEach(sheetRowNumber => {
    const rowIndex = sheetRowNumber - 1;
    const row = data[rowIndex];
    if (!row) return;

    const recipient = getCellByHeader(row, headerMap, config.mappings.recipient);
    const country = config.mappings.country ? getCellByHeader(row, headerMap, config.mappings.country) : config.defaults.country;
    const phoneNumberId = config.mappings.phoneNumberId
      ? getCellByHeader(row, headerMap, config.mappings.phoneNumberId)
      : config.defaults.phoneNumberId;
    const templateId = config.mappings.templateId
      ? getCellByHeader(row, headerMap, config.mappings.templateId)
      : config.defaults.templateId;

    const parameters = (config.parameterMappings || []).map(colName => {
      return getCellByHeader(row, headerMap, colName);
    });

    const payload = {
      phoneNumberId: String(phoneNumberId || ''),
      channel: 'whatsapp',
      recipient: String(recipient || ''),
      message: {
        type: 'template',
        template: {
          templateId: String(templateId || ''),
          parameters: parameters
        }
      },
      saveToInbox: true
    };

    if (country) payload.country = String(country);

    try {
      validatePayload(payload);

      const response = UrlFetchApp.fetch('https://api.mercuri.cx/v1/send_message', {
        method: 'post',
        contentType: 'application/json',
        headers: {
          Authorization: 'Bearer ' + apiKey
        },
        payload: JSON.stringify(payload),
        muteHttpExceptions: true
      });

      const code = response.getResponseCode();
      const bodyText = response.getContentText();
      let body = {};

      try {
        body = JSON.parse(bodyText);
      } catch (e) {}

      results.push({
        rowNumber: sheetRowNumber,
        httpCode: code,
        success: code >= 200 && code < 300 && body.status === 'success',
        messageId: body.messageId || '',
        status: body.status || 'failed',
        errorCode: body.errorCode || '',
        error: body.errors ? JSON.stringify(body.errors) : bodyText
      });
    } catch (err) {
      results.push({
        rowNumber: sheetRowNumber,
        httpCode: '',
        success: false,
        messageId: '',
        status: 'failed',
        errorCode: 'SCRIPT_ERROR',
        error: err.message
      });
    }
  });

  writeResults(results, config.resultColumns || {});
  return results;
}

function getCellByHeader(row, headerMap, headerName) {
  const idx = headerMap[headerName];
  return idx === undefined ? '' : row[idx];
}

function validatePayload(payload) {
  if (!payload.phoneNumberId) throw new Error('Missing phoneNumberId');
  if (!payload.recipient) throw new Error('Missing recipient');
  if (!payload.message?.template?.templateId) throw new Error('Missing templateId');
  if (!Array.isArray(payload.message.template.parameters)) {
    throw new Error('Template parameters must be an array');
  }
}

function writeResults(results, resultColumns) {
  const sheet = SpreadsheetApp.getActiveSheet();
  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  const headerMap = {};
  headers.forEach((h, i) => headerMap[String(h).trim()] = i + 1);

  results.forEach(r => {
    const row = r.rowNumber;
    if (resultColumns.status && headerMap[resultColumns.status]) {
      sheet.getRange(row, headerMap[resultColumns.status]).setValue(r.status);
    }
    if (resultColumns.messageId && headerMap[resultColumns.messageId]) {
      sheet.getRange(row, headerMap[resultColumns.messageId]).setValue(r.messageId);
    }
    if (resultColumns.error && headerMap[resultColumns.error]) {
      sheet.getRange(row, headerMap[resultColumns.error]).setValue(r.error);
    }
  });
}