Writing Beeceptor Payloads to Google Sheets

In this tutorial, you'll learn how to store payloads from Beeceptor to your Google Sheets. The two prerequisites are - we need an empty Google Sheets file, and that we’ll need a HTTP Callout rule to forward the payload.

We’ll perform this operation: Receiving the payload, and only if the payload contains delivered in the key email.event, it will be forwarded to a Google AppsScript Web App, which flattens the values in the payload recursively, and adds these values across columns in our Google Sheets file. We'll implement a mock POST /webhooks/email-status rule that is an HTTP Proxy/ Callout rule. This rule will filter/modify the payload and then send it to the Google Apps Script Web App deployment which will flatten the payload and store it inside the Google Sheets file.

Setting up Google Sheets for receiving payloads

Step 1 - Create a Google Sheets file

The first prerequisite is to create a Google Sheets file. Make sure to note the URL of this Google Sheets file (https://docs.google.com/spreadsheets/d/[SPREADSHEET_ID]/…). You will require this SPREADSHEET_ID for the next step.

Create a Google Sheets file, and note its spreadsheet id

Step 2 - Open Google Apps Script

With the sheet ready, navigate to Extensions > Apps Script and click on it.

Open Apps Script from Google Sheets file menu

A new tab will open showing you the Google Apps Script platform, containing an editor. Paste this script on Code.gs:

/**
 * Handles incoming POST requests (webhooks) from Beeceptor.
 * It parses the JSON payload, flattens the nested data, and writes it
 * as a new row to a SPECIFIC Google Sheet defined by its ID.
 * * @param {Object} e The event parameter object containing the POST request data.
 * @returns {GoogleAppsScript.Content.TextOutput} A JSON response indicating success or failure.
 */
function doPost(e) {
  // 1. Configuration: Use the SPECIFIC Spreadsheet ID and Sheet Tab Name.
  const SPREADSHEET_ID = '[ENTER_YOUR_SPREADSHEET_ID_HERE]'; 
  const SHEET_NAME = "Sheet1"; 

  // Open the spreadsheet by its ID
  const spreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID);
  const sheet = spreadsheet.getSheetByName(SHEET_NAME);

  // Check if the target sheet exists
  if (!sheet) {
    const message = `Error: Sheet '${SHEET_NAME}' not found in the spreadsheet with ID ${SPREADSHEET_ID}.`;
    Logger.log(message);
    return ContentService.createTextOutput(JSON.stringify({ status: "error", message: message }))
      .setMimeType(ContentService.MimeType.JSON);
  }

  try {
    // 2. Parse the incoming JSON payload from the request body
    const jsonString = e.postData.contents;
    const data = JSON.parse(jsonString);

    // 3. Flatten the JSON data
    const flatData = flattenJson(data);

    // 4. Extract headers (keys) and values from the flattened data
    const headers = Object.keys(flatData);
    const values = Object.values(flatData);

    // 5. Ensure headers are written to the first row if the sheet is empty
    if (sheet.getLastRow() === 0) {
      sheet.appendRow(headers);
    }

    // 6. Write the flattened data (values) to a new row
    sheet.appendRow(values);

    // 7. Return a successful JSON response
    return ContentService.createTextOutput(JSON.stringify({ status: "success", rowAdded: sheet.getLastRow(), sheet: SHEET_NAME }))
      .setMimeType(ContentService.MimeType.JSON);

  } catch (error) {
    // Log any processing errors and return an error response
    Logger.log("Error processing request: " + error.toString());
    return ContentService.createTextOutput(JSON.stringify({ status: "error", message: error.toString() }))
      .setMimeType(ContentService.MimeType.JSON);
  }
}

/**
 * Utility function to recursively flatten a nested JSON object.
 * Keys are joined using an underscore (_).
 * * @param {object} obj The object to flatten.
 * @param {string} prefix The prefix to use for nested keys (internal use).
 * @param {object} res The result object (internal use).
 * @returns {object} The flattened object.
 */
function flattenJson(obj, prefix = '', res = {}) {
  for (const key in obj) {
    if (obj.hasOwnProperty(key)) {
      const value = obj[key];
      const newKey = prefix ? prefix + '_' + key : key; 

      if (typeof value === 'object' && value !== null && !Array.isArray(value)) {
        flattenJson(value, newKey, res);
      } else {
        res[newKey] = value;
      }
    }
  }
  return res;
}

Writing code in the Apps Script code editor

If your working Google Sheets file has a different name for its sheet besides Sheet1, make sure to reflect those changes in the code.

Step 3 - Deploy Apps Script as a Web App

Now, save the code by pressing Ctrl + S. Now you have the script ready, and all you need to do is deploy it as a web app. Go to the top right corner, click on the Deploy dropdown button and select New Deployment.

Deploy Apps Script

A modal dialog box will appear. Click on the gears icon next to “Select type” and pick Web app, then give it a description, keep the “Execute As” menu select as-is (your email id), and select Anyone for the “Who has Access” menu select, then hit “Deploy”.

Select deployment type as "Web App"

When you click on Deploy for the first time, Google may ask permission from you to have this Web app access your Google Sheets. Give it a yes, and you will have a Google Apps Script Web app deployed in no time.

Authorize Apps Script Deployment

Note down the Web App URL - It should be in this format: https://script.google.com/macros/s/[DEPLOYMENT_ID]/exec

We'll use this URL as the target endpoint later in the tutorial.

Step 4 - Setting up Beeceptor's HTTP Callout Rule

Now that the Google Sheets side of things have been completed, we’ll go to Beeceptor. We can write a HTTP callout rule in our Beeceptor mock server to only forward payloads containing the string value delivered in the key email.event to our Google Sheets. Any payloads with a different value in email.event will be discarded.

We’ll write this rule:

• Method: POST
• Path :/webhooks/email-status
• Request condition: Request body matches a regular expression: "email"\s*:\s*\{\s*[^}]*"event"\s*:\s*"delivered"
• Synchronous Response Configuration: Ensure the the dropdown has Wait for target response (synchronous) selected.
• Synchronous Request Configuration (HTTP Callout):
  • Method: POST
  • Target endpoint: Your Apps Script's Web App deployment URL (https://script.google.com/macros/s/[DEPLOYMENT_ID]/exec)
  • Configure payload: Forward original payload

Save this rule.

Create an HTTP Callout Rule

Forwarding Payloads: API Client → Beeceptor → Apps Script

Now that your rule is ready, we need to send payloads to Beeceptor. We'll try sending these two payloads via your API Client (Postman, Bruno, etc.) to simulate a webhook producer. We'll use Bruno:

Request 1: Sending an email clicked event.

• Method: POST
• URL: Your Beeceptor mock server base URL + "/webhooks/email-status"
• Request Body:

{
  "type": "email",
  "event_id": "9bfc9f49-f0e2-4470-b7b0-1863ea83fcee",
  "timestamp": "Sun Aug 17 2025 18:20:45 GMT+0530 (India Standard Time)",
  "email": {
    "recipient": "rakesh-schulz584@sbcglobal.name",
    "event": "clicked",
    "campaign_id": "CMP-954"
  }
}

• Response Body:

Hey ya! Great to see you here. Btw, nothing is configured for this request path. Create a rule and start building a mock API.

Beeceptor throws a generic response mentioning that this request path wasn't configured.

Request 1 - Sending an email clicked event

Request 1 - Google Sheets does not get updated

Indeed we have a rule that matched this request path, so what is happening here?

Remember the second request condition - we check if the payload contains delivered for email.event. Our request fails to match with this request condition, and the rule ignores this request path as well as its payload, and thus the payload isn't forwarded to the Google Apps Script Web App, and thus the value isn't stored in Google Sheets.

Upon inspecting the Google Sheets file, nothing has been appended to the rows.

Request 2: Sending an email delivered event (will be processed)

• Method: POST
• URL: Your Beeceptor mock server base URL + "/webhooks/email-status"
• Request Body:

{
  "type": "email",
  "event_id": "bfa5f05c-4b00-44ca-a19c-e134b2f87a21",
  "timestamp": "Wed Jan 22 2025 18:08:41 GMT+0530 (India Standard Time)",
  "email": {
    "recipient": "dilip_mahato785@uol.dev",
    "event": "delivered",
    "campaign_id": "CMP-758"
  }
}

• Response Body:

{
  "status": "success",
  "rowAdded": 2,
  "sheet": "Sheet1"
}

As we can see, a success response was returned to the API call. You can see two rows added. One row being the heading row based on the key name in the flattened payload, and the next row being its values.

Request 2 - Sending an email delivered event

Request 2 - Google Sheets gets updated

When you run either of the requests from Bruno, the requests are sent to Beeceptor. Beeceptor's rule will filter and forward only those requests where the payload contained the string value delivered for the nested key email.event. This means only requests with a request body where email.event was equal to the string value delivered will be forwarded to the Google Apps Script Web App deployment, and the Web App will receive these values, flatten them recursively, and insert them into our Google Sheets file.

What's Next

This is not the end of the story. You can perform a wide host of features using just Google Apps Script to store data in various methods.

Infact, this isn't even limited to Apps Script. You can make use of automation platforms like n8n or Zapier to perform tasks of similar nature.