Google Spreadsheet Count of colored cells in a range in Custom Function

Count of colored cells in a range in Custom Function

I have the following formula in cell E9:
=countColoredCells(A1:C6,E1)

and the following formula in cell F9:
=countColoredCells(A1:C6,F1)

countColoredCells is a custom function created with the help of the following script code, in this function you need to pass the range in first parameter in which you want to count the colored cells, and also you need to pass the cell in the second parameter which have the background that you need to count in the range (provided in first parameter).

Put the following script code in the script editor of your spreadsheet:

/////////////////////////////////////////////////

/**
* @param {range} countRange Range to be evaluated
* @param {range} colorRef Cell with background color to be searched for in countRange
* @return {number}
* @customfunction
*/

function countColoredCells(countRange,colorRef) {
  var activeRange = SpreadsheetApp.getActiveRange();
  var activeSheet = activeRange.getSheet();
  var formula = activeRange.getFormula();
  
  var rangeA1Notation = formula.match(/\((.*)\,/).pop();
  var range = activeSheet.getRange(rangeA1Notation);
  var bg = range.getBackgrounds();
  var values = range.getValues();
  
  var colorCellA1Notation = formula.match(/\,(.*)\)/).pop();
  var colorCell = activeSheet.getRange(colorCellA1Notation);
  var color = colorCell.getBackground();
  
  var count = 0;
  
  for(var i=0;i<bg.length;i++)
    for(var j=0;j<bg[0].length;j++)
      if( bg[i][j] == color )
        count=count+1;
  return count;
};

/////////////////////////////////////////////////

NOTE:

When you change the color of any cell the value will not get auto updated in the result,

so you need to temporary change the value of any of one of the cell in the range (and then change it back as you want..)

this will update the color counting result...

For getting sum instead of count of cells having a background color, look at the following link:
http://igoogledrive.blogspot.com/2015/11/google-spreadsheet-sum-of-colored-cells.html

Google Spreadsheet Sum of a colored cells in a range in Custom Function

Sum of a colored cells in a range in Custom Function

I have the following formula in cell E9:
=sumColoredCells(A1:C6,E1)

and the following formula in cell F9:
=sumColoredCells(A1:C6,F1)

sumColoredCells is a custom function created with the help of the following script code, in this function you need to pass the range in first parameter in which you want to sum the values of the colored cells, and also you need to pass the cell in the second parameter which have the background that you need to sum in the range (provided in first parameter).

Put the following is the script code in the script editor of your spreadsheet:

////////////////////////////////////////////////////

/**
* @param {range} sumRange Range to be evaluated
* @param {range} colorRef Cell with background color to be searched for in sumRange
* @return {number}
* @customfunction
*/

function sumColoredCells(sumRange,colorRef) {
  var activeRange = SpreadsheetApp.getActiveRange();
  var activeSheet = activeRange.getSheet();
  var formula = activeRange.getFormula().toString();
  formula = formula.replace(new RegExp(';','g'),',');
  
  
  var rangeA1Notation = formula.match(/\((.*)\,/).pop();
  var range = activeSheet.getRange(rangeA1Notation);
  var bg = range.getBackgrounds();
  var values = range.getValues();
  
  var colorCellA1Notation = formula.match(/\,(.*)\)/).pop();
  var colorCell = activeSheet.getRange(colorCellA1Notation);
  var color = colorCell.getBackground();
  
  var total = 0;
  
  for(var i=0;i<bg.length;i++)
    for(var j=0;j<bg[0].length;j++)
      if( bg[i][j] == color )
        total=total+(values[i][j]*1);
  return total;
};

////////////////////////////////////////////////////

NOTE:

When you change the color of any cell the value will not get auto updated in the sum result,

so you need to temporary change the value of any of one of the cell in the range (and then change it back as you want..)

this will update the colored cells sum result...

For getting count instead of sum of values of cells having a background color, look at the following link:
http://igoogledrive.blogspot.com/2015/11/google-spreadsheet-count-of-colored.html

Google Spreadsheet Getting the last 5 values before the previous last 5

Question:

Hi, I am trying to get the average values in a row present at the last 5 rows before the last 5 rows, in other words in a sheet with 15 rows in a column I would like to know how to attain the average value of rows 5 to 10.

Solution:

Following is the screenshot of the sheet having data in column A:

Now I have the following formula in cell C1:
=arrayformula({A:A,row(A:A)})
to get the same values in column C and the row number in column D

Now I have the following formula in cell C1:
=query(arrayformula({A:A,row(A:A)}),"select Col1,Col2 where Col1 is not null",0)
to get the both columns having equal number of rows (that is rows having values in Column C)

