Description
- connect Form Block with Google Sheets, pull data from Google Sheets to site and add a search/filter dropdown
- view demo – password: abc
- buy me a coffee
#1. Install Code
#1.1. First, make sure you have Form Blocks with fields like this.
#1.2. Next, make sure you connected with Google Drive – Google Sheets
#1.3. Next, make sure you have Columns like this in Google Sheets file
(Ảnh)
#1.4. Next, add a Code Block with syntax like this
<div class="form-sheets-search"></div>
#1.5. Next, use this code to Page Header Injection
<!-- 08.26c24v4 Form - Google Sheet - Search -->
<script>
window.FormGoogleSheetSearchOptions = {
applyTo: '.form-sheets-search',
csvUrl: 'https://docs.google.com/spreadsheets/d/e/2PACX-1vROqkw9t4gf96QKkExA0K4ajbJN_sEQDMiYTBAFPU7wjqVLe-36Vpt3lVb3qQTIQ_PCeT-ayQS0L2KN/pub?gid=0&single=true&output=csv',
enableDropdown: true,
enableSearch: true,
companyPlaceholder: 'Choose a company',
searchPlaceholder: 'Search for a member...',
controlTextSize: 14,
tableHeaderTextSize: 16,
tableBodyTextSize: 14,
controlHeight: 48,
cellPaddingY: 10,
cellPaddingX: 12,
controlBackground: '#ffffff',
controlTextColor: '#1a1a1a',
controlBorderColor: '#1a1a1a',
dropdownHoverBackground: '#f1f1f1',
dropdownSelectedBackground: '#e5e5e5',
dropdownItemHeight: 36,
dropdownMaxHeight: 260,
loadingText: 'Loading members...',
noResultsText: 'No members found.',
errorText: 'Unable to load members.'
};
</script>
<script>
(() => {
const options = window.FormGoogleSheetSearchOptions || {};
const selector = options.applyTo || '.form-sheets-search';
const dropdownEnabled = options.enableDropdown !== false;
const searchEnabled = options.enableSearch !== false;
if (!options.csvUrl) return;
const style = document.createElement('style');
style.textContent = `
.form-sheets-search-ui {
width: 100%;
color: inherit;
}
.form-sheets-search-controls {
display: grid;
grid-template-columns: repeat(2, minmax(0, 1fr));
gap: 14px;
margin-bottom: 24px;
position: relative;
z-index: 10;
}
.form-sheets-search-controls.has-one-control {
grid-template-columns: minmax(0, 1fr);
}
.form-sheets-search-dropdown {
position: relative;
width: 100%;
}
.form-sheets-search-trigger,
.form-sheets-search-input {
width: 100%;
height: ${options.controlHeight ?? 48}px;
box-sizing: border-box;
border: 1px solid ${options.controlBorderColor || 'currentColor'};
border-radius: 0;
background: ${options.controlBackground || '#ffffff'};
color: ${options.controlTextColor || 'inherit'};
font-family: inherit;
font-size: ${options.controlTextSize ?? 14}px !important;
}
.form-sheets-search-trigger {
display: flex;
align-items: center;
justify-content: space-between;
gap: 15px;
padding: 0 16px;
margin: 0;
text-align: left;
text-transform: none;
cursor: pointer;
appearance: none;
}
.form-sheets-search-trigger::after {
content: '';
width: 8px;
height: 8px;
flex: 0 0 auto;
border-right: 1.5px solid currentColor;
border-bottom: 1.5px solid currentColor;
transform: translateY(-2px) rotate(45deg);
transition: transform .2s ease;
}
.form-sheets-search-dropdown.is-open .form-sheets-search-trigger::after {
transform: translateY(2px) rotate(225deg);
}
.form-sheets-search-list {
position: absolute;
top: calc(100% + 6px);
left: 0;
z-index: 100;
width: 100%;
max-height: ${options.dropdownMaxHeight ?? 260}px;
box-sizing: border-box;
overflow-y: auto;
list-style: none !important;
margin: 0 !important;
padding: 6px !important;
border: 1px solid ${options.controlBorderColor || 'currentColor'};
background: ${options.controlBackground || '#ffffff'};
color: ${options.controlTextColor || 'inherit'};
box-shadow: 0 10px 30px rgba(0, 0, 0, .12);
}
.form-sheets-search-list[hidden] {
display: none !important;
}
.form-sheets-search-option {
display: flex;
align-items: center;
width: 100%;
height: ${options.dropdownItemHeight ?? 36}px;
box-sizing: border-box;
list-style: none !important;
margin: 0 !important;
padding: 0 12px !important;
font-size: ${options.controlTextSize ?? 14}px !important;
line-height: normal !important;
cursor: pointer;
outline: none;
}
.form-sheets-search-option:hover,
.form-sheets-search-option:focus {
background: ${options.dropdownHoverBackground || '#f1f1f1'};
}
.form-sheets-search-option[aria-selected='true'] {
background: ${options.dropdownSelectedBackground || '#e5e5e5'};
}
.form-sheets-search-input {
min-width: 0;
padding: 0 16px;
margin: 0;
outline: none;
appearance: none;
}
.form-sheets-search-input::placeholder {
color: inherit;
opacity: .55;
}
.form-sheets-search-table-wrapper {
width: 100%;
overflow-x: auto;
}
.form-sheets-search-table {
width: 100%;
min-width: 560px;
border-collapse: collapse;
color: inherit;
}
.form-sheets-search-table th,
.form-sheets-search-table td {
padding: ${options.cellPaddingY ?? 10}px ${options.cellPaddingX ?? 12}px;
border-bottom: 1px solid currentColor;
text-align: left;
vertical-align: top;
}
.form-sheets-search-table th {
font-size: ${options.tableHeaderTextSize ?? 16}px !important;
}
.form-sheets-search-table td {
font-size: ${options.tableBodyTextSize ?? 14}px !important;
overflow-wrap: anywhere;
}
.form-sheets-search-table th:first-child,
.form-sheets-search-table td:first-child {
width: 72px;
text-align: center;
}
.form-sheets-search-status {
margin: 0;
font-size: ${options.tableBodyTextSize ?? 14}px !important;
}
@media (max-width: 767px) {
.form-sheets-search-controls {
grid-template-columns: minmax(0, 1fr);
}
.form-sheets-search-table {
min-width: 480px;
}
}
`;
document.head.appendChild(style);
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 createCell = (tag, value) => {
const cell = document.createElement(tag);
cell.textContent = value;
return cell;
};
const initialize = target => {
if (target.dataset.formSheetsSearch) return;
target.dataset.formSheetsSearch = 'loading';
const ui = document.createElement('div');
const controls = document.createElement('div');
const tableWrapper = document.createElement('div');
const table = document.createElement('table');
const tableHead = document.createElement('thead');
const tableBody = document.createElement('tbody');
const status = document.createElement('p');
const headerRow = document.createElement('tr');
let dropdown;
let trigger;
let triggerText;
let list;
let searchInput;
let populateCompanies = () => {};
let selectedCompany = '';
let records = [];
ui.className = 'form-sheets-search-ui';
controls.className = 'form-sheets-search-controls';
tableWrapper.className = 'form-sheets-search-table-wrapper';
table.className = 'form-sheets-search-table';
status.className = 'form-sheets-search-status';
status.textContent = options.loadingText || 'Loading members...';
tableWrapper.hidden = true;
const render = () => {
const term = searchEnabled
? searchInput.value.toLowerCase().trim()
: '';
const filtered = records.filter(record => {
const companyMatch =
!dropdownEnabled ||
!selectedCompany ||
record.Company === selectedCompany;
const searchText =
`${record.Name || ''} ${record.Email || ''}`.toLowerCase();
return companyMatch &&
(!term || searchText.includes(term));
});
tableBody.replaceChildren();
filtered.forEach((record, index) => {
const row = document.createElement('tr');
row.append(
createCell('td', String(index + 1)),
createCell('td', record.Name || ''),
createCell('td', record.Email || '')
);
tableBody.appendChild(row);
});
const empty = filtered.length === 0;
tableWrapper.hidden = empty;
status.hidden = !empty;
status.textContent = empty
? options.noResultsText || 'No members found.'
: '';
};
if (dropdownEnabled) {
dropdown = document.createElement('div');
trigger = document.createElement('button');
triggerText = document.createElement('span');
list = document.createElement('ul');
dropdown.className = 'form-sheets-search-dropdown';
trigger.className = 'form-sheets-search-trigger';
list.className = 'form-sheets-search-list';
trigger.type = 'button';
trigger.setAttribute('aria-haspopup', 'listbox');
trigger.setAttribute('aria-expanded', 'false');
triggerText.textContent =
options.companyPlaceholder || 'Choose a company';
list.hidden = true;
list.setAttribute('role', 'listbox');
const closeDropdown = () => {
dropdown.classList.remove('is-open');
list.hidden = true;
trigger.setAttribute('aria-expanded', 'false');
};
const openDropdown = () => {
dropdown.classList.add('is-open');
list.hidden = false;
trigger.setAttribute('aria-expanded', 'true');
const selected =
list.querySelector('[aria-selected="true"]') ||
list.firstElementChild;
selected?.focus();
};
const selectOption = item => {
selectedCompany = item.dataset.value;
triggerText.textContent = item.textContent;
list.querySelectorAll('.form-sheets-search-option')
.forEach(option => {
option.setAttribute(
'aria-selected',
String(option === item)
);
});
closeDropdown();
render();
trigger.focus();
};
populateCompanies = () => {
const companies = [...new Set(
records
.map(record => (record.Company || '').trim())
.filter(Boolean)
)];
const values = [
{
value: '',
label: options.companyPlaceholder || 'Choose a company'
},
...companies.map(company => ({
value: company,
label: company
}))
];
list.replaceChildren();
values.forEach((company, index) => {
const item = document.createElement('li');
item.className = 'form-sheets-search-option';
item.dataset.value = company.value;
item.textContent = company.label;
item.tabIndex = -1;
item.setAttribute('role', 'option');
item.setAttribute('aria-selected', String(index === 0));
item.addEventListener('click', () => selectOption(item));
list.appendChild(item);
});
};
trigger.addEventListener('click', () => {
list.hidden ? openDropdown() : closeDropdown();
});
trigger.addEventListener('keydown', event => {
if (event.key === 'ArrowDown' || event.key === 'ArrowUp') {
event.preventDefault();
openDropdown();
}
if (event.key === 'Escape') closeDropdown();
});
list.addEventListener('keydown', event => {
const items = [...list.children];
const currentIndex = items.indexOf(document.activeElement);
if (event.key === 'ArrowDown') {
event.preventDefault();
items[(currentIndex + 1) % items.length]?.focus();
}
if (event.key === 'ArrowUp') {
event.preventDefault();
items[(currentIndex - 1 + items.length) % items.length]?.focus();
}
if (event.key === 'Enter' || event.key === ' ') {
event.preventDefault();
selectOption(document.activeElement);
}
if (event.key === 'Escape') {
closeDropdown();
trigger.focus();
}
});
document.addEventListener('click', event => {
if (!dropdown.contains(event.target)) closeDropdown();
});
trigger.appendChild(triggerText);
dropdown.append(trigger, list);
controls.appendChild(dropdown);
}
if (searchEnabled) {
searchInput = document.createElement('input');
searchInput.className = 'form-sheets-search-input';
searchInput.type = 'search';
searchInput.placeholder =
options.searchPlaceholder || 'Search for a member...';
searchInput.addEventListener('input', render);
controls.appendChild(searchInput);
}
if (controls.children.length) {
if (controls.children.length === 1) {
controls.classList.add('has-one-control');
}
ui.appendChild(controls);
}
['Order', 'Name', 'Email'].forEach(label => {
headerRow.appendChild(createCell('th', label));
});
tableHead.appendChild(headerRow);
table.append(tableHead, tableBody);
tableWrapper.appendChild(table);
ui.append(status, tableWrapper);
target.replaceChildren(ui);
const url = new URL(options.csvUrl);
url.searchParams.set('_', Date.now());
fetch(url)
.then(response => {
if (!response.ok) throw new Error('CSV request failed');
return response.text();
})
.then(text => {
records = parseCsv(text);
if (dropdownEnabled) {
populateCompanies();
}
target.dataset.formSheetsSearch = 'ready';
render();
})
.catch(() => {
tableWrapper.hidden = true;
status.hidden = false;
status.textContent =
options.errorText || 'Unable to load members.';
target.dataset.formSheetsSearch = 'error';
});
};
const start = () => {
document.querySelectorAll(selector).forEach(initialize);
new MutationObserver(records => {
records.forEach(record => {
record.addedNodes.forEach(node => {
if (node.nodeType !== 1) return;
if (node.matches?.(selector)) initialize(node);
node.querySelectorAll?.(selector).forEach(initialize);
});
});
}).observe(document.body, {
childList: true,
subtree: true
});
};
document.readyState === 'loading'
? document.addEventListener('DOMContentLoaded', start)
: start();
})();
</script>
#1.6. The code might not work properly for your specific case. You can use AI to customize the code to suit your needs.
you can use ChatGPT, Claude or this AI Assistant (designed for Squarespace sites)