Pre-fill field options from Google Sheets

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
    
})