Superb tutorial! What about comparing 3 sheets for duplicates? is it possible to use this formula?
@CanYeniseyКүн бұрын
Thank you, @Spencer Farris! I see this is from 2021 yet still today very much relevant - and I have a ChatGPT+ subscription yet still couldn't hack this simple thing. You rock my man🤘🏻
@AngelSalazar-f6u5 күн бұрын
Neat tutorial!
@diallooumar32875 күн бұрын
Hello i wnat o ask you a help. i want to lock the row entre entering a specifing value like " manager" then that row is locked only the person who has the access can edit in case there is mistake during the entring process. please can you help i will be happy. i alredy sent you a message in your email. Thank you
@140dc1406 күн бұрын
Could you help me I'm having problems? I could send you over my test spreadsheet.
@limestar97519 күн бұрын
Is there a way to lock drop downs with this?
@SpencerFarris8 күн бұрын
This locks any edits
@aremarketing99489 күн бұрын
Low sound ;(
@SpencerFarris9 күн бұрын
Darn, it sounded fine when I recorded it. It may have lost audio quality when I moved it through the other programs. I'll probably have to re-record. Thanks for letting me know.
@SpencerFarris8 күн бұрын
I made a new one. Hopefully the audio comes across better kzbin.info/www/bejne/inO1nqONl6uBrNU
@TrenerZvezdnaia9 күн бұрын
Yesss = best!
@JacquiePershingLCSW10 күн бұрын
Hey there! I am using your script to get checkboxes to increment and decrement, and it works great, but I can not figure out how to get multiple groups to calculate. Please help! Columns C (+) and D (-) add and subtract Column B total. How do I add columns E, F, and G to the script? I can not figure out how to also get F and G to add and subtract Column E's Total... Script I am using... function onEdit(e){ if (!e) throw "Do not run from editor"; incrementAndDecrement(e); } function incrementAndDecrement(e){ const src = e.source.getActiveSheet(); const r = e.range; if (src.getName() != "Individual Student/Family Services" || r.columnStart < 3 || r.columnStart > 4) return; let out; if (r.columnStart == 3){ out = r.offset(0,-1); out.setValue(out.getValue()+1) } else { out = r.offset(0,-2); out.setValue(out.getValue()-1) } r.setValue("FALSE"); }
@ThomasHansen-t1i16 күн бұрын
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!
@SpencerFarris9 күн бұрын
Did you make an installed trigger along with the simple trigger?
@Crumbsism16 күн бұрын
Hi, not sure why but when i copied the code and only changed the location of the column for the drop down it takes the selected row and then the last row also. The only change was for the column which for me is 9 instead of 3
@Crumbsism16 күн бұрын
Also a follow up, my data is coming from a google sheet is there a reason that the coding works if i manually type in something but if its the data from the form it does not move to the other tabs?
@SpencerFarris9 күн бұрын
@@Crumbsism Interesting. Can you share the spreadsheet with me?
@durcastraushun28820 күн бұрын
so, it only works for 1 cell, if I have 100 cells, i have to do drawings for each one and enter the code for each one
@SpencerFarris20 күн бұрын
Use checkboxes :) kzbin.info/www/bejne/iJekqnWFh8itfsU
@diegoortiz593121 күн бұрын
You're fire, bro
@juliajones594821 күн бұрын
THANK YOU!!!!!!!! This is exactly what I needed. What if you want to duplicate a whole notebook with protections?
@taraperkins539721 күн бұрын
Lets say I want all of column j3-j25 what would the code be
@phoenixfeather205324 күн бұрын
You are AMAZING! I have been copying/moving/deleting for years. This saves SOOOOOOO much time. Thank you very much!
@icyherenow26 күн бұрын
My goodness, I've been looking for this for so long. Thank you so much!
@candytordesillas68728 күн бұрын
Can you also make a video on how to make an onChange trigger from programmatically added entries to Google Sheets? For example entries that came in from AppSheet to Google Sheets. Thanks in advance!
@GraemeStewart-k9w29 күн бұрын
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?
@seawayaccounts29 күн бұрын
Hi Spence, could you help me, it is not working at all. I have used this: function onEdit(e){ if (e.range.rowStart <= 2 || e.range.columnStart !== 9 ) { 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); } } The cells with the dropdow start in the row 2, column I. I am using the new future Dropdown, instead of Dropdown from a range. Mmmm what could be happening?
@FORYOU-wb1rx29 күн бұрын
Love from India (Bharat)💐👍
@meganpowell4754Ай бұрын
hello, I'm having a few issues with this script. One, I'd like it to just copy the row to the new sheet and not delete it from the original sheet. I removed the delete part of the script but it is still deleting the first cell in the row when it moves the data over. Also, I can't get it to copy over the cells in the row that have dropdown menus. Any advice? I have been messing around with it for awhile with no luck.
@SpencerFarris29 күн бұрын
Use .copyTo instead of .moveTo I'm not sure what you mean with your second question
@user-pj4oh6mx9dАй бұрын
thank you!
@MCS.VEDIOSАй бұрын
Thanks for the Video Brother 😊
@JennaWenaas-c6cАй бұрын
Hey Spencer, I've copied your script and am trying to adjust it for my spreadsheet. I've shared the file with you. Our team has four members, and we're tracking all our projects. Each of us has our own sheet that we use. When a project is marked as complete from the dropdown, we want it to move from the individual employee's tracking sheet to the 'Complete' sheet. Could you please let me know how to do this or take a look at the Google Sheet I shared with you? Thanks!
@SpencerFarrisАй бұрын
Answered on the email, but to be clear clicking a dropdown and selecting the value that was already there doesn't count as an edit.
@MichelNabilАй бұрын
Thanks, a lot for your awesome hacks excuse me, I'm beginner to scripting using google App sheet. The script in the video doesn't work when I copy and paste the cell values into the sheet I want to change. for example, when I paste lots of cells containing "M", I want them to automatically get changed to "Male" and so on... but it only happens to the first cell, not the rest in column. Also, I want to only trigger this script in a certain sheet, is there a way to do so? This is the current applied script: function onEdit(e){ if (!e) throw "Do not run from editor, the functipon runs automaticallly whenever an edit is made" autoReplaceValues(e); } function autoReplaceValues(e){ const values = ["M","F","O"]; const index = values.indexOf(e.value); if (index == -1) return; const replacments = ["Male","Female","Other"]; e.range.setValue(replacments[index]); }
@SpencerFarrisАй бұрын
This script is written for an edit in a single cell, not mapped along an array To only use it on a certain sheet add const src = e.source.getActiveSheet(); if (src.getName() != "sheet name") return; put that before any of the actions in autoReplaceValues
@tom4tenmark729Ай бұрын
Hey, first of all, thank you for your tutorial. I have one question tho. I'm a Admin for a Twitch Streamer, he wants to send out Postcards to everyone who subs 5 or more subs or subs at tier 3. The Moderators then have a google sheets in which they fill out the subs Discord ID, Twitch ID, when he subbed and so on. The Admins then text those people they've written down and change a dropdown status to "Done" for "Texted and received the Address" only Admins should change this status tho. The main problem ist, that I want Admins to have their own seperate sheet with a extra tab called "Addresses" in which we put all the Addresses down, the mods should not have access to it tho, hence why I want to connect both Google Sheets but only for the "Postcards" tab the moderators fill out in the seperate sheet. Those datas should be 2 way synced so admins receive what mods have written down and mods receive the changed status the admins applied. I tried your Script but once I run it it gives me the error: TypeError: Cannot read properties of undefined (reading 'source') Any idea of whats happening here? My code looks exactly like yours and I exchanged the two names + the ID to what my spreadheet tab is called. I added it in the Spreadsheet of the Admins, do I need to add anything to the Moderators one as well? Thank you in advance.
@SpencerFarrisАй бұрын
Don't run it. The script relies on information from the edit which it only gets when the script runs automatically. Just make a trigger for the function.
@caitythekittenАй бұрын
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 &&?
@SpencerFarrisАй бұрын
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"
@caitythekittenАй бұрын
@@SpencerFarris Got it. Thank you!
@VIJAYAMIRTHARAJPROMINANCEАй бұрын
Great Information...Thanks But, Who is Using this Script (triggering the Script), They Can also Edit the Protected Cells. Is there any way to Edit only by Sheet Owners Not by the Triggerers?? @spencer Awaiting for your Reply
@SpencerFarrisАй бұрын
Yes, you need to use an Installed Trigger instead so that you can make it run as the sheet owners
@TheJSJoshАй бұрын
Is this outdated? Not even the first pass of this (the simple concatenation) works. Just, nothing happens with the dropdowns, other text will change, just not the dropdowns.
@SpencerFarrisАй бұрын
I just tested on the shared spreadsheet and it worked fine
@K1LLA_KING_KONGАй бұрын
I used your copy sheet, however I prefer to protect the SHEET and add exceptions. How do I modify this script for SHEET?
@SpencerFarrisАй бұрын
I honestly need to make another video with that
@mrjp2149Ай бұрын
You make it sound so easy. Is there an easier way 2 years later?
@SpencerFarrisАй бұрын
I don't think so
@GorillaVape-ui4ndАй бұрын
Hello. Excellente video. Unfortunately, the new sheet generated does not copy the protections. In my case, the original sheet is protected but I set exceptions in some cells. Would it be the cause of the script not to run properly?
@SpencerFarrisАй бұрын
Yes, that's exactly why. This script is written with range protections in mind and sheet protections work completely differently. I honestly need to just make a new video about that
@AdaKetchumАй бұрын
Hello! Thank you for the helpful video! Is there a way to open to a range when you open a specific tab?
@SpencerFarrisАй бұрын
No. There is no trackable event for "open sheet"
@johntelesengmecАй бұрын
Wow! You're a life saver, mate. Thanks! Just subscribed!
@philliprenault69Ай бұрын
This video was great. Is there a way to apply this across more than two sheets? Right now I have tried =MATCH(A1,INDIRECT("Sheet2! & Sheet3!A:A"),0) without any luck. Or do I just need to add a rule for each sheet I'm attempting to match with? Thank you.
@SpencerFarrisАй бұрын
Sheet2!Sheet3!A:A is clearly not a valid range. What exactly do you want? Formatting if A1 is matched on BOTH Sheet2 and Sheet3, or if it's matched on EITHER?
@philliprenault69Ай бұрын
@@SpencerFarris if it’s matched on any. I have around 14 tabs and I’m looking to see if a name appears on multiple tabs. I feel that this is more a database solution that I’m trying to make sheets work with.
@SpencerFarrisАй бұрын
@@philliprenault69 I would have a single MASTER sheet that brings in data from every sheet and then just check against that one
@manuelmedels2624Ай бұрын
Hei i'm trying to do this now but it doesn't seem to be recognizing .range or .source. Am I missing a library or something (i haven't added any)
@SpencerFarrisАй бұрын
No, no library or anything extra needed
@technologyhifiАй бұрын
not working
@solidrootsflowerfarmgarden3518Ай бұрын
I'm looking for help to open a couple sheets to the current week, can I email you?
@pravingordeАй бұрын
i have updating data formula in Roster sheet which is by importrage function from other sheet which i want to shift to new copy pasted sheet as formula and then their it changes value as per importrange function from other sheet... is it possible kindly please let know.. if yes then how can be done?
@JosephSalonga2 ай бұрын
i cannot press the run button what do you think is the problem?
@SpencerFarris2 ай бұрын
Best guess is it didn't save
@forgodani2 ай бұрын
Man, this is life saving! Such a great tutorial... thanks!
@BaherSaid2 ай бұрын
Great video man, this can work for abbreviations as well which is awesome, I just have a question, which trigger should I enter if I want to do the same but instead of onedit I want it to happen also on copy and paste, because right now when I paste the value nothing changes
@SpencerFarris2 ай бұрын
Does it not even work when you paste a single value?
@JessicaGreen-yf7yc2 ай бұрын
What di I do if it isn't running automatically?
@SpencerFarris2 ай бұрын
Check the executiona
@FreshSheets2 ай бұрын
Sooo good! Would've been useful to clearly state that the row with the check box is what should be in " if (e.range.columnStart != 10 || e.value != "TRUE") return;" *modify the != value*
@SpencerFarris2 ай бұрын
Thanks for the feedback
@shane10632 ай бұрын
this was great. however when I tried it didn't work I'm assuming its cause I'm trying to replace an alpha numeric value with a word value. example I'm trying to replace "a1" with "cheerios"
@SpencerFarris2 ай бұрын
That should work fine. Paste your code?
@shane10632 ай бұрын
@@SpencerFarris function onEdit(e){ if (!e) throw "Do not run from editor, the function runs automatically whenever an edit is made" autoReplaceValues(e); } function autoReplaceValues(e){ if (e.value != "o1") return; e.range.setValue("george"); } function autoReplaceValues(e){ const values = ["o1","o2","o3","o4","o5","o6"]; const index = values.indexOf(e.value); if (index == -1) return; const replacements = ["george","dan","chris","david","james","paul"]; e.range.setValue(replacements[index]); }
@shane10632 ай бұрын
@@SpencerFarris actually hold on. i swear it wasn't working the other day. god programming is. i changed nothing now everything is fine
@SpencerFarris2 ай бұрын
@@shane1063 Hooray!
@Felixitously2 ай бұрын
Hugely helpful, had no idea it'd be such a pain to search up a solution for what I thought would be a common need, but your explanation was succinct and simple! Much appreciated (:
@vinicius2336SP2 ай бұрын
AMAZING! Thank you so much!
@mohammedfarook53762 ай бұрын
How to Hide Formulas In Google Sheets
@SpencerFarris2 ай бұрын
what?
@user-rs7tm8oi7i2 ай бұрын
@spencer thank you for this excellent walk-through and script! I have it working in my Google Sheets workbook. It's all working fine, but the script executes *really* slowly. It literally takes 4-5 minutes to copy one sheet (tab) with protections. (The sheet being copied has some fairly complex formulas and a lot of individual cell protections, but still...) Do you have any tips on how I could speed up the script execution time? Thanks so much.
@SpencerFarris2 ай бұрын
All that has to do with your computer and Internet, and the difficult of copying the sheet itself. Oftne there isn't a way to speed it up