Pass range in Google Apps script custom function without using A1 notation

Question

I'm new to Google Apps script and I would like to create a function for a spreadsheet that sums cell values when the cells meet certain criteria, such as the background color. Moreover, I'd like to pass the range as an array and not using A1 notation for the following reason.

I found a function that uses A1 notation here. The problem is that when I have it on a given cell

=sumWhereBackgroundColorIs("white", "A1:A10")

and I copy the value to the right adjacent cell the result will be again

=sumWhereBackgroundColorIs("white", "A1:A10")

while I'd like to have

=sumWhereBackgroundColorIs("white", "B1:B10")

otherwise I've always to modify manually the input argument and I want to avoid this since I've to use this function extensively.

Hence I've tried by passing a range as an array of values by using

=sumIfBgColor(#ffffff, A1:A10)


function sumIfBgColor(color, range){
    var x = 0;
    for(var i = 0; i < range.length; i++){
      for(var j = 0; j < range[i].length; j++){

        var cell = getCell();

        if(cell.getBackgroundColor() == color)
          x += parseFloat(range[i][j]);
      }
    }
    return x;
}

but I do not know how to get the cell (i.e. the object of type Range) starting from what I have.

Tom Horwood · Answer 1 · 2017-10-09T23:47:00.810

To @Jacob's claim of impossibility, I refute it thus... (but thanks for the improved speed)

using:

=sumIfBgColor("#ffffff", A1:A10, COLUMN(A1), ROW(A1))

with the following functions will do what you want.

/**
 * Sums cell values in a range if they have the given background color
 * 
 * @param  {String} color    Hex string of color eg ("#ffffff")
 * @param  {Array.Array} range    Values of the desired range
 * @param  {int} startcol The column of the range
 * @param  {int} startrow The first row of the range
 * 
 * @return {int}          Sum of all cell values matching the condition
 */
function sumIfBgColor(color, range, startcol, startrow){
  // convert from int to ALPHANUMERIC - thanks to 
  // Daniel at http://stackoverflow.com/a/3145054/2828136
  var col_id = String.fromCharCode(64 + startcol);
  var endrow = startrow + range.length - 1
  // build the range string, then get the background colours
  var range_string = col_id + startrow + ":" + col_id + endrow
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var getColors = ss.getRange(range_string).getBackgrounds();

  var x = 0;
  for(var i = 0; i < range.length; i++) {
    for(var j = 0; j < range[0].length; j++) {
      // Sometimes the cell background is eg 'white' rather than '#ffffff'.
      // I don't know why - I think it's a bug.
      // so we remove that inconsistency with colourNameToHex
      // courtesy of Greg at http://stackoverflow.com/a/1573141/2828136
      if(colourNameToHex(getColors[i][j].toString()) == color) {
        x += range[i][j];
      }
    }
  }
  return x;
}

/**
 * Takes a colour string and returns it to a hex string. If a non-matching string is
 * passed, it will return the argument as is - for this situation it means that a
 * hex string can be passed to it and be returned as is. This is not for production.
 * 
 * @param  {string} color    Must be either a colour name or hex string of color eg ("#ffffff")
 * 
 * @return {object|string}          hex string of color eg ("#ffffff") or the argument given.
 */
function colourNameToHex(colour)
{
    var colours = {"aliceblue":"#f0f8ff","antiquewhite":"#faebd7","aqua":"#00ffff","aquamarine":"#7fffd4","azure":"#f0ffff",
    "beige":"#f5f5dc","bisque":"#ffe4c4","black":"#000000","blanchedalmond":"#ffebcd","blue":"#0000ff","blueviolet":"#8a2be2","brown":"#a52a2a","burlywood":"#deb887",
    "cadetblue":"#5f9ea0","chartreuse":"#7fff00","chocolate":"#d2691e","coral":"#ff7f50","cornflowerblue":"#6495ed","cornsilk":"#fff8dc","crimson":"#dc143c","cyan":"#00ffff",
    "darkblue":"#00008b","darkcyan":"#008b8b","darkgoldenrod":"#b8860b","darkgray":"#a9a9a9","darkgreen":"#006400","darkkhaki":"#bdb76b","darkmagenta":"#8b008b","darkolivegreen":"#556b2f",
    "darkorange":"#ff8c00","darkorchid":"#9932cc","darkred":"#8b0000","darksalmon":"#e9967a","darkseagreen":"#8fbc8f","darkslateblue":"#483d8b","darkslategray":"#2f4f4f","darkturquoise":"#00ced1",
    "darkviolet":"#9400d3","deeppink":"#ff1493","deepskyblue":"#00bfff","dimgray":"#696969","dodgerblue":"#1e90ff",
    "firebrick":"#b22222","floralwhite":"#fffaf0","forestgreen":"#228b22","fuchsia":"#ff00ff",
    "gainsboro":"#dcdcdc","ghostwhite":"#f8f8ff","gold":"#ffd700","goldenrod":"#daa520","gray":"#808080","green":"#008000","greenyellow":"#adff2f",
    "honeydew":"#f0fff0","hotpink":"#ff69b4",
    "indianred ":"#cd5c5c","indigo ":"#4b0082","ivory":"#fffff0","khaki":"#f0e68c",
    "lavender":"#e6e6fa","lavenderblush":"#fff0f5","lawngreen":"#7cfc00","lemonchiffon":"#fffacd","lightblue":"#add8e6","lightcoral":"#f08080","lightcyan":"#e0ffff","lightgoldenrodyellow":"#fafad2",
    "lightgrey":"#d3d3d3","lightgreen":"#90ee90","lightpink":"#ffb6c1","lightsalmon":"#ffa07a","lightseagreen":"#20b2aa","lightskyblue":"#87cefa","lightslategray":"#778899","lightsteelblue":"#b0c4de",
    "lightyellow":"#ffffe0","lime":"#00ff00","limegreen":"#32cd32","linen":"#faf0e6",
    "magenta":"#ff00ff","maroon":"#800000","mediumaquamarine":"#66cdaa","mediumblue":"#0000cd","mediumorchid":"#ba55d3","mediumpurple":"#9370d8","mediumseagreen":"#3cb371","mediumslateblue":"#7b68ee",
    "mediumspringgreen":"#00fa9a","mediumturquoise":"#48d1cc","mediumvioletred":"#c71585","midnightblue":"#191970","mintcream":"#f5fffa","mistyrose":"#ffe4e1","moccasin":"#ffe4b5",
    "navajowhite":"#ffdead","navy":"#000080",
    "oldlace":"#fdf5e6","olive":"#808000","olivedrab":"#6b8e23","orange":"#ffa500","orangered":"#ff4500","orchid":"#da70d6",
    "palegoldenrod":"#eee8aa","palegreen":"#98fb98","paleturquoise":"#afeeee","palevioletred":"#d87093","papayawhip":"#ffefd5","peachpuff":"#ffdab9","peru":"#cd853f","pink":"#ffc0cb","plum":"#dda0dd","powderblue":"#b0e0e6","purple":"#800080",
    "red":"#ff0000","rosybrown":"#bc8f8f","royalblue":"#4169e1",
    "saddlebrown":"#8b4513","salmon":"#fa8072","sandybrown":"#f4a460","seagreen":"#2e8b57","seashell":"#fff5ee","sienna":"#a0522d","silver":"#c0c0c0","skyblue":"#87ceeb","slateblue":"#6a5acd","slategray":"#708090","snow":"#fffafa","springgreen":"#00ff7f","steelblue":"#4682b4",
    "tan":"#d2b48c","teal":"#008080","thistle":"#d8bfd8","tomato":"#ff6347","turquoise":"#40e0d0",
    "violet":"#ee82ee",
    "wheat":"#f5deb3","white":"#ffffff","whitesmoke":"#f5f5f5",
    "yellow":"#ffff00","yellowgreen":"#9acd32"};

    if (typeof colours[colour.toLowerCase()] != 'undefined')
        return colours[colour.toLowerCase()];

    return colour;
}

score 4 · Answer 2 · answered Nov 11 '15 at 22:12

Reference: http://igoogledrive.blogspot.com/2015/11/google-spreadsheet-sum-of-colored-cells.html

Instead of passing parameters as string to the custom function, following script takes input as a range:

/**
* @param {string} color String as background color to be searched for in sumRange
* @param {range} sumRange Range to be evaluated
* @return {number}
* @customfunction
*/

function sumColoredCells(color,sumRange) {
  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 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;
};

Have a look at the following screenshot:

score 1 · Answer 3 · answered Oct 10 '13 at 08:35

The following little script will do the trick.

Code

function sumIfBgColor(color, range){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var getColors = ss.getRange(range).getBackgrounds();
  var getValues = ss.getRange(range).getValues(), x = 0;
  for(var i = 0; i < getValues.length; i++) {
    for(var j = 0; j < getValues[0].length; j++) {
      if(getColors[i][j].toString() == color) {
        x += getValues[i][j];
      }
    }
  }
  return x;
}

Explained

First, the active spreadsheet is determined. Then both the values and the colors, based on the range, are fetched. The values will be used to iterate through the colors and ultimately the summation.

Usage

enter image description here

Example

I've created an example file for you: Sum based on Background

Pass range in Google Apps script custom function without using A1 notation

3 Answers3

Code

Explained

Usage

Example

Linked