Google Sheets: onEdit Basics

[@zania6285]

thank you! finally found a tutorial that was simple enough to follow. finally got this to work! yeyeyey 🎉 thanks 😄

[@SpencerFarris]

So glad!

[@ginahsu8388]

Thank you for sharing this cool method!!!

[@SpencerFarris]

You're so welcome!

[@davidaw104]

What would you recommend if I want a trigger base on a change in cell value? Onedit is only trigger when a user make changes. It won't work if the cells are change base on formula or importrange.

[@SpencerFarris]

Hey David. Apologies the delay. I posted a video about using onChange instead of onEdit, but be warned that it's a bit hacky!

[@GraemeStewart-k9w]

Thank you for the video. Is there a way to return the edited cells range with an onEdit(e) trigger when a filter is applied to the sheet?

[@SpencerFarris]

A filter isn't an edit

[@mbevoip8912]

Hello Spencer! Could you help me to modify the below code so that it works only one a specific sheet? The code works fine but would want it to work on a sheet for example sheet1. function onEdit(e){ if(e.value != ""TRUE"") return; e.source.getActiveSheet().getRange(e.range.rowStart,e.range.columnStart+1).setValue(new Date()); } I modified the code using e.range in place of e.source but to no avail. Thank you in advance .

[@SpencerFarris]

if (e.value != "TRUE" || e.source.getActiveSheet().getName() != "name")

[@d.decristoforo9190]

Hello! I have a question for you. I have two sheets. I need a drop down to be chosen based off of the town's name. Sheet 1 has a column with a drop down called court locations and a column with town names that need to be filled out by the customer. I also have another sheet that has all the towns and the court locations that need to be chosen based off of the towns name. If someone lives in a certain town they have to go that specific court. Is this an on edit function? If so, how do I use it. I'd be more than happy to hire you for this task

[@SpencerFarris]

It may be, but it may just be a LOOKUP table. You can email me if you want

[@ruairidhgrass3479]

Hey Spencer, none of these triggers like onEdit and onOpen seem to work anymore, do we have to add these triggers manually now?

[@THDYoung]

What are 'columnStart' and 'rowStart'? They aren't functions e.g columnStart() or variables and don't seem to be properties of the event object as far as I can tell from the documentation. I am missing something simple. What is it?

[@SpencerFarris]

columnStart, rowStart, and some other elements are Properties of the Range object. Unfortunately, there is no location I've found actually documenting all of these. Rather, I learned them from seeing others' code and replicating it myself.

[@kenjleex]

@@SpencerFarris Incredible! How can documentation not be available?

[@SpencerFarris]

@@kenjleex No idea :/

[@mp89na]

Hello, thank you for the video. I'm trying to make onEdit function work but there is no way. I've tried both on my organization google suite that on my simple one but nothing. It doesn't work at all. I've tried to copy different onEdit samples code but none of them does anything. May this be due to some new restriction? Do i have to activate something more than the code or allow a google resource? Thank you in advance for your help.

[@mp89na]

I forgot to say that I've also activated the onEdit trigger by clicking on the clock icon, but it sill doesn't work.

[@SpencerFarris]

@@mp89na For a simple onEdit() there usually aren't any scopes. I often like to test that the onEdit() is firing at all by creating a really simple onEdit() script and making sure it runs. Something like: function onEdit(e){ e.range.offset(0,1).setValue("Hello world"); } That will literally just put the string "Hello world" one cell to the right of an edit. Make sure that works, then you can move on to others.

[@SpencerFarris]

@@SweetCanadianMolasses Hahahahahaha

[@Serindan223]

Hey Spenser, thanks for the vid! As I'm not that proficient in coding, I can't seem to be able to compile my 2 onedit triggers to 1. Any ideas how to do it? The the triggers are the following: function onEdit(e) { if (e.range.rowStart < 3 || e.range.columnStart !== 12 ) { return; } if (e.value && e.oldValue && !e.oldValue.includes(e.value)){ e.range.setValue(`${e.oldValue}, ${e.value}`); } else if (e.oldValue.includes(e.value)) { e.range.setValue(e.oldValue); } } AND function onEdit(e) { if (e.range.rowStart < 3 || e.range.columnStart !== 13 ) { return; } if (e.value && e.oldValue && !e.oldValue.includes(e.value)){ e.range.setValue(`${e.oldValue}, ${e.value}`); } else if (e.oldValue.includes(e.value)) { e.range.setValue(e.oldValue); } } Thanks!

[@SpencerFarris]

The simplest way is to rename them and wrap them in a single onEdit script: function onEdit(e){ if (!e) throw "do not run from editor"; edit1(e); edit2(e); } Then rename your current scripts as edit1(e) and edit2(e)

[@jessicaburgin1753]

Thank you for this video! In theory it is exactly what I need to set up my automatic emails. I've tried this code both modified to fit my needs and with zero edits on a spreadsheet that mirrors your example to see if that worked. However, I can not get it to work. I've gotten it to a place where the execution shows "Completed" but there is no log information and an email never sends. I saw another commenter had the same issue but didn't see a comment on the solution. Any help greatly appreciated!

[@krishnamanda71]

TypeError: Cannot read property 'range' of undefined. I am getting this error upon running the script. Can anyone please help?

[@SpencerFarris]

The first point in the description answers this: onEdit() triggers are not meant to be manually run; rather, they automatically run whenever a user changes a value on the spreadsheet.

