Dynamic Data Entry Form — Step‑by‑step Guide

Quick start — what this app does

• Generates a dynamic form UI that matches the headers in a target sheet.
• Supports creating new records and updating existing rows in the target sheet.
• Auto-detects field types (text, number, email, date, url, select) and builds appropriate inputs.
• Supports dropdown options from inline values or another sheet/range configured in the Config sheet.

1) Open the add‑on

1. Open the Google Sheet where you want the form to write/read data.
2. From the menu: Form → 📋 Dynamic Data Entry Form to open the form sidebar.
3. To configure the form behavior, choose Form → 🛠️ Configure Form (Setup Wizard).

2) Configure the target sheet (Setup Wizard)

The Setup Wizard writes settings into a Config sheet. Follow these steps:

1. Select Target Sheet — the sheet that holds your table (headers + data).
2. Set Header Row — the row that has column names (usually 1).
3. Set Data Start Row — the first row that contains data (usually 2).
4. Set ID Column — the numeric column that contains a unique ID (1=A, 2=B...). If you don't have IDs, the form will auto-generate numeric IDs when adding records.
5. Optionally set Column(s) (`colRanges`) to limit which columns the form uses. Examples: B,C,E:H.
6. Under Dropdown Fields, add rows for any field you want to be a select. Put either comma-separated options (e.g., Red,Green,Blue) or a sheet reference like Lists!A:A to pull options from another sheet.
7. Click 💾 Save Configuration and test with 🧪 Test Configuration to ensure the app can read headers and sample rows.

The app normalizes column blocks and supports specifying single columns (e.g., C) or ranges (E:H) when using Column Ranges.

3) How the dynamic form works

• When the form opens it calls the spreadsheet to read Config and target sheet headers using getSheetInfo().
• Headers are converted into fields. Field types are detected from header names and sample data (e.g., headers containing "date" become date inputs; URLs, images, video become URL inputs).
• Dropdown fields use configured options (from Config or another sheet).
• ID fields are read-only for existing records and auto-assigned when adding new records (incremental numbers).

4) Add a new record

1. Click New Record (or clear the form) to enable adding mode.
2. Fill required fields (marked *). ID field may be left blank — the app will auto-generate the next numeric ID.
3. Click Save Record. The addRecord function appends a new row and returns the created ID and row number.
4. After saving the form reloads visible records so you can navigate to the new row.

If you try to add a record with an ID that already exists, the app will return an error and ask you to pick another ID.

5) Edit an existing record

1. Use the Search by ID dropdown and click Search, or use the navigation buttons ← / → to move between records.
2. Edit fields (ID field will be disabled for existing rows).
3. Click Save Record. The updateRecord function writes only changed values and preserves existing formulas in the row.

If an existing cell in the row contains a formula, the app keeps the formula instead of overwriting it with a blank or typed value.

6) Working with dropdowns

1. Open Setup Wizard → Dropdown Fields to add dropdown configuration.
2. You can provide options as comma-separated values or point to a sheet/range (example: Lists!A:A).
3. If the dropdown field contains a value that's not currently in the options list, the form automatically adds it as an option (so editing won't lose values already in the sheet).

7) Searching & navigation

• The ID dropdown is populated from the target sheet's ID column using getColumnAValues().
• Search will fetch the row with getRecordById(id) and display it in the form. If found, it merges the returned record into the in-memory list.
• Use Previous/Next buttons to navigate through the loaded visible records (getVisibleRecords).

8) Troubleshooting & common errors

• "Target sheet does not exist" — Ensure the sheet name selected in Config matches exactly (case-sensitive).
• Missing headers — Confirm header row contains column names and that Header Row is set correctly in Config.
• Permissions — Make sure your Google account can edit the spreadsheet.
• ID conflicts — If you see an "ID already exists" error when adding, choose a unique ID or let the app auto-generate one.
• Dropdown values not found — If you referenced another sheet (e.g., Lists!A:A), verify the sheet exists and contains non-empty values.
• Form fields missing or misaligned — Check Column Ranges in Config: if you limited columns, confirm ranges cover the headers you want the form to show.

9) Admin tips (for maintainers)

• Use createConfigSheet() to auto-create a starter Config sheet if needed.
• To include only certain blocks/columns in the form, set Column Ranges to comma-separated column blocks (e.g., B,C,F:H).
• To force a field to be treated as URL, name the header containing image or video — the app forces URL type for those fields.
• When updating the script, keep the client HTML input IDs synced with sanitized header names; the form uses a sanitize utility to map header → element id.