CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Patrick
Patrick

Reputation: 85

How do I search Column O for a value listed on another sheet and change row color?

I have a spreadsheet where I want to search row by row and compare the value in column O to one on my "Menu" page. If the value in column O is less than the value of Menu, turn the whole row red.

This is what I have so far but it's not working.

    function checkDate(sheet){
  menu = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Menu");
  var rows = sheet.getDataRange().getValues();
  for(var i=0; i < rows.length; i++){
    if(rows[i][14] > menu.getRange("B5").getValue()){
      var changeRange = sheet.getRange(1,i,1,sheet.getLastColumn());
      changeRange.setBackgroundRGB(255, 0, 0);
    }
  }
}

Upvotes: 0

Views: 37

Answers (2)

Cooper
Cooper

Reputation: 64082

If Column 15 is a date then try it this way

function checkDate(sheetName) {
  const ss = SpreadsheetApp.getUi();
  const menu = ss.getSheetByName("menu");
  const b5 = new Date(menu.getRange("B5").getValue()).valueOf();
  const sh = ss.getSheetByName(sheetName);
  const sr = 2;//data start row
  var rows = sh.getRange(sr, 14, sh.getLastRow() - sr + 1).getValues();
  rows.forEach((r,i) => {
    if(new Date(r[14]).valueOf() > b5) {
      sh.getRange(i + 1, 1, 1, sh.getLastColumn()).setBackground("#ff0000");
    }
  });
}

Upvotes: 1

Spectral Instance
Spectral Instance

Reputation: 2494

This appears to work...

 function checkDate(sheetName){
      menu = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("menu");
      sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
      var rows = sheet.getDataRange().getValues();
      console.log(sheet.getName());
      for(var i=0; i < rows.length; i++){
       if(rows[i][14] > menu.getRange("B5").getValue()){
         sheet.getRange(i+1,1,1,sheet.getLastColumn()).setBackgroundRGB(255, 0, 0);
       }
      }
    }

Upvotes: 1

Related Questions