How do I use Eventbrite's API to load all of my event data, attendees, etc. into Google Sheets?

Question

I organize many events on Eventbrite using my company's account, and now I'd like to use the Eventbrite API to extract all the data and load it into tables on Google Sheets without using connectors like Zapier.

I just need the event data and attendee data. Can I do this myself in either the Google Sheets Script Editor or by using formulas like IMPORTDATA() and IMPORT.JSON()?

If I can get it into Google Sheets, then I can treat the loading of data as a "data dump". Then using another sheet, I can create formulas to do automatic analyses and more.

Another challenge I have is I don't know how to create the formula to make these data pulls. I believe I need to find the event data endpoint and attendee data endpoint, but it's not very clear what these are in the documentation.

What would be the best approach to get this data loaded into Google Sheets?

Answer 1

Google Apps Script has a service called UrlFetchApp that allows you to make requests to external APIs.

Calling a JSON API looks something like this:

function getFromAPI(parameter, accessToken) {
  const url = `https://my.example.com/api?parameter=${encodeURIComponent(parameter)}`
  const response = UrlFetchApp.fetch(url, {
    headers: {
      'Authorization': `Brearer ${accessToken}`
    }
  })

  const result = JSON.parse(response.getContentText())
  return result.items.map(item => [item.name, item.value])
}

In this example, it would be called with something like =GETFROMAPI('parameter-value', 'access-token').

This example won't work for your API as it assumes a structure that almost certainly won't be the same. In this example, the answer could be something like:

{
  "items": [
    { "id": "xxx", "name": "X", "value": 32},
    { "id": "yyy", "name": "Y", "value": 2}
  ]
}

With this structure and this code, the result would be a 2x2 data:

(name)	(value)
X	32
Y	2

Note that you may also make a function and execute it from the editor. In it, insert the response manually by using the SpreadsheetApp.

Also, if you have a question in regards to Google Apps Script, use the google-apps-script tag.

References

• UrlFetchApp (Google Apps Script reference)
• UrlFetchApp.fetch(url, params) (Google Apps Script reference)
• SpreadsheetApp (Google Apps Script reference)