Good point! Updated with a fix.

Thanks for the question! The error “TypeError: Cannot read properties of undefined (reading ‘value’)” usually means your script wants to access the “value” property of a cell that doesn’t exist or is empty. Here are a few things I’d try (in order).

Check column F for empty cells or hidden rows that might cause the error.
Double-check your script to ensure correct range references for column F.
Add conditional checks to handle empty cells gracefully.
Simplify your script to isolate the problem.
Review data validation and merged cells for potential conflicts.
Use Logger.log() to debug and pinpoint the error location.

I hope this helps!

which is infuriating because I worked backwards and used the first macro code that allows repetitions and specifies column 3 and row 1 and gradually modified one by one to fit my spreadsheet. I’m trying to make all of column F allow multiple edits with no repetitions. The code was working when I was using the code that allowed repetitions but then stops when I add in the else statement that takes out repetitions. When I converted back to allow repetitions, even deleting and starting from scratch with the copied code, it’s not letting my column 6 run.

I don’t understand, any other number column works. Column A and B are merged, but on tests, 6 is the right number. Data validation also is derived from more than one sheets, but the time I made it work, it didn’t matter. How do I resolve this error

Maybe try something like this?

function onEdit(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var activeCell = ss.getActiveCell();

// Check if the new value is already in the array of old values
if (oldValues.indexOf(newValue) < 0) { // Add the new value and join the array back into a string oldValues.push(newValue); activeCell.setValue(oldValues.join(', ')); } // If the value already exists, we do nothing (or reset to oldValue if needed) // This else block can be uncommented if you wish to explicitly reset the oldValue, but it's redundant in this context // else { // activeCell.setValue(oldValue); // } } }

I have the below code in the Apps Script. When I run this code, I keep getting ‘9:58:55 AM Error An unknown error has occurred, please try again later.’

I am hoping for multiple selections with no repetition in column 11. Do you notice anything that I have incorrect? Thank you!

function onEdit(e) {
var oldValue;
var newValue;
var ss=SpreadsheetApp.getActiveSpreadsheet();
var activeCell = ss.getActiveCell();
if(activeCell.getColumn() == 11 && ss.getActiveSheet().getName()==”Sheet1″) {
newValue=e.value;
oldValue=e.oldValue;
if(!e.value) {
activeCell.setValue(“”);
}
else {
if (!e.oldValue) {
activeCell.setValue(newValue);
}
else {
if(oldValue.indexOf(newValue) <0) {
activeCell.setValue(oldValue+','+newValue);
}
else {
activeCell.setValue(oldValue);
}
}
}
}
}

No problem! It looks like the issue might be the quotes. Those are curly quotes, but we need them to be regular double quotes. Make sure the double quotes around “Sheet1” are standard straight double quotes. If you copied and pasted the code, sometimes formatting issues might cause the quotes to be incorrect. Please try replacing line 9 with this:

ss.getActiveSheet().getName() == "Sheet1"

Thank you Jmarkus! I copy and pasted exactly how you have it and I am getting an error message :Synxtax error: Invalid or unexpected token line: 9 file: Code.gs .
Which is:
ss.getActiveSheet().getName() == “Sheet1”

The name of the sheet is sheet1. (also, for context, when I say I am a beginner – I am extremely new, :/ apologies if I have incorrectly implemented the code you provided and thanks for your patience. Are you able to diagnose?

Try this instead:

function onEdit(e) {
var newValue = e.value;
var oldValue = e.oldValue;
var ss = SpreadsheetApp.getActiveSpreadsheet();
var activeCell = e.range; // Use e.range to get the edited cell

if (
(activeCell.getColumn() == 4 || activeCell.getColumn() == 5) && // Use double equals (==) for comparisons
ss.getActiveSheet().getName() == “Sheet1”
) {
if (!newValue) {
activeCell.setValue(“”); // Clear the cell if the new value is empty
} else {
if (!oldValue) {
activeCell.setValue(newValue); // Set the cell value to the new value if there was no previous value
} else {
var oldValueArray = oldValue.split(‘, ‘); // Split the old value into an array

if (oldValueArray.indexOf(newValue) < 0) { oldValueArray.push(newValue); // Add the new value to the array if it's not already present activeCell.setValue(oldValueArray.join(', ')); // Update the cell value with the updated array } else { // If the new value is already in the old value, do nothing or handle it as you prefer } } } } }

Hi Kai! Extremely new to coding… I want to apply this logic to my google sheet, if I remove an option from my list, I want it to be deleted from the previously selected rows. I tried your coding, but I am not implementing it correctly; below is my code – are you able to help? Also, I am currently getting an error message for line 7, but the code is still working. Does it look right to you? Appreciate any help!

1. function unit(e) {
2. var oldValue;
3. var newValue;
4. var ss=SpreadsheetApp.getActiveSpreadsheet();
5. var activeCell = ss.getActiveCell();
6. if(activeCell.getColumn() == 4 || 5 && ss.getActiveSheet().getName()==”Sheet1″) {
7. newValue=e.value;
8. oldValue=e.oldValue;
9. if(!e.value) {
10. activeCell.setValue(“”);
11. }
12. else {
13. if (!e.oldValue) {
14. activeCell.setValue(newValue);
15. }
16. else {
17. if(oldValue.indexOf(newValue) <0) {
18. activeCell.setValue(oldValue+', '+newValue);
19. }
20. else {
21. activeCell.setValue(oldValue);
}
}
}
}
}