2

I've been searching for a way to change a cell value (e.g. "Text" if cell color is red) based on another cell color?

Is there a way to do this?

I know there's a way to change a cell color based on cell value, but I want the opposite way,

anyone have idea? be it a script or formula

user7254740
  • 103
  • 1
  • 5
  • 17

2 Answers2

6

There is something like this in Google appscript, not sure if any direct formula is also available in spreadsheet, but here it is:

I colored A1 cell of Sheet1 as Red i.e. #ff0000 and then got the color using the following code:

function test()
{
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var color = sheet.getRange(1, 1).getBackground();
  Logger.log(color);
}

Output

#ff0000

So, you just have to check if(color == "#ff0000") (or code of any color you want) and then set values.

EDIT

Here is the code that will fulfill your requirements. I have also added the comments so that you can develope it further. 

function myFunction() {
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");

  var data1 = sheet1.getDataRange().getValues();  //2D array of sheet1 cell values
  var bg;

  for(var i=1; i<data1.length; i++)
  {
    bg = sheet1.getRange(i+1, 2).getBackground();
    if(bg == "#ff0000")  //Add more colors in else if and else if you have 5-6 different colors, this one is for red
    {
      sheet2.getRange(i+1, 3).setValue("For Verification");  //Set value in corresponding row of sheet2
    }    
  }
}


/**
* For to-be filled records in future, you can 
* set a trigger of onEdit if you are manually 
* filling sheet 1
**/
Shyam Kansagra
  • 892
  • 12
  • 24
  • thank you sir for the code, may I ask how can I test the script output? like I can put the function into cell to determine the color? – user7254740 Jan 10 '17 at 06:57
  • First of all, no "sir" dude! call me your buddy ;) . Now, this code is already applied to A1 cell and you can also perform the operations you want by writing the code in the same function `test()` itself. You can run your code from script editor or also can set time-based triggers. What exactly are you looking for? Describe your process. – Shyam Kansagra Jan 10 '17 at 07:03
  • You can refer this for sheet operations: [link](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app) – Shyam Kansagra Jan 10 '17 at 07:11
  • okay bud, this is my process, I set-up a conditional formatting from another sheet (sheet1), each formatting has different color and each color has a value, so on sheet2, using the color from sheet1 I want to display its value based on that color, thanks – user7254740 Jan 11 '17 at 06:30
  • checking on the view->logs it displays the output hexcode of color, but I tried to put it on a cell =test() but no luck, also =if(color =="") does not work? "color not recognized" :( – user7254740 Jan 11 '17 at 06:32
  • Using `=test()` in your spreadsheet cell has limitations like you can only refer that particular sheet and cannot play with other sheets. So, do not use `=test()` instead share the copy of your sheets and I will tell you how to automate the whole process. Also, `=if()` will not work as the function name itself is test() and `if()` is a whole different function. – Shyam Kansagra Jan 11 '17 at 06:49
  • [link](https://docs.google.com/spreadsheets/d/1fgSVaLIG6jFwBKCZwyqU8cLJLsSYt6EsrTHFbmQnnK8/edit?usp=sharing) this the link to my sample worksheet, – user7254740 Jan 12 '17 at 02:29
  • on sheet1 Column Remarks, if the color is Red, is should display the value "For Verification" something like that, :) – user7254740 Jan 12 '17 at 02:30
  • Check the edit section in my answer above, also I have inserted and run the same code in the sheet that you have shared. – Shyam Kansagra Jan 12 '17 at 10:24
0

I did this script to update some colors, but it may give you an Idea on how to do it.

Keep in mind that in Google Sheets, the x,y matrix is always x=row and then y=column. So when you update values you must think first value of the pair is the row and then column, in order to avoid mistakes.

Also, getRange is 1 based and arrays are 0 based.

Finally, getBackground operation is quite expensive because it needs to fetch data from the sheet for each time you call it, if you will run a script over several cells, then it is better to use getBackgrounds() in order to get the matrix of all backgrounds in the range.

/** @OnlyCurrentDoc */
function calculateValues() {
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("origin")
  var data = sheet1.getDataRange();
  var values = data.getValues();
  var bg = data.getBackgrounds()

  Logger.log("Rows: " + values.length)
  Logger.log("Columns: " + values[0].length)

  for (var y = 0; y < values.length; y++) {

  Logger.log("Row: " + (y+1) + " with length " + values[y].length)

    for (var x = 0; x  < values[y].length; x++) {
      var i = x+1
      var j = y+1

      Logger.log("row, column: " + j + "," + i + " bg: " + bg[y][x])

      if(bg[y][x] == "#34a853") {  // green
        sheet1.getRange(j,i).setValue(2)

      } else if (bg[y][x] == "#fbbc04") {  //yellow
        sheet1.getRange(j,i).setValue(1)

      } else if (bg[y][x] == "#d9d9d9") { //gray
        sheet1.getRange(j,i).setValue(0)

      } else if (bg[y][x] == "#6aa84f" ) { // green 2 
        sheet1.getRange(j,i).setBackground("#34a853")
        sheet1.getRange(j,i).setValue(2)

      } else if (bg[y][x] == "#f1c232"  ) { // yellow 2 
        sheet1.getRange(j,i).setBackground("#fbbc04")
        sheet1.getRange(j,i).setValue(1)
      
      } else if (bg[y][x] == "#b7b7b7") { // gray 2 
        sheet1.getRange(j,i).setBackground("#d9d9d9")
        sheet1.getRange(j,i).setValue(0)
      }
 
    }

  }

}
htafoya
  • 18,261
  • 11
  • 80
  • 104