Cyber Cafeteria

How to Add a Status Flag Column Based on Row Colors in Google Sheets
If macros or scripts aren’t an option, here’s a simple way to create a status flag column based on existing row highlights in Google Sheets.

1. Add a new column “Color Flag”
2. Click “Extensions” > Add script
3. Add following script

function flagByRealHighlightColor() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const startRow = 5;
  const lastRow = sheet.getLastRow();
  const checkCol = 1;  // Column A
  const flagCol = 14;  // Column N

  const colors = sheet.getRange(startRow, checkCol, lastRow - startRow + 1, 1).getBackgrounds();

  for (let i = 0; i < colors.length; i++) {
    const bg = colors[i][0].toLowerCase();

    // Replace with your actual colors if different
    if (bg === '#ffff00' || bg === '#b7e1cd') {
      sheet.getRange(startRow + i, flagCol).setValue(1);
    } else {
      sheet.getRange(startRow + i, flagCol).setValue(0);
    }
  }

  sheet.getRange(4, flagCol).setValue("Color Flag");
}

4. Click “Run”

Note: You may need to allow permission as follows if there is a case. Otherwise, you can skip it.

How to proceed safely:

    On the warning screen, scroll down and click “Advanced”.

    Then click:
    “Go to [your script name] (unsafe)”

    Click Allow to grant permission.

Once done, the script will run and can:

    Read background colors

    Write values to your sheet

You won’t be asked again unless the script changes significantly or you revoke its access.

Add on report table as follows:

Total Inquiry Formula

=COUNTIFS(C5:C, “Phone”, N5:N, “<>2”)

Conversion Formula

=COUNTIFS(C5:C, “Phone”, N5:N, 0)

Rate(%) Formula

=IF(Q5=0, 0, ROUND(R5/Q5*100, 2))