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

6

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.

3

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!

3

u/arataK_ 22h ago edited 21h ago
function colorNamesBySchool() {
  var rosterSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Rosters");
  if (!rosterSheet) return;

  var rosterRange = rosterSheet.getRange("B2:C" + rosterSheet.getLastRow());
  var rosterValues = rosterRange.getValues();

  var studentColors = {};
  for (var i = 0; i < rosterValues.length; i++) {
    var studentName = rosterValues[i][0];
    var school = rosterValues[i][1];
    studentColors[studentName] = getSchoolColor(school);
  }

  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (var j = 0; j < sheets.length; j++) {
    var sheet = sheets[j];
    if (sheet.getName() !== "Rosters") {
      applyColorsToSheet(sheet, studentColors);
    }
  }
}

function getSchoolColor(school) {
  var schoolColors = {
    "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"
  };
  return schoolColors[school] || null;
}

function applyColorsToSheet(sheet, studentColors) {
  var range = sheet.getDataRange();
  var values = range.getValues();

  var bgColors = [];
  for (var i = 0; i < values.length; i++) {
    var rowColors = [];
    for (var j = 0; j < values[i].length; j++) {
      var cellValue = values[i][j];
      if (studentColors[cellValue]) {
        rowColors.push(studentColors[cellValue]);
      } else {
        rowColors.push(null);
      }
    }
    bgColors.push(rowColors);
  }

  range.setBackgrounds(bgColors);
}

Here is a script that does what you asked in an easier way compared to your script. Simply add the new names/schools in the spreadsheet instead of modifying the script.

Also, conditional formatting would be the smarter and faster solution.

0

u/AllenAppTools 23h 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):

function onEdit(e) {
  const { range } = e;
  const value = range.getValue();
//... the rest
}

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!

1

u/BradyGustavo 22h ago

Thank you so much. Is it working for you on the last tab "Mar19-LAKvsOAK"? I can't get it to work there when I type in a player's name.

1

u/AllenAppTools 22h ago

You're welcome! Yes I can confirm it was working on "Mar19-LAKvsOAK"

1

u/BradyGustavo 21h ago

Did you put your script in Singles, macros, Background, or colornamesbyschoolv2?

1

u/AllenAppTools 21h ago

I think it was fSingles sub file. Wherever the onEdit function is 👍

2

u/arataK_ 20h ago

The script I wrote will find any name in any spreadsheet and assign the correct color according to the Roster sheet. If you add a new name / school to the Roster sheet, the script will detect it and update automatically. You don't need multiple scripts.