[@krishnamanda71]

@@SpencerFarris Ohh this is brilliant. Much appreciated. My bad, the trigger was set incorrectly. Apologies for the false alarm. Thank you so very much :)

[@SpencerFarris]

@@krishnamanda71 Glad you got it :)

[@david_sdiego]

What trigger can we use when a script makes a change?

[@SpencerFarris]

On Change

[@youcefkaddour01]

Hi ! Can I email you please ? I need your help on a script. I want to send a email automatically when a new row is updated (from blank to "ready") but on a specific sheet. i tried your video method but it doesn't work... Please can you help me ? Thank you !!

[@SpencerFarris]

[e-mail]

[@syahmi1012]

thank you very much sir!

[@mohamed.montaser]

hey, thanks for the video, i have a question lets say we have 2 columns ( id , order date ) my question is how to make the ID column increment by 1 when the order date is not blank? and if the order date is deleted don't remove the ID value can you make a video about this please?

[@SpencerFarris]

Could you please email me with a sheet detailing what you need? I'll be happy to make a video on it once it's solved :)

[@mohamed.montaser]

@@SpencerFarris sure, what is ur email?

[@SpencerFarris]

@@mohamed.montaser [e-mail]

[@ivanajurasinovic]

Hi! Thank you for the idea, I was looking for something like that. I have created a spreadsheet in Google Sheets which allows me to add or subtract points for my students. However, I am having trouble with the second part. In a column next to the points, I added a drop down menu (items such as: +1 point for good behaviour, -1 point for being late etc.) Now, I created one sheet per student in the same Google Sheet. What I still have to do is the following: - I click on an item in the drop down menu for student A. - Automatically, that item with a timestamp is saved in the sheet for the student A. And this happens every time I choose an item, every edit in a separate row with a timestamp. - If I choose an item from the menu for student K, the item with a timestamp is saved in the sheet for the student K. I really need help, if you could help me with student A, I would just follow the step with the other students. Kind regards,

[@SpencerFarris]

Can you please email me the sheet so we can make it work?

[@ivanajurasinovic]

@@SpencerFarris I have sent an email with my question. Kind regards,

[@judequinston]

Thanks for the info

Hi, if we erase the date, in column 2 it seem to write it back next column 3 lol

[@SpencerFarris]

Yes, of course. I have a lot of other things that show more specific ways to use onEdit. This is just a basics overview

[@kenjleex]

can't find .rowStart or columnStart in the AppsScript reference. I'm asking because I have an onEdit that works for some users but not others. When I made it an installed trigger I get failure report: 2023-06-18 16:03:01 Eastern Daylight Time onEdit TypeError: Cannot read properties of undefined (reading 'getRow') open 2023-06-18 16:03:02 Eastern Daylight Time Here's complete code: function onEdit(e) { var range = e.range; var sName = e.source.getSheetName(); var row = range.getRow(); var col = range.getColumn(); var dothis = e.value; // var priorItem = e.oldValue; // Action Selected // Browser.msgBox(dothis + 'row '+row +' col '+col) // sss.getRange(row,col).clearContent(); if (sName == "SignUp" && col == 2 && row == 5 ){ var removeRow = ss.getSheetByName('DB').getRange('delete.row').getValue(); if ( dothis == 'ADD' && removeRow == "NOT IN" ){ var lastRow = 10 + sss.getRange('next.db.row').getValue(); sss.getRange("B3:B4").copyTo(ss.getSheetByName('DB').getRange(lastRow,1),SpreadsheetApp.CopyPasteType.PASTE_VALUES,true); } if( dothis == 'REMOVE' && removeRow != "NOT IN" ){ ss.getSheetByName('DB').getRange(removeRow,1,1,2).clearContent(); } ss.getSheetByName('DB').getRange('A2:b').sort(2); sss.getRange("B5").clearContent(); sss.getRange("B4").activateAsCurrentCell(); }

[@SpencerFarris]

Sorry it's been SO LONG. Is this still an issue?

[@kenjleex]

@@SpencerFarris Nope, thanks for checking

[@arianekae9266]

Hi, I have 7 or more users on my worksheet with multiple tabs(I need my code to be applied in all tabs), but this code only works on me. And I installed triggers as what I read in stackoverflow but it's still doesn't work. The users are using the worksheet simultaneously. Please help! Here's my code below: function onEdit(e){ var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet(); var row = ss.getActiveCell().getRow(); var col = ss.getActiveCell().getColumn(); var date = new Date(); if(col === 5 && row > 2){ sheet.getRange(row, 10).setValue(date); } } Thank you in advance

[@SpencerFarris]

I would rewrite as follows, but it should work for all users, and you don't have to install a special trigger. Do you have any other onEdit functions? function onEdit(e){ if (e.range.columnStart != 5 | e.range.rowStart == 1) return; e.range.offset(0,5).setValue(new Date()); }

[@arianekae9266]

@@SpencerFarris Thanks for your quick response. And i don't have any other onEdit functions. Tried your code but it's not working 😢.

[@SpencerFarris]

@@arianekae9266 Can you add me as an editor? [e-mail]

[@arianekae9266]

@@SpencerFarris I couldn't; I'm not allowed to share outside organization. Is it okay to email you so i could send snippets?

[@SpencerFarris]

@@arianekae9266 Ya