Changing Color of cell in google sheets

I would really appreciate if someone could help me.

I want to change cell color based on cell value. I can do this with conditional formatting but the orginal file is to big to do it and I am afraid that too many conditional formatting conditions will slow down the spreadsheet…

I created sample spreadsheet to show what I want to achieve.
Sample SpreadSheet
ConditionalFormating sheet

There is a list of products and theirs inventory status on the left. Above you have order list and which product it will need. I want to change color of the cell to any color except white to be able to see that I am out of stock for this order.

For example:
=SUM(D5:F5)>B5 => change color to red

I started to write a code but the I realized that with my approach I would have to write it for every row and then I got stuck…

    function myFunction() {
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ConditionalFormating");
  var range1 = sheet1.getRange(10,7,1,sheet1.getLastColumn()).getValues();
  var inv1 = sheet1.getRange(10,3);

  for(var i = 0; i < range1.length; i++){
    if(range1[i] > inv1){
     sheet1.getRange(10,i).setBackground(`red`);
    }
  }

}

1 thought on “Changing Color of cell in google sheets”

  1. I believe your goal as follows.

    • In your sheet "ConditionalFormating", for example, the cell "D4" is the total number from the cells "D5:D7". Cells "D5", "D6" and "D7" are the product "A", "B" and "C", respectively.
      • For example, at the row 6, "order 1" and "order 2" are 3 and 1, respectively. In this case, the total orders are 4 and the inventory is 6. By this, the background color is white. And, at 3 of "order 3", the total orders are 7 and this is over the inventory of 6. By this, the background color is red.
    • But in your actual situation, the start cells of "Product" and "Inventory" are "B10" and "C10". And, the start cell of "order" is "G10". In your actual situation, you have around 150 Columns and around 350 rows.

    In this case, I would like to propose the following sample script.

    Sample script:

    Please copy and paste the following script to the script editor of your sample Spreadsheet. And, please set the sheet name, and run the script. By this, the background colors of cells starting from "G10" are modified. When your sample "ConditionalFormating1" sheet is used, "G10" is white and the cells "H10" and "G11:H14" are red.

    function myFunction() {
      const sheetName = "###";  // Please set the sheet name.
    
      // 1. Retrieve values from sheet.
      const ss = SpreadsheetApp.getActiveSpreadsheet();
      const sheet = ss.getSheetByName(sheetName);
      const values = sheet.getRange(10, 1, sheet.getLastRow() - 9, sheet.getLastColumn()).getValues();
      
      // 2. Create an array for modifying the background colors.
      const backgroundColors = values.map(([,,c,,,,...g]) =>
        g.reduce((o, e) => {
          o.total += e;
          o.colors.push(c >= o.total ? null : "red");
          return o;
        }, {colors: [], total: 0}).colors
      );
      
      // 3. Modify the background colors of cells.
      sheet.getRange(10, 7, backgroundColors.length, backgroundColors[0].length).setBackgrounds(backgroundColors);
    }
    

    Note:

    • This sample script is for the sheet of ConditionalFormating1 in your 2nd sample Spreadsheet. So when the structure of sheet is changed, the script is required to be modified. Please be careful this.

    References:

    Reply

Leave a Comment