# Google Sheet

This document explains how to set up Google Apps Script so your Google Sheet can automatically send data into CDP whenever certain cells are updated or when a new row is added.

You will use a simple trigger (`onEdit`) to automatically run a script whenever a cell in your Google Sheet is edited. This allows the system to detect changes in specific columns or detect new rows, then send an event to your API/webhook.

***

### 1. Open the Apps Script Editor

* In your Google Sheet, go to **Extensions** in the top menu.
* Select **Apps Script**.
  * A new tab will open with the Apps Script code editor.

<figure><img src="/files/Ni1Zco4O05VJ1SRz0YA2" alt=""><figcaption></figcaption></figure>

### 2. Write Your Apps Script

Replace the existing content in **Code.gs** with the script below.

```
/**
 * Computes the MD5 hash of a given string.
 *
 * This helper wraps Google Apps Script's Utilities.computeDigest()
 * and returns a lowercase hexadecimal MD5 hash.
 *
 * @param {string} str - The input string to hash.
 * @return {string} The MD5 hash in hex format.
 */
function _cdpMD5(str) {
  var bytes = Utilities.computeDigest(
    Utilities.DigestAlgorithm.MD5,
    str
  );
  var hex = '';
  for (var i = 0; i < bytes.length; i++) {
    var v = (bytes[i] + 256) % 256;
    hex += ('0' + v.toString(16)).slice(-2);
  }
  return hex;
}

/**
 * Global script configuration.
 *
 * @property {string} SHEET_NAME - Name of the sheet to monitor, or '*' for any sheet.
 * @property {number} HEADER_ROW - Row index of the header row.
 * @property {string} LOG_SHEET_NAME - Name of the log sheet for event tracking.
 * @property {boolean} MUTUAL_EXCLUSIVE - Placeholder for future logic (unused).
 * @property {string[]} MONITORED_HEADERS - Column headers that trigger events on edit.
 */
const CONFIG = {
  SHEET_NAME: 'Sheet1',
  HEADER_ROW: 1,
  LOG_SHEET_NAME: 'Event_Log',
  MUTUAL_EXCLUSIVE: false,
  MONITORED_HEADERS: ['Status', 'Name', 'Phone']
};

/**
 * Trigger function fired when a user edits the spreadsheet.
 *
 * Detects:
 *  - Direct edits to monitored columns → COLUMN_CHANGED
 *  - Completion of all monitored columns → ROW_READY
 *
 * Logs the event and sends an event payload to an external API.
 *
 * @param {GoogleAppsScript.Events.SheetsOnEdit} e - The onEdit() event object.
 */
function onEdit(e) {
  try {
    if (!e) return;

    const range = e.range;
    const sheet = range.getSheet();

    // Ignore edits outside configured sheet
    if (CONFIG.SHEET_NAME !== '*' && sheet.getName() !== CONFIG.SHEET_NAME) return;

    // Ignore header row
    if (range.getRow() <= CONFIG.HEADER_ROW) return;

    const headers = sheet.getRange(CONFIG.HEADER_ROW, 1, 1, sheet.getLastColumn()).getValues()[0];

    // Get column indices for monitored headers
    const monitoredCols = CONFIG.MONITORED_HEADERS
      .map(h => headers.indexOf(h) + 1)
      .filter(colIdx => colIdx > 0);

    if (monitoredCols.length === 0) return;

    const editedCol = range.getColumn();
    const row = range.getRow();
    const rowValues = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];

    let eventType = null;
    let meta = {};

    // Detect individual monitored column change
    if (range.getNumRows() === 1 && range.getNumColumns() === 1 &&
        monitoredCols.includes(editedCol)) {
      
      const newValue = e.value;
      const oldValue = e.oldValue;

      eventType = 'COLUMN_CHANGED';
      meta = {
        editedColumnHeader: headers[editedCol - 1],
        oldValue: oldValue,
        newValue: newValue
      };

      /**
       * Optional: Mutual exclusive behavior for checkbox-like columns.
       *
       * When enabled (CONFIG.MUTUAL_EXCLUSIVE === true), if the user checks a
       * monitored checkbox column, all other monitored columns in the same row
       * will be unchecked automatically.
       *
       * This behaves like radio-button style selection but using checkboxes.
       */
      if (CONFIG.MUTUAL_EXCLUSIVE && (newValue === 'TRUE' || newValue === true)) {
        monitoredCols.forEach(col => {
          if (col !== editedCol) {
            const otherCell = sheet.getRange(row, col);

            // Uncheck other monitored checkboxes
            if (otherCell.isChecked && otherCell.isChecked()) {
              otherCell.setValue(false);
            }
          }
        });
      }
    }

    // Detect completion of all monitored columns
    const monitoredValues = monitoredCols.map(c => rowValues[c - 1]);
    const allFilled =
      monitoredValues.length > 0 &&
      monitoredValues.every(v => v !== '' && v !== null && v !== undefined);

    if (!eventType && allFilled) {
      eventType = 'ROW_READY';
      meta = { completedColumns: CONFIG.MONITORED_HEADERS };
    }

    if (!eventType) return;

    // Send event to API
    sendRowEvent_(headers, rowValues, {
      eventType,
      rowNumber: row,
      meta
    });

  } catch (err) {
    Logger.log(err);
  }
}

/**
 * Sends data about the edited row to an external API endpoint.
 *
 * This includes:
 *  - Event category & action
 *  - MD5-based UID
 *  - Customer dimensions (hashed phone)
 *  - All row data in "extra"
 *
 * @param {string[]} headers - Column headers from the sheet.
 * @param {Array} rowValues - Values from the edited row.
 * @param {Object} context - Event metadata such as type and row number.
 */
function sendRowEvent_(headers, rowValues, context) {
  const EVENT_ENDPOINT_URL =
    'https://a.cdp.asia/event?resp_type=json&is_server=true&portal_id={{PORTAL_ID}}&prop_id={{PROP_ID}}';

  const data = {};
  headers.forEach((h, idx) => { if (h) data[h] = rowValues[idx]; });

  const phone = data['Phone'] || '';
  const name = data['Name'] || '';
  const customerId = phone ? _cdpMD5(phone) : null;

  var apiDataDims = {
    customers: {
      customer_id: customerId,
      name: name,
      phone: phone
    }
  };

  var options = {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify({
      ec: '', // UPDATE VALUE HERE
      ea: '', // UPDATE VALUE HERE
      uid: _cdpMD5(phone),
      dims: apiDataDims,
      items: [],
      extra: data
    })
  };

  try {
    const response = UrlFetchApp.fetch(EVENT_ENDPOINT_URL, options);
    const responseCode = response.getResponseCode();
    const responseBody = response.getContentText();

    console.log('Response Code:', responseCode);
    console.log('Response Body:', responseBody);
  } catch (e) {
    console.log('Error calling API:', e.message);
  }
}

```

### 3. Create a trigger for `onEdit`

* In the Apps Script editor, open the left sidebar and click **Triggers** (⏰ icon).
* Click the **“+ Add Trigger”** button in the bottom-right corner.
* In the popup, configure:
  * **Choose which function to run**: `onEdit`
  * **Choose which deployment should run**: `Head`
  * **Select event source**: `From spreadsheet`
  * **Select event type**: `On edit`
* Click **Save**.

From now on, every time a user edits the sheet:

* Google Sheets fires the **on edit** event.
* The trigger calls your `onEdit(e)` function.
* Your script checks which row/column changed and decides whether to send an event to your API/CDP.

<figure><img src="/files/qsCZT9x74rxLZfktjSjq" alt=""><figcaption></figcaption></figure>

### 4. Verify that everything works

1. Go back to your **Google Sheet**.
2. Edit one of the columns that your script is watching (for example: update a status or tick a payment checkbox).
3. Return to **Apps Script → Executions** (left sidebar):
   * Check that there is a new successful run of `onEdit`.
4. Check log in CDP
   * Verify that the event was received with the correct row data.

>


---

# 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.antsomi.com/developers-guide/3rd-party-integrations/google-sheet.md?ask=<question>
```

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.