How to sum specific cells filled with color?

Question

Google Sheets

There is a line of 30 (number of days per month) cells, each indicating the number of hours worked per employee per day. Sometimes you need to pay, randomly, some shifts. For example: 5th, 13th and 27th. In order to understand the balance at the end of the month, you need to subtract the already paid shifts from the total amount of hours (5th, 13th and 27th). Now it has to be calculated manually for each person. I would like the entire line to be analyzed for a match, and if there are yellow (for example) cells, then their values ​​​​are summed up and their sum is subtracted from the total amount of hours for the month.

I reviewed the solutions with options for creating new scripts, but they all give an error and there are problems with updating. Maybe I chose the wrong direction and can I mark differently paid cells (dates)?

Answer 1

Maybe something like this. To specify normal ranges, you can parse the function arguments, but this is more reliable. You can do anything with the received data. If you need an amount - wrap it in SUM ()

function filterByColor(dataRangeName, colorRangeName) {
  let ss = SpreadsheetApp.getActiveSpreadsheet();

  let colorRange = ss.getRangeByName(colorRangeName);
  let color = colorRange.getBackground();

  let dataRange = ss.getRangeByName(dataRangeName);
  let data = dataRange.getValues();
  let colors = dataRange.getBackgrounds();

  return data.map((row,ri)=>row.map((val,ci)=>color===colors[ri][ci]?val:""));
}

Demo table