Code.gs

/*
 * NOTE:
 *   I have also added the following to appsscript.json:
 *
 *   "oauthScopes": [
 *     "https://www.googleapis.com/auth/spreadsheets.currentonly",
 *     "https://www.googleapis.com/auth/spreadsheets"
 *   ]
 *
 */


//Used to save the mergedRanges so they can be put back together
var mergedRanges;

//Adds a menu item to run the calcHourlySplitByColour function:
function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [];
  menuEntries.push({name: "Calculate Hourly Split", functionName: "calcHourlySplitByColour"});
  ss.addMenu("CustomFunctions", menuEntries);
};

function calcHourlySplitByColour() {
  var activeRange = SpreadsheetApp.getActiveRange();
  var activeSheet = activeRange.getSheet();
  var formula = activeRange.getFormula();

  var rangeToSearch = "'Sheet1'!B2:H30"; //Range to count for the colours
  var purpleColourRef = "'Sheet1'!J2"; //Where to get the bg colour to count
  var purpleColourCountResult = "'Sheet1'!K2"; //Where to store the result

  //Break apart the merged cells
  breakApartRange(rangeToSearch);

  var purpleColourCount = countColouredCells2(rangeToSearch, purpleColourRef);

  //Stitch the previously merged cells back together
  reMergeRange(rangeToSearch);

  //Save the result to the spreadsheet
  saveResult(purpleColourCount, purpleColourCountResult) //Saves value of param1 to the location of param2 (note: I haven't included this function in the script)
};

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());
};

//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();

  SpreadsheetApp.getUi().alert("1st Pop" + activeRange.getFontFamily());
  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;
};

/*
 * NOTE:
 *   The above is my attempt at refactoring the below method:
 *
 * Gets the count of cells with a particular colour within the range. Merged cells count as 1
 * Works from spreadsheet but don't know what to pass in/how to pass in as SpreadsheetApp.getActiveRange(); is just null
 *
 * 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;
 *   };
 *
 *
 */