Change colour of cell based on multiple conditions

Question

I want to take a range of cells which are like this:

Dashboard

If the date matches the date in a column in another sheet and the role matches the role in another column on that sheet, which looks like this:

Matching Sheet

I want to take the data in Cell C3 (for example) and put it in the relevant date and role cell where it has matched and turn that cell red, like this:

Final Result

I have tried to code this in Apps Script but am struggling, I have a little experience in VBA but not much at all in GAS.

My code so far looks like this:

function onEdit(e) {
  var dash = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Dashboard');
  var response = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form responses 1');
  var dashcells = dash.getRange("B3:NB7");
  var dashdates = dash.getRange("B2:NB2");
  var dashroles = dash.getRange("A3:A7");
  var numRows = dashcells.getNumRows();
  var numCols = dashcells.getNumColumns();
  var dates = response.getRange("V2:V").getValues();
  var roles = response.getRange("J2:J").getValues();
  var SOF = response.getRange("U2:U").getValues();
  var colorToRed = [];
  var colorToGreen = [];
  var colorToYellow = [];

 
  
  for (var i = 0; i < numRows; i++) {
    for (var j = 0; j < numCols; j++) {
      if dashdates[0][j] = dates.find(dashdates[0][j]).values {
        if dashroles[i][0] = roles.find(dashroles[i][0]).values {
          if SOF[i] <> "" {
            colorToRed.push("R"+ (i+1) + "C" + (j+1))
          }
        }
      }

    }
  }

  sheet.getRangeList(colorToRed).setBackground("#FFDD88"); 
}

I don't think I'm on the right track.

Any help would be appreciated.

Answer 1

Sheets.getRangeList parameter should be an array of A1 notations not R1C1 notations. You might use columnToLetter function from AdamL (see his answer to Convert column index into corresponding column letter)