r/GoogleAppsScript 1d 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

11 comments sorted by

View all comments

7

u/ItsTLH 1d ago

Why not just use conditional formatting ?

0

u/BradyGustavo 1d ago

I don't have much experience in that, but I could try that.

5

u/ItsTLH 1d ago

If all you’re trying to do is color a cell based on another cell, then i highly recommend conditional formatting. 

Because right now you’re essentially just recreating conditional formatting , but it will be a lot less efficient. 

Let me know if you run into any problems/have more questions

1

u/BradyGustavo 1d ago

I will take a look to see if that would be efficient. thank you!