CODEWITHSUNDEEP
for-loopgoogle-apps-scriptgoogle-sheetsconditional-formatting
Ian Propst-Campbell
Ian Propst-Campbell

Reputation: 158

Change cell color based on selection from dropdown menu

This is a script I am writing to help me figure out how to write a more complex script.

In my spreadsheet, I have one column that contains a list of values ("testRange","testRangeValues"). I also have a dropdown list ("testCell","testCellValue"). The dropdown list contains some values that are in the "testRange" list and some that are not.

I would like my script to function in such a way that when I select a value from the dropdown list that matches value from the testRange list, the background color of the corresponding value in the testRange list changes to red. 

function onEdit3(e) {

var testRange = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("A4:A8");
var testRangeValues = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("A4:A8").getValues();
var testCell = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("C4");
var testCellValue = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("C4").getValue(); 

for(var i = 0;i<testRange.length;i++){
if(testCellValue==testRangeValues[i]){
testRange[i].setBackground("Red");
}
}



}

Currently no color change is happening. I guess I'm not quite sure how to correctly write my for loop/if statement to achieve the intended effect. Is there perhaps a way to do this using just some sort of conditional formatting formula in my spreadsheet? enter image description here

Upvotes: 1

Views: 1067

Answers (1)

Tanaike
Tanaike

Reputation: 201428

  • You want to change the background color of cell in the range of "A4:A8" that the value of dropdown list of "C4" is the same.
  • You want to achieve this using Google Apps Script.
  • From your script, you don't want to use the event object.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Modification points:

  • The sheet object can be written by var sheet = SpreadsheetApp.getActiveSheet();. By this, you can use sheet for retrieving the range.
  • sheet.getRange("A4:A8") and sheet.getRange("C4") can be used one time.
  • In this case, the range object cannot be used in the loop, because testRange.length returns null.

When above points are reflected to your script, it becomes as follows.

enter image description here

Pattern 1:

In this pattern, only background color of the selected value is changed to the red color. So for example, when "A" is selected, the background color of "A" is changed to the red color. And when "C" is selected, the background color of "C" is changed to the red color. In this case, the background color of "A" is reset.

Modified script:

function onEdit3(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var testRange = sheet.getRange("A4:A8");
  var testRangeValues = testRange.getValues();
  var testCell = sheet.getRange("C4");
  var testCellValue = testCell.getValue();

  var backgroundColors = testRangeValues.map(function([a]) {return [a == testCellValue ? "Red" : ""]});
  testRange.setBackgrounds(backgroundColors);
}

Pattern 2:

In this pattern, the background color of the selected value is saved. So for example, when "A" is selected, the background color of "A" is changed to the red color. And when "C" is selected, the background color of "C" is changed to the red color. In this case, the background color of "A" is kept the red color.

Modified script:

function onEdit3(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var testRange = sheet.getRange("A4:A8");
  var testRangeValues = testRange.getValues();
  var testCell = sheet.getRange("C4");
  var testCellValue = testCell.getValue();

  for (var i = 0; i < testRangeValues.length; i++) {
    if (testRangeValues[i] == testCellValue) {
      sheet.getRange("A" + (4 + i)).setBackground("Red");
      break;
    }
  }
}

Modern javascript:

You can use modern javascript syntax. For this you need to enable V8 runtime. After this you can

/**
 *
 * @param {GoogleAppsScript.Events.SheetsOnEdit} e
 */
const onEdit = e => {
  const sheet = e.range.getSheet();
  const value = e.range.getValue();
  if (sheet.getName() == 'Sheet1' && e.range.getA1Notation() === 'C2') {
    const range = sheet.getRange(2, 1, sheet.getLastRow() - 1, 1);
    range.setBackgrounds(
      range.getValues().map(row => [row[0] === value ? 'red' : ''])
    );
  }
};

References:

If I misunderstood your question and this was not the direction you want, I apologize.

Upvotes: 5

Related Questions