CODEWITHSUNDEEP
google-sheets
chloobs
chloobs

Reputation: 27

Counting number of cells w/ same background color of each row

I'm using Google Spreadsheets which is populated with responses from Google Forms. Right now, I have the spreadsheet to auto-color the background of cells containing "wrong" responses by comparing the value of a cell to the top cell of each column (using conditional formatting). For columns CF through CH, I want to set up as the "scores" section.

concept explanation example


With the above image, I've manually illustrated what my end goal is:
I want to be able to count the cells with a specific background color (light red 1 [#de6666] in this case) for EACH row and output the number in the corresponding cell in column CH. From there, I'm planning to use that number to calculate the raw score & percent score by comparing to the total # of questions.



I did some searching, hoping for an already-answered similar question that might help with my situation, and found these two: (1) & (2).

Now, I'm not at all skilled in the art of coding and I would like to ask you to be patient with me if I am not understanding things correctly or need extra assistance. My question is, is there a way to tweak these to make it do what I want to perform? Or is a completely new custom function required?

Upvotes: 0

Views: 1797

Answers (1)

Alice
Alice

Reputation: 36

I ran into the same problem, here was my solution, fitted to what I can see of your needs. Its not exactly universal, but as long as you change the ranges to suit your needs it should work just fine. I did my best to explain the parts that were confusing to me so you can tweak it if needed. Good Luck, hope this helps!

function countbackgrounds(){
  var book = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = book.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var cellColors = sheet.getRange(4, 1, lastRow-3, 57).getBackgrounds();//(startrow, startcolumn,numRows,numCols)
  /*since you want all rows and columns before CF:CH, since CF is column 58, you  want 57 columns.
  //to exclude your headers you want to start on a certain row, 
  //be mindful of which row number you are starting on, it looks to me like 4, 
  //so subtract 'startrow-1' from 'lastRow' so you code doesnt continue past your entries. */
  var colorY = "#de6666"; 
  var count = 0;
  for(var i = 0; i < cellColors.length; i++) {
    var thisRow = sheet.getRange(i+4, 60 ); //get output cell for this row in CH
    /*note: 'i' is an index number within the loop not the row number, if you are starting at another row 
    //you will need to change this, just add the 'startrow' number */
    for(var j = 0; j < cellColors[0].length ; j++) { 
      if(cellColors[i][j] == colorY) { 
        count = count + 1;
      };//end if statement
    };//end for each cell in a row 
    thisRow.setValue(count); 
    count = 0;//reset count to 0 before next row
  };//end for each row
};

Upvotes: 2

Related Questions