CODEWITHSUNDEEP
google-sheetsgoogle-apps-scriptcurlwebhookseventbrite
Young and Social
Young and Social

Reputation: 1

Google Apps Script Eventbrite Webhook Integration

I'm trying to integrate a Google Apps Script to log data from Eventbrite into a Google Sheet. I've deployed my script as a web app and set it to be accessible by "Anyone." Here's the script I'm using (with specifics redacted as asterisks for privacy). I've tried using console.log and logger.log but I'm not getting any logs.

I've tried curl with the following code and it works,

CURL

curl -X POST -H "Content-Type: application/json" -d "{\"event_id\": \"**********\", \"config\": {\"action\": \"order.placed\", \"endpoint_url\": \"**********/exec\", \"user_id\": \"2084338405213\", \"webhook_id\": \"**********\"}, \"attendees\": [{\"profile\": {\"name\": \"John Doe\", \"email\": \"[email protected]\", \"company\": \"Example Co.\", \"job_title\": \"Developer\"}, \"answers\": [{\"question\": \"Are you in your 20s, 30s, or 40s?\", \"answer\": \"30s\"}, {\"question\": \"Gender\", \"answer\": \"Male\"}, {\"question\": \"Sexual Orientation\", \"answer\": \"Straight\"}, {\"question\": \"How would you like someone you match with to contact you?\", \"answer\": \"Email: [email protected]\"}]}]}" "**********"

Command prompt Curl Return

<HTML>
<HEAD>
<TITLE>Moved Temporarily</TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF" TEXT="#000000">
<!-- GSE Default Error -->
<H1>Moved Temporarily</H1>
The document has moved <A HREF="**********">here</A>.
</BODY>
</HTML>

Eventbrite Return on test

302 Found Request ID: ********** Attempts: 1    **********T19:54:57Z
Resend This Request

Request Headers

X-Eventbrite-Event: test
Accept: text/plain
User-Agent: Eventbrite Hookshot 91008c8
X-Eventbrite-Delivery: **********
Content-type: application/json
User-ID-Sender: **********
Request Payload

{
  "api_url": "https://www.eventbriteapi.com/{api-endpoint-to-fetch-object-details}/",
  "config": {
    "action": "test",
    "endpoint_url": "**********",
    "user_id": "**********",
    "webhook_id": "**********"
  }
}
Response Headers

X-XSS-Protection: 1; mode=block
Content-Security-Policy: frame-ancestors 'self'
X-Content-Type-Options: nosniff
Content-Encoding: gzip
Transfer-Encoding: chunked
Expires: Mon, 01 Jan 1990 00:00:00 GMT
Server: GSE
Location: **********
Pragma: no-cache
Cache-Control: no-cache, no-store, max-age=0, must-revalidate
Date: Wed, 15 May 2024 02:54:56 GMT
X-Frame-Options: SAMEORIGIN
Alt-Svc: h3=":443"; ma=2592000,h3-29=":443"; ma=2592000
Content-Type: text/html; charset=UTF-8
Access-Control-Allow-Origin: *
Response Body

<HTML>
<HEAD>
<TITLE>Moved Temporarily</TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF" TEXT="#000000">
<!-- GSE Default Error -->
<H1>Moved Temporarily</H1>
The document has moved <A HREF="**********">here</A>.
</BODY>
</HTML>

CODE


// Global variables
var SHEET_ID = '**********'; // Replace with your Google Sheet ID
var EVENT_ID = '**********'; // Replace with your Eventbrite Event ID
var SHEET_NAME = '**********'; // Replace with your desired sheet name
var API_KEY = '**********'; // Replace with your API key

// Function to generate a unique event number
function generateUniqueEventNumber(sheet) {
  try {
    var min = 1000;
    var max = 9999;
    var uniqueNumber;
    var isUnique = false;

    while (!isUnique) {
      uniqueNumber = Math.floor(Math.random() * (max - min + 1)) + min;
      var values = sheet.getRange("G:G").getValues();
      if (!values.flat().includes(uniqueNumber)) {
        isUnique = true;
      }
    }
    return uniqueNumber;
  } catch (error) {
    console.log("Error generating unique event number: " + error.message);
    throw new Error("Error generating unique event number: " + error.message);
  }
}

// Function to get the answer from attendee responses
function getAnswer(attendee, question) {
  var answers = attendee.answers;
  for (var i = 0; i < answers.length; i++) {
    if (answers[i].question === question) {
      return answers[i].answer;
    }
  }
  return '';
}

