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.

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.

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.