Automatically Replace Value When Entered

[@MichaelCorbettUK]

A great video! I really like the way that you think out loud and go through your thought processes with the viewer.

[@SpencerFarris]

Thanks! I figured seeing the human side can be better than just a pre-made perfect script. I actually don't pre-write any of the scripts I video

[@markonakic8870]

Hi Spencer, very well explained and detailed instructions, thank you very much! Google sheets don't have option to input special characters, I was wondering about a script for find and replace. My idea is to have "gl_a" as abbreviation for alfa, which is CHAR(945), but have no idea how to make that work, any help is highly appreciated!

[@junesqueek]

it worked, thank you! if i only wanted it to work within one sheet though, how would i do that? i have sheet1 and sheet2 but i only want the script to apply to sheet1 and not sheet2.

[@lapneto]

Very cool. Thanks for sharing!

[@BaherSaid]

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

[@SpencerFarris]

Does it not even work when you paste a single value?

[@MichaelDaniels]

Nice video. Thanks

[@ward90xc]

@SpencerFarris Love this! Would it be possible to have a protected sheet, where the script did not run? For example, I have an appeal order form. The data is being entered via a Google Form and I don't want to touch the raw data. I want to create a summary sheet, where the product description is converted to a SKU. So "T-Shirt - Large" would be converted to "TS-LG-0001" automatically.

[@Caleb_3D]

Thank you so much for making this video! This function is exactly what I need for a school project. Unfortunately, the code doesn't seem to be working for me. I opened a new sheet, opened the apps script, pasted the code from shane0163's comment, went back to the original sheet, typed "o1," and it didn't replace it. This was after trying to follow the tutorial and experiencing the same issue after the test at the 3 minute mark, where it just didn't replace it. Do you have any idea what's going on?

[@SpencerFarris]

can you share the spreadsheet with me? [e-mail]

[@Caleb_3D]

@@SpencerFarris Unfortunately, I no longer have access to the blank sheet that I tested it in, and when I tried shane's code in a new blank sheet, it worked as expected, but my alternate solution does work (I just have to type the prices in manually instead of the names). I will be saving this video for if I ever need it, so thank you for making it!

[@SpencerFarris]

@@Caleb_3D Glad you got it sorted :)

[@shane1063]

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"

[@SpencerFarris]

That should work fine. Paste your code?

[@shane1063]

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

[@shane1063]

@@SpencerFarris actually hold on. i swear it wasn't working the other day. god programming is. i changed nothing now everything is fine

[@SpencerFarris]

@@shane1063 Hooray!

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

[@brandonpeterson8154]

Thank you for this. is there a way to do the same thing with data that is auto filled from a google form?

[@SpencerFarris]

You could use an On Form Submit trigger

[@THDYoung]

nice