Collection of Google Apps scripts

google-apps-scripts.js

// Code for getting the values of cells by their fill color
/****************************************************************************************************
 By LouD 2022-11-29 (adaption of countColoredCells())
 Get cell value(s) based on the cell color in Google Sheets in the cell + rows where the formula is in
 Use =returnColoredCelldata(RANGE; CELLID) or =returnColoredCelldata(RANGE, CELLID) depending on your region
 where RANGE is the cell range to count and CELLID is the CELL to compare the colors with.
*****************************************************************************************************/

function returnColoredCelldata (cellRange, colorRef) {
  var activeRange = SpreadsheetApp.getActiveRange();
  var activeSheet = activeRange.getSheet();
  var formula = activeRange.getFormula();
  
  var rangeNotation = formula.match(/\((.*)[\,\;]/).pop();
  var range = activeSheet.getRange(rangeNotation);
  var bg = range.getBackgrounds();
  var values = range.getValues();
  
  var colorCellNotation = formula.match(/[\,\;](.*)\)/).pop();
  var colorCell = activeSheet.getRange(colorCellNotation);
  var color = colorCell.getBackground();

  var data = [];

  for(var i=0;i<bg.length;i++)
    for(var j=0;j<bg[0].length;j++)
      if( bg[i][j] == color )
        data.push(values[i][j]);
  return data;
}

// Code for counting cells based by their fill color
/****************************************************************************************************
 By Sumit > https://spreadsheetpoint.com/count-cells-based-on-cell-color-google-sheets/
 Updated by LouD 2022-11-29 to work with either a semicolon or a comma (locale settings)
 Count Cells based on the Cell Color in Google Sheets.
 Use =countColoredCells(RANGE; CELLID) or =countColoredCells(RANGE, CELLID) depending on your region
 where RANGE is the cell range to count and CELLID is the CELL to compare the colors with.
*****************************************************************************************************/

function countColoredCells(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;
};


// Code for retrieving the sheet ID inside a sheet cell - Google Sheets
/*-----------------------------------------------------------------------
 From Sennsei 11/30/2017
 Updated by LouD 2022-11-10
 This function will return the spreadsheet key for your current file.
 Use =URL(id) to get the SS key, use =URL() to get the full URL.
-----------------------------------------------------------------------*/

function URL(text) {
    var str = SpreadsheetApp.getActiveSpreadsheet().getUrl();
    var id = SpreadsheetApp.getActiveSpreadsheet().getId();
     if (typeof text == 'string') {
         return id;
    } else {
         return str;
    }
 }