0

I am trying to use a Google script to set a cell in the last row only of the 2nd column in a Google sheet to green color if it is:

1. <0, and

2. not equal to #N/A

Partial preferred approach

I have the following if statement (without using a loop):

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
lastrow = sheet.getLastRow()
if (sheet.getRange(lastrow, 2, 1, 1) >0.00 && sheet.getRange(lastrow, 2, 1, 1) !='#N/A') {
  sheet.getRange(lastrow, 2, 1, 1).setFontColor('green');
}

However, this is not working. It is simply not assigning the color green to the font.

Not the preferred approach

I could do this using a loop, based on this answer, and loop over all rows in the column one at a time:

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
lastrow = sheet.getLastRow()
var oValues = sheet.getRange(2, 2, lastrow, 1).getValues();

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

However, the disadvantage is that this approach is formatting all the rows in the column. I only need to format the last row.

Is there a way to avoid looping over all rows and just check if the last row meets 1. and 2. from above?

Rubén
  • 34,714
  • 9
  • 70
  • 166
edesz
  • 11,756
  • 22
  • 75
  • 123

1 Answers1

1

How about this answer?

Modification points :

  • In your script, sheet.getRange(lastrow, 2, 1, 1) > 0.00 and sheet.getRange(lastrow, 2, 1, 1) !='#N/A' mean the comparison with the range. When you want to compare the value of a cell, you can use sheet.getRange(lastrow, 2, 1, 1).getValue().
  • The condition of <0 and not equal to #N/A can be written by if (value < 0 && value) {}.
  • The cell of last row of 2nd column can be written by sheet.getRange(sheet.getLastRow(), 2).
    • In your case, you can also use sheet.getRange(sheet.getLastRow(), 2, 1, 1).

The modified script which was reflected above is as follows.

Modified script :

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
var range = sheet.getRange(sheet.getLastRow(), 2); // Last row of 2nd column
var value = range.getValue(); // Value of last row of 2nd column
if (value < 0 && value) { // <0 and not equal to #N/A
  // range.setBackground("green"); // This line give the background color of cell.
  range.setFontColor("green"); // This line give the font color of cell.
}

If I misunderstand your question, please tell me. I would like to modify.

Tanaike
  • 181,128
  • 11
  • 97
  • 165