Sorry for the lengthy explanation! Let me know if you need more details or examples from the JavaScript file/spreadsheet I'm working with.
Situation:
I'm facing challenges with Google Apps Script (GAS) while trying to count the number of background colors in a child's schedule that I've developed.
I have a basic week view divided into 30-minute intervals from 5:30 am to 7:30 pm. Cells are colored differently based on whether the child is at home, different events, etc. Here's an example: https://i.sstatic.net/10J0j.png
My goal is to calculate the number of purple, orange, and green cells. I plan to divide these counts by 2 to determine the hours spent at each location during the week.
Steps Followed:
An important aspect is that I must use merged cells within the target range for counting.
This resource was quite helpful: Script Google Sheets that will count the amount of cells with a specific background colour
This led me to create a custom function like this:
//Gets the count of cells with a particular colour within the range. Merged cells count as 1
function countColouredCells(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;
};
//Usage in cell formula: "=countcolouredcells($E$11:$K$39,L46)" where $E$11:$K$39 is the range to count the occurances of cells with the same background as L46However, this method considers merged cells as 1, whereas I want it to count the actual number of cells covered by the merge. For example, in the image above, the green "School" cell on Monday should be counted as 14, not 1.
Further investigation led me to develop two functions borrowed from solutions on StackOverflow, which can break apart and remerge merged cells.
var mergedRanges;
function breakApartRange(breakRange){
var activeRange = SpreadsheetApp.getActiveRange();
var activeSheet = activeRange.getSheet();
var formula = activeRange.getFormula();
var fullRange = activeSheet.getRange(breakRange);
//break merged cells
mergedRanges = fullRange.getMergedRanges();
mergedRanges.forEach(range => range.setValue(range.breakApart().getValue()));
};
function reMergeRange(breakRange){
var activeRange = SpreadsheetApp.getActiveRange();
var activeSheet = activeRange.getSheet();
var formula = activeRange.getFormula();
var fullRange = activeSheet.getRange(breakRange);
//re merge cells
mergedRanges.forEach(range => range.merge());
};A limitation is that the breakApart() method cannot be directly called within a custom function used in a formula. To overcome this, I created a custom menu item triggered by the onOpen method as a workaround.
//Adds a menu item to run the function:
// The onOpen function is executed automatically every time a Spreadsheet is loaded
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [];
menuEntries.push({name: "Calculate Hourly Split", functionName: "calcHourlySplitByColour"});
ss.addMenu("CustomFunctions", menuEntries);
};To make this work, I had to use fixed references for the range and color, but it serves my purpose. Now, I have a custom function named calcHourlySplitByColour, which is invoked through the menu item, and does the following:
- Breaks apart the merges
- Calls another custom function (
countColouredCells) to count the colored cells - Stores the count in a separate cell for viewing
- Merges the cells back to their original state
Individually, each step works fine (the breaking apart and remerging tested successfully when triggered from the custom menu item, and countColouredCells seems to work when invoked from a formula in the sheet.)
Challenge Faced:
The issue arises when I try to integrate everything, especially when attempting to call countColouredCells via the custom menu item. To make it functional, I need to pass the ranges into countColouredCells statically, but encounter difficulties when executing the pop() method:
//Gets the count of cells with a particular colour within the range. Merged cells count as 1
function countColouredCells2(countRange,colorRef) {
var activeRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(countRange);
var activeSheet = activeRange.getSheet();
var formula = activeRange.getFormula();
//Testing code just to see if the activeRange was null - it wasn't, this returned "1st PopCalibri" in the alert
SpreadsheetApp.getUi().alert("1st Pop" + activeRange.getFontFamily());
//This is the line that causes the error (i.e. I never see an alert for "2nd Pop"):
var rangeA1Notation = formula.match(/\((.*)\,/).pop();
var range = activeSheet.getRange(rangeA1Notation);
var bg = range.getBackgrounds();
var values = range.getValues();
SpreadsheetApp.getUi().alert("2nd Pop");
var colorCellA1Notation = formula.match(/\,(.*)\)/).pop();
var colorCell = activeSheet.getRange(colorCellA1Notation);
var color = colorCell.getBackground();
SpreadsheetApp.getUi().alert("Post 2nd Pop");
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;
};This is a draft version, a modification of my initial countColouredCells function. While I haven't handled the colorRef parameter yet, the problematic line is:
var rangeA1Notation = formula.match(/\((.*)\,/).pop();
which pertains only to the first parameter, countRange. The error message received is as follows: https://i.sstatic.net/WOGeo.png
https://i.sstatic.net/dAZLv.png
You can access an example of the spreadsheet here. In case you're unable to view the apps script for a shared sheet, find the related content here.