Google scripts if statement with conditional formatting on last row only

Question

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?

Tanaike · Accepted Answer · 2017-12-11 23:15:50Z

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.

answered Dec 11, 2017 at 23:15

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

edesz Over a year ago

Thanks! I was completely missing the .getValue() part.

Collectives™ on Stack Overflow

Google scripts if statement with conditional formatting on last row only

1 Answer 1

Modification points :

Modified script :

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modification points :

Modified script :

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related