Trying to conditional format Google sheet that uses a Google form for data entry

Question

I have moved over to using google forms for data entry to try and improve data integrity. However, the conditional formatting is thrown off after ever new entry is added onto the bottom of the sheet. It means constantly having to change the formatting range manually, which isnt a lot of work but a bit frustrating when you want to leave the sheet running by itself.

I am new to apps script (and coding in general!) but have been trying to piece together something that might work. I want the cells in column A (A4:A) to change colour depending on the value of the cell. So far I have the below - which works for Row 4 but none of the other rows. Im struggling to get the code to recognise that there is more data below the first row.

The error I'm getting says:

"The number of rows in the data does not match the number of rows in the range. The data has 1 but the range has 196."

function conditionalformatting(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Warehouse Queries');
  var range = sheet.getRange(4,1,sheet.getLastRow(),1);
  var cellvalues= range.getValues(); 
  var colors = [];

     if (cellvalues = 'Warehouse Admin to Action') {
      colors.push(["#6db05f"]);} 
      else if (cellvalues = 'Warehouse Coordinator to Action') {
      colors.push(["#f0c784"]);} 
      else if (cellvalues = 'POD required - Supply Chain to source') {
      colors.push(["#84bef0"]);} 
      else {colors.push([""]);}

      range.setBackgrounds(colors);
      }

Thanks!

Answer 1

Modification points:

• I think that in your script, colors.push(["#6db05f"]) is always run. Because cellvalues = 'Warehouse Admin to Action' is used as true. And, cellvalues = 'Warehouse Admin to Action' means that a text of 'Warehouse Admin to Action' is substituted to a varible cellvalues. If you want to compare the value, please use == or === instead of =.
• And, I think that in your script, colors is always ["#6db05f"].
• var range = sheet.getRange(4,1,sheet.getLastRow(),1); might be var range = sheet.getRange(4, 1, sheet.getLastRow() - 3, 1);. And, I think that var range = sheet.getRange("A4:A" + sheet.getLastRow()); might be able to be also used.
• If you want to compare each cell value with 'Warehouse Admin to Action', 'Warehouse Coordinator to Action', 'POD required - Supply Chain to source', it is required to use a loop.

When these points are reflected in your script, how about the following modification?

Modified script:

function conditionalformatting(e) {
  var obj = {
    'Warehouse Admin to Action': "#6db05f",
    'Warehouse Coordinator to Action': "#f0c784",
    'POD required - Supply Chain to source': "#84bef0",
  };
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Warehouse Queries');
  var range = sheet.getRange("A4:A" + sheet.getLastRow());
  var cellvalues = range.getValues();
  var colors = cellvalues.map(([a]) => [obj[a] || null]);
  range.setBackgrounds(colors);
}

• When this script is run, the cell values are retrieved from "A4:A", and the background colors are set using obj. And, the background color of "A4:A" is changed.

• In order to search the values and set the color code, I used obj.

Reference:

• map()