Format row color based on cell value

0
votes

I am trying to adapt the example script from this previous, related question. For rows where the cell value in column K is zero, I want to make the row yellow.

Here is my current adapted code:

function colorAll() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 3;
  var endRow = sheet.getLastRow();

  for (var r = startRow; r <= endRow; r++) {
    colorRow(r);
  }
}

function colorRow(r){
  var sheet = SpreadsheetApp.getActiveSheet();
  var c = sheet.getLastColumn();
  var dataRange = sheet.getRange(r, 1, 1, c);

  var data = dataRange.getValue();
  var row = data[0];

  if(row[0] === "0"){
    dataRange.setBackground("white");
  }else{
    dataRange.setBackground("yellow");
  }

  SpreadsheetApp.flush(); 
}

function onEdit(event)
{
  var r = event.source.getActiveRange().getRowIndex();
  if (r >= 3) {
    colorRow(r);
  }
}

function onOpen(){
  colorAll();
}

My problem is, I can't figure out how to reference column K. In the linked answer above, the script's creator claims, "[h]ere is a Google Apps Script example of changing the background color of an entire row based on the value in column A." First, and most importantly, I can't figure out where he's referencing column A. I thought changing "var dataRange = sheet.getRange(r, 1, 1, c);" to "var dataRange = sheet.getRange(r, 11, 1, c);" would do it, but that just added 10 blank columns to the end of my sheet, and then the script crashed. I do not understand why.

Secondly, but more as an aside, his claim that the script affects entire rows is inaccurate, as his original "var dataRange = sheet.getRange(r, 1, 1, 3);" only colored the first three columns - which is why I added "var c" and changed "3" to "c".

Furthermore, when I play/debug the script, or run "onEdit" from the spreadsheet script manager, I get "TypeError: Cannot read property "source" from undefined." I can see that "source" is undefined - I had mistakenly assumed it was a Method at first - but I'm not sure how to fix this issue either.

Lastly, column K will not always be the reference column, as I mean to add more columns to the left of it. I assume I'll have to update the script every time I add columns, but there is a column heading in row 2 that will never change, so if someone can help me devise a bit of code that will look for a specific string in row 2, then get that column reference for use in function colorRow(), I would appreciate it.

I can't tell if this script is structured efficiently, but ideally, I want my spreadsheet to be reactive - I don't want to have to rerun this script after editing a driving cell, or upon opening; it reads like it's supposed to do that (were it not buggy), but this is my first attempt at using Google Apps Script, and I don't feel certain of anything.

I'm not great with scripting, but I took a programming fundamentals/Python class in grad school back in 2006, and spent 4 years working with Excel & Access shortly after that, often creating and adapting Macros. I can't really design from scratch, but I understand the basic principles and concepts, even if I can't translate everything (e.g., I don't understand what the "++" means in the third argument in the "for" statement I'm using: "for (var r = startRow; r <= endRow; r++)." I think I'm allegorically equivalent to a literate Spanish speaker trying to read Italian.

Help, and educational explanations/examples, will be much appreciated. Thank you kindly for reading/skimming/skipping to this sentence.

2
google-apps-scriptgoogle-sheets

2 Answers

3
votes

Rather than rewriting the code which you have already got some help with, I will try to give you explanations to the specific questions you asked. I see that you have some of the answers already but I am putting thing in completely as it helps understanding.

My problem is, I can't figure out how to reference column K.

Column A = 1, B = 2,... K = 10. 

I can't figure out where he's referencing column A.

You were close when you altered the .getRange. .getRange does different things depending on how many arguments are in the (). With 4 arguments it is getRange(row, column, numRows, numColumns).

sheet.getRange(r, 1, 1, c)  // the first '1' references column A

starts at row(r) which is initially row(3), and column(1). So this is cell(A3). The range extends for 1 row and (c) columns. As c = sheet.getLastColumn(), this means you have taken the range to be 1 row and all the columns.

When you changed this to

var dataRange = sheet.getRange(r, 11, 1, c)  // the '11' references column L

You have got a range starting at row(3) column(L) as 11 = L. This runs to row(3) column(getLastColumn()). This is going to do weird things if you have gone out of range. You may have pushed it in to an infinite for loop which would cause the script to crash

Secondly, but more as an aside, his claim that the script affects entire rows is inaccurate, as his original "var dataRange = sheet.getRange(r, 1, 1, 3);" only colored the first three columns - which is why I added "var c" and changed "3" to "c".

