Using Bookwhen API With Google Apps Script

Recently I was tasked with using the Bookwhen API in a Google Spreadsheet to list every event ever created by an account.

This is the spaghetti code that I managed to throw together to get the job done.

When it’s all put together it fills specific columns in a specific Google Spreadsheet with every events Start Time, End Time, Title, Location, Details, Capacity and the number of Attendees.

get Locations

I created the getLocations() function to get all unique locations used in the account.

This was done separately from the Events in order to speed up the process, as I didn’t want to lookup the name of each events location using the Location ID stored within them.

function getLocations() {
  const apiToken = '[Your API Token Here]';
  const baseUrl = 'https://api.bookwhen.com/v2/locations/';
  let nextPageUrl = baseUrl;

  const headers = {
    'Authorization': 'Token ' + apiToken,
    'Accept': 'application/json'
  };

  Logger.log("-------------- Find Locations --------------");
  
  var result = [];
  while (nextPageUrl) {
    const options = {
      method: 'get',
      headers: headers,
      muteHttpExceptions: true
    };

    try {
      const response = UrlFetchApp.fetch(nextPageUrl, options);
      const statusCode = response.getResponseCode();

      if (statusCode === 200) {
        const data = JSON.parse(response.getContentText());

        // Loop through entries
        data.data.forEach(locations => {
          const locationId = locations.id;
          const locationName = locations.attributes.address_text;
          var r = result.some(loc => loc[0] === locationId);
          // Logger.log(`Index: ${r}`);
          if (!r) {
            // Logger.log(`Location ID: ${locationId}`);
            // Logger.log(`Location Name: ${locationName}`);
            
            result.push([locationId, locationName]);
          }
          
          // Logger.log("Result: " + JSON.stringify(result));
        });

        // Pagination: check if there's a next page
        nextPageUrl = data.links && data.links.next ? data.links.next : null;

      } else {
        Logger.log(`Error ${statusCode}: ${response.getContentText()}`);
        break;
      }
    } catch (e) {
      Logger.log("Location Exception: " + e.message);
      break;
    }
  }
  // Logger.log("-------------- Locations List --------------");
  // for (let i = 0; i < result.length; i++) {
  //   Logger.log(`Location ID: ${result[i][0]}`);
  //   Logger.log(`Location Name: ${result[i][1]}`);
  // }
  Logger.log("-------------- Locations End --------------");
  return result;
  
}

get First Empty Row

Used by me to simply get the first empty row in a specific sheet for a specific set columns starting from row 3.

function getFirstEmptyRow() {
  // Open the spreadsheet and get the sheet by its name
  var automation1 = SpreadsheetApp.openById("[Your Spreadsheet ID Here]").getSheetByName('[Your Sheet Name Here]');
  // Get all data in columns
  var columnAValues = automation1.getRange('G:M').getValues();
  // Loop through the values in columns
  for (var i = 2; i < columnAValues.length; i++) {
    if (columnAValues[i][0] === "") {
      // Return the row number (i + 1 because row numbers start from 1)
      return i + 1;
    }
  }
  // If no empty cell is found, return -1 (or handle it as needed)
  return -1;
}

list Bookwhen Events

Used to retrieve all events between specific dates before then pasting them row by row into a spreadsheet.

function listBookwhenEvents(filters) {
  const apiToken = '[Your API Token Here]';
  const baseUrl = 'https://api.bookwhen.com/v2/events' + filters;
  let nextPageUrl = baseUrl;

  const headers = {
    'Authorization': 'Token ' + apiToken,
    'Accept': 'application/json'
  };

  /**
   * GET LOCATIONS
   */
  const locations = getLocations();

  var result = [];
  while (nextPageUrl) {
    const options = {
      method: 'get',
      headers: headers,
      muteHttpExceptions: true
    };

    try {
      const response = UrlFetchApp.fetch(nextPageUrl, options);
      const statusCode = response.getResponseCode();

      if (statusCode === 200) {
        const data = JSON.parse(response.getContentText());

        // Loop through entries
        data.data.forEach(events => {
          // Logger.log("Event: " + JSON.stringify(events));
          const title = events.attributes.title;
          // Logger.log(`Title: ${title}`);
          const start = events.attributes.start_at;
          const end = events.attributes.end_at;

          var location;
          if(events.relationships.location.data == null || events.relationships.location.data.id == null) {
            location = "N/A";
          } else {

            // Logger.log(`Location ID: ${events.relationships.location.data.id}`);
            const entry = locations.find(loc => loc[0] === events.relationships.location.data.id);
            // Logger.log("Entry: " + JSON.stringify(entry));
            if (entry) {
              location = "" + entry[1];
              // Logger.log(`Found location name: ${location}`);
            } else {
              // Logger.log(`Location ID ${events.relationships.location.data.id} not found.`);
            }
            // location = getLocationByID(events.relationships.location.data.id);
          }
          // Logger.log(`Location: ${location}`);

          var startTime;
          if(start == null) {
            startTime = "N/A";
          } else {
            startTime = new Date(start);
          }
          // Logger.log(`Start: ${startTime}`);

          var endTime;
          if(end == null) {
            endTime = "N/A";
          } else {
            endTime = new Date(end);
          }
          // Logger.log(`End: ${endTime}`);

          var details = events.attributes.details;
          if(details == "" || details == null) {
            details = "N/A";
          }
          var capactiy = events.attributes.attendee_limit;
          if(capactiy == null) {
            capactiy = "N/A";
          }
          var participants = events.attributes.attendee_count;
          if(participants == null) {
            participants = "N/A";
          }

          Logger.log(`Title: ${title}, Start: ${startTime}`);

          // Logger.log(`Title: ${title}, Start: ${startTime}, End: ${endTime}, Location: ${location}, Details: ${details}, Capacity: ${capactiy}, Participants: ${participants}`);
          result.push([startTime, endTime, title, location, details, capactiy, participants]);
          // Logger.log("Result: " + JSON.stringify(result));
        });

        // Pagination: check if there's a next page
        nextPageUrl = data.links && data.links.next ? data.links.next : null;

      } else {
        Logger.log(`Error ${statusCode}: ${response.getContentText()}`);
        break;
      }
    } catch (e) {
      Logger.log("Event Exception: " + e.message);
      break;
    }
  }
  // Get the target sheet
  let sheet = SpreadsheetApp.openById("[Your Spreadsheet ID Here]").getSheetByName('[Your Sheet Name Here]');
  // Get existing values in G3:M (from row 3 down to the last row)
  let lastRow = getFirstEmptyRow();
  Logger.log("Last Row: " + lastRow);
  var row = lastRow;
  Logger.log("-------------- Begin Print --------------");
  for (let i = 0; i < result.length; i++) {
    // Logger.log("Row: " + row + " Result: " + result[i]);
    sheet.getRange(row++, 7, 1, 7).setValues([result[i]]); // (startRow, startCol, numRows, numCols)
  }
  sheet.getRange(row++, 7, 1, 7).clearContent();
  Logger.log("-------------- End Print --------------");
}

Run All

A set of functions for the bulk running of the previous function through various years (usually leading to it timing out halfway through a year, at which point it can be run again with the completed years commented out).

/**
 * For running multiple years in a row.
 * Tip: May time out after printing several years, simply comment out the completed years and re-run.
 */
function runAll() {
  Logger.log("-------------- Begin --------------");
  Logger.log("-------------- 2020 to 2021 --------------");
  list2020to2021();
  Logger.log("-------------- 2021 to 2022 --------------");
  list2021to2022();
  Logger.log("-------------- 2022 to 2023 --------------");
  list2022to2023();
  Logger.log("-------------- 2023 to 2024 --------------");
  list2023to2024();
  Logger.log("-------------- 2024 to 2025 --------------");
  list2024to2025();
  Logger.log("-------------- 2025 to 2026 --------------");
  list2025to2026();
  Logger.log("-------------- 2026 to 2027 --------------");
  list2026to2027();
  Logger.log("-------------- 2027 to 2028 --------------");
  list2027to2028();
  Logger.log("-------------- 2028 to 2029 --------------");
  list2028to2029();
  Logger.log("-------------- Done --------------");
}


function list2020to2021() {
  listBookwhenEvents('?filter[from]=20200401&filter[to]=20210401');
}
function list2021to2022() {
  listBookwhenEvents('?filter[from]=20210402&filter[to]=20220401');
}
function list2022to2023() {
  listBookwhenEvents('?filter[from]=20220402&filter[to]=20230401');
}
function list2023to2024() {
  listBookwhenEvents('?filter[from]=20230402&filter[to]=20240401');
}
function list2024to2025() {
  listBookwhenEvents('?filter[from]=20240402&filter[to]=20250401');
}
function list2025to2026() {
  listBookwhenEvents('?filter[from]=20250402&filter[to]=20260401');
}
function list2026to2027() {
  listBookwhenEvents('?filter[from]=20260402&filter[to]=20270401');
}
function list2027to2028() {
  listBookwhenEvents('?filter[from]=20270402&filter[to]=20280401');
}
function list2028to2029() {
  listBookwhenEvents('?filter[from]=20280402&filter[to]=20290401');
}

Hopefully posting my work in my blog like this will make someone’s google search a bit quicker.

Leave a like or a comment if it helped!