Get Booking Data For Google Sheets

googlesheets.js

/**
 * Fetches all bookings from Bookla API with pagination
 * @param {string} companyID - The company UUID
 * @param {string} startDate - Start date (YYYY-MM-DD or ISO format)
 * @param {string} endDate - End date (YYYY-MM-DD or ISO format)
 * @param {string} apiKey - API key for authentication
 * @returns {Array} Table with headers and booking data
 */
function BOOKINGS_TABLE(companyID, startDate, endDate, apiKey) {
  const baseUrl = `https://us.bookla.com/api/v1/companies/${companyID}/bookings`;
  const limit = 100;
  const headers = { "Accept": "application/json" };
  if (apiKey && apiKey.trim()) headers["x-api-key"] = apiKey.trim();

  // Convert dates to ISO format with timezone
  const fromDate = new Date(startDate).toISOString();
  const toDate = new Date(endDate).toISOString();

  let allBookings = [];
  let allServices = new Map();
  let allResources = new Map();
  let allClients = new Map();
  let offset = 0;
  let hasMore = true;

  while (hasMore) {
    const url = `${baseUrl}?from=${encodeURIComponent(fromDate)}&to=${encodeURIComponent(toDate)}&offset=${offset}&limit=${limit}`;

    const res = UrlFetchApp.fetch(url, {
      method: "get",
      headers,
      muteHttpExceptions: true,
    });

    const code = res.getResponseCode();
    if (code < 200 || code >= 300) {
      return [["HTTP " + code, res.getContentText()]];
    }

    const json = JSON.parse(res.getContentText());

    // Merge services and resources into maps
    (json.services || []).forEach(s => allServices.set(s.id, s.name || s.id));
    (json.resources || []).forEach(r => allResources.set(r.id, r.name || r.id));

    // Build clients map with email
    (json.clients || []).forEach(c => {
      allClients.set(c.id, {
        name: c.name || "",
        email: c.email || ""
      });
    });

    // Collect bookings
    const bookings = json.bookings || [];
    allBookings = allBookings.concat(bookings);

    // Check if there are more pages
    if (bookings.length < limit) {
      hasMore = false;
    } else {
      offset += limit;
    }
  }

  // Map bookings to rows
  const rows = allBookings.map(b => {
    const client = allClients.get(b.clientID) || {};
    return [
      b.startTime || "",
      allServices.get(b.serviceID) || b.serviceID || "",
      allResources.get(b.resourceID) || b.resourceID || "",
      b.price != null ? b.price / 100 : "",
      b.status || "",
      client.email || ""
    ];
  });

  // Sort by date ascending
  rows.sort((a, b) => new Date(a[0]) - new Date(b[0]));

  return [
    ["Date", "Service", "Resource", "Price", "Status", "Email"],
    ...rows
  ];
}