Generating Event Badges with Avery Labels Using the Accelevents API and Google Sheets

Learn how to create event badges using Avery Labels, the Accelevents API, and Google Sheets. This guide will walk you through the steps to retrieve registration data from Accelevents and automatically populate it into a custom event badge design.

1. On your Google Sheets file, click Extensions then select Apps Script.

2. Paste the script below and click Save.

// Global variables to store configurations

var SHEET_NAME_CONFIG = 'Config';

var SHEET_NAME_DATA = 'Registrations';

function onOpen() {

var ui = SpreadsheetApp.getUi();

ui.createMenu('Registration Tool')

.addItem('Show Configuration', 'showConfiguration')

.addItem('Retrieve New Registrations', 'fetchNewRegistrationsAndGenerateQRCodes')

.addItem('Update QR Size', 'updateQRSize')

.addToUi();

}

function showConfiguration() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheetByName(SHEET_NAME_CONFIG);

if (!sheet) {

sheet = ss.insertSheet(SHEET_NAME_CONFIG);

}

// Define the configuration items

var configItems = [

['API Key', ''],

['Event Name', ''],

['Last Ticket ID', '0'],

['QR Size', '100'] // Changed default to 100

];

// Check existing values and update/add as necessary

for (var i = 0; i < configItems.length; i++) {

var existingValue = sheet.getRange(i + 1, 1).getValue();

if (existingValue !== configItems[i][0]) {

// If the config item doesn't exist, add it

sheet.getRange(i + 1, 1, 1, 2).setValues([configItems[i]]);

} else {

// If it exists, ensure the label is correct (column A)

sheet.getRange(i + 1, 1).setValue(configItems[i][0]);

}

}

sheet.autoResizeColumns(1, 2);

sheet.showSheet(); // Ensure the sheet is visible

sheet.activate();

}

function fetchNewRegistrationsAndGenerateQRCodes() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var configSheet = ss.getSheetByName(SHEET_NAME_CONFIG);

var apiKey = configSheet.getRange('B1').getValue();

var eventName = configSheet.getRange('B2').getValue();

var lastProcessedId = Number(configSheet.getRange('B3').getValue()) || 0;

var qrSize = Number(configSheet.getRange('B4').getValue()) || 100;

if (!apiKey || !eventName) {

SpreadsheetApp.getUi().alert('Please fill in both API Key and Event Name in the Config sheet.');

return;

}

var baseUrl = 'https://api.accelevents.com/rest/events/' + eventName + '/staff/allAttendees';

var pageSize = 100;

var page = 0;

var allNewAttendees = [];

var latestId = lastProcessedId;

var shouldContinue = true;

while (shouldContinue) {

var apiUrl = baseUrl + '?page=' + page + '&size=' + pageSize + '&recurringEventId=&searchString=&ticketTypes=&ticketStatus=Booked,Checked%20In,UNPAID&ticketTypeIds=&isUnique=false&isBulkPrintPage=false';

var options = {

'method': 'GET',

'headers': {

'Key': apiKey,

'accept': 'application/json'

}

};

var response = UrlFetchApp.fetch(apiUrl, options);

var data = JSON.parse(response.getContentText());

for (var i = 0; i < data.attendees.length; i++) {

var attendee = data.attendees[i];

if (attendee.userId > lastProcessedId) {

allNewAttendees.push(attendee);

latestId = Math.max(latestId, attendee.userId);

} else {

shouldContinue = false;

break;

}

}

if (data.attendees.length < pageSize || !shouldContinue) {

break;

}

page++;

}

if (allNewAttendees.length === 0) {

SpreadsheetApp.getUi().alert('No new registrations found.');

return;

}

// Get or create the data sheet

var sheet = ss.getSheetByName(SHEET_NAME_DATA);

if (!sheet) {

sheet = ss.insertSheet(SHEET_NAME_DATA);

}

// Check if header row exists, if not, add it

if (sheet.getLastRow() === 0) {

var headers = ['First Name', 'Last Name', 'Email', 'Ticket Type', 'Status', 'Barcode ID', 'Registration Timestamp', 'QR Code URL'];

sheet.getRange(1, 1, 1, headers.length).setValues([headers]);

}

// Calculate the initial number of registrations (excluding header)

var initialRegistrations = Math.max(0, sheet.getLastRow() - 1);

// Populate data

var lastRow = sheet.getLastRow();

var newItemsStartRow = lastRow + 1;

