In sheet 1 I have a conditional rule that displays the cell as red if it has data or green if it is empty.

In sheet 2, I use the formula =Sheet1!A1 to retrieve the data from sheet 1 in the corresponding sheet 2 cells. However, I only want to retrieve the cell colour not the data value.

How can I retrieve the colour from the cell, and not the data?

CodePudding user response：

In your situation, I thought that Google Apps Script is required to be used. So, for example, how about the following sample script?

Sample script:

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

const GETCOLOR = range => SpreadsheetApp.getActiveSpreadsheet().getRange(range).getBackgrounds();

function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  if (sheet.getSheetName() != "Sheet1") return;
  const f = "=GETCOLOR";
  const t = "=sample";
  [[f, t], [t, f]].forEach(([a, b]) => e.source.createTextFinder(`^\\${a}`).matchFormulaText(true).useRegularExpression(true).replaceAllWith(b));
}

• When you use this script, please put a formula of =GETCOLOR("Sheet1!A1") and =GETCOLOR("Sheet1!A1:B10") as a custom function. By this, the background color is returned as the HEX value.

• About the added function onEdit, from In sheet 1 I have a conditional rule that displays the cell as red if it has data or green if it is empty., in this case, I thought that when a cell is edited and the background color is changed, you might want to update the custom function of GETCOLOR. In order to achieve this, I used onEdit. When the cells of "Sheet1" are edited, the custom function of GETCOLOR is updated.

Note:

• If you want to copy and paste the background color from the source range to the destination range, how about the following sample script? When you run this script with the script editor, the background color is copied.

    function sample() {
      const srcRange = "Sheet1!A1"; // Please set the source range as the A1Notation.
      const dstRange = "Sheet2!A1"; // Please set the destination range as the A1Notation.
      const ss = SpreadsheetApp.getActiveSpreadsheet();
      const bk = ss.getRange(srcRange).getBackgrounds();
      ss.getRange(dstRange).offset(0, 0, bk.length, bk[0].length).setBackgrounds(bk);
    }
  

Reference:

• Custom Functions in Google Sheets