Google Sheets
You can use Google App Scripting to send rows from a Google Sheet to Jomablue. This guide explains how to automatically send attendee information stored in Google Sheets to Jomablue using a webhook import API. The script runs inside Google Sheets (via Google Apps Script) and processes each row as an attendee record.
The script is designed to:
- Read attendee information directly from a Google Sheet (e.g. first name, last name, job title, company, email address, mobile number, and dietary requirements).
- Convert each row into a JSON payload that matches Jomablue’s ingest format.
- Securely send the data to Jomablue using a Bearer token in the request header.
- Ensure no more than one record is sent per second to comply with default rate limits
- Track which rows have already been processed using a “Sent to Jomablue” column, so only new or unsent rows are transmitted.
This approach allows management of attendee data in Google Sheets while ensuring it is reliably and securely pushed into Jomablue. It also provides a simple retry mechanism: only rows without a “Sent to Jomablue” status are processed, making it easy to re-run the script without duplicating records.
function sendRowsToJomablue() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Change sheet name if needed
const data = sheet.getDataRange().getValues();
const webhookUrl = "https://your-endpoint.com/ingest"; // Replace with your Jomablue webhook endpoint
const bearerToken = "YOUR_BEARER_TOKEN_HERE"; // Replace with your token
// Header row is index 0
const headers = data[0];
let sentColIndex = headers.indexOf("Sent to Jomablue");
// If "Sent to Jomablue" column doesn’t exist, add it at the end
if (sentColIndex === -1) {
sheet.getRange(1, headers.length + 1).setValue("Sent to Jomablue");
sentColIndex = headers.length;
}
// Loop through rows (skip header row)
for (let i = 1; i < data.length; i++) {
let row = data[i];
// Skip if already sent
if (row[sentColIndex] === "Yes") {
continue;
}
// Build JSON body
let payload = {
first_name: row[0],
last_name: row[1],
job_title: row[2],
company: row[3],
email: row[4],
mobile: row[5],
dietary_requirements: row[6]
};
let options = {
method: "post",
contentType: "application/json",
headers: {
"Authorization": "Bearer " + bearerToken
},
payload: JSON.stringify(payload),
muteHttpExceptions: true
};
try {
let response = UrlFetchApp.fetch(webhookUrl, options);
let code = response.getResponseCode();
if (code >= 200 && code < 300) {
// Mark as sent
sheet.getRange(i + 1, sentColIndex + 1).setValue("Yes");
Logger.log("Row " + (i + 1) + " sent successfully.");
} else {
Logger.log("Row " + (i + 1) + " failed. Response: " + code + " - " + response.getContentText());
}
} catch (e) {
Logger.log("Error sending row " + (i + 1) + ": " + e);
}
// Wait 1 second before next request
Utilities.sleep(1000);
}
}
What this does:
- Reads your sheet (Sheet1 by default).
- Ensures a “Sent to Jomablue” column exists (creates it if missing).
- Loops rows one by one:
- Skips rows already marked Yes.
- Sends JSON with your specified fields.
- On success → marks "Yes" in "Sent to Jomablue" column.
- Adds a 1 second delay between requests to avoid hammering the endpoint.
How to use this:
- Open your Google Sheet.
- Click Extensions > App Script.
- Paste the code above into the editor.
- Update:
- Sheet1 → your actual sheet name
- https://your-endpoint.com/ingest → your webhook URL
- YOUR_BEARER_TOKEN_HERE → your Jomablue API token
- Approve the authorization when prompted.
- Save and run sendRowsToWebhook.