Pre-fill field options with dynamically pulled data from a Google Sheet
Overview
This rule runs as the first step of the form is loaded. It queries a Google Sheet that has a Class Name header and several class names defined below, within the same column. The class names are then set as options of the CoursesDropdown field.
Rule Logic
function fetchSheetData(spreadsheetId, sheetName, apiKey, callback) {
// API endpoint URL
const url = `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values/${sheetName}?key=${apiKey}`;
// Fetch data from Google Sheets
fetch(url)
.then(response => response.json())
.then(data => callback(data.values)); // Return the sheet values
}
function convertToJSON(data) {
const [headers, ...rows] = data;
return rows.map(row => {
let obj = {};
headers.forEach((header, index) => obj[header] = row[index]);
return obj;
});
}
// Usage
const API_KEY = 'DUMMY_KEY'; // Replace with your Google Sheets API Key
const SPREADSHEET_ID = 'DUMMY_ID'; // Replace with your spreadsheet ID
const SHEET_NAME = 'Sheet1'; // Replace with your sheet name
fetchSheetData(SPREADSHEET_ID, SHEET_NAME, API_KEY, (data) => {
const jsonData = convertToJSON(data)
const classes = jsonData.map(account => account["Class Name"])
CoursesDropdown.options = classes
})