for (var i = 0; i < allNewAttendees.length; i++) {

var attendee = allNewAttendees[i];

var timestamp = new Date(attendee.orderDate);

var rowData = [

attendee.firstName,

attendee.lastName,

attendee.email,

attendee.ticketType,

attendee.status,

attendee.barcode,

timestamp.toLocaleString(),

generateQRCodeUrl(attendee.barcode, qrSize)

];

sheet.getRange(lastRow + 1, 1, 1, rowData.length).setValues([rowData]);

lastRow++;

}

// Adjust column widths

if (allNewAttendees.length > 0) {

sheet.autoResizeColumns(1, 8);

}

// Update last processed ID in config

configSheet.getRange('B3').setValue(latestId);

// Calculate total number of registrations after adding new ones

var totalRegistrations = sheet.getLastRow() - 1; // Subtract 1 to exclude header

// Display completion message with new and total registration counts

SpreadsheetApp.getUi().alert('Process completed successfully!\n\n' +

'New registrations added: ' + allNewAttendees.length + '\n' +

'Total registrations: ' + totalRegistrations + '\n' +

'New items start at row: ' + newItemsStartRow);

}

function generateQRCodeUrl(data, size) {

return "https://api.qrserver.com/v1/create-qr-code/?size=" + size + "x" + size + "&data=" + encodeURIComponent(data);

}

function updateQRSize() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var configSheet = ss.getSheetByName(SHEET_NAME_CONFIG);

var dataSheet = ss.getSheetByName(SHEET_NAME_DATA);

if (!dataSheet) {

SpreadsheetApp.getUi().alert('No data sheet found. Please run "Retrieve New Registrations" first.');

return;

}

var qrSize = Number(configSheet.getRange('B4').getValue()) || 100;

var lastRow = dataSheet.getLastRow();

var lastColumn = dataSheet.getLastColumn();

// Find the QR Code URL column

var headerRow = dataSheet.getRange(1, 1, 1, lastColumn).getValues()[0];

var qrColumnIndex = headerRow.indexOf('QR Code URL') + 1;

if (qrColumnIndex === 0) {

SpreadsheetApp.getUi().alert('QR Code URL column not found in the data sheet.');

return;

}

// Find the Barcode ID column

var barcodeColumnIndex = headerRow.indexOf('Barcode ID') + 1;

if (barcodeColumnIndex === 0) {

SpreadsheetApp.getUi().alert('Barcode ID column not found in the data sheet.');

return;

}

// Update QR Code URLs

var barcodes = dataSheet.getRange(2, barcodeColumnIndex, lastRow - 1, 1).getValues();

var newQrUrls = barcodes.map(function(row) {

return [generateQRCodeUrl(row[0], qrSize)];

});

dataSheet.getRange(2, qrColumnIndex, lastRow - 1, 1).setValues(newQrUrls);

SpreadsheetApp.getUi().alert('QR Code URLs updated successfully with size ' + qrSize + 'x' + qrSize + '.');

}

3. Go back to the Google Sheet and Registration Tool will appear in the menu. Click it and select Show Configuration.

A pop-up window will appear requiring authentication. Click OK.

Select your Google account, check the boxes to allow access, click Continue.

A new sheet called Config will be created and will have these values:

4. Paste the API key from your organizer console and the event name from the URL of your event.

5. After pasting the API Key and Event Name, click Registration Tools and select Retrieve New Registrations.

It creates a new sheet called Registrations and will have the registration information from your event. Click OK.

1. Create a Google Doc for your event badges. Click Extensions > Add-ons > Get Add-ons.

2. Type avery in the search bar and find Foxy Labels. Click Install.

3. Click Continue. Select an account to sign in with and click Continue again.

Click Allow then select Done.

4. Go back to the Google Spreadsheet and add the word IMAGE to the QR Code URL header in the Registrations sheet.

5. Return to the Google Doc. Click Extensions, select Foxy Labels - Label Maker for Avery & Co, then click Create Labels.

6. A menu will appear on the right side. Select a label template then click Apply Template.

Click Select a Spreadsheet then search and select the Google Spreadsheet with the registration information.

Under Sheet, select Registrations.

7. Under Merge Fields, select the fields to include in the Badge Design. The badge design content can be customized however you want it to be, images can also be uploaded.

8. Once the design is finalized, click Create Labels. When it generates, click Open.

9. It will redirect you to a separate document that has the labels. Each label can be edited as needed. Once finalized, the labels can be printed.