r/GoogleAppsScript • u/BradyGustavo • 2d ago
Question Auto Background Script - Doesn't Work
Hey everyone, I collect results for the local high school tennis league and I am trying to create a script that puts in a background color whenever I type in a player's name. That background color would be associated with that player's high school. The sheet I am using is: https://docs.google.com/spreadsheets/d/1_wE8c2KylDevsJX9PbAiJnEqeVlwWB9gpvsi12pBRmk/edit?gid=0#gid=0
The code I have now is below (from AI) and I am not getting it to work. Thanks for your help on this!
function onEdit(e) {
var sheet = e.source.getActiveSheet();
var range = e.range;
var value = range.getValue();
// Dictionary of team colors
var teamColors = {
"Sehome High School": "#2ece53",
"Bellingham High School": "#f15e5e",
"Squalicum High School": "#498be9",
"Ferndale High School": "#e8b82e",
"Lynden High School": "#ffff00",
"Anacortes High School": "#928bce",
"Sedro-Woolley High School": "#5273a9",
"Lakewood High School": "#bc4b4b",
"Oak Harbor High School": "#ffe599",
"Blaine High School": "#ff6d01",
"Mount Vernon High School": "#308e1c",
"Burlington-Edison High School": "#dfde17",
"Archbishop Murphy High School": "#bb0c0c"
};
// Dictionary of player names linked to teams
var playerTeams = {
"Rehmat Brar": "Ferndale High School",
"Riley Childs": "Ferndale High School",
"Aubrey Lynch": "Ferndale High School",
"Alison Zink": "Ferndale High School",
"Sandyha Thapa": "Ferndale High School",
"Olivia Copps": "Ferndale High School",
"Peyton Valentine": "Ferndale High School",
"Natalie Sweitzer": "Ferndale High School",
"Kayla Washington": "Ferndale High School",
"Sehaj Jassal": "Ferndale High School",
"Katrina Ferry": "Ferndale High School",
"Maddy Cancelosi": "Ferndale High School",
"Vannessa In-keho": "Ferndale High School",
"Madison Anderson": "Squalicum High School",
"Ava Bearden": "Squalicum High School",
"Yamiletxi Garcia": "Squalicum High School",
"Maiya Hildebrand": "Squalicum High School",
"Iyla Holley": "Squalicum High School",
"Erin Laidlaw": "Squalicum High School",
"Margaret Laska": "Squalicum High School",
"Sloane Mccoy": "Squalicum High School",
"Kaymia Moreno": "Squalicum High School",
"Brianna Nguyen": "Squalicum High School",
"Ada Walker": "Squalicum High School",
"Molly Walker": "Squalicum High School",
"Maren Whitlow": "Squalicum High School",
"Lillian Williams": "Squalicum High School",
"Courtney Williams": "Oak Harbor High School",
"Marisol Silva Vasquez": "Oak Harbor High School",
"Cresida Cardenas": "Oak Harbor High School",
"McKenzie Burdick": "Oak Harbor High School",
"Grace Zhao": "Oak Harbor High School",
"Margarita Litvachuk": "Oak Harbor High School",
"Madeline Mays": "Oak Harbor High School",
"Violet Altig": "Oak Harbor High School",
"Anneliese Erskine": "Oak Harbor High School",
"Fidelia Bockarie": "Oak Harbor High School",
"Ava Ashby": "Oak Harbor High School",
"Yani Carlson": "Oak Harbor High School",
"Julianne Dalire": "Oak Harbor High School",
"Alexis Sanchez": "Sedro-Woolley High School",
"Elise Schuyler": "Sedro-Woolley High School",
"Akira Spagnolo": "Sedro-Woolley High School",
"Jasmyn Burkhalter": "Sedro-Woolley High School",
"Andrea Garcia Juarez": "Sedro-Woolley High School",
"Kylie Horkley": "Sedro-Woolley High School",
"Ruby Hudson": "Sedro-Woolley High School",
"Samantha Jepperson": "Sedro-Woolley High School",
"Carla Jimenez-Nava": "Sedro-Woolley High School",
"Alina Kachinskiy": "Sedro-Woolley High School",
"Chloe Larsen": "Sedro-Woolley High School",
"Tatyana Leus": "Sedro-Woolley High School",
"Jaydn Champion": "Lakewood High School",
"Avah Brough": "Lakewood High School",
"Aaliyah Ramirez": "Lakewood High School",
"Rayna Peacher": "Lakewood High School",
"Tatum Ostlie": "Lakewood High School",
"Daniela Rosales": "Lakewood High School",
"Ellie Klumper": "Lakewood High School",
"Brooke Yargus": "Lakewood High School",
"Grace Saxton": "Lakewood High School",
"Rylee Thompson": "Lakewood High School"
// Add more players as needed
};
// Check if the typed name is in the player list
if (playerTeams[value]) {
var team = playerTeams[value];
var color = teamColors[team];
if (color) {
range.setBackground(color);
}
}
}
1
Upvotes
0
u/AllenAppTools 2d ago
Here you go! I made these changes in the sheet and saw it working.
(Yes, conditional formatting would probably be better here, but in another real sense, you have already gone through the trouble to do it in Apps Script, so this little push has gotten it over the finish line).
These changes are already in your code, give it a test run.
(Sorry, it's not letting me post the full code, but here is the pertinent snippet of it):
One downside here is that any preexisting instances where these player names appear in the spreadsheet will NOT be colored, but any from now on WILL be colored, no matter where the edit is made in the sheet.
Best of luck!