Changing Color of cell in google sheets

Question

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`);
    }
  }

}

I saw your sample Spreadsheet. But unfortunately, I cannot understand about your goal from your 4 sheets. And I cannot understand about 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.. And I cannot find "COMPONENTS" sheet in your sample Spreadsheet. I apologize for this. where it the sample sheet of your sample input? And, can you provide the sample output situation you expect? — Tanaike
@Tanaike sample spreadsheet is a sample output. Check the ConditionalFormating sheet. Also please check the conditional formatting for each red cell, then I am sure you will understand what my goal is. As you can see there I do not have enough of product A to make orders 2,3,4 — Timonek
Thank you for replying. I have to apologize for my poor understanding. Unfortunately, I cannot understand about your current issue and your goal from the sheet "ConditionalFormating". And also, I cannot understand about As you can see there I do not have enough of product A to make orders 2,3,4 . I deeply apologize for my poor English skill. Can I ask you about the detail of them? — Tanaike
Thank you for replying. 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. Is my understanding correct? If my understanding is correct, 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. Is my understanding correct? — Tanaike
@Tanaike Yes, that's correct! But please keep in mind that original data has a lot of more rows and columns — Timonek

Tanaike Tanaike · Accepted Answer · 2021-02-19T07:58:11

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) => {
      if (e.toString() != "") {
        o.total += e;
        o.colors.push(c >= o.total ? null : "red");
      } else {
        o.colors.push(null);
      }
      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.

Changing Color of cell in google sheets

1 Answers

Sample script:

Note:

References: