Running Multiple onEdit events
HTML-код
- Опубликовано: 9 сен 2024
- How to run multiple onEdit events in the same script.
Important notes:
* It was not shown in the video, but you cannot have more than one function of the same name in the same script.
* The actual events used in my Sheet are less important than the construction.
Sheet (to copy from File - Make a copy)
docs.google.co...
Script
function onEdit(e) {
if (!e)
throw "Do not run onEdit from script editor";
const src = e.source.getActiveSheet();
const r = e.range;
assignedDatetime(src,r);
moveProcessed(src,r);
}
function assignedDatetime(src, r) {
if (src.getName() != "Incoming" || r.columnStart != 2) return;
r.offset(0, 1).setValue(new Date());
}
function moveProcessed(src, r) {
if (src.getName() != "Incoming" || r.columnStart != 4) return;
const dest = SpreadsheetApp.getActive().getSheetByName("Processed");
r.offset(0, -3, 1, 4).moveTo(dest.getRange(dest.getLastRow() + 1, 1, 1, 4));
src.deleteRow(r.rowStart);
}
Connect with me:
• spencer.farris@gmail.com
• spencerfarris.me
• www.linkedin.com/in/spencer-farris/
• Twitter @FarrisSpencer
• Google Product Expert support.google.com/docs/profile/12305
I just wanted to give a huge shout out to Spencer! He took time out of his day yesterday to help me with a project I have been working on for the last 2 months and helped push me over the finish line with it.
Time is our most precious asset and we didn't even know each other until yesterday. Yet his love for his craft and his expertise was able to help me when I hit a wall. I am so Thankful for that and wanted to make it known, because all we seem to hear about is bad news each day when there are still great people out there doing great things. So Thank You Spencer, I am so grateful for your time and your kindness! Your genius amazes me and I hope to reach your level of expertise as I continue to build new projects.
You're most welcome, and thank you so much for the kind words!
My goodness, I've been looking for this for so long. Thank you so much!
Spencer, you're a life saver!
Hi Spencer, this was really helpful, again giving so much valuable information in little time, thank you !
Thank you so much
Superrr
Hi Spencer,
function sumCells(e){
const r = e.range;
if (r.rowStart != 18 && r.rowStart != 19 && r.rowStart != 20 || r.columnStart != 4) return;
let outCell = r.offset(0,1);
outCell.setValue(+outCell.getValue() + parseInt(e.value));
}
this code adds the written number to the right side, I want to add another one here, add a number written to the cell to the left. for example; Row 21 column 5 , let outCell = r.offset(0,-1)
That's exactly how it should work. Is it not working for you?
Spencer, this is hugely helpful for the task I'm trying to solve. I do have one question and it may be pretty basic: In the sheet, it looks like you do want the source name to be Incoming and the column to be 2. So why then do you use the != rather than ===? And if you want both of those to be true why use || instead of &&?
There are two ways to approach these clauses: "If this is true then do the stuff" or "If this is false then quit"
I prefer the second, it's just how I think about it and gets the script to quit faster if it's wrong. So what I'm saying is "If the sheet is not 'Incoming' then quit"
@@SpencerFarris Got it. Thank you!
Hi Spencer, I need some help.
On a sheet named "Fill Me Out" there is a drop down menu of '6 Days', '5 Days', '4 Days', '3 Days'. How do I go about hiding and unhiding rows on another sheet named "Program" automatically dependent on this drop down menu. For example I would like when '5 Days' is selected on 'Fill Me Out' Sheet to hide the rows 63:74 on the 'Program' Sheet'. And also to Unhide these rows if I were to go back to 'Fill Me Out' Sheet and select '6 Days' on the dropdown. I would like this to be done on the edit (without the use of a button). Could you help?
Hi group, this works perfectly for me. The only entry that is not recorded is drop down cell changing status to close. My ultimate aim is to build a changelog. Who changed (email) what filed, when. From.. To.
A video on this would be ace!
Many thanks
I'm not following the ask, sorry
Hello, can you explain how we can multiple option selection across multiple columns and all the rows in these columns?
Hi Spencer, great video. I am running a similar script but having issues when others are checking multiple boxes at the same time. The script ends up moving the wrong row and it seems the only way around it is checking the box one at a time. We have multiple people using the sheet and I was hoping to create a fail safe to avoid this issue. If you have any suggestions, that would be great!
Did you make an installed trigger along with the simple trigger?
This dude is amazing. I was struggling on making multiple of the same onEdit Autosort command in google sheets to work, and he not only solved the problem, but made a super easy to understand code. He did it terrifyingly fast too. Amazing guy, and his tutorials do work too. Definitely will be going through them again soon enough for my other projects. He's in a spreadsheets discord community, you can google and find the link in a reddit post.
I also actually started a specif Google Apps Script Discord server discord.gg/JScQ5tt
How would the script look like if you combine the Move Rows (column) and the 2 way Sync onEdit scripts??
I really love this idea, but can't get it to work for my own situation. I wish I knew why
What's your code?
Thank you for your video! I don't know anything about writing app script, do you have any recommendations on where to start to learn the syntax of the language? Also, is it possible to create 3 different actions with 1 action being on another tab in the spreadsheet? Thanks again!
Any beginning JavaScript course would be a good start for the language syntax. Yes, you can set up scripts they do multiple actions on various sheets
This video was incredibly helpful and I got both functions to execute. The issue I am now faced with is that only one row will move to the next sheet and any following selections will delete from the first sheet but not populate to the second sheet. It also appears that the function will miss the checkbox click sometimes and then when you unclick the box it will process the change and move it over. Is there a way to change it to if the value is true vs false instead of any changes at all to be sure it is recognizing the selection?
Yeah, you can check for e.value == "true"
@@SpencerFarristhank you! I found that it was moving it to the next sheet but it was populating it at the bottom of the sheet because I had check boxes on a new column. Once I deleted those check boxes it would populate to line 2 but would delete whatever was there before. I tried quite a few times and used chatgpt to try and get it to populate the next available line but it simply kept deleting the existing line on the next sheet. My solution was to have it insert a new row on row 2 and then input the data onto the newly inserted row. It worked like a charm. This was a great starting place though.
Thank you for this! It is very helpful. What about how do I use autosort on different tabs in the spreadsheet? I'm trying to sort the dates but it only works on one tab at the moment..
Well what script do you currently have?
Thank you for making this video. But I wanna ask you something. If I want delete the specific group of cells data with the help of onedit function and one cell as trigger to run the script, what i need to write in Appscript. Please help in this regard.
Thanks in advance
Can you post a link to the sheet here so we can take a look?
Hi Spencer,
I love your videos they are very helpful to someone like me who's learning coding in google sheets. I just have a question though, I'm creating a time sheet that records start time and end time then just applied a formula to get the total handling time. Is there a way to write a script that would work the same way as "pause" and "resume" function wherein "pause" will record the pause time and the "resume" will just record the resume time and then eventually get the sum = the total handling time?
function onEdit(e) {
const sh = e.range.getSheet();
if (sh.getName() == "Sheet1" && e.range.columnStart == 2 && e.value) {
e.range.offset(0, 7).setValue(new Date())
}
if (sh.getName() == "Sheet1" && e.range.columnStart == 8 && e.value == "Done") {
e.range.offset(0, 2).setValue(new Date());
let Start = new Date(sh.getRange(e.range.rowStart,8).getValue());
let End = new Date(sh.getRange(e.range.rowStart,9).getValue());
}
}
Here's what I wrote to run the start and end.
@@saoxiv4386 Please add me as an editor on the sheet
Is there a way to do exactly this but, upon the check of that box, a popup comes up and theres a prompted to "sign" by typing their name and hitting a button that says "accept" , or cancel... and if they hit accept it moves it to the other tab??
Yes, look here
developers.google.com/apps-script/reference/base/ui
Thanks Spemcer, this trick work very well for my google sheets project.
it work for function that related manipulate cell (copy, paste, delete, insert, etc) but if call process open link or call API, i tried many times not working. I make sheet that can send Whatsapp message from list data, it work with button perfectly but when I apply your trick Not working to call API sender. do you have any tricks for this problem? because i want to open this sheets file on my mobile phone. now its working on my mac, but fail on mobile phone
You have to name the function something other than onEdit and then install a trigger for it. Installed triggers can call other services, simple cannot.
Spenser I am still using the triger on edit you gave me to send emails automatically but now I was wondering if there is a way I could make a row stay emty and move content added on its column move a row down. The purpose is to keep the row as just data entry row and keep the recent content on top of the screen without having to scroll or edit and manually add a row above every time. Thanks a lot.
I'm sure there is, but I don't understand what you want
Assuming I am filling a format with 5 columns. The moment I put value on the cell E1 I want that row to be pushed down so A1 B1 C1 D1 and E1 is always empty. basically I want the most recent data on top. And yes you could think why not put the content on second row or third then but its a format I share with others and several people add information. Which is why I wanted to do it automatically since I can't rely they actually create a row every time the add something and push content down.
Spencer, Thank You for this video! I am trying to use an onEdit script in my workbook but I'm having an issue. I have a sheet called 'Activity' with a bunch of different buttons I created that are all attached to different scripts. These scripts add a number to the corresponding column on my 'Database' sheet to track activity for the day (Ex. Click New Call on the Activity sheet and it adds 1 to the New Call column on the Database sheet). I am trying to copy the values from the Database sheet to another sheet titled "Historical Info' so I can track numbers long term since our activity and sales start over each month. I have used this video and got the numbers to copy from the Database to the Historical Info sheet if I manually type a number into the Database sheet, but it doesn't work if I click one of the button on my Activity sheet. Can you think of a way to make this work? I would be glad to share my workbook with you if you are willing to take a look. Thanks again though, this video was a huge help!
If you would share the Sheet to me we can look at it. spencerfarris@gmail.com
@@SpencerFarris just shared it with you. Thank you for the reply!
Hello Spencer,
Can we update many google sheets using only one google sheet's app script? If yes then how? Please make a video
Do you mean multiple files or multiple tabs?
@@SpencerFarris in multiple files....
example- I have a master sheet and I have 10 copies of this master sheet and I want to rename column name in master sheet then in all 10 copies it should be update......
I am using Library but here onEdit function is working only for master sheet and rest files are not updating.
@@rohitkumawat6416 Store the sheet IDs in an array, loop through them, make the changes
@@SpencerFarris Ok, Thank you spencer
I m curious is there a way to onedit script two actions in google sheets where - 2 way sync (b/w master and child) and then update duplicates in the master sheet so that it could automatically update in the child sheet that the duplicate is linked to?
Well I'm sure it's possible. But I don't really understand what the second desired thing is.
@@SpencerFarris thank you for your response. What I mean in the second part is that in the master sheet we would have duplicates of words/actions that are linked to different people, so I was wondering if there is a way to update those duplicate actions (without the filtering and copy paste option) so that it could update in master sheet and update immediately in the child sheet
@@sumadoolpeep7171 I'm sure there is, but without looking at the sheet I can't really help
could I send you an example?
@@sumadoolpeep7171 yes spencer.farris@gmail.com
Hi Spencer,
I really appreciate your work of sharing your knowledge and I already use some of your codes in GS.
Now, I am trying to combine your "move row with dropdown" code but it doesn't work. I'm trying to figure out the problem but I am a newbie and very frustrating. Can you please guide me on how to correct the issue?
I have several dropdown menus and the code I copy and edit from your videos is below.
function onEdit(e) {
const src = e.source.getActiveSheet();
const r = e.range;
sonlandi(src,r);
partner(src,r);
portfoy(src,r);
}
function sonlandi(src,r){
if (r.columnStart != 8 || r.rowStart == 1 || e.value == src.getName()) return;
src.getRange(r.rowStart,1,1,19).moveTo(dest.getRange(dest.getLastRow()+1,1,1,19));
const dest = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(e.value);
dest.getRange(dest.getLastRow(),20).setValue(new Date());
src.deleteRow(r.rowStart)
}
function partner(src,r){
if (r.columnStart != 11 || r.rowStart == 1 || e.value == src.getName()) return;
const destpart = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(e.value);
src.getRange(r.rowStart,1,1,19).copyTo(destpart.getRange(destpart.getLastRow()+1,1,1,19));
}
function portfoy(src,r){
if (r.columnStart != 10 || r.rowStart == 1 || e.value == src.getName()) return;
const destport = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(e.value);
src.getRange(r.rowStart,1,1,19).copyTo(destport.getRange(destport.getLastRow()+1,1,1,19));
}
What exactly do you mean "doesn't work?" Is it throwing an error?
@@SpencerFarris When combining more than one onEdit function, it gave me an undefined object error, as I don't know anything about coding, etc. I couldn't figure it out. After some research, I added every function with the trigger on the clock icon and it is now working. Thanks for your great videos. I am using some of your works already and will be using 2-way sync as soon as possible as well!
@@Emraheskincom I'm glad it worked out. I like wrapping each in the main onEdit(e) simple trigger, but that works, too!