# 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**
1. Open your Google Sheet
2. 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:**
1. Click the **Plus (+)** icon next to **Files**
2. Select **HTML**
3. Rename the file to `Sidebar.html`
> ⚠️ Make sure the file name matches exactly. Otherwise, the script may throw an error.
4. Copy and paste the following HTML code into the file.
**Sidebar.html**
{% code title="Sidebar.html" overflow="wrap" lineNumbers="true" expandable="true" %}
```html
Mercuri Sender
Add each Mercuri variable and map it directly to a Google Sheet column.
Loading selection...
```
{% endcode %}
***
**B. To Create the `Code.gs` File**
1. Click the **Plus (+)** icon next to **Files**
2. Select **Script**
3. Rename the file to `Code.gs`
> ⚠️ Ensure the file name is exactly `Code.gs` to avoid script errors.
4. Copy and paste the following code into the editor.
**Code.gs**
{% code title="Code.gs" overflow="wrap" lineNumbers="true" expandable="true" %}
```js
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);
}
});
}
```
{% endcode %}
***
### **Step 3: Deploy the Script**
1. Click **Deploy** in the top-right corner
2. Select **New Deployment**
3. Under **Select type**, choose **Web App**
4. Fill in the required deployment details
5. Click **Deploy**
***
### **Step 4: Configure the Mercuri API Key**
1. In the Apps Script editor, click the **Settings** icon from the left sidebar
2. Scroll down to the **Script Properties** section
3. Add the following property:
| Property | Value |
| --------------------------- | -------------------- |
| `MERCURI_MESSAGING_API_KEY` | Your Mercuri API Key |
{% hint style="info" %}
Click here to know how to create [API Token in Mercuri](/features/introduction-mercuri-api/how-do-i-create-an-api-token-in-mercuri.md)
{% endhint %}
***
### **Step 5: Open the Mercuri Sender in Google Sheets**
Once the integration is set up successfully:
1. Return to your Google Sheet
2. From the top menu bar, go to **Mercuri → Open Sender**
This will open the **Mercuri WhatsApp Sender** side panel on the left side of the sheet.
***
### **Step 6: Map the Fields**
Map each required field with the corresponding column in your Google Sheet.
**Recipient Column**
* Map the **Recipient** field to the column containing your customer or client phone numbers
**Phone Number ID & Template ID**
* Enter the **Phone Number ID** and **Template ID**
* These can be obtained from the Mercuri Dashboard
{% hint style="info" %}
Click here to learn how to get the [**Phone Number ID**](/features/introduction-mercuri-api/faqs.md#where-can-i-find-the-phone-number-id-of-my-whatsapp-sms-number) and [**Template ID**.](/features/introduction-mercuri-api/faqs.md#how-to-find-the-template-id-of-any-whatsapp-sms-template)
{% endhint %}
***
### **Step 7: Select Rows and Send Messages**
You can select specific rows in the sheet to send messages only to those customers.
Once all fields are mapped:
1. Select the required rows
2. Click **Send** from the side panel
---
# Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://docs.mercuri.cx/integrations-guide/google-sheets.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.