How to make [Conditional Formatting] script?

Question

I want,

If Sheet1 ColumnB = Sheet89 ColumnA

Then matched Sheet1 Column B cells will be green Here is my demo sheet.

Based on some guideline I made this but not working.

function formatting() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var columnB = sheet.getRange(1, 2, sheet.getLastRow()-1, 1);
  var bValues = columnB.getValues();
  var sheet89 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet89');
  var columnO = sheet89.getRange(1, 1, sheet.getLastRow()-1, 1);
  var oValues = columnO.getValues();

        for (var h = 0; h < bValues.length; h++) {
           for (var i = 0; i < oValues.length; i++) {
              if (oValues[i][0] == bValues[h][0]) {
                 sheet.getRange(i + 2, 1, 1, 1).setBackgroundColor('green');
              }
           }
        }
}

The new google sheets allow you to do this without gas, using conditional formatting with ranges — Zig Mandel, Jan 03 '14 at 01:53
@ZigMandel is it possible to use conditional formatting across sheets? — Chris, Jan 03 '14 at 05:31
see here for an example on how to compare against values in other cells: http://stackoverflow.com/questions/20545961/google-spreadsheet-conditional-formatting-based-on-another-cell-value/20547577#20547577 — Zig Mandel, Jan 03 '14 at 06:02
@Zig Mandel: New sheet does not allow VLOOKUP formula. What I'm expecting to get. Its allow some basic function only. — Zils, Jan 03 '14 at 08:42

score 0 · Answer 1 · answered Mar 22 '14 at 06:05

This solution below will iterate through each cell with a value in column B of sheet1 and check it against every value in Column A of sheet89 (although you named this ColumnO, according to your getValues function, it will grab values from Column A).

If it finds a match, it will turn green the cell in column B of sheet1. In your example code you use the i loop variable (which iterates through rows on sheet89) to get the cell on sheet1 to turn green. It's not clear which cells you want to turn green. I assumed it was the cells on sheet1 so I changed the the code to

sheet.getRange(h+1, 2).setBackgroundColor('green');

Also, the getRange function for a single cell only requires 2 arguments, so I removed the numRows and numColumns arguments for the line which colors the cell green.

I'm not sure why bValues and oValues exclude the last row, but I removed the -1 in each of these as it will cause the code to fail if for any reason it is run on a blank worksheet. The getLastRow() returns the last row with a value, not the next blank row in the sheet. If you want to capture the whole sheet, then you shouldn't use -1.

function formatting() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');

  var columnB = sheet.getRange(1, 2, sheet.getLastRow(), 1);
  var bValues = columnB.getValues();

  var sheet89 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet89');
  var columnO = sheet89.getRange(1, 1, sheet.getLastRow(), 1);
  var oValues = columnO.getValues();

  for (var h = 0; h < bValues.length; h++) {
     for (var i = 0; i < oValues.length; i++) {

       if (oValues[i][0] == bValues[h][0]) {
         sheet.getRange(h + 1, 2).setBackgroundColor('green');
       }  
     }
  }
}

score 0 · Answer 2 · answered May 26 '17 at 20:00

What I understand to be required (and not what the example sheet shows at present) is possible with Conditional formatting.

In Google Spreadsheets conditional formatting across sheets is not nearly as straightforward as within a single sheet, because of security and therefore authorisation. You may, for speed for example, prefer to copy the content of Sheet89 into Sheet1 (just two cells) to avoid that issue, or indeed write a script instead. At least keep the ranges as small as practical.

However it is possible, though may be slow and require authorisation.

Please clear any conditional formatting from Sheet1 ColumnA then:

Select ColumnA in Sheet1, Format, Conditional formatting..., Format cells if... Custom formula is and

=countif(IMPORTRANGE(" k e y ","Sheet89!A:A"),A1)<>0

with highlighting of your choice and Done.

k e y above represents the unique identification code for Sheet89 (will look something like 1u4vq8vDne-aKMVdJQPREGOxx7n99FqIb_kuJ_bG-PzM).

The image shows at present what is in ColumnC of the image (but is in ColumnA of the example) and F1 and F2 in the image show what is in ColumnA of Sheet89 of the example. The paler brown has been applied with Conditional formatting::

How to make [Conditional Formatting] script?

2 Answers2