You are correct. The (3) says that the range extend for 3 columns.

"TypeError: Cannot read property "source" from undefined."

What is happening here is not intuitively clear. You can't run the function onEdit(event) from the spreadsheet script manager because it is expecting an "event".

  • onEdit is a special google trigger that runs whenever any edits the spreadsheet.
  • it is passed the (event) that activated it and
  • event.source. refers to the sheet where the event happened so
  • var r = event.source.getActiveRange().getRowIndex(); gets the row number where the edit happened, which is the row that is going to have its color changed.

If you run this in the manager there is no event for it to read, hence undefined. You can't debug it either for the same reasons.

Lastly, column K will not always be the reference column, as I mean to add more columns to the left of it. I assume I'll have to update the script every time I add columns, but there is a column heading in row 2 that will never change, so if someone can help me devise a bit of code that will look for a specific string in row 2, then get that column reference for use in function colorRow(), I would appreciate it.

Before I give you code help her, I have an alternative suggestion because you are also talking about efficiency and it is often faster to run functions in the spreadsheet than using scripts. You could try having column A as an index columns where ColumnA(Row#) = ColumnK(Row#). If you put the following into cell(A1), ColumnA will be an exact match of Column K. 

=ArrayFormula(K:K)

Even better, if you add/remove Columns between A and K, the formula will change its reference without you doing anything. Now just hide columnA and your sheet is back to its originator appearance.

Here is your code help, utilizing some of your own code.

function findSearchColumn () {
  var colNo;  // This is what we are looking for.
  var sheet = SpreadsheetApp.getActiveSheet();
  var c = sheet.getLastColumn();

  // gets the values form the 2nd row in array format
  var values = sheet.getRange(2, 1, 1, c).getValues();
  // Returns a two-dimensional array of values, indexed by row, then by column.

  // we are going to search through values[0][col] as there is only one row
  for (var col = 0; col < data[0].length; col++) { // data[0].length should = c
    if (data[0][col] == value) {
      colNo = col;
      break; // we don't need to do any more here.
    }
  }
  return(colNo);
}

If break gives you a problem just delete it and let the look complete or replace it with col = data[0].length;

I can't tell if this script is structured efficiently, but ideally, I want my spreadsheet to be reactive - I don't want to have to rerun this script after editing a driving cell, or upon opening; it reads like it's supposed to do that (were it not buggy), but this is my first attempt at using Google Apps Script, and I don't feel certain of anything.

It is ok, the fine tuning of efficiency depends on the spreadsheet. function onEdit(event) is going to run every time the sheet is edited, there is nothing you can do about that. However the first thing it should do is check that a relevant range has been edited. The line if (r >= 3) seems to be doing that. You can make this as specific as you need. My suggestion on a hidden index column was aimed a efficiency as well as being much easier to implement.

I'm not great with scripting,

You are doing ok but could do with some background reading, just look up things like for loops. Unfortunate Python is grammatically different from many other languages. A for loop in google script is the same as VBA, C, JAVA, and many more. So reading about these basic operations is actually teaching you about many languages.

I don't understand what the "++" means in the third argument in the "for" statement It is why the language C++ gets its name, as a programmer joke.

r++ is the same as saying r = r+1

r-- means r = r-1

r+2 means r = r+2

So 

for (var r = startRow; r <= endRow; r++)
  • means r begins as startRow, which in this case is 3.
  • the loop will run until r <= endRow, which in this case is sheet.getLastRow()
  • after each time the loop runs r increments by 1, so if endRow == 10, the loop will run from r = 3 to r = 10 => 8 times
0
votes

1.The onEdit is a special function that is automatically called when you edit the spreadsheet. If you run it manually, the required arguments won't be available to it.

2.To change the colour of the entire row when column K is 0, you have to make simple modifications to the script . See below

function colorRow(r){
  var sheet = SpreadsheetApp.getActiveSheet();
  var c = sheet.getLastColumn();
  var dataRange = sheet.getRange(r, 1, 1, c); 

  var data = dataRange.getValues(); 

  if(data[0][10].toString() == "0"){ //Important because based on the formatting in the spreadsheet, this can be a String or an integer 
    dataRange.setBackground("white");
  }else{
    dataRange.setBackground("yellow");
  }

  SpreadsheetApp.flush(); 
}