Now I have the following formula in cell C1:
=query(arrayformula({A:A,row(A:A)}),"select Col1,Col2 where Col1 is not null order by Col2 desc",0)
to get the data in descending order of row number, as we want second last 5 rows...

Now I have the following formula in cell C1:
=query(arrayformula({A:A,row(A:A)}),"select Col1,Col2 where Col1 is not null order by Col2 desc offset 5",0)
to start the data from second last 5 rows, I have used "offset" in query formula.

Now I have the following formula in cell C1:
=query(arrayformula({A:A,row(A:A)}),"select Col1,Col2 where Col1 is not null order by Col2 desc limit 5 offset 5",0)
to limit the data upto only 5 rows.

Now I have the following formula in cell C1:
=query(arrayformula({A:A,row(A:A)}),"select Col1 where Col1 is not null order by Col2 desc limit 5 offset 5",0)
to get the data in only column C of second last 5 rows.

Now I have the following formula in cell C1:
=average(iferror(query(arrayformula({A:A,row(A:A)}),"select Col1 where Col1 is not null order by Col2 desc limit 5 offset 5",0)))
to finally get the average of second last 5 rows.

Google Spreadsheet Script for manipulating JSON

The following URL "http://shanghai.anjuke.com/ajax/pricetrend/comm?cid=10052", has the JSON data:

Now to fetch this data in Google Spreadsheet, use the following script:

/////////////////////////////////////////////////

function extract(url){

  var response = UrlFetchApp.fetch(url);

  var data = JSON.parse(response.getContentText());

  var comm = data.comm;

  

  var keyVal = function(o) {

    var key = Object.keys(o)[0];

    return [parseInt(key),parseInt(o[key])];

  } ;

  

  var result = new Array();

  var count=0;

  for (i in comm){

    result[count] = new Array();

    result[count] = keyVal(comm[count]);

    count++;

  }

  return result;

};

/////////////////////////////////////////////////

save the above script code in the script editor of your Spreadsheet and then you can call the custom function "extract" directly from your spreadsheet.

Have a look at the following screenshot:

I have the URL in cell A1, and the following formula in cell B1:
=transpose(extract(A1))

Google Spreadsheet return a subset given complex criteria

Question:

Here is my sample sheet:

Given the example data in this sheet, I want to return a subset automatically that meets the following criteria:

* 4 total rows

* 1 row where color=green

* 1 row where color=red

* 2 rows where color=blue

* The COMBINED value of the PRICE column of the four returned rows cannot exceed a sum of 30

* The returned set should provide the highest possible COMBINED value of the POINTS column of the four returned rows. 

In short, I would like to find the four records in the entire table that result in the highest possible combined POINTS value, without exceeding a combined PRICE value of 30.

Help is greatly appreciated!

Solution:

Insert the following script code in the script code of your spreadsheet:

////////////////////////////////////////////////////////

function subset(v) {
  //var ss = SpreadsheetApp.getActiveSpreadsheet();
  //var s = ss.getSheetByName('Sheet1');
  //var r = s.getRange('A:E');
  //var v = r.getValues();
  var lastrow = getLastPopulatedRow(v);
  var green = new Array();
  var red = new Array();
  var blue = new Array();
  
  for(var i=0;i<lastrow;i++) {
    if(v[i][2]=='green')
      green.push(v[i]);
    else if(v[i][2]=='red')
      red.push(v[i]);
    else if(v[i][2]=='blue')
      blue.push(v[i]);
  }
  
  var combinations = new Array();
  var c=0;
  var total = new Array();
  
  for(var w=0;w<green.length;w++) {
    for(var x=0;x<red.length;x++) {
      for(var y=0;y<blue.length-1;y++) {
        for(var z=y+1;z<blue.length;z++) {
          combinations[c] = new Array();
          combinations[c].push(green[w]);
          combinations[c].push(red[x]);
          combinations[c].push(blue[y]);
          combinations[c].push(blue[z]);
          total.push([c,(green[w][3]+red[x][3]+blue[y][3]+blue[z][3]),(green[w][4]+red[x][4]+blue[y][4]+blue[z][4])]);
          c=c+1;
        }
      }
    }
  }
  var total = total.sort(function(a,b) { return a[1] - b[1]; });
  //Logger.log(total);
  for(var j=total.length-1;j>=0;j--)
    if(total[j][2]<=30) break;
  
  return combinations[total[j][0]];
};

function getLastPopulatedRow(data) {
  for (var i=data.length-1;i>=0;i--)
    for (var j=0;j<data[0].length;j++)
      if (data[i][j]) return i+1;
  return 0;
};

////////////////////////////////////////////////////////

Now you can use the following custom function in your spreadsheet:
=subset(A2:E)

