Find start and end cells of colored range in Google Sheets row

Other Questions Google Sheets
1

I’m working on a Gantt-like spreadsheet in Google Sheets. My setup has colored cells in each row to represent task durations. I need help creating two custom functions to determine the first and last colored cells in a given row.

Here’s what I want to achieve:

  • Function 1: Find the column letter of the first colored cell
  • Function 2: Find the column letter of the last colored cell

These functions should take two inputs:

  1. The range of cells to check (e.g., ‘D2:I2’)
  2. The row number (e.g., 2)

The output should be used to populate columns B and C respectively.

I’m not sure how to approach this problem. Any suggestions on how to write these custom functions would be greatly appreciated. Thanks!

2

hey tom, i’ve dealt with similar stuff before. you’ll need to use Apps Script for this. create a custom function that loops through the range, checks each cell’s background color, and returns the first/last non-empty column. might be tricky to implement but totally doable. lemme know if u want more specifics!

3

I’ve implemented a similar solution in my projects. Here’s a high-level approach:

Use Apps Script to create two custom functions: one for the first colored cell, another for the last.

In each function, utilize getBackgrounds() to retrieve the color array for the specified range. Iterate through this array to identify the first/last non-white cell.

Convert the found index to a column letter. You’ll need a helper function for this conversion.

Implement these as custom functions in your sheet. You can then call them like =findFirstColored(D2:I2, 2) in cells B2 and C2.

This method is efficient and scalable. If you need more specific implementation details, feel free to ask.

4

I’ve actually implemented something similar in my work projects. Here’s what worked for me:

Create two separate functions in Apps Script, let’s call them findFirstColored and findLastColored. Each function should take the range and row number as parameters.

Inside the functions, use getBackgrounds() to get an array of background colors for the range. Then, loop through this array to find the first/last non-white cell (assuming white is your default).

Convert the index to a column letter using columnToLetter(index). This’ll require a helper function to convert numbers to letters.

Remember to set these as custom functions in your sheet. You can then use them like =findFirstColored(D2:I2, 2) in cells B2 and C2.

It’s a bit complex, but once set up, it’ll save you tons of time. Let me know if you need more details on implementation!