> ## Documentation Index
> Fetch the complete documentation index at: https://bolna.samvit.xyz/llms.txt
> Use this file to discover all available pages before exploring further.

# Bolna + Google Sheets

> 🎥 Prefer to follow along? Watch the walkthrough for this guide here: [https://youtu.be/UuoblYCPRl4](https://youtu.be/UuoblYCPRl4).

[![Watch the walkthrough on YouTube](https://files.samvit.xyz/bolna/tn-yt.png)](https://youtu.be/UuoblYCPRl4)

# Bolna + Google Sheets: Call Reports and Triggers

> End-to-end flow: set up a Google Sheet and webhook → connect that webhook to a Bolna agent → place a test call and watch execution data land in the sheet → optionally trigger new Bolna calls directly from the same sheet.

## Prerequisites

* Google account with access to Google Sheets & Apps Script.
* Bolna account ([https://platform.bolna.ai/login](https://platform.bolna.ai/login)).
* A phone number you can verify in Bolna.

## Getting Bolna Calls Data in Google Sheet

Capture call execution data from Bolna by exposing a Google Apps Script Web App webhook and wiring it to your Bolna agent.

1. **Create the Google Sheet.** Open [https://sheets.new](https://sheets.new) and name it something like **Bolna Report**. ![New Google Sheet named “Bolna Report”](https://files.samvit.xyz/bolna/1.png)
2. **Open Apps Script.** In the sheet, go to **Extensions → Apps Script** and optionally rename the project to **Bolna Script**. ![Open Apps Script from the Extensions menu](https://files.samvit.xyz/bolna/2.png)
3. **Add the webhook Apps Script code.** This script handles POST requests from Bolna and appends execution data into the sheet (generates a tab named **Calls** with columns for timestamp, call ID, status, toNumber, start and end times).

![Apps Script editor with webhook handler code](https://files.samvit.xyz/bolna/3.png)

> For the exact payload structure from Bolna, refer to the executions API: [https://www.bolna.ai/docs/api-reference/executions/get\_execution](https://www.bolna.ai/docs/api-reference/executions/get_execution).

```javascript theme={null}
/***********************
 * CONFIG
 ***********************/
const SHEET_NAME      = 'Calls';               // Change if you want another sheet name

// Columns we want to keep structured
const HEADER_NAMES = [
  'toNumber',                    // A – numbers to call (required)
  'Call Status',                 // e.g. queued, completed, failed
  'Execution ID',                // Bolna execution / call id
  'Conversation Time (sec)',     // conversation_time
  'Duration (sec)',              // telephony_data.duration
  'Total Cost',                  // total_cost
  'Answered by Voicemail',       // answered_by_voice_mail
  'Recording URL',               // telephony_data.recording_url
  'Hangup By',                   // telephony_data.hangup_by
  'Hangup Reason',               // telephony_data.hangup_reason
  'Transcript',                  // transcript (if provided)
  'Extracted Data (JSON)',       // extracted_data as JSON
  'Recipient Name',              // context_details.recipient_data.name
  'Recipient Email',             // context_details.recipient_data.email
  'Last Updated At'              // timestamp when we last wrote
];

/***********************
 * 1) WEBHOOK HANDLER
 *    (Bolna -> Sheet)
 ***********************/
function doPost(e) {
  const sheet = getOrCreateSheet_();
  const headersMap = ensureHeaders_(sheet);

  try {
    if (!e || !e.postData || !e.postData.contents) {
      return jsonResponse_({ ok: false, error: 'Empty request body' });
    }

    const rawBody = e.postData.contents;
    const payload = JSON.parse(rawBody);

    // Bolna sends an "execution payload" object when a phone call ends.
    // We try to be tolerant to slightly different shapes.
    const execution = extractExecutionFromWebhook_(payload);
    if (!execution) {
      return jsonResponse_({ ok: false, error: 'No execution object found in payload' });
    }

    const execId = execution.id || execution.execution_id || '';
    const telephony = execution.telephony_data || execution.transfer_call_data || {};
    const context = execution.context_details || {};
    const recipientData = context.recipient_data || {};

    const toNumber =
      telephony.to_number ||
      context.recipient_phone_number ||
      recipientData.phone ||
      '';
    const normalizedToNumber = normalizePhone_(toNumber);

    // Try to find the row: prefer Execution ID, fall back to toNumber
    let rowIndex = -1;
    if (execId && headersMap['Execution ID']) {
      rowIndex = findRowByValue_(sheet, headersMap['Execution ID'], execId);
    }
    if (rowIndex === -1 && normalizedToNumber && headersMap['toNumber']) {
      rowIndex = findRowByValue_(
        sheet,
        headersMap['toNumber'],
        normalizedToNumber,
        normalizePhone_
      );
    }

    // If nothing found, append a new row
    if (rowIndex === -1) {
      rowIndex = sheet.getLastRow() + 1;
      if (headersMap['toNumber'] && toNumber) {
        sheet.getRange(rowIndex, headersMap['toNumber']).setValue(toNumber);
      }
    }

    // Write all the pretty data into that row
    writeExecutionToRow_(
      sheet,
      headersMap,
      rowIndex,
      execution,
      telephony,
      context,
      recipientData,
      toNumber
    );

    return jsonResponse_({ ok: true });
  } catch (err) {
    Logger.log('Webhook error: ' + err + '\n' + (err.stack || ''));
    return jsonResponse_({ ok: false, error: String(err) });
  }
}

/***********************
 * Helpers
 ***********************/

function getOrCreateSheet_() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName(SHEET_NAME);
  if (!sheet) {
    sheet = ss.insertSheet(SHEET_NAME);
  }
  return sheet;
}

/**
 * Ensures header row exists and contains all HEADER_NAMES.
 * Returns a map { headerName: columnIndex }.
 * Guarantees col 1 header is exactly "toNumber".
 */
function ensureHeaders_(sheet) {
  let lastRow = sheet.getLastRow();
  let lastCol = sheet.getLastColumn();

  // Completely empty sheet – write all headers in one go
  if (lastRow === 0 || lastCol === 0) {
    sheet.getRange(1, 1, 1, HEADER_NAMES.length).setValues([HEADER_NAMES]);
  } else {
    const row = sheet.getRange(1, 1, 1, lastCol).getValues()[0];
    const headerMap = {};

    for (let c = 0; c < row.length; c++) {
      const name = String(row[c] || '').trim();
      if (name) headerMap[name] = c + 1;
    }

    // Force first column header to be "toNumber"
    if (!row[0] || String(row[0]).trim() !== 'toNumber') {
      sheet.getRange(1, 1).setValue('toNumber');
      headerMap['toNumber'] = 1;
    }

    // Append missing headers to the right
    let col = lastCol + 1;
    HEADER_NAMES.forEach(function (name) {
      if (!headerMap[name]) {
        sheet.getRange(1, col).setValue(name);
        headerMap[name] = col;
        col++;
      }
    });

    return headerMap;
  }

  // After writing fresh headers, build map
  const headers = sheet
    .getRange(1, 1, 1, sheet.getLastColumn())
    .getValues()[0];

  const headerMap = {};
  for (let c = 0; c < headers.length; c++) {
    const name = String(headers[c] || '').trim();
    if (name) headerMap[name] = c + 1;
  }
  return headerMap;
}

/**
 * Find the first row (>=2) in column colIndex whose value matches the given value.
 * If normalizer is provided, compare normalized values (e.g., to align phone formats).
 * Returns row index or -1 if not found.
 */
function findRowByValue_(sheet, colIndex, value, normalizer) {
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) return -1;

  const numRows = lastRow - 1;
  const values = sheet.getRange(2, colIndex, numRows, 1).getValues();
  const needle = normalizer
    ? normalizer(value || '')
    : String(value || '').trim();

  for (let i = 0; i < values.length; i++) {
    const cell = normalizer
      ? normalizer(values[i][0])
      : String(values[i][0] || '').trim();
    if (cell && cell === needle) {
      return i + 2; // actual sheet row
    }
  }
  return -1;
}

/**
 * Try to normalize different possible webhook payload shapes
 * into a single "execution" object.
 *
 * Bolna's execution objects typically contain fields like:
 *  id, agent_id, status, conversation_time, total_cost,
 *  telephony_data { duration, to_number, from_number, recording_url, hangup_by, hangup_reason, ... },
 *  extracted_data, context_details, etc. :contentReference[oaicite:3]{index=3}
 */
function extractExecutionFromWebhook_(payload) {
  if (!payload) return null;

  if (payload.execution) return payload.execution;

  if (Array.isArray(payload.data) && payload.data.length > 0) {
    return payload.data[0];
  }

  // Fall back to assuming payload itself IS the execution object
  return payload;
}

/**
 * Write execution data into a row, using the header map.
 */
function writeExecutionToRow_(sheet, headersMap, rowIndex,
                              execution, telephony, context,
                              recipientData, toNumber) {
  if (headersMap['toNumber'] && toNumber) {
    sheet.getRange(rowIndex, headersMap['toNumber']).setValue(toNumber);
  }

  if (headersMap['Call Status'] && execution.status) {
    sheet.getRange(rowIndex, headersMap['Call Status']).setValue(execution.status);
  }

  if (headersMap['Execution ID']) {
    const execId = execution.id || execution.execution_id || '';
    if (execId) {
      sheet.getRange(rowIndex, headersMap['Execution ID']).setValue(execId);
    }
  }

  if (headersMap['Conversation Time (sec)'] &&
      typeof execution.conversation_time !== 'undefined') {
    sheet.getRange(rowIndex, headersMap['Conversation Time (sec)'])
      .setValue(execution.conversation_time);
  }

  if (headersMap['Duration (sec)'] &&
      typeof telephony.duration !== 'undefined') {
    sheet.getRange(rowIndex, headersMap['Duration (sec)'])
      .setValue(telephony.duration);
  }

  if (headersMap['Total Cost'] &&
      typeof execution.total_cost !== 'undefined') {
    sheet.getRange(rowIndex, headersMap['Total Cost'])
      .setValue(execution.total_cost);
  }

  if (headersMap['Answered by Voicemail'] &&
      typeof execution.answered_by_voice_mail !== 'undefined') {
    sheet.getRange(rowIndex, headersMap['Answered by Voicemail'])
      .setValue(execution.answered_by_voice_mail);
  }

  if (headersMap['Recording URL'] && telephony.recording_url) {
    sheet.getRange(rowIndex, headersMap['Recording URL'])
      .setValue(telephony.recording_url);
  }

  if (headersMap['Hangup By'] && telephony.hangup_by) {
    sheet.getRange(rowIndex, headersMap['Hangup By'])
      .setValue(telephony.hangup_by);
  }

  if (headersMap['Hangup Reason'] && telephony.hangup_reason) {
    sheet.getRange(rowIndex, headersMap['Hangup Reason'])
      .setValue(telephony.hangup_reason);
  }

  if (headersMap['Transcript'] && execution.transcript) {
    sheet.getRange(rowIndex, headersMap['Transcript'])
      .setValue(execution.transcript);
  }

  if (headersMap['Extracted Data (JSON)'] && execution.extracted_data) {
    sheet.getRange(rowIndex, headersMap['Extracted Data (JSON)'])
      .setValue(JSON.stringify(execution.extracted_data));
  }

  if (headersMap['Recipient Name'] && recipientData.name) {
    sheet.getRange(rowIndex, headersMap['Recipient Name'])
      .setValue(recipientData.name);
  }

  if (headersMap['Recipient Email'] && recipientData.email) {
    sheet.getRange(rowIndex, headersMap['Recipient Email'])
      .setValue(recipientData.email);
  }

  if (headersMap['Last Updated At']) {
    sheet.getRange(rowIndex, headersMap['Last Updated At'])
      .setValue(new Date());
  }
}

/**
 * Simple JSON response helper for webhooks.
 */
function jsonResponse_(obj) {
  return ContentService
    .createTextOutput(JSON.stringify(obj))
    .setMimeType(ContentService.MimeType.JSON);
}
```

4. **Deploy as a Web App.** Click **Deploy → New deployment**. ![Deploy script as Web App](https://files.samvit.xyz/bolna/4.png) Select **Web app** as the deployment type. ![Choose Web App deployment type](https://files.samvit.xyz/bolna/5.png) Use description “Web hook for Bolna calls,” set **Execute as** to **Me**, and **Who has access** to **Anyone** so Bolna can reach it. ![Configure Web App access](https://files.samvit.xyz/bolna/6.png) Click **Deploy**, authorize when prompted, then copy the **Web App URL** for use in Bolna. ![Copy the Web App URL after deployment](https://files.samvit.xyz/bolna/7.png)

> Deploying the Web App may require granting the script permission to access your Google Sheet.

> Treat the Web App URL as a secret webhook endpoint. Do not share it publicly.

5. **Connect the webhook in a Bolna agent.** Log in to Bolna ([https://platform.bolna.ai/login](https://platform.bolna.ai/login)). Click **New agent**. ![Create a new Bolna agent](https://files.samvit.xyz/bolna/8.png) Choose the prebuilt **Announcement** agent. ![Choose the Announcement prebuilt agent](https://files.samvit.xyz/bolna/9.png) Open the **Analytics** tab. ![Open the Analytics tab](https://files.samvit.xyz/bolna/10.png) Paste the Web App URL into **Your webhook URL**. ![Paste the Web App URL into the webhook field](https://files.samvit.xyz/bolna/11.png) Click **Save agent**. ![Save the agent after adding the webhook](https://files.samvit.xyz/bolna/12.png)
6. **Place a test call and verify data.** Click **Get call from agent**. ![Start a test call from the agent](https://files.samvit.xyz/bolna/13.png) In the **Place outbound calls** dialog, enter your phone number and click **Place call**. ![Enter your number and place the call](https://files.samvit.xyz/bolna/14.png)

> ⚠️ Trial accounts can only make calls to verified phone numbers. Verify your number first at [https://platform.bolna.ai/verified-phone-numbers](https://platform.bolna.ai/verified-phone-numbers) before placing calls. ![Verified phone numbers page](https://files.samvit.xyz/bolna/16.png)

If your number isn’t verified, you’ll see an error. ![Error shown when number is not verified](https://files.samvit.xyz/bolna/15.png) After verification, place the call again; once the call completes, execution data will arrive automatically in your sheet. ![Google Sheet showing webhook data from Bolna](https://files.samvit.xyz/bolna/17.png)

## Triggering Bolna Calls from Google Sheet

Reuse the same spreadsheet to initiate new Bolna calls by reading phone numbers from a sheet column and calling Bolna’s API via Apps Script.

1. **Prepare the sheet for outbound calls.** Add a new tab (e.g., **Calls**) with the first column titled `toNumber` and list the phone numbers to call. ![Sheet tab with toNumber column for outbound calls](https://files.samvit.xyz/bolna/18.png)
2. **Get the Agent ID and API key from Bolna.** In your agent, click **Copy agent id**. ![Copy the Bolna agent ID](https://files.samvit.xyz/bolna/19.png) Go to the **Developers** tab. ![Open the Developers tab](https://files.samvit.xyz/bolna/20.png) Create a new API key and copy it. ![Create and copy a Bolna API key](https://files.samvit.xyz/bolna/21.png)

> ⚠️ Keep your Bolna API key secret. Do not share it or commit it to source control.

3. **Create the `triggerCalls` Apps Script file.** In Apps Script, add a new file (e.g., `triggerCalls.gs`) and paste the function below. Replace the placeholders `BOLNA_AGENT_ID`and `BOLNA_API_KEY` with your values. ![Add a new Apps Script file for triggerCalls](https://files.samvit.xyz/bolna/22.png)

```javascript theme={null}
/***********************
 * OUTBOUND CALLER
 *    triggerCalls()
 ***********************/
const BOLNA_API_KEY     = 'your-api-key';  // Provided Bolna key
const BOLNA_AGENT_ID    = 'your-agent-id'; // The agent that should place calls
const BOLNA_FROM_NUMBER = '';              // Optional: verified caller ID in E.164, or '' for default
const BOLNA_USER_DATA   = {                // Data to pass through to the agent
  variable1: 'value1',
  variable2: 'value2',
  variable3: 'some phrase as value'
};

/**
 * Iterate through rows, starting at row 2, taking the phone number
 * from the first column (header MUST be "toNumber"), validating,
 * and calling Bolna /call for each valid number.
 */
function triggerCalls() {
  const sheet = getOrCreateSheet_();
  const headersMap = ensureHeaders_(sheet);

  const toNumberCol    = headersMap['toNumber'];
  const statusCol      = headersMap['Call Status'];
  const execIdCol      = headersMap['Execution ID'];
  const lastUpdatedCol = headersMap['Last Updated At'];

  const lastRow = sheet.getLastRow();
  if (lastRow < 2) {
    Logger.log('No data rows found');
    return;
  }

  const numRows = lastRow - 1;
  const toNumbers = sheet.getRange(2, toNumberCol, numRows, 1).getValues();

  for (let i = 0; i < numRows; i++) {
    const rowIndex = i + 2;
    const cellValue = toNumbers[i][0];

    if (!cellValue) continue;

    const phone = String(cellValue).trim();
    if (!phone) continue;

    // Skip already processed rows (you can tweak this condition)
    let currentStatus = statusCol ? sheet.getRange(rowIndex, statusCol).getValue() : '';

    // Validate E.164 format (e.g. +919876543210, +12025551234)
    if (!isValidE164Number_(phone)) {
      if (statusCol) {
        sheet.getRange(rowIndex, statusCol).setValue('invalid_number');
      }
      continue;
    }

    try {
      const result = makeBolnaCall_(phone); // calls /call

      if (statusCol) {
        sheet.getRange(rowIndex, statusCol).setValue(result.status || 'queued');
      }
      if (execIdCol) {
        const execId = result.execution_id || result.run_id;
        if (execId) {
          sheet.getRange(rowIndex, execIdCol).setValue(execId);
        }
      }
      if (lastUpdatedCol) {
        sheet.getRange(rowIndex, lastUpdatedCol).setValue(new Date());
      }
    } catch (err) {
      Logger.log('Error placing call for row ' + rowIndex + ': ' + err);
      if (statusCol) {
        sheet.getRange(rowIndex, statusCol).setValue('error');
      }
      const transcriptCol = headersMap['Transcript'];
      if (transcriptCol) {
        sheet.getRange(rowIndex, transcriptCol).setValue(String(err));
      }
    }
  }
}

/**
 * Actually hits Bolna's /call API.
 * Endpoint: POST https://api.bolna.ai/call with JSON body
 *   { agent_id, recipient_phone_number, user_data?, from_phone_number? }
 * Auth: Authorization: Bearer <token>
 */
function makeBolnaCall_(phoneNumber) {
  const url = 'https://api.bolna.ai/call';

  const payload = {
    agent_id: BOLNA_AGENT_ID,
    recipient_phone_number: phoneNumber,
    user_data: BOLNA_USER_DATA
  };

  if (BOLNA_FROM_NUMBER) {
    payload.from_phone_number = BOLNA_FROM_NUMBER;
  }

  const options = {
    method: 'post',
    muteHttpExceptions: true,
    contentType: 'application/json',
    headers: {
      Authorization: 'Bearer ' + BOLNA_API_KEY
    },
    payload: JSON.stringify(payload)
  };

  const response = UrlFetchApp.fetch(url, options);
  const statusCode = response.getResponseCode();
  const bodyText = response.getContentText();

  if (statusCode < 200 || statusCode >= 300) {
    throw new Error('Bolna /call failed (' + statusCode + '): ' + bodyText);
  }

  // Expected response: { message: "done", status: "queued", execution_id: "...", run_id: "..." }
  const data = JSON.parse(bodyText);
  return data;
}

/**
 * Very basic E.164 validation: +countrycode + 6–14 more digits.
 * Required by Bolna's API for phone numbers. :contentReference[oaicite:2]{index=2}
 */
function isValidE164Number_(value) {
  const str = String(value || '').trim();
  return /^\+[1-9]\d{6,14}$/.test(str);
}

/**
 * Normalize phone numbers so different formats map to the same comparable value.
 * Removes every non-digit character.
 */
function normalizePhone_(value) {
  return String(value || '').replace(/[^\d]/g, '');
}
```

4. **Run the function manually (first test).** In Apps Script, select `triggerCalls` and click **Run**. Authorize if prompted. Bolna will call the numbers listed in `toNumber`, and the script writes call status and execution ID.
5. **Optional: Add a button in the sheet to trigger calls.** In the sheet, go to **Insert → Drawing**. ![Insert a drawing to create a button](https://files.samvit.xyz/bolna/23.png) Draw a small rectangle to serve as a button. ![Draw a rectangle button](https://files.samvit.xyz/bolna/24.png) Click the drawing’s three-dots menu and choose **Assign script**. ![Assign a script to the drawing](https://files.samvit.xyz/bolna/25.png) Enter `triggerCalls` as the function name. ![Enter the triggerCalls function name](https://files.samvit.xyz/bolna/26.png) Clicking the button now runs `triggerCalls` and starts calls for the numbers in the sheet.

> The button is a simple convenience. You can also add custom menus or time-based triggers if you prefer.

You can now view inbound call execution reports in Google Sheets and launch new Bolna calls directly from the same Sheet.