Following is the screenshot of the spreadsheet, showing the desired subset in range G2:K5
I have inserted the custom formula in cell G2.

Google Spreadsheet generating a list of the most frequently used words in an entire sheet

Question:

Hey guys,

I have a spreadsheet made up of columns that list the top 100 search terms on a website for each month of the year. Is there a formula I can use to search all of the columns and generate a list of the overall top 20 terms?

Any help would be greatly appreciated.

Thanks!

Solution:

Screenshot of "Sheet2":

First of all to get the unique values of Sheet1, I have the following formula in cell A1:
=unique({Sheet1!B3:B102;Sheet1!C3:C102;Sheet1!D3:D102;Sheet1!E3:E102;Sheet1!F3:F102;Sheet1!G3:G102;Sheet1!H3:H102;Sheet1!I3:I102;Sheet1!J3:J102;Sheet1!K3:K102;Sheet1!L3:L102;Sheet1!M3:M102;Sheet1!N3:N102;Sheet1!O3:O102;Sheet1!P3:P102;Sheet1!Q3:Q102})

and then to count the values in Column A, I have the following formula in cell B1:
=arrayformula(if(len(A1:A),countif(Sheet1!B3:Q102,A1:A),))

and then to sort the values of Column A and B in descending order of Column B, I have the following formula in cell E1:
=sort(A:B,2,false)


========================================


Alternatively you can also have the solution with a following single advanced formula:

=query(arrayformula({unique({Sheet1!B3:B102;Sheet1!C3:C102;Sheet1!D3:D102;Sheet1!E3:E102;Sheet1!F3:F102;Sheet1!G3:G102;Sheet1!H3:H102;Sheet1!I3:I102;Sheet1!J3:J102;Sheet1!K3:K102;Sheet1!L3:L102;Sheet1!M3:M102;Sheet1!N3:N102;Sheet1!O3:O102;Sheet1!P3:P102;Sheet1!Q3:Q102}),countif(Sheet1!B3:Q102,unique({Sheet1!B3:B102;Sheet1!C3:C102;Sheet1!D3:D102;Sheet1!E3:E102;Sheet1!F3:F102;Sheet1!G3:G102;Sheet1!H3:H102;Sheet1!I3:I102;Sheet1!J3:J102;Sheet1!K3:K102;Sheet1!L3:L102;Sheet1!M3:M102;Sheet1!N3:N102;Sheet1!O3:O102;Sheet1!P3:P102;Sheet1!Q3:Q102}))}),"select * order by Col2 desc",0)

Screenshot of "Sheet3":

Google Sheet Select items by column that contains few parameters separated by comma

Question:

Hi everyone, I am trying to create some sort of inventory database using google sheets. I have one sheet 'Inventory' that lists all the items in my database. In another sheet 'Search' I want to have an ability to display items that qualify size dispersion. The problem is that items have multiple sizes listed in one cell separated by comma.

Timestamp	Name	Weight	Price	Tire sizes
07.10.2015 11:04:01	Chain 1	220	100	200, 300
07.10.2015 14:04:44	Chain 2	135	350	100
07.10.2015 14:05:27	Chain 3	453	560	100, 200, 300

I need to come up with formula that would take following input

Size from	Size to
50	120

And would display items that qualify this input dispersion like

Timestamp	Name	Weight	Price	Tire sizes
07.10.2015 14:04:44	Chain 2	135	350	100
07.10.2015 14:05:27	Chain 3	453	560	100, 200, 300

Solution:

Following is the screenshot of Sheet "Inventory"

Now, put the following script in the script editor of your spreadsheet:

function inventory() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var s = ss.getSheetByName('Inventory');

  var r = s.getRange('A:E');

  var v = r.getValues();

  var lastrow  = getLastPopulatedRow(v);

  var arr = new Array();

  arr.push(v[0]);

  

  for(var i=1;i<lastrow;i++) {

    var e = v[i][4].toString().split(',');

    for(var j=0;j<e.length;j++){

      arr.push( [ v[i][0],v[i][1],v[i][2],v[i][3],e[j].trim()*1 ] );

    }

  }

  return arr;

};

function getLastPopulatedRow(data) {

  for (var i=data.length-1;i>=0;i--)

    for (var j=0;j<data[0].length;j++)

      if (data[i][j]) return i+1;

  return 0;

};

save the above code and then refresh your spreadsheet.

Now, just try the following custom function in a new sheet in your spreadsheet:

=inventory()

The above custom function will give you the following output:

Now, we can use this output in a Query formula, try the following formula in cell A2 of sheet "Search":
=query(inventory();"select * where Col5>="&A2&" and Col5<="&B2;1)

Following is the screenshot of Sheet "Search":