Superb tutorial! What about comparing 3 sheets for duplicates? is it possible to use this formula?

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🤘🏻

Neat tutorial!

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

Could you help me I'm having problems? I could send you over my test spreadsheet.

Is there a way to lock drop downs with this?

Low sound ;(

Yesss = best!

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"); }

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!

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

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

You're fire, bro

THANK YOU!!!!!!!! This is exactly what I needed. What if you want to duplicate a whole notebook with protections?

Lets say I want all of column j3-j25 what would the code be

You are AMAZING! I have been copying/moving/deleting for years. This saves SOOOOOOO much time. Thank you very much!

My goodness, I've been looking for this for so long. Thank you so much!

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!

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?

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?

Love from India (Bharat)💐👍

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.

thank you!

Thanks for the Video Brother 😊

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!

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]); }

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.

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 &&?

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

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.

I used your copy sheet, however I prefer to protect the SHEET and add exceptions. How do I modify this script for SHEET?

You make it sound so easy. Is there an easier way 2 years later?

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?

Hello! Thank you for the helpful video! Is there a way to open to a range when you open a specific tab?

Wow! You're a life saver, mate. Thanks! Just subscribed!

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.

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)

not working

I'm looking for help to open a couple sheets to the current week, can I email you?

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?

i cannot press the run button what do you think is the problem?

Man, this is life saving! Such a great tutorial... thanks!

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

What di I do if it isn't running automatically?

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*

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"

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 (:

AMAZING! Thank you so much!

How to Hide Formulas In Google Sheets

@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.