CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Matthew Hamlyn
Matthew Hamlyn

Reputation: 1

indirect with r1c1 references and App Script formula

I'm using Kishan's custom function countColors() posted at their website on 12 November 2015.

The custom function counts the number of cells that have the same color as the first cell in a range. It works fine with A1 type range references, but I'm trying to use the indirect() function to refer to the ranges, which does not work with this custom function.

Cell W2 has a reference to cell B6 which is the beginning of the range that should be colored.

Cell X2 has a reference to the last cell in the range. This formula goes in cell Y2.

=countcolors((INDIRECT("R[0]C[-2]",false)):(indirect("R[0]C[-1]",false)),(indirect("R[0]C[-2]",false)))

Here is Kishan's code for reference:

 /**
 @param {range} countRange Range to be evaluated
 @param {range} colorRef Cell with background color to be searched for in countRange
 @return {number}
 @customfunction
*/
function countColors(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;
};

The custom function expects A1 type range references and does not work with my indirect() reference. How do I make the custom function accept an indirect() reference?

Upvotes: 0

Views: 316

Answers (1)

Tanaike
Tanaike

Reputation: 201358

From doubleunary's comment, I understood that the showing script retrieves rangeA1Notation (countRange) and colorCellA1Notation (colorRef) by parsing the formula. When you want to give the values countRange,colorRef as R1C1 of a cell inputted formula, I thought that directly giving the values of R1C1 as the arguments might be useful. When this is reflected in a sample script, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of Google Spreadsheet and save the script.

When you use this script, as a sample, please put a custom function like =SAMPLE("R[1]C[0]:R[5]C[5]","R[0]C[1]") into a cell "A1". By this, countRangeR1C1 and colorRef become "A2:F6" and "B1", and the number of background colors from cells "A2:F6" is counted using the background color of a cell "B1".

function SAMPLE(countRangeR1C1, colorRef) {
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getActiveRange();
  [countRangeR1C1, [colorRef]] = [countRangeR1C1.split(":"), [colorRef]].map(e =>
    e.map(f => {
      const t = f.trim().match(/R\[(.*?)\]C\[(.*?)\]/);
      if (t) {
        return [Number(t[1]), Number(t[2])];
      }
      throw new Error("Invalid R1C1 value.");
    })
  );
  const rangeCountRangeR1C1 = sheet.getRange(countRangeR1C1.map(e => range.offset(...e).getA1Notation()).join(":"));
  const rangeColorRef = range.offset(...colorRef);
  const countBackgrounds = rangeCountRangeR1C1.getBackgrounds();
  const refBackground = rangeColorRef.getBackground();
  // console.log(rangeCountRangeR1C1.getA1Notation()); // You can check the value as A1Notation in the log.
  // console.log(rangeColorRef.getA1Notation()); // You can check the value as A1Notation in the log.
  const count = countBackgrounds.reduce((t, r) => {
    r.forEach(c => {
      if (c == refBackground) t++;
    });
    return t;
  }, 0);
  return count;
}

Testing:

When the above script is used, the following result is obtained.

enter image description here

References:

Added:

Your following reply,

but what if I want to use a cell reference for the beginning and end of the range? Example: in cell C1 I would enter a reference for cell a2 and in C2 I'd put a reference for F6? Just the names of the first and last cells of the range, but in separate cells?

Unfortunately, from your question, I couldn't understand that you wanted to use the a1Notation written in the cell. For your reply, how about the following sample script?

function SAMPLE(countRange, color) {
  const sheet = SpreadsheetApp.getActiveSheet();
  const countBackgrounds = sheet.getRange(countRange).getBackgrounds();
  const refBackground = sheet.getRange(color).getBackground();
  const count = countBackgrounds.reduce((t, r) => {
    r.forEach(c => {
      if (c == refBackground) t++;
    });
    return t;
  }, 0);
  return count;
}

  • In this case, for example, when the above sample image is used when the cells C1 and D1 are "A2" and "F6", and the cell E1 is "B1", you can retrieve 6 by putting a custom function of =SAMPLE(C1&":"&D1,E1) into a cell.

  • By the way, if you want to use a formula like =countcolors((INDIRECT("R[0]C[-2]",false)):(indirect("R[0]C[-1]",false)),(indirect("R[0]C[-2]",false))), please put a custom function of =SAMPLE(JOIN(":",INDIRECT("R[0]C[2]:R[0]C[3]",FALSE)),INDIRECT("R[0]C[4]",FALSE)) into a cell "A1" using my 2nd script. By this, JOIN(":",INDIRECT("R[0]C[2]:R[0]C[3]",FALSE)) and INDIRECT("R[0]C[4]",FALSE) return "A2:F6" and "B1", and the result is returned using those a1Notations.

Upvotes: 0

Related Questions