Connect Google Sheets

← Back to Developers

This guide adds a Shoal menu to a Google Sheet with two choices. Send Attendees to Shoal adds or updates everyone on a tab called Attendees. Get Attendees from Shoal fills a tab called From Shoal with everyone on the event, including who has registered and checked in. You set it up once by pasting a script; after that it's just menu clicks.

1. Set up your sheet

Make a new Google Sheet (or open the one with your list).
Rename the tab with your people to Attendees (double-click the tab name at the bottom).
Put these headings in the first row, one per column: Email, First Name, Last Name, Job Title, Company. Only Email is required.
Optional columns: Status (Invited, Registered or Cancelled), Table, Seat and Track (the track's name). Any other column is sent as the answer to the registration question with the same name, such as Dietary Needs. Delete any column you don't want sent.
Fill in one person per row below the headings.

2. Paste the script

In the sheet's menu, choose Extensions, then Apps Script. A new tab opens with a code editor.
Delete everything in the editor, then copy the whole script below (use the copy icon) and paste it in.
At the top of the script, replace PASTE_YOUR_API_KEY_HERE with your key and PASTE_YOUR_EVENT_ID_HERE with your event id. Keep the quote marks.
Choose the save icon (or press Ctrl+S, Command+S on a Mac).
Go back to your sheet's tab and reload the page. After a few seconds a Shoal menu appears next to Help.

// ===== Your Shoal settings: paste between the quotes =====
var SHOAL_KEY = "PASTE_YOUR_API_KEY_HERE";
var EVENT_ID = "PASTE_YOUR_EVENT_ID_HERE";
// ==========================================================

var SHOAL_URL = "https://shoalevents.com/api/v1/events/";

// Column headings in your Attendees tab and the Shoal field each one fills.
// Every other column is sent as the answer to the registration question with the same name.
var COLUMNS = {
  "email": "email", "first name": "first_name", "last name": "last_name",
  "job title": "job_title", "company": "company", "status": "status",
  "table": "table", "seat": "seat", "track": "track"
};

// Adds the Shoal menu when the sheet opens.
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("Shoal")
    .addItem("Send Attendees to Shoal", "sendAttendees")
    .addItem("Get Attendees from Shoal", "getAttendees")
    .addToUi();
}

// Sends one request to Shoal and returns its answer, or stops with Shoal's error message.
function shoal(method, path, body) {
  var options = {
    method: method,
    headers: { Authorization: "Bearer " + SHOAL_KEY },
    muteHttpExceptions: true
  };
  if (body) {
    options.contentType = "application/json";
    options.payload = JSON.stringify(body);
  }
  var response = UrlFetchApp.fetch(SHOAL_URL + EVENT_ID + path, options);
  var text = response.getContentText();
  if (response.getResponseCode() >= 300) {
    var message = text;
    try { message = JSON.parse(text).error; } catch (e) {}
    throw new Error("Shoal said: " + message);
  }
  return JSON.parse(text);
}

// Adds or updates everyone on the Attendees tab. Empty cells are skipped, so they never erase anything.
function sendAttendees() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Attendees");
  if (!sheet) throw new Error("Rename the tab with your people to Attendees first.");
  var rows = sheet.getDataRange().getDisplayValues();
  var headings = rows.shift();
  var people = [];
  rows.forEach(function (row) {
    var person = {};
    var answers = {};
    headings.forEach(function (heading, i) {
      var name = String(heading).trim();
      var value = String(row[i]).trim();
      if (!name || !value) return;
      var field = COLUMNS[name.toLowerCase()];
      if (field === "status") person.status = value.toLowerCase();
      else if (field) person[field] = value;
      else answers[name] = value;
    });
    if (Object.keys(answers).length) person.answers = answers;
    if (person.email) people.push(person);
  });

  var created = 0;
  var updated = 0;
  for (var i = 0; i < people.length; i += 500) {
    var result = shoal("post", "/attendees", { attendees: people.slice(i, i + 500) });
    created += result.created;
    updated += result.updated;
  }
  SpreadsheetApp.getActiveSpreadsheet().toast(created + " added and " + updated + " updated in Shoal.", "Shoal");
}

// Fills the From Shoal tab with everyone on the event.
function getAttendees() {
  var people = [];
  var page;
  do {
    page = shoal("get", "/attendees?limit=500&offset=" + people.length);
    people = people.concat(page.data);
  } while (page.data.length > 0 && people.length < page.total);

  var questions = [];
  people.forEach(function (p) {
    Object.keys(p.answers).forEach(function (q) {
      if (questions.indexOf(q) === -1) questions.push(q);
    });
  });
  var headings = ["Email", "First Name", "Last Name", "Job Title", "Company", "Status", "Registered At",
    "Checked In At", "Table", "Seat", "Track", "Ticket Type", "Ticket Link"].concat(questions);
  var rows = people.map(function (p) {
    return [p.email, p.first_name, p.last_name, p.job_title, p.company, p.status, p.registered_at,
      p.checked_in_at, p.table, p.seat, p.track, p.ticket_type, p.ticket_url]
      .concat(questions.map(function (q) { return p.answers[q]; }))
      .map(function (v) {
        v = v === null || v === undefined ? "" : String(v);
        // Text starting with = + - or @ would run as a formula, so it's kept as plain text.
        return /^[=+\-@]/.test(v) ? "'" + v : v;
      });
  });

  var book = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = book.getSheetByName("From Shoal") || book.insertSheet("From Shoal");
  sheet.clearContents();
  sheet.getRange(1, 1, 1, headings.length).setValues([headings]);
  if (rows.length) sheet.getRange(2, 1, rows.length, headings.length).setValues(rows);
  book.toast(people.length + " people from Shoal.", "Shoal");
}

3. Run it the first time

In your sheet, choose Shoal, then Send Attendees to Shoal.
Google asks you to allow the script. Choose Continue, then pick your Google account.
If Google says it hasn't verified this app, that's normal for a script you wrote yourself. Choose Advanced, then Go to Untitled project (unsafe), then Allow.
Choose Shoal, then Send Attendees to Shoal again. A message in the bottom corner says how many people were added and updated.

Change your list and send again whenever you like: people already in Shoal are updated, not added twice. Everyone new arrives as Invited (unless your Status column says otherwise), and nobody is emailed until you send invitations or tickets from the Attendees screen.

Bring registrations back

Choose Shoal, then Get Attendees from Shoal. A From Shoal tab fills with everyone on the event: their status, when they registered and checked in, their table, seat, track and ticket link, plus a column for each registration question. It's rewritten each time, so don't type in that tab; keep your own notes on another one.

Refresh it automatically (optional)

In the Apps Script tab, choose Triggers (the alarm clock icon on the left).
Choose Add Trigger in the bottom corner.
Under Choose which function to run, pick getAttendees.
Under Select event source, pick Time-driven, then choose how often, such as Hour timer and Every hour.
Choose Save. The From Shoal tab now updates on its own.

Next: Connect Zapier or Make