Trying to use app script in sheets not working, don't understand a few things
Tom Mort
To allow users to add new values to a drop-down list from the drop-down cell itself, you need a Google Apps Script that uses an
onEdit() trigger to handle the new input and append it to the source data range of the dropdown.
Setup Instructions
Prepare your sheet:
Create a dedicated sheet for your list data (e.g., "ListOptions").
In that sheet, create your list of items in a single column (e.g., column A).
On your main sheet (e.g., "MainSheet"), set up a data validation rule for your target cells (e.g., column A) using the range from "ListOptions" (e.g., 'ListOptions'!A2:A).
In the Advanced options of the Data validation rules panel, under "If the data is invalid:", select Show a warning instead of "Reject input". This allows users to type in new values.
Add the Google Apps Script:
Go to Extensions > Apps Script in your Google Sheets menu.
Delete any existing code in the editor and paste the following script.
javascript
function onEdit(e) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const mainSheetName = 'MainSheet'; // Name of the sheet with the dropdown
const listSheetName = 'ListOptions'; // Name of the sheet with the source list
const targetColumn = 1; // Column number for the dropdown (e.g., 1 for A)
const listColumn = 1; // Column number for the source list (e.g., 1 for A)
const range = e.range;
const sheet = range.getSheet();
// Check if the edit occurred on the correct sheet and column
if (sheet.getName() === mainSheetName && range.getColumn() === targetColumn) {
const newValue = e.value;
// Get the values from the list sheet
const listSheet = ss.getSheetByName(listSheetName);
const lastRow = listSheet.getLastRow();
const listRange = listSheet.getRange(1, listColumn, lastRow, 1);
const listValues = listRange.getValues().flat();
// Check if the new value is already in the list
if (newValue && !listValues.includes(newValue)) {
// Append the new value to the bottom of the list
listSheet.getRange(lastRow + 1, listColumn).setValue(newValue);
// Re-sort the list if desired
//listSheet.getRange(1, listColumn, lastRow + 1, 1).sort(listColumn);
}
}
}
Save the script and go back to your Google Sheet.
I don’t know what Save the script means or see anyway to do it in the Scripts App editor window.
I think I followed all of the steps.
Now, when a user types a new value into the dropdown cell on the "MainSheet" (which currently only shows a warning), the script will automatically add that value to the bottom of the source list on the "ListOptions" sheet, making it available for future use in all cells linked to that data validation range.
Mario Rossi
- Salvataggio / deployment
- Se apri Extensions > Apps Script dal foglio, lo script è “bound” al foglio: non serve alcun deployment per un semplice onEdit. Basta salvare il progetto nell'editor (File > Salva o l'icona salva). Il nuovo editor di solito salva automaticamente ma puoi premere Ctrl/Cmd+S per sicurezza.
- Deploy è necessario solo per web app, add-on o esecuzioni esterne. Per onEdit non serve.
- Trigger onEdit
- onEdit(e) è un simple trigger che viene invocato automaticamente quando un utente modifica il foglio. Non richiede installazione.
- Limitazioni: simple trigger non può fare alcune azioni che richiedono autorizzazioni speciali in altri servizi, ma per leggere/scrivere nello stesso Spreadsheet va bene.
- Se preferisci, puoi creare un installable trigger (Triggers > + Add Trigger) per onEdit — questo richiederà autorizzazioni la prima volta.
- Controlli comuni se non funziona
- Lo script deve essere nel progetto collegato al foglio corretto (aperto da Extensions > Apps Script).
- I nomi dei fogli (MainSheet, ListOptions) devono corrispondere esattamente (maiuscole/minuscole).
- La convalida deve essere impostata su “Mostra avviso” (non “Rifiuta”).
- L'edit deve essere su una singola cella: paste multipla o modifiche di più celle non popolano e.value (lo script non verrà eseguito come previsto).
- Se cancelli la cella o usi Esc, e.value può essere undefined => lo script ignora.
- Se la lista è vuota, getLastRow() può restituire 0 e causare errori; meglio gestirlo.
- Debug rapido
- Aggiungi Logger.log(e) o SpreadsheetApp.getActive().toast(...) per vedere se onEdit viene chiamato.
- Controlla Esecuzioni (Executions) nell'editor per errori.
- Script migliorato con controlli (copia/incolla nel tuo editor)
function onEdit(e) {
if (!e) return;
const ss = SpreadsheetApp.getActiveSpreadsheet();
const mainSheetName = 'MainSheet';
- const listSheetName = 'ListOptions';
const targetColumn = 1; // colonna A
const listColumn = 1; // colonna A nella ListOptions
const sheet = range.getSheet();
if (range.getNumColumns() !== 1 || range.getNumRows() !== 1) return; // solo singola cella
if (range.getColumn() !== targetColumn) return;
if (!newValue) return; // niente da aggiungere (es. cancellazione)
if (!listSheet) return;
// se lista vuota, considera la prima riga disponibile
if (lastRow < 1) lastRow = 0;
const listRange = listSheet.getRange(1, listColumn, Math.max(1, lastRow), 1);
const listValues = listRange.getValues().flat().filter(v => v !== '');
if (listValues.includes(newValue)) return;
// listSheet.getRange(1, listColumn, lastRow + 1, 1).sort({column: listColumn, ascending: true});
}
- adattare lo script alla struttura esatta del tuo foglio (dimmi nomi fogli, riga header),
- fornirti la versione che crea un trigger installable e istruzioni passo-passo per autorizzazioni.
Tom Mort
After you replied I wanted to try working through this. I did realize I hadn’t saved it.
The first part is what I had found at that time. I had other things to do in the afternoon and thought I’d try again on a brand new sheet and also try the improved code.
Here is the intial things I tried and my commentary:
Saving / Deployment
If you open Extensions > Apps Script from the sheet, the script is bound to the sheet: no deployment is needed for a simple onEdit. Just save the project in the editor (File > Save or the save icon). The new editor usually saves automatically, but you can press Ctrl/Cmd+S to save.
I didn’t realize what the save icon was. Maybe because many of the other icons also had a label and it was a light color. I pressed that and the screen sort of blinked so I suppose it was saving.
I went back and tried to enter a new item in the list but nothing was added.I had been thinking of trying control S earlier because that was a common way of saving in earlier times but forgot. When I did this I again saw the screen blink so it was probably saving. I again tried to enter a new value in the drop down list box and again nothing was added.
It looks like if saving needed to be done there was another issue as well.
Deployment is only required for web apps, add-ons, or external executions. It's not required for onEdit.
OK, good to know
Trigger onEdit
onEdit(e) is a simple trigger that is automatically invoked when a user edits the sheet. It doesn't require installation.
Limitations: Simple trigger cannot do some actions that require special permissions in other services, but for reading/writing to the same Spreadsheet it is fine.
If you prefer, you can create an installable trigger (Triggers > + Add Trigger) for onEdit — this will require permissions the first time.
Looks like neither of these is causing an issue
Common checks if it doesn't work
The script must be in the project linked to the correct sheet (opened from Extensions > Apps Script).
I opened the Apps Script editor under Extensions while I had the active workbook open. I don’t recall which sheet was open, probably the main sheet as that is the sheet the drop downs are on. The script has references in it that refer to both the main sheet and the sheet with the list with the range of values.
I will try again opening the script editor while I have the sheet with the list active and also try a sheet that has the dropdown boxes and the range of items for the drop down both on the same sheet.
The sheet names (MainSheet, ListOptions) must match exactly (case sensitive).
I will double check, but I copied and pasted to prevent any typos
Validation must be set to “Show warning” (not “Reject”).
Show warning was and still is checked
The edit must be on a single cell: multiple pastes or edits to multiple cells do not populate e.value (the script will not run as expected).
I don’t really follow that. I have been trying simply to type in with the keyboard the value I want to add while on the cell with the drop down box control
If you delete the cell or use Esc, e.value may be undefined => the script ignores.
I haven’t used Esc of deleted anything
If the list is empty, getLastRow() may return 0 and cause errors; it's best to handle this.
The list is not empty. For testing I just made a list with three items.
Quick debugging
Add Logger.log(e) or SpreadsheetApp.getActive().toast(...) to see if onEdit is called.
I’m guessing I’d add these to the script at the end
Check Executions in the editor for errors.
12:51:20 PM
Notice
Execution started
12:51:20 PM
That is with the old script.
I thought I’d try the new script this evening with another start from the beginning of another new sheet.
I put the revised script in and saved it. At one point I thought that the first line that had just javascript wasn’t part of the script and left it off. It wouldn’t save. I put it back in and it did save, but still didn’t work and when I ran the debugger I got:
9:36:12 PM
Notice
Execution started
9:36:12 PM
Error
ReferenceError: javascript is not defined
(anonymous)
@ Code.gs:1
I thought I’d try the edit control inside the droplist. It was just the settings for the range to get the list from. This time I selected the entire column A which is where the list was.
I tried again and it sort of worked. I entered a couple characters that weren’t on the list. They did appear in the listbox but never got put in the list either.
I did scroll all the way to the bottom to see if maybe it had gone all the way to the bottom and didn’t see it. I also removed the two slashes in front of the line near the end that would resort the list. There were no errors, but no change either.
I would like to try to figure this out, but I’d also like to get this project started too.
Maybe I’ll take you up on you entering the references.
For my main sheet, the one with the dropdown list the name is 2025. For the sheet with the list I’ll just use ListOptions the same as the script does.
When testing today I just used a list with three items in column a of the sheet named ListOptions. The real list has 68 rows and will probably grow larger. The items on this list are descriptions from another source for the records I’m adding. I entered quite a few more earlier today and I know there were some that are not on this current list. I will copy all these new terms and paste them in, sort and remove any duplicates later.
It looks like the way to specify the range is to specify ListOptions!$A:$A which is what you get if you select the entire column. This makes sense if you are going to add items to have plenty of room.
So I would like to work through this, but if you modify the script to work with my project I can, hopefully have it ready to go now. Looks like it might take a while for me to figure this out, even with help.
Tom
--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/QObsC1Za5cI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CACrgDdqMbNXEPsJz2gTiAOmOjnsR7ntC80g1JVn1cMcAO1PtPA%40mail.gmail.com.
Mario Rossi
- Remove the stray text "javascript" at top of the file — that produced ReferenceError when you ran the script.
- Do not press Run in the editor for an onEdit simple trigger: running it directly has no event object e and will error. Test by editing a single cell in the sheet instead, and watch Executions / Logger or toast messages.
- Selecting the whole column (ListOptions!A:A) is fine, but getValues() will include blank cells — handle lastRow when building the list.
- Validation must be “Show warning” and edits must be a single cell (typing in cell with dropdown). Multi-cell edits or pastes don’t provide e.value.
function onEdit(e) {
if (!e) return;
const ss = SpreadsheetApp.getActiveSpreadsheet();
const listSheetName = 'ListOptions';
const targetColumn = 1; // column A
const listColumn = 1; // column A in ListOptions
const sheet = range.getSheet();
if (sheet.getName() !== mainSheetName) return;
if (range.getNumColumns() !== 1 || range.getNumRows() !== 1) return;
if (!newValue) return;
if (!listSheet) return;
if (lastRow < 1) lastRow = 0;
const valuesRange = listSheet.getRange(1, listColumn, Math.max(1, lastRow), 1);
const listValues = valuesRange.getValues().flat().map(v => (v || '').toString().trim()).filter(v => v !== '');
if (listValues.includes(newValue.toString().trim())) return;
// optional: sort
// listSheet.getRange(1, listColumn, lastRow + 1, 1).sort({column: listColumn, ascending: true});
}
- Save the script (Ctrl/Cmd+S) and then edit one cell in sheet 2025 (type a new value in the validated cell). Do not run the function from the editor.
- If nothing happens, add SpreadsheetApp.getActive().toast('onEdit fired: ' + e.value) at the top (for quick visibility) and check Executions for errors.
- If your ListOptions sheet has a header row, tell me which row is header and I’ll adapt getRange() to start from row 2.
- Modify the script for a header row,
- Provide an installable trigger variant (so it runs with broader permissions),
- Or paste the exact script directly into your project if you grant access (or share a copy of the sheet).
Gary Odums
Mario Rossi
Gary Odums
Mario Rossi
Gary Odums
Mario Rossi
var startRow = 2; // inizia dalla riga 2, mantiene la riga 1 come header
var startCol = 1;
sheet.getRange(startRow, startCol, values.length, values.length).setValues(values);
}
var outputToRowOffset = 1;
Mario
Tom Mort
Hi Mario & Gary,
I tried the new script with my sheet names.
I can select an item or several items (if I have allow multiple selections checked) that are on the list. I can also enter an item that isn’t on the list, however it is not added to the list and I wind up getting an error that the entry violates the validation rule for the cell with the drop down.
I didn’t set any validation rule for that cell other than to specify the range to get the list from and that new value of course was not in that range.
When the error showed up there was a panel that pointed where the error was and there was an option to remove the rule. When I selected remove the rule the dripdown disappeared.
I tried this with the list source as both the whole column specified and also a smaller area containing all the items on the list and a a few bland cells so there was room for a few new items.
Here are some screenshots
Here I have added a value not on the list
This is the error message that pops up
After I close the error message earlier this panel appears. If I select “remove all” the dropbox disappears.
I have tried both selecting the whole column and also a range with a few more columns than the list uses as seen here.
The sheet with the range of values has no header. I see some discussion on how to deal with that, but in this case I don’t use one.
I haven’t tried the script that Gary suggested yet later. I will a little later. My day has begun and I have to deal with some other things right now. In looking at adding buttons and images and attaching scripts to them I at first thought it was referring to adding buttons to opening images.
Displaying images will be another part of this project. I thought I could give a little better picture of the project I’m working on where the dropdown list is just one part of.
I’m trying to put togher a listing over time of all the addresses that have had neighborhood businesses, what and where they were located, what sorts of goods or services that they did. I’m using city directories and some older fire insurance maps. The directories include addresses, names and description of the business activity. My list is a collection of the business activities I’ve collected so far. I figure a lot of the same sorts of descriptions will be used again and having the drop down box will speed up data entry and allow for doing it more consistently, prevent typos, etc.
Probably a database would be better and ultimately this data will get put into a GIS which of course includes a database. Right now though I want to collect the data and hopefully can get some other people to help gather it, maintain things going forward, etc. Spreadsheets are more accessible and how things are set up and work is more straightforward. Dbs need more skilled people which might be hard to find for a neighborhood project.
Besides name and addresses over the years I plan on having some columns to links for images and possibly larger documents about the businesses. There is a lot of this sort of stuff around but it is all scattered so I’d like to save it all to one location and have the links not to the original source but to a special collection. Links break after a while. Most of this is history and static.
Right now I have information for some of the streets in 2025 collected. I want to see all the current possible addresses, descriptions, how the directories are laid out, etc. I’ll then set up the sheets to be in the same order, etc as the directories.
--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/QObsC1Za5cI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
Mario Rossi
- Perché compare l’errore
- La convalida dei dati è impostata per “Reject input” (rigetta valori non presenti nell’intervallo). Quando inserisci un valore nuovo, Google Sheets lo rifiuta e mostra l’errore. Se scegli “Remove rule” viene rimossa la convalida e quindi sparisce il menu a tendina.
- Opzioni rapide da controllare
- Controlla la regola di convalida: Data → Data validation → assicurati se vuoi “Show warning” (consente il valore ma avvisa) invece di “Reject input”.
- Verifica l’intervallo sorgente della lista: se punti a un intervallo fisso non aggiornato, i nuovi valori non saranno presenti.
- Soluzioni per aggiungere automaticamente nuovi valori
- Usare uno script Apps Script che intercetta onEdit(e): quando l’utente digita un valore non presente, lo appende alla lista sorgente (foglio ListOptions) e aggiorna la convalida per includere la nuova riga.
- Usare un intervallo dinamico per la sorgente della convalida: named range con formula (es. OFFSET o FILTER) o usare l’intervallo dell’intera colonna (es. ListOptions!A:A) se la convalida lo supporta nel tuo caso.
- Alternativa semplice: lasciare “Show warning” e periodicamente copiare i nuovi valori in ListOptions o usare uno script per deduplicare e appendere.
- Nota su immagini/bottoni
- Gli script possono essere attaccati a pulsanti/immagini o usare onEdit per comportamento automatico; Gary ha proposto uno script alternativo — vale la pena provarlo quando hai tempo.
- Fornire uno snippet di onEdit che aggiunge automaticamente nuovi valori a ListOptions e aggiorna la data validation, oppure
- Controllare il tuo script attuale se lo incolli qui.
Martin Molloy
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAH8gT%3Dv9qYA_K3Xzw%3DMG2cudH5%3D1nfjuNFkTvQCh5%3DuWb-Y-Zg%40mail.gmail.com.
Mario Rossi
Tom
Martin Molloy
--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
Mario Rossi
Tom Mort
Mario Rossi
- La convalida della cella deve puntare al range che contiene gli elementi (es. ListOptions!A:A o ListOptions!$A$1:$A$86). Se usi ListOptions!A:A non devi aggiornare il range quando aggiungi righe nuove.
- Se la convalida è impostata su “Reject input” (rifiuta input non presenti nella lista) allora inserire manualmente un valore non presente genera l’errore che hai visto. Puoi cambiare l’opzione in “Show warning” per permettere l’inserimento senza errore, oppure lasciare “Reject” e assicurarti che lo script aggiunga prima l’elemento alla lista.
- Nel tuo script assicurati che la variabile targetColumn corrisponda alla colonna dove hai la dropdown (colonna F = 6). Se lo script scrive l’elemento nella lista, usa un range dinamico (colonna intera o named range) o fai in modo che lo script espanda/aggiorni la convalida dopo aver aggiunto l’elemento.
- Quando scegli “Remove rule” in quel pannello, rimuovi la convalida e quindi sparisce la dropdown — è normale.
Michael Bingham-Hawk
- the sheet ID
- the table ID
- the column header
- the table ID
- the table name
- all of the column headers of that table
- Click on Service.
- Add one.
- Find Google Sheets API.
--------------------Script to Output Table Properties---------------------
Michael Bingham-Hawk
HIGH ALTITUDE SPECIAL EVENTS MANAGEMENT
Event Management | Race Timing | Equipment Rental
Media & Marketing | Athlete Photography | Large Event Internet Services
mic...@highaltitudeevents.com
720-608-4725 office
303-656-0732 mobile
--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
Mario Rossi
- lo script che popola i drop-down dalle colonne di una table (esempi di utilizzo e gestione degli ID table/colonne);
- lo script che stampa le proprietà delle tabelle in formato umano e CSV.
Gary Odums
Mario Rossi
- elencare le tabelle e le loro proprietà (formato umano/CSV),
- popolare/aggiornare i drop‑down da colonne di una table (con esempi di gestione ID table/colonne).
Mario Rossi
Tom Mort
--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/103db4b4-461a-49d3-95cf-f8c0043ff1a8n%40googlegroups.com.
Mario Rossi
- If possible scan pages flat (library scanner or a flatbed) to avoid fold distortion; smartphone photos can work if taken carefully and cropped/deskewed.
- For OCR: try Google Drive OCR or Adobe Scan for quick results; ABBYY/FineReader is better for batch cleanup. Post-process with a simple script or Google Sheets to normalize addresses.
- For Polk/Data Axle content, check library subscriptions and terms of use — if you can’t access a digital subscription, photographing book pages and OCRing them is a reasonable fallback.
- NotebookLM and similar LLM-based tools can help extract structured data, but check privacy/training usage in the terms before uploading proprietary scans.
- For manual entry, a Google Form tied to a normalized Sheet often speeds data capture and keeps formats consistent; you can adapt the form for two-volume vs four-volume layouts.
- If you want, share a few sample images or a spreadsheet template and I can suggest an OCR + extraction workflow or a small Apps Script to normalize the data and populate drop-downs.
Joseph Vaughan
Mario Rossi
- Enable Advanced Google Sheets API in the script project (Services → Add → Google Sheets API).
- Use Sheets.Spreadsheets.get(ssId) to retrieve sheet metadata, find the sheet and the table with the tableId, read t.range to compute startRow/startCol/numCols.
- Read the header row from the sheet (sheet.getRange(startRow, startCol, 1, numCols).getValues()). Find the header index for the desired column header.
- Read that column’s values (sheet.getRange(startRow+1, startCol+headerIndex, numRows, 1).getValues()) and apply them as a dropdown via SpreadsheetApp.newDataValidation().requireValueInList(values, true).build() on the target range.
const ss = SpreadsheetApp.getActive();
const ssId = ss.getId();
const meta = Sheets.Spreadsheets.get(ssId); // Advanced Sheets API
for (const s of meta.sheets || []) {
if (!s.tables) continue;
for (const t of s.tables) {
if (t.tableId == tableId) {
const sheet = ss.getSheetByName(s.properties.title);
const r = t.range;
const startRow = (r.startRowIndex || 0) + 1;
const startCol = (r.startColumnIndex || 0) + 1;
const numCols = (r.endColumnIndex || startCol) - (r.startColumnIndex || 0);
const headers = sheet.getRange(startRow, startCol, 1, numCols).getValues();
const idx = headers.indexOf(headerName);
if (idx === -1) throw new Error('Header not found');
const data = sheet.getRange(startRow+1, startCol+idx, sheet.getLastRow()-startRow, 1)
.getValues().flat().filter(v => v !== '');
const rule = SpreadsheetApp.newDataValidation().requireValueInList(data, true).build();
ss.getRange(targetRangeA1).setDataValidation(rule);
return;
}
}
}
throw new Error('Table ID not found');
}
Laurie Nason
- Have your dropdown based on a range (sounds like you already have this)
- Allow the dropdown to show a warning if data is invalid (advanced Options)
- Display style to Arrow (advanced Options) - the default chip dropdown can be deleted v. easily by users
- The range you are using to build the dropdown - use the formula SORT(UNIQUE(range of dropdowns cells e.g. A2:A)
- This way you can add new values into the dropdown by typing in the cells - this then gets added to the list that the dropdown is based on automatically for future use.
- If you need some static values in your dropdown at the top of the list, then put the formula in 4 above below the list you want to use as the values of the dropdown.
- It's open to users entering incorrect or different capitalisation into the list so potentially the formula in 4 could include PROPER() function too depending on your data. That would force future values to be consistent.
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAH8gT%3Dv9qYA_K3Xzw%3DMG2cudH5%3D1nfjuNFkTvQCh5%3DuWb-Y-Zg%40mail.gmail.com.
Mario Rossi
Tom
Laurie Nason
https://docs.google.com/spreadsheets/d/1NodXA6izqBVYQJfBWtioxhTrbfnwvE0D7PzJAee69ko/copy
--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
Mario Rossi
Tom
Tom Mort
Yesterday was a hectic day and I didn’t get a chance to look into things until late. I wanted to go further but figured it would be best to wait for the morning when I was more rested. So here is a listing of what I did and the results:
I tried putting the validation rule back in for F3 did not enter or get put into the list either.
Here are a few screenshots of how things are setup
The two screenshots below show the validations and other settings for the drop down list
Below is the section of code I changed. I first changed both 1s to 6, but realized one was for the target and the other was fro the list.
It’s late and I’m tired at this point and I will have to look through a lot of messages about debugging and the right thing to test or log to chack, but it will be later.
I will also try the other suggestions about adding a button and adding a script to the button and also the other suggestion about using tables. I keep seeing these popups showing them.
I thought I’d try the other method that was suggested of assigning a script to an image. I was able to successfully open the text box with the script in the example.
I then replaced the example function with the full script provided, I changed the OutputToSheet to outputToListOptions on lines 24 & 25.
When I clicked on the button I got this this message:
I did a search of the script and it activateMyButton is in the script but down near the bottom.
I also saw there were two more instances of OutputToSheet on lines 17 & 18. This section seems more about responding to the user input though and I haven’t got thar far so haven’t changed that.
Here is the script with my configurations as I entered into the App Script editor:
function confirmUserDecisionWithAlert(ui,selectedValue) {
return ui.alert(
"Please confirm",
`Are you sure you want to continue with adding "${selectedValue}"?`,
ui.ButtonSet.YES_NO,
);
}
// User clicked "Yes".
function userDecisionSuccessfulAlert(ui,selectedValue,outputToSheetName) {
ui.alert(`Operation successful. "${selectedValue}" has been added to ${outputToSheetName}.`);
}
// User encoutered an error while attempting to add the value
function userDecisionUnsuccessfulAlert(ui,outputToSheetName,e) {
ui.alert(`Error encountered while attempting to add a new cell to ${outputToSheetName}`);
// Insert the line Logger.log(e) here for more details about the error.
}
// User clicked "No" or X in the title bar.
function userCanceledAlert(ui,selectedValue,outputToListOptions) {
ui.alert(`Operation cancelled. "${selectedValue}" was not added to ${outputToListOptions}.`);
}
function addValueToRange(value,sheet,column,outputToTopOfColumn,topOfColumnOffset) {
if (outputToTopOfColumn === true) {
const insertRowAtIndex = 1 + topOfColumnOffset;
sheet.getRange(insertRowAtIndex,column).insertCells(SpreadsheetApp.Dimension.ROWS);
sheet.getRange(insertRowAtIndex,column).setValue(value);
} else {
const lastCellInRangeIndex = sheet.getRange(1,column,Math.max(1,sheet.getLastRow()),1).getLastRow();
const lastCellInRangeValue = sheet.getRange(lastCellInRangeIndex,column).getValue();
const insertRowAtIndex = `${lastCellInRangeValue}`.length === 0 ? 1 : lastCellInRangeIndex + 1;
sheet.getRange(insertRowAtIndex,column).insertCells(SpreadsheetApp.Dimension.ROWS);
sheet.getRange(insertRowAtIndex,column).setValue(value);
}
}
// CONFIGURE THE VALUES DEFINED NEAR THE TOP OF THIS FUNCTION TO MATCH YOUR OWN NEEDS (Lines 21 to 27)
function activateMyButton() {
const dropdownSheetName = "Sheet1";
const dropdownCellAddress = [1,2] // [Row,Column] // example [1, 2] for cell B1
const outputToSheetName = "Sheet2";
const outputToColumn = 1;
const outputToTopOfColumn = true; // Set to true to write new values to the top of the row or to false to add to the bottom
const outputToRowOffset = 0; // Set an offset here to allow for header rows; only applies is outputToTopOfColumn is true
const outputToSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(outputToSheetName);
const dropdownSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dropdownSheetName);
const selectedValue = dropdownSheet.getRange(...dropdownCellAddress).getValue();
const ui = SpreadsheetApp.getUi();
if (confirmUserDecisionWithAlert(ui,selectedValue) === ui.Button.YES) {
try {
addValueToRange(selectedValue,outputToSheet,outputToColumn,outputToTopOfColumn,outputToRowOffset);
userDecisionSuccessfulAlert(ui,selectedValue,outputToSheetName);
} catch (e) {
userDecisionUnsuccessfulAlert(ui,outputToSheetName,e);
}
} else {
userCanceledAlert(ui,selectedValue,outputToSheetName);
}
}
I also checked to make sure I had copied the full script suggested by Gary and it was what was sent.
I thought I’d go back and try the first improved suggested script again. I did it last night but it was late and thought I’d try again. I did it with the existing sheet and a brand new one. Didn’t get very far at all.
I would get an error when I tried to save the script. At first I used the sheet I had already used after clearing out the script for using the image button. I then set up a brand new set of sheets. One was empty and the other contained the list. I tried it as the script was originally written and I made the changes for column 6/F. I got the same error each time:
Syntax error: SyntaxError: Missing initializer in const declaration line: 27 file: Code.gs
My day is starting to begin and I’ll be busy with other things for a while. I’m just curling and getting frustrated. Doing something else for a while will be good. I figure I’ll post this in the meantime.
I want to also look into using tables or forms as suggested. I wonder if either might have the ability to do this sort of thing without using a script?
Hi Tom,Below is a configurable script which can achieve your specified goal by using an insertable image as a button.Simply attach the function named activateMyButton to the button by following these instructions: https://developers.google.com/apps-script/guides/menus#clickable_images_and_drawings_inReach out with any questions you may have about this approach.function confirmUserDecisionWithAlert(ui,selectedValue) {return ui.alert("Please confirm",`Are you sure you want to continue with adding "${selectedValue}"?`,ui.ButtonSet.YES_NO,);}// User clicked "Yes".function userDecisionSuccessfulAlert(ui,selectedValue,outputToSheetName) {ui.alert(`Operation successful. "${selectedValue}" has been added to ${outputToSheetName}.`);}// User encoutered an error while attempting to add the valuefunction userDecisionUnsuccessfulAlert(ui,outputToSheetName,e) {ui.alert(`Error encountered while attempting to add a new cell to ${outputToSheetName}`);// Insert the line Logger.log(e) here for more details about the error.}// User clicked "No" or X in the title bar.function userCanceledAlert(ui,selectedValue,outputToSheetName) {ui.alert(`Operation cancelled. "${selectedValue}" was not added to ${outputToSheetName}.`);}function addValueToRange(value,sheet,column,outputToTopOfColumn,topOfColumnOffset) {if (outputToTopOfColumn === true) {const insertRowAtIndex = 1 + topOfColumnOffset;sheet.getRange(insertRowAtIndex,column).insertCells(SpreadsheetApp.Dimension.ROWS);sheet.getRange(insertRowAtIndex,column).setValue(value);} else {const lastCellInRangeIndex = sheet.getRange(1,column,Math.max(1,sheet.getLastRow()),1).getLastRow();const lastCellInRangeValue = sheet.getRange(lastCellInRangeIndex,column).getValue();const insertRowAtIndex = `${lastCellInRangeValue}`.length === 0 ? 1 : lastCellInRangeIndex + 1;sheet.getRange(insertRowAtIndex,column).insertCells(SpreadsheetApp.Dimension.ROWS);sheet.getRange(insertRowAtIndex,column).setValue(value);}}// CONFIGURE THE VALUES DEFINED NEAR THE TOP OF THIS FUNCTION TO MATCH YOUR OWN NEEDS (Lines 21 to 27)function activateMyButton() {const dropdownSheetName = "Sheet1";const dropdownCellAddress = [1,2] // [Row,Column] // example [1, 2] for cell B1const outputToSheetName = "Sheet2";const outputToColumn = 1;const outputToTopOfColumn = true; // Set to true to write new values to the top of the row or to false to add to the bottomconst outputToRowOffset = 0; // Set an offset here to allow for header rows; only applies is outputToTopOfColumn is trueconst outputToSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(outputToSheetName);const dropdownSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dropdownSheetName);const selectedValue = dropdownSheet.getRange(...dropdownCellAddress).getValue();const ui = SpreadsheetApp.getUi();if (confirmUserDecisionWithAlert(ui,selectedValue) === ui.Button.YES) {try {addValueToRange(selectedValue,outputToSheet,outputToColumn,outputToTopOfColumn,outputToRowOffset);userDecisionSuccessfulAlert(ui,selectedValue,outputToSheetName);} catch (e) {userDecisionUnsuccessfulAlert(ui,outputToSheetName,e);}} else {userCanceledAlert(ui,selectedValue,outputToSheetName);}}ThanksOn Tuesday, March 10, 2026 at 10:02:25 PM UTC-4 Mario Rossi wrote:Hi Tom — thanks for the detailed debugging notes. Quick points and a ready-to-paste script tailored to your setup.Primary issues you reported
- Remove the stray text "javascript" at top of the file — that produced ReferenceError when you ran the script.
- Do not press Run in the editor for an onEdit simple trigger: running it directly has no event object e and will error. Test by editing a single cell in the sheet instead, and watch Executions / Logger or toast messages.
- Selecting the whole column (ListOptions!A:A) is fine, but getValues() will include blank cells — handle lastRow when building the list.
- Validation must be “Show warning” and edits must be a single cell (typing in cell with dropdown). Multi-cell edits or pastes don’t provide e.value.
Adapted onEdit script (paste into the bound project and save)
function onEdit(e) {
if (!e) return;
const ss = SpreadsheetApp.getActiveSpreadsheet();const mainSheetName = '2025';
const listSheetName = 'ListOptions';
const targetColumn = 1; // column A
const listColumn = 1; // column A in ListOptionsconst range = e.range;
const sheet = range.getSheet();
if (sheet.getName() !== mainSheetName) return;
if (range.getNumColumns() !== 1 || range.getNumRows() !== 1) return;if (range.getColumn() !== targetColumn) return;const newValue = e.value;
if (!newValue) return;const listSheet = ss.getSheetByName(listSheetName);
if (!listSheet) return;let lastRow = listSheet.getLastRow();
if (lastRow < 1) lastRow = 0;// read only used rows to avoid huge arrays when using A:A
const valuesRange = listSheet.getRange(1, listColumn, Math.max(1, lastRow), 1);
const listValues = valuesRange.getValues().flat().map(v => (v || '').toString().trim()).filter(v => v !== '');
if (listValues.includes(newValue.toString().trim())) return;listSheet.getRange(lastRow + 1, listColumn).setValue(newValue);
// optional: sort
// listSheet.getRange(1, listColumn, lastRow + 1, 1).sort({column: listColumn, ascending: true});
}Quick tests to run
- Save the script (Ctrl/Cmd+S) and then edit one cell in sheet 2025 (type a new value in the validated cell). Do not run the function from the editor.
- If nothing happens, add SpreadsheetApp.getActive().toast('onEdit fired: ' + e.value) at the top (for quick visibility) and check Executions for errors.
- If your ListOptions sheet has a header row, tell me which row is header and I’ll adapt getRange() to start from row 2.
If you want, I can:
--
- Modify the script for a header row,
- Provide an installable trigger variant (so it runs with broader permissions),
- Or paste the exact script directly into your project if you grant access (or share a copy of the sheet).
Tell me whether ListOptions has a header row and if you want the sort enabled.
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/QObsC1Za5cI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/17c1382f-9cfa-4d8c-b85b-f05cef7a7201n%40googlegroups.com.
Mario Rossi
- Rimuovi ogni testo estraneo in cima al file (es. la parola "javascript").
- Evita di eseguire onEdit(e) dal editor: onEdit semplice richiede l'evento e; testa modificando la cella nella sheet.
- Verifica che non ci siano dichiarazioni const duplicate con lo stesso nome in altri punti del file.
- Apri View > Executions e Logger per vedere errori dettagliati. Gli errori "Missing initializer in const declaration" sono spesso dovuti a una riga troncata, a una dichiarazione duplicata o a una parentesi/acapo mancante.
- Per il bottone assegnato all'immagine, assegna la funzione activateMyButton esattamente con quel nome.
- Se usi cella F3 come dropdown, configura dropdownCellAddress = [3, 6] (riga 3 colonna 6).
- Quando salvi e provi, esegui activateMyButton manualmente (Run) per vedere errori; la funzione usa SpreadsheetApp e può essere eseguita dal progetto per debug (ti chiederà autorizzazioni).
return ui.alert(
"Please confirm",
Are you sure you want to continue with adding "${selectedValue}"?,ui.ButtonSet.YES_NO
);
}
ui.alert(
Operation successful. "${selectedValue}" has been added to ${outputToSheetName}.);}
ui.alert(
Error encountered while attempting to add a new cell to ${outputToSheetName});Logger.log(e);
}
ui.alert(
Operation cancelled. "${selectedValue}" was not added to ${outputToSheetName}.);}
if (outputToTopOfColumn === true) {
const insertRowAtIndex = 1 + topOfColumnOffset;
sheet.getRange(insertRowAtIndex, column).insertCells(SpreadsheetApp.Dimension.ROWS);
sheet.getRange(insertRowAtIndex, column).setValue(value);
} else {
const lastCellInRangeIndex = sheet.getRange(1, column, Math.max(1, sheet.getLastRow()), 1).getLastRow();
const lastCellInRangeValue = sheet.getRange(lastCellInRangeIndex, column).getValue();
const insertRowAtIndex =
${lastCellInRangeValue}.length === 0 ? 1 : lastCellInRangeIndex + 1;sheet.getRange(insertRowAtIndex, column).insertCells(SpreadsheetApp.Dimension.ROWS);
sheet.getRange(insertRowAtIndex, column).setValue(value);
}
}
function activateMyButton() {
const dropdownSheetName = "2025"; // sheet that has the dropdown
const dropdownCellAddress = [3, 6]; // [row, column] — e.g. F3 is [3,6]
const outputToColumn = 1; // column A = 1
const outputToTopOfColumn = false; // true = insert at top, false = append at bottom
const outputToRowOffset = 0;
const outputToSheet = ss.getSheetByName(outputToSheetName);
const dropdownSheet = ss.getSheetByName(dropdownSheetName);
if (!outputToSheet || !dropdownSheet) {
SpreadsheetApp.getUi().alert("Sheet not found: check names.");
return;
}
const ui = SpreadsheetApp.getUi();
try {
addValueToRange(selectedValue, outputToSheet, outputToColumn, outputToTopOfColumn, outputToRowOffset);
userDecisionSuccessfulAlert(ui, selectedValue, outputToSheetName);
} catch (e) {
userDecisionUnsuccessfulAlert(ui, outputToSheetName, e);
}
} else {
userCanceledAlert(ui, selectedValue, outputToSheetName);
}
}
Tom Mort
Hi Tom,Below is a configurable script which can achieve your specified goal by using an insertable image as a button.Simply attach the function named activateMyButton to the button by following these instructions: https://developers.google.com/apps-script/guides/menus#clickable_images_and_drawings_in
function confirmUserDecisionWithAlert(ui,selectedValue) {
return ui.alert("Please confirm",
`Are you sure you want to continue with adding "${selectedValue}"?`,ui.ButtonSet.YES_NO,);}// User clicked "Yes".function userDecisionSuccessfulAlert(ui,selectedValue,outputToSheetName) {ui.alert(`Operation successful. "${selectedValue}" has been added to ${outputToSheetName}.`);}// User encoutered an error while attempting to add the valuefunction userDecisionUnsuccessfulAlert(ui,outputToSheetName,e) {
ui.alert(`Error encountered while attempting to add a new cell to ${outputToSheetName}`);// Insert the line Logger.log(e) here for more details about the error.}// User clicked "No" or X in the title bar.function userCanceledAlert(ui,selectedValue,outputToSheetName) {
ui.alert(`Operation cancelled. "${selectedValue}" was not added to ${outputToSheetName}.`);}function addValueToRange(value,sheet,column,outputToTopOfColumn,topOfColumnOffset) {
if (outputToTopOfColumn === true) {const insertRowAtIndex = 1 + topOfColumnOffset;
sheet.getRange(insertRowAtIndex,column).insertCells(SpreadsheetApp.Dimension.ROWS);sheet.getRange(insertRowAtIndex,column).setValue(value);} else {const lastCellInRangeIndex = sheet.getRange(1,column,Math.max(1,sheet.getLastRow()),1).getLastRow();const lastCellInRangeValue = sheet.getRange(lastCellInRangeIndex,column).getValue();
const insertRowAtIndex = `${lastCellInRangeValue}`.length === 0 ? 1 : lastCellInRangeIndex + 1;
sheet.getRange(insertRowAtIndex,column).insertCells(SpreadsheetApp.Dimension.ROWS);sheet.getRange(insertRowAtIndex,column).setValue(value);}}// CONFIGURE THE VALUES DEFINED NEAR THE TOP OF THIS FUNCTION TO MATCH YOUR OWN NEEDS (Lines 21 to 27)
function activateMyButton() {
const dropdownSheetName = "Sheet1";const dropdownCellAddress = [1,2] // [Row,Column] // example [1, 2] for cell B1const outputToSheetName = "Sheet2";const outputToColumn = 1;const outputToTopOfColumn = true; // Set to true to write new values to the top of the row or to false to add to the bottomconst outputToRowOffset = 0; // Set an offset here to allow for header rows; only applies is outputToTopOfColumn is trueconst outputToSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(outputToSheetName);const dropdownSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dropdownSheetName);
const selectedValue = dropdownSheet.getRange(...dropdownCellAddress).getValue();const ui = SpreadsheetApp.getUi();
if (confirmUserDecisionWithAlert(ui,selectedValue) === ui.Button.YES) {try {addValueToRange(selectedValue,outputToSheet,outputToColumn,outputToTopOfColumn,outputToRowOffset);userDecisionSuccessfulAlert(ui,selectedValue,outputToSheetName);} catch (e) {userDecisionUnsuccessfulAlert(ui,outputToSheetName,e);}} else {userCanceledAlert(ui,selectedValue,outputToSheetName);}}
Reach out with any questions you may have about this approach.
Thanks
On Tuesday, March 10, 2026 at 10:02:25 PM UTC-4 Mario Rossi wrote:
Hi Tom — thanks for the detailed debugging notes. Quick points and a ready-to-paste script tailored to your setup.Primary issues you reported
- Remove the stray text "javascript" at top of the file — that produced ReferenceError when you ran the script.
- Do not press Run in the editor for an onEdit simple trigger: running it directly has no event object e and will error. Test by editing a single cell in the sheet instead, and watch Executions / Logger or toast messages.
- Selecting the whole column (ListOptions!A:A) is fine, but getValues() will include blank cells — handle lastRow when building the list.
- Validation must be “Show warning” and edits must be a single cell (typing in cell with dropdown). Multi-cell edits or pastes don’t provide e.value.
Adapted onEdit script (paste into the bound project and save)
function onEdit(e) {
if (!e) return;
const ss = SpreadsheetApp.getActiveSpreadsheet();
Mario Rossi
- Why the whole list doesn’t show
- A script that inserts a single value into the list sheet won’t automatically open a UI dropdown showing all choices. The spreadsheet’s dropdown (data validation) is what displays the list; the script only modifies the list source. Make sure your dropdown’s validation points to the full list range (or a named/dynamic range), not a single cell.
- Use a dynamic range for the source list
- On the sheet that holds choices (e.g. "ListOptions") use a named range or a range formula that only includes non-empty cells. Example approaches:
- Create a named range manually and update its range when you add items.
- Use a helper column with FILTER or use Apps Script to compute the last row and then setDataValidation to that exact range.
This lets new entries appear in every dropdown automatically.
- Adding new items to the list (script behavior)
- If new values are being added to the wrong sheet/column, adjust the script so it writes to the ListOptions sheet and to the intended column. In short: getSheetByName('ListOptions') and setValue(row, col) there.
- Your onEdit script example (shared earlier in thread) is the right idea: it checks the edited cell, compares with existing list items and appends to the list sheet if missing. Use getLastRow() on ListOptions to append.
- Dropdown or button per row
- Preferred: use data validation on the entire target column (so every row has the dropdown). That is simpler and more native than a button per row.
- If you need a per-row button, use an image/drawing assigned to a function. The script can detect the active cell (SpreadsheetApp.getActiveRange()) or prompt for the target row. You can also have one button in a header that applies the currently selected value to the active row.
- Scripts do not auto-change when copied like formulas — but they can read the active cell or take a row argument so a single script works for any row.
- Handling empty cells, insertion position, and ordering
- Decide whether you want new list items at top or bottom. The provided addValueToRange function in Gary’s message already supports adding to top or bottom; adapt the target sheet/column and header offset if you have header rows.
- Practical next steps for you
- If you want, I can:
- Adapt the onEdit script to your exact sheet names and whether ListOptions has a header row.
- Provide a small script that updates the data validation range dynamically after adding an item.
- Provide a version that uses an installable trigger if you need broader permissions or dialogs.
- Names of the two sheets (main sheet and list sheet)
- Which column holds the dropdown on the main sheet (A, B, etc.)
- Whether the list sheet has a header row
I’ll paste a ready-to-use script tuned to those details.
Tom Mort
I’ve been pretty busy with other things and there has been some weather. It’s taken me some time to find the time to look at this and into it. I really appreciate and want to thank you for all of the assistance and your gracious offers.
I’m both using this to reply and also gather together the various replies and scripts and suggestions I’ve received. I seem to have gotten on an email list but not actually on the board. I get a lot of messages, but many of them aren’t for me. Finding the ones I need takes some digging.
I’m switching back to dropdown lists and the script for them. I have inserted them at the top of a column so they and the validation rules, ets are for all of the columns. I have set the list range for the list to the entire column, etc. I have made adjustments to the script for some of this. I find that I can’t save it though. I get this error: Syntax error: SyntaxError: Missing initializer in const declaration line: 27 file: Code.gs
I am also getting an error on this line if I step through with the debugger. I remember you saying not to use the debugger for this script though. I checked this line of the script against the one you posted and they are the same.
Why the whole list doesn’t show
A script that inserts a single value into the list sheet won’t automatically open a UI dropdown showing all choices. The spreadsheet’s dropdown (data validation) is what displays the list; the script only modifies the list source. Make sure your dropdown’s validation points to the full list range (or a named/dynamic range), not a single cell.
OK, I see. I have changed the range to =ListOptions!$A:$A
Use a dynamic range for the source list
On the sheet that holds choices (e.g. "ListOptions") use a named range or a range formula that only includes non-empty cells. Example approaches:
Create a named range manually and update its range when you add items.
Use a helper column with FILTER or
Looks like helper is a function I’m not familiar with, but I guess it would be looking for any cells in the whole range that contain “”. I have used if A1<>””,01 and copied it down a column to distinguish between blank cells and those with values. Will have to think about how to then find the last row number, etc.
use Apps Script to compute the last row and then setDataValidation to that exact range.
I’m not familiar with javascript at all, but thought I’d see if I can find something and found this. I think in my case the rows would be sorted and there wouldn’t be areas at least in some columns where there might be gaps, but his bit of code would get the correct last row number regardless
const ss = SpreadsheetApp.getActive(); const sh = ss.getSheetByName('Sheet1') const lrow = sh.getLastRow(); const Avals = sh.getRange("A1:A"+lrow).getValues(); const Alast = lrow - Avals.reverse().findIndex(c=>c[0]!='');
This, if I can figure out how to use it, seems simpler. I supposed I would just need to insert it before the insert of the new item entry occurs.
This lets new entries appear in every dropdown automatically.
Adding new items to the list (script behavior)
If new values are being added to the wrong sheet/column, adjust the script so it writes to the ListOptions sheet and to the intended column. In short: getSheetByName('ListOptions') and setValue(row, col) there.
I did some of that and maybe will have to do it again. Right now I can’t save the script.
Your onEdit script example (shared earlier in thread) is the right idea: it checks the edited cell, compares with existing list items and appends to the list sheet if missing. Use getLastRow() on ListOptions to append.
Looks like getLastRow would be used in conjunction with with script I mentioned above along with a little more code.
Dropdown or button per row
Preferred: use data validation on the entire target column (so every row has the dropdown). That is simpler and more native than a button per row.
That is how I have it at the moment. I have a two row header on the sheet and it is set to stay visible as you scroll through the sheet. I don’t need any dropdowns there. Maybe there is away for change how these cells look through formatting. The upper cell as nothing in it and the lower cell does display the header text for that column. So it can function that way, but the drop downs being there could cause some confusion. Maybe I could get an image of the headers and insert them above that row if nothing else.
If you need a per-row button, use an image/drawing assigned to a function. The script can detect the active cell (SpreadsheetApp.getActiveRange()) or prompt for the target row. You can also have one button in a header that applies the currently selected value to the active row.
I think the drop down will work better in this case
Scripts do not auto-change when copied like formulas — but they can read the active cell or take a row argument so a single script works for any row.
OK, that addresses my concern about that and is good to hear.
Handling empty cells, insertion position, and ordering
Decide whether you want new list items at top or bottom. The provided addValueToRange function in Gary’s message already supports adding to top or bottom; adapt the target sheet/column and header offset if you have header rows.
I’d like the list to be in alphabetical order and intend to use the optional sort in the script you provided. It doesn’t seem like in that case where the entries are put initially will matter. Please let me know if this is not the case.
Practical next steps for you
If you want, I can:
Adapt the onEdit script to your exact sheet names and whether ListOptions has a header row.
I think this might address the questions I had about header rows.
Provide a small script that updates the data validation range dynamically after adding an item.
This sounds like you are addressing where I mention the script for finding the last row, etc.
Provide a version that uses an installable trigger if you need broader permissions or dialogs.
I’m not sure if this would be needed or not, but sounds like it could be of use
Tell me:
Names of the two sheets (main sheet and list sheet)
Right now the main sheet is called 2025 and the list sheet is ListOptions. There will be a main sheet for each year (or at least which year there is a source of information) going back in time from 2025 and possibly going forward to keep a running record.
Which column holds the dropdown on the main sheet (A, B, etc.)
The column that has the dropdown on the mainsheet is F. The first row that will hold drop entries in is 3. The first two rows are part of the header.
Whether the list sheet has a header row
No, the list sheet has no header row. There is only the one row and the sheet name explains what is on the list
I’ll paste a ready-to-use script tuned to those details.
Add Value to Range Script
function addValueToRange(value,sheet,column,outputToTopOfColumn,topOfColumnOffset) {
if (outputToTopOfColumn === true) {
const insertRowAtIndex = 1 + topOfColumnOffset;
sheet.getRange(insertRowAtIndex,column).insertCells(SpreadsheetApp.Dimension.ROWS);
sheet.getRange(insertRowAtIndex,column).setValue(value);
} else {
const lastCellInRangeIndex = sheet.getRange(1,column,Math.max(1,sheet.getLastRow()),1).getLastRow();
const lastCellInRangeValue = sheet.getRange(lastCellInRangeIndex,column).getValue();
const insertRowAtIndex = `${lastCellInRangeValue}`.length === 0 ? 1 : lastCellInRangeIndex + 1;
sheet.getRange(insertRowAtIndex,column).insertCells(SpreadsheetApp.Dimension.ROWS);
sheet.getRange(insertRowAtIndex,column).setValue(value);
}
}
NOTES ABOUT NOTEBOOKLM OCR ETC
I did get a chance to try notebooklm a little. I did have some pictures of some pages from a directory that I had taken recently. The were just taken with a cellphone while standing in front of the open book on the table, one page at a time (just of the few pages of interest, not the whole book). I took a little over twenty pictures, but a few of them were of the same page.
I did just a single page at first and it uploaded pretty quickly and I selected table and it was created pretty quickly and it looked pretty good, but I had to wait a while to compare the original image to the file that was created. What was there was pretty good, but the list of locations on the NotebookLM table had way fewer entries than what was on the image.
The book was just open on the table, not pressed against a flat plane scanner. The books are at a library and they do have a scanner or at least a copier that can also save as pdf, maybe in other format or maybe a dedicated scanner. I’m not sure.
I first upload just a page or two. Things went pretty quickly. I then figured why not upload all twenty or so. Things just bogged down at that point. Maybe when the screen went to sleep every process did too.
Later I experienced the same thing when only doing one or two pages. Maybe the network traffic is high, maybe some other glitch.
If possible scan pages flat (library scanner or a flatbed) to avoid fold distortion;
I will stop by and see what they have and try to get some better scans. I suppose taking the book apart and using a drum scanner would be better yet, but I don’t think that is an option in this case.
smartphone photos can work if taken carefully and cropped/deskewed.
I tried to be careful in taking them, mainly to be sure to include the whole page or at least to make sure so include all the parts of the page with information I’m interested in. I know there were sections of some of the images I took near the curve binding that I couldn't read myself. I also see that cropping the image to only include the information of interest might improve things at least in terms of less image to process. Probably a “real” camera and a tripod might help too. I do have a good tripod and a more standard camera that might work well for this
For OCR: try Google Drive OCR or Adobe Scan for quick results; ABBYY/FineReader is better for batch cleanup. Post-process with a simple script or Google Sheets to normalize addresses.
I was thinking of trying some other OCR method. Thanks for listing these.
For Polk/Data Axle content, check library subscriptions and terms of use — if you can’t access a digital subscription, photographing book pages and OCRing them is a reasonable fallback.
I will ask at the library about if they have a subscription to the Data Axle material and if so I can access it and how far back it may go. I’m not affiliated with or a student at any college or university in the area, but they might have a digital subscription. I’ll look into that as well and if so see if I might be able to interest a professor or someone in this project. It might even get some helpers, knowledge and insight.
NotebookLM and similar LLM-based tools can help extract structured data, but check privacy/training usage in the terms before uploading proprietary scans.
I’m not sure what you mean by a proprietary scan. Maybe the Data Axle stuff? Maybe the Polk Directory? But, i’ll look into that as well. I’m just an individual doing this as a community project for whatever that is worth. I’m not doing this to create a product or service to sell, etc.
For manual entry, a Google Form tied to a normalized Sheet often speeds data capture and keeps formats consistent; you can adapt the form for two-volume vs four-volume layouts.
I’ve been meaning to look into that a little. The spreadsheet is pretty straightforward as it is and I am trying to have it in the same order as the directories have things listed in. I when I get to the older books, and the older things on microfilm the format may change a few times. I did find a very old Polk directory that had been digitized. It really had little more than a few street addresses with no other information.
If you want, share a few sample images or a spreadsheet template and I can suggest an OCR + extraction workflow or a small Apps Script to normalize the data and populate drop-downs.
At the moment this is sort of in progress and I’ve got one with all the information as I received it and also added some cells with formulas to parse out some things to sort and standardize things. That includes some others to just work through how to do some other things. Those columns are all there, but off screen to the right and I’ve put the relevant things in the first few columns on screen.
Right now I have collected a list of all the street address points of the area and have added some that were not in the current list from the GIS. I have found in the directories and other listings of things some additional addresses. Often a business in a storefront or strip mall will expand and take up a larger space and there will be fewer addresses and at other times there are more businesses in the structure and there will be more addresses.
Often there are listings for more than business that have been at the same address in the Polk Directory for a single year. Sometimes one business closes and another takes its place. Sometimes I suspect a business is long gone, but Polk isn’t sure and retains it and sometimes there might be multiple businesses with different names that actually use the same street number address and just use their name or suite number to distinguish each other.
In those cases there is a list of them for that address. Ultimately I’d like to get this data into a GIS. I need a separate record for each entity even if they have the same address. The format of the street addresses from the county GIS had the street number, street name and street direction in all caps concatenated as a string. I don’t care for that and separated that out as street number then the rest of the string, but I’m thinking of adding a column number for each entity in the same year and then concatenate the earlier string with that number. That way each entity will have its own row and a unique identifier and the same address.
I will put together a copy of a sheet as described above.
--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/QObsC1Za5cI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
Tom Mort
Hi Mario,
I had intended to send some of those page images I used, but forgot. I also thought I’d play around trying some other OCR software that you suggested and some others. I have heard of ABBYY/FineReader. I see not only is it Windows only it is also a subscription service. I use Linux, but do have Windows11 on a VM. The current state of windows is sort of torture and having to pay for experience that torture just compounds things. I see there might be a way to run it on Linux, but I’m not sure if it is current.s
Looks like the Adobe app is also not free and I suppose also a subscription and of course it is Adobe, different but similar to above.
I was able to at least run the demo for the Google Cloud OCR. It worked reasonably well for about half a the page I tried it on. The half of the page that was the best imae was not included. Maybe that was a limitation of the demo. It did read things pretty weil, but it didn’t categorize things and put them into a table like the NotebookLM does. I do see there are all sorts of APIs and ways to configure it to do different things. Looks like that could be a bigger project than just manual entry. I’ll be going back quite a few years, but the actual number or addresses of interest will not be all that large. I suspect this too is fee based.
I played around a little with a linux app, ImageReader which uses Tesseract. ImageReader got some words/strings right while it looks like many others got translated to some other language entirely. I’ve downloaded and attached my demo session with the Google Cloud OCR. I’m attaching it and some of the images I had of pages that I tried. I’ll also attach the text files I generated with the other apps.
I see there are many OCR apps and services for pdf. It would be easy enough to convert an image file to a pdf, but it would only have an image layer. Maybe they are to scan a pdf that has only an image and it does OCR and adds a text layer.
I don’t think I will have a chance to get to the library and use any sort of flatbed scanner today, but will go there when I get a chance. I think I’ll do a few scans of the newer four volume books, a few of the older two volume books and then the older ones on Microfilm. I can get an idea what the layouts are of the different ones and for the microfilm ones how readable the text is.
--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/QObsC1Za5cI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.