Google Apps Script conditional loop

Question

I am trying to loop over one column in my sheet and set the value an adjacent cell dependent upon the value and color of the active cell. this is what I've tried but I cannot seem to get working...

function setValue(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var end = SpreadsheetApp.getActiveSheet().getLastRow();

  for( var i = 1; i < end + 1; ++i){
    var value = sheet.getRange(i, 4).getValue();
    var color = sheet.getRange(i, 4).getColor();
    if (value == "Authenticated" && color == "#ffffff") {
      sheet.getRange(i, 5).setValue("True");
      }
     else {
       sheet.getRange(i, 5).setValue("False");
       }
  }
}

UPDATE:

Thank you! I have a very large dataset so this function times out. I was thinking of making it a one cell at a time function. I've tried this but I can't seem to get the color of the inputValue cell's background color...

function setKeep(inValue){
  var sheet = SpreadsheetApp.getActiveSheet();
  var cell = sheet.getActiveCell();
  var color = cell.getBackgroundColor();
  var outValue = "";

  if (inValue == "AUTHENTICATED" && color == "white"){
    outValue = "TRUE";
    }
  else{
    outValue = "FALSE";
    }
  return outValue;

}

Answer 1

Not sure why you choose this "step by step" solution, you could make the initial script much more efficient with quite little modifications.

I'm sure that if you had mentioned that you had a lot of data Srik would have suggested another version of his code.

Based on this article about best practices in GAS here is a fast version of your script that should not time out.

for info, on a sheet with 1000 rows I get this result :

Execution succeeded [0.282 seconds total runtime]

Give it a try... ;-)

function setValue(){
  var sheet = SpreadsheetApp.getActiveSheet(); 
  var end = SpreadsheetApp.getActiveSheet().getLastRow();
  var range = sheet.getRange(1,4,end,2);// get both columns you need
  var colors = range.getFontColors();// batch read colors in range
  var values = range.getValues();// batch read values in range
  for( var i = 0; i < values.length; ++i){ // loop in the array instead of sheet, it's really faster
    Logger.log(colors[i][0]); 
    if (values[i][0] == "Authenticated" && colors[i][0] == "#ffffff") { // compare the array values
      values[i][1]='True';// and write result in the array as well
    }
    else {
      values[i][1]='False';// and write result in the array as well
    }
  }
  range.setValues(values);// batch write back the results in sheet 
}

Answer 2

Looking at the code, I think you have not used the script editor or the prompts available in the editor. Thats because some of the methods you've used do not exist.

function setValue(){
  var sheet = SpreadsheetApp.getActiveSheet(); // You want the sheet not the spreadsheet.
  var end = SpreadsheetApp.getActiveSheet().getLastRow();

  for( var i = 1; i < end + 1; ++i){
    var value = sheet.getRange(i, 4).getValue();
    var color = sheet.getRange(i, 4).getFontColor(); // There is no Range.getColor()
    Logger.log(color); // Print out the color to see what you're getting. I was getting a 'general-black' as the color
    if (value == "Authenticated" && color == "#ffffff") {
      sheet.getRange(i, 5).setValue("True");
    }
    else {
      sheet.getRange(i, 5).setValue("False");
    }
  }
}

Answer 3

You can also replace the if statement with a one-liner:

function setValue(){
  var sheet = SpreadsheetApp.getActiveSheet(); 
  var end = sheet.getLastRow() + 1;             // reuse existing sheet

  for( var i = 1; i < end; i++) {               // traditional for loop iterates with i++
    var value = sheet.getRange(i, 4).getValue();
    var color = sheet.getRange(i, 4).getFontColor();
    // The ('value == ...') returns true or false
    sheet.getRange(i, 5).setValue(value == "Authenticated" && color == "#ffffff");
  }
}