In Google Sheet/ Google App Script, when i edit a cell, i want to call function on another cell, but dosnt work

Question

i have a little problem. On my Google Sheet, i want to : When i change background color of a cell, i want to write in another cell the HEX code of the color that i changed. I do a little code who get the background color of the cell, but when i edit the color of the cell, ma cell where is the HEX Code, doesn't update. I put here my code to get the HEX code color of the other cell.

function GetCellColorCode(input)
  {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var cell = ss.getRange(input)
    return cell.getBackground();
  }

I put an image of my Excel Table. For exemple i call this function on L4 cell and i put in parameters the C4 cell. The code work well the first time i call the function, but when i update the color of the other cell, nothing is happening When i update the background color of C4 cell, i wont that the L4 cell update his value automatically.

this is my excel table

Thanks for your help

There is no solution. The onEdit trigger does not fire when you change background color. — Cooper
If you want to create your own color changer on the sidebar for example you could change the background and set the code in the adjacent cell. But it's not going to work with the standard interface. — Cooper

Cooper Cooper · Accepted Answer · 2020-01-23T21:40:09

This is a crude and quickway of doing it.

function customBGDialog() {
  var ss=SpreadsheetApp.getActive();
  var html='<html><head><script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script><link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css"><script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>';
  html+='</head><body>'; 
  html+='<h1>Change Background Color Of Active Cell<h1>';
  html+='<br /><select id="sel1"><option value="#ffff00">Yellow</option><option value="#ff0000">Red</option><option value="#0000ff">Blue</option></select><input type="button" value="Change Background" onClick="doIt()" />';
  html+='<script>function doIt(){google.script.run.changeBackground($("#sel1").val());}</script>';
  html+='</body></html>';
  var userInterface=HtmlService.createHtmlOutput(html);
  SpreadsheetApp.getUi().showSidebar(userInterface);
}

function changeBackground(color) {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  sh.getActiveCell().setBackground(color);
  sh.getActiveCell().offset(0,1).setValue(color);
}

Animation:

In Google Sheet/ Google App Script, when i edit a cell, i want to call function on another cell, but dosnt work

1 Answers