Google Sheets: onEdit Basics
HTML-код
- Опубликовано: 22 дек 2020
- Here I show some basics of using onEdit() triggers.
* onEdit() triggers are not meant to be manually run; rather, they automatically run whenever a user changes a value on the spreadsheet.
* An event object is typically passed to the function, typically called e, which contains information such as the range, source, oldValue, and value of the edit made.
* You cannot have multiple functions called onEdit() in the same script file (only the last one will be recognized). Rather, consolidate all conditionals and actions into a single function.
Resources:
Simple Triggers Documentation - developers.google.com/apps-sc...
Event Objects Documentation - developers.google.com/apps-sc...
Thank you for sharing this cool method!!!
thank you! finally found a tutorial that was simple enough to follow. finally got this to work! yeyeyey 🎉 thanks 😄
So glad!
Thanks for the info
thank you very much sir!
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?
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!
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.
Hey David. Apologies the delay. I posted a video about using onChange instead of onEdit, but be warned that it's a bit hacky!
Hey Spencer, none of these triggers like onEdit and onOpen seem to work anymore, do we have to add these triggers manually now?
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!
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)
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?
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 :)
@@SpencerFarris sure, what is ur email?
@@mohamed.montaser spencerfarris@gmail.com
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.
I forgot to say that I've also activated the onEdit trigger by clicking on the clock icon, but it sill doesn't work.
@@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.
@@SweetCanadianMolasses Hahahahahaha
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,
Can you please email me the sheet so we can make it work?
@@SpencerFarris I have sent an email with my question.
Kind regards,
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
It may be, but it may just be a LOOKUP table. You can email me if you want
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 .
if (e.value != "TRUE" || e.source.getActiveSheet().getName() != "name")
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 !!
Spencer.farris@gmail.com
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?
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.
@@SpencerFarris Incredible! How can documentation not be available?
@@kenjleex No idea :/
What trigger can we use when a script makes a change?
On Change
Hi, if we erase the date, in column 2 it seem to write it back next column 3 lol
Yes, of course. I have a lot of other things that show more specific ways to use onEdit. This is just a basics overview
TypeError: Cannot read property 'range' of undefined. I am getting this error upon running the script. Can anyone please help?
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.
@@SpencerFarris Ohh this is brilliant. Much appreciated. My bad, the trigger was set incorrectly. Apologies for the false alarm. Thank you so very much :)
@@krishnamanda71 Glad you got it :)
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();
}
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
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());
}
@@SpencerFarris Thanks for your quick response. And i don't have any other onEdit functions.
Tried your code but it's not working 😢.
@@arianekae9266 Can you add me as an editor?
spencer.farris@gmail.com
@@SpencerFarris I couldn't; I'm not allowed to share outside organization. Is it okay to email you so i could send snippets?
@@arianekae9266 Ya