// Function to handle order placed
function handleOrderPlaced(e) {
  try {
    var eventData = JSON.parse(e.postData.contents);
    console.log("Event Data: ", eventData);

    if (eventData.event_id !== EVENT_ID) return; // Ignore other events
    var attendee = eventData.attendees[0];
    var sheet = SpreadsheetApp.openById(SHEET_ID).getSheetByName(SHEET_NAME);

    var eventNumber = generateUniqueEventNumber(sheet);
    sheet.appendRow([
      attendee.profile.name,
      attendee.profile.email,
      getAnswer(attendee, 'Are you in your 20s, 30s, or 40s?'),
      getAnswer(attendee, 'Gender'),
      getAnswer(attendee, 'Sexual Orientation'),
      getAnswer(attendee, 'How would you like someone you match with to contact you?'),
      eventData.id,
      eventNumber,
      ''
    ]);
  } catch (error) {
    console.log("Error handling order placed: " + error.message);
  }
}

// Function to handle order refunded
function handleOrderRefunded(e) {
  try {
    var eventData = JSON.parse(e.postData.contents);
    console.log("Event Data: ", eventData);

    if (eventData.event_id !== EVENT_ID) return; // Ignore other events
    var orderId = eventData.id;
    var sheet = SpreadsheetApp.openById(SHEET_ID).getSheetByName(SHEET_NAME);
    var data = sheet.getDataRange().getValues();

    for (var i = 1; i < data.length; i++) {
      if (data[i][6] === orderId) {
        sheet.deleteRow(i + 1);
        break;
      }
    }
  } catch (error) {
    console.log("Error handling order refunded: " + error.message);
  }
}

// Function to handle order updated
function handleOrderUpdated(e) {
  try {
    var eventData = JSON.parse(e.postData.contents);
    console.log("Event Data: ", eventData);

    if (eventData.event_id !== EVENT_ID) return; // Ignore other events
    var attendee = eventData.attendees[0];
    var sheet = SpreadsheetApp.openById(SHEET_ID).getSheetByName(SHEET_NAME);
    var data = sheet.getDataRange().getValues();

    for (var i = 1; i < data.length; i++) {
      if (data[i][6] === eventData.id) {
        sheet.getRange(i + 1, 1, 1, 9).setValues([[
          attendee.profile.name,
          attendee.profile.email,
          getAnswer(attendee, 'Are you in your 20s, 30s, or 40s?'),
          getAnswer(attendee, 'Gender'),
          getAnswer(attendee, 'Sexual Orientation'),
          getAnswer(attendee, 'How would you like someone you match with to contact you?'),
          eventData.id,
          data[i][7], // Event Number remains the same
          data[i][8] // Checked In status remains the same
        ]]);
        break;
      }
    }
  } catch (error) {
    console.log("Error handling order updated: " + error.message);
  }
}

// Function to handle attendee checked in
function handleAttendeeCheckedIn(e) {
  try {
    var eventData = JSON.parse(e.postData.contents);
    console.log("Event Data: ", eventData);

    if (eventData.event_id !== EVENT_ID) return; // Ignore other events
    var attendee = eventData.attendees[0];
    var sheet = SpreadsheetApp.openById(SHEET_ID).getSheetByName(SHEET_NAME);
    var data = sheet.getDataRange().getValues();

    for (var i = 1; i < data.length; i++) {
      if (data[i][6] === eventData.id) {
        sheet.getRange(i + 1, 8).setValue('Yes');
        break;
      }
    }
  } catch (error) {
    console.log("Error handling attendee checked in: " + error.message);
  }
}

// Function to handle attendee updated
function handleAttendeeUpdated(e) {
  try {
    var eventData = JSON.parse(e.postData.contents);
    console.log("Event Data: ", eventData);

    if (eventData.event_id !== EVENT_ID) return; // Ignore other events
    var attendee = eventData.attendees[0];
    var sheet = SpreadsheetApp.openById(SHEET_ID).getSheetByName(SHEET_NAME);
    var data = sheet.getDataRange().getValues();

    for (var i = 1; i < data.length; i++) {
      if (data[i][6] === eventData.id) {
        sheet.getRange(i + 1, 1, 1, 9).setValues([[
          attendee.profile.name,
          attendee.profile.email,
          getAnswer(attendee, 'Are you in your 20s, 30s, or 40s?'),
          getAnswer(attendee, 'Gender'),
          getAnswer(attendee, 'Sexual Orientation'),
          getAnswer(attendee, 'How would you like someone you match with to contact you?'),
          eventData.id,
          data[i][7], // Event Number remains the same
          data[i][8] // Checked In status remains the same
        ]]);
        break;
      }
    }
  } catch (error) {
    console.log("Error handling attendee updated: " + error.message);
  }
}

function doPost(e) {
  try {
    console.log("doPost triggered");
    console.log("Webhook received: " + JSON.stringify(e.postData.contents));
    
    var eventData = JSON.parse(e.postData.contents);
    var eventType = eventData.config.action;
    console.log("Event type received: " + eventType);

    if (eventType === 'test') {
      console.log("Test event received from Eventbrite.");
      return ContentService.createTextOutput(JSON.stringify({result: 'success'}))
        .setMimeType(ContentService.MimeType.JSON);
    }

    if (eventData.event_id !== EVENT_ID) {
      console.log("Ignoring event with ID: " + eventData.event_id);
      return ContentService.createTextOutput(JSON.stringify({result: 'ignored'}))
        .setMimeType(ContentService.MimeType.JSON);
    }

    console.log("Processing event type: " + eventType);

    if (eventType === 'order.placed') {
      console.log("Handling order placed.");
      handleOrderPlaced(e);
    } else if (eventType === 'order.refunded') {
      console.log("Handling order refunded.");
      handleOrderRefunded(e);
    } else if (eventType === 'order.updated') {
      console.log("Handling order updated.");
      handleOrderUpdated(e);
    } else if (eventType === 'attendee.checked_in') {
      console.log("Handling attendee checked in.");
      handleAttendeeCheckedIn(e);
    } else if (eventType === 'attendee.updated') {
      console.log("Handling attendee updated.");
      handleAttendeeUpdated(e);
    }

    console.log("Event processed successfully.");
    return ContentService.createTextOutput(JSON.stringify({result: 'success'}))
      .setMimeType(ContentService.MimeType.JSON);
  } catch (error) {
    console.log("Error in doPost: " + error.message);
    return ContentService.createTextOutput(JSON.stringify({result: 'error', message: error.message}))
      .setMimeType(ContentService.MimeType.JSON);
  }
}

// Setup function to initialize the sheet
function setup() {
  try {
    var sheet = SpreadsheetApp.openById(SHEET_ID).getSheetByName(SHEET_NAME);
    sheet.clear(); // Optional: Clear existing data
    sheet.appendRow(['Name', 'Email', 'Age Range', 'Gender', 'Sexual Orientation', 'Match Contact Info', 'Event Number', 'Checked In?']);
  } catch (error) {
    console.log("Error in setup: " + error.message);
  }
}

I've also tried the following code. This also works with curl but not with Eventbrite

function doPost(e) {
  try {
    Logger.log("doPost triggered");
    var eventData = e.postData.contents;
    Logger.log("Webhook received: " + eventData);
    
    var sheet = SpreadsheetApp.openById('**********').getSheetByName('Registrations');
    sheet.getRange("A2").setValue(eventData); // Write the event data to cell A2
    
    return ContentService.createTextOutput("Success").setMimeType(ContentService.MimeType.TEXT);
  } catch (error) {
    Logger.log("Error in doPost: " + error.message);
    return ContentService.createTextOutput("Error: " + error.message).setMimeType(ContentService.MimeType.TEXT);
  }
}

Upvotes: 0

Views: 187

Answers (1)

Young and Social
Young and Social

Reputation: 1

I figured out the issue to my satisfaction. Once you deploy the web app, you can't edit the code.

I thought you could edit the code, then click Deploy > Manage Deployments > Click the pencil to edit > Click Deploy, and then it would update the code that the web app was running. This is not the case.

It would only read from curl the data formatted to fit my original use case. Since none of the Eventbrite data was formatted that way, I couldn't read anything.

For some reason, logger.log does not add anything to executions, and console.log does not add anything to Google Cloud Logs, so it was hard to troubleshoot.

So my advice is if you don't know the JSON structure upfront, create a test deployment to read the whole package for each case. Then, using that information, create a new app script project formatting the desired information correctly.

Here is some code you can use to do that. I'd also like to know if anyone knows how to get the logging to work.

Note: Curl and Eventbrite give errors saying they're redirecting, but they post the data to the sheet.

function doPost(e) {   var sheetId = 'Your_sheet_ID';   var sheet = SpreadsheetApp.openById(sheetId).getActiveSheet();
     // Log the received data for debugging   Logger.log('Received POST data: ' + e.postData.contents);
     try {
    // Parse the received JSON data
    var data = JSON.parse(e.postData.contents);
    
    // Log the parsed data for debugging
    Logger.log('Parsed data: ' + JSON.stringify(data));
    
    // Flatten the JSON data
    var flattenedData = flattenJson(data);
    
    // Prepare row data
    var rowData = [new Date()];  // Add timestamp
    for (var key in flattenedData) {
      rowData.push(key + ": " + flattenedData[key]);
    }
    
    // Append row to the spreadsheet
    sheet.appendRow(rowData);
    
    return ContentService.createTextOutput("Payload received and appended").setMimeType(ContentService.MimeType.TEXT);   } catch (error) {
    // Log the error
    Logger.log('Error: ' + error.message);
    return ContentService.createTextOutput("Error processing request: " + error.message).setMimeType(ContentService.MimeType.TEXT);   } }

function flattenJson(data, prefix = '', result = {}) {   for (var key in data) {
    if (typeof data[key] === 'object' && data[key] !== null && !Array.isArray(data[key])) {
      flattenJson(data[key], prefix + key + '.', result);
    } else {
      result[prefix + key] = data[key];
    }   }   return result; }

Upvotes: 0

Related Questions