Skip to content

Instantly share code, notes, and snippets.

@Palbahngmiyine
Created February 2, 2023 12:16
Show Gist options
  • Save Palbahngmiyine/5b2cade1193908cd1a5f3fcc9528caef to your computer and use it in GitHub Desktop.
Save Palbahngmiyine/5b2cade1193908cd1a5f3fcc9528caef to your computer and use it in GitHub Desktop.
Background Cell sum funciton
function sumColoredCells(sumRange,colorRef) {
var activeRg = SpreadsheetApp.getActiveRange();
var activeSht = SpreadsheetApp.getActiveSheet();
var activeformula = activeRg.getFormula();
var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
var sumValues = activeSht.getRange(countRangeAddress).getValues();
var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
// 필요에 따라 변경
var BackGround = activeSht.getRange(colorRefAddress).getBackground();
var totalValue = 0;
for (var i = 0; i < backGrounds.length; i++)
for (var k = 0; k < backGrounds[i].length; k++)
if ( backGrounds[i][k] == BackGround )
if ((typeof sumValues[i][k]) == 'number')
totalValue = totalValue + (sumValues[i][k]);
return totalValue;
};
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment