Form Dropdown Get Data from Google Sheets

Description

• form block, dropdown field will get options from Google Sheets
• view demo – password: abc
• buy me a coffee

#1. Install Code

#1.1. Make sure you use Form Block with a Dropdown Field

#1.2. Make sure you have a Google Sheets file with Name column

#1.3. Use code to Page Header Injection

You can use an AI to tweak code for your desired needs.

<!-- 09.26c18v5 Form Dropdown Get Data from Google Sheets -->
<script>
window.FormDropdownGoogleSheetsOptions = {
  csvUrl: 'https://docs.google.com/spreadsheets/d/e/2PACX-1vSURbSeUy9iTud1ZHs8gtYnVvWklWGun_iPLqSs3J3mUB0xKEOE8QH8cgkMl675nGHXHDBmGbAvS3uM/pub?output=csv',
  dropdownLabel: 'Name',
  orderColumn: 'Order',
  nameColumn: 'Name',
  placeholderText: 'Select a name',
  loadingText: 'Loading names...',
  errorText: 'Unable to load names'
};
</script>
<script>
(() => {
  if (window.FormDropdownGoogleSheetsInitialized) return;
  window.FormDropdownGoogleSheetsInitialized = true;

  const options = window.FormDropdownGoogleSheetsOptions || {};
  const csvUrl = options.csvUrl || '';
  const dropdownLabel = options.dropdownLabel || 'Name';
  const orderColumn = options.orderColumn || 'Order';
  const nameColumn = options.nameColumn || 'Name';
  const placeholderText = options.placeholderText || 'Select a name';
  const loadingText = options.loadingText || 'Loading names...';
  const errorText = options.errorText || 'Unable to load names';

  if (!csvUrl) return;

  let status = 'loading';
  let names = [];

  const parseCsv = text => {
    const rows = [];
    let row = [];
    let field = '';
    let quoted = false;

    for (let index = 0; index < text.length; index += 1) {
      const character = text[index];

      if (quoted) {
        if (character === '"' && text[index + 1] === '"') {
          field += '"';
          index += 1;
        } else if (character === '"') {
          quoted = false;
        } else {
          field += character;
        }
      } else if (character === '"') {
        quoted = true;
      } else if (character === ',') {
        row.push(field);
        field = '';
      } else if (character === '\n') {
        row.push(field);
        rows.push(row);
        row = [];
        field = '';
      } else if (character !== '\r') {
        field += character;
      }
    }

    if (field || row.length) {
      row.push(field);
      rows.push(row);
    }

    const headers = (rows.shift() || []).map((header, index) =>
      index === 0
        ? header.replace(/^\uFEFF/, '').trim()
        : header.trim()
    );

    return rows
      .filter(values => values.some(value => value.trim()))
      .map(values => Object.fromEntries(
        headers.map((header, index) => [
          header,
          (values[index] || '').trim()
        ])
      ));
  };

  const findColumn = (record, requestedName) =>
    Object.keys(record).find(
      key => key.toLowerCase() === requestedName.toLowerCase()
    );

  const isTarget = select => {
    const field = select.closest('.form-item');
    const label = field?.querySelector('label.title')?.textContent.trim();

    return select.getAttribute('aria-label')?.trim() === dropdownLabel ||
      label === dropdownLabel;
  };

  const createOption = (text, value = '') => {
    const option = document.createElement('option');
    option.textContent = text;
    option.value = value;
    return option;
  };

  const updateSelect = select => {
    const token = status === 'ready'
      ? `ready-${names.length}-${names.join('|')}`
      : status;

    if (select.dataset.googleSheetDropdown === token) return;
    select.dataset.googleSheetDropdown = token;

    if (status === 'loading') {
      const loading = createOption(loadingText);
      loading.selected = true;
      select.replaceChildren(loading);
      select.disabled = true;
      return;
    }

    if (status === 'error') {
      const error = createOption(errorText);
      error.selected = true;
      select.replaceChildren(error);
      select.disabled = true;
      return;
    }

    const placeholder = createOption(placeholderText);
    placeholder.disabled = true;
    placeholder.hidden = true;
    placeholder.selected = true;

    select.replaceChildren(
      placeholder,
      ...names.map(name => createOption(name, name))
    );
    select.disabled = false;
    select.value = '';
    select.classList.add('show-placeholder');
  };

  const syncDropdowns = () => {
    document.querySelectorAll('.sqs-block-form select')
      .forEach(select => {
        if (isTarget(select)) updateSelect(select);
      });
  };

  const loadNames = async () => {
    try {
      const url = new URL(csvUrl, window.location.href);
      url.searchParams.set('_', Date.now());

      const response = await fetch(url, { cache: 'no-store' });
      if (!response.ok) throw new Error('CSV request failed');

      const records = parseCsv(await response.text());
      if (!records.length) throw new Error('CSV is empty');

      const orderKey = findColumn(records[0], orderColumn);
      const nameKey = findColumn(records[0], nameColumn);
      if (!nameKey) throw new Error('Name column was not found');

      names = [...new Set(
        records
          .map((record, index) => ({
            index,
            order: orderKey ? Number(record[orderKey]) : index,
            name: record[nameKey]?.trim() || ''
          }))
          .filter(record => record.name)
          .sort((first, second) => {
            const firstOrder = Number.isFinite(first.order)
              ? first.order
              : Number.MAX_SAFE_INTEGER;
            const secondOrder = Number.isFinite(second.order)
              ? second.order
              : Number.MAX_SAFE_INTEGER;

            return firstOrder - secondOrder || first.index - second.index;
          })
          .map(record => record.name)
      )];

      if (!names.length) throw new Error('No names were found');
      status = 'ready';
    } catch (error) {
      status = 'error';
    }

    syncDropdowns();
  };

  const start = () => {
    syncDropdowns();
    loadNames();

    new MutationObserver(syncDropdowns).observe(document.body, {
      childList: true,
      subtree: true
    });
  };

  document.readyState === 'loading'
    ? document.addEventListener('DOMContentLoaded', start, { once: true })
    : start();
})();
</script>