CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
jgjuara
jgjuara

Reputation: 11

How to make a custom function made in Google Apps Script reactive to changes in the background of a cell in Google Sheets?

I have this function that returns the hex code of the background color of the pointed cell. Its ok so far but I need this to be reactive to changes in the background color of the pointed cell. As it is, every time the background color of the cell changes I have to call again the function in order to it takes notice of that the background color has changed. There is any way to make this function reactive to changes in the background of thhe pointed cell?

  function color(input) {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var activeRange = SpreadsheetApp.getActiveRange();
  var formula = activeRange.getFormula();
  var address = formula.substr(7,2);

  var cell = ss.getRange(address); 
  var result = cell.getBackground(); 
  return result;
}

Upvotes: 0

Views: 238

Answers (2)

Ismail
Ismail

Reputation: 644

You should try this

function onChange(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  console.log(ss.getCurrentCell().getA1Notation() + " onchange" );
}

This log will be available to you in trigger executions logs section.

Upvotes: 0

Cooper
Cooper

Reputation: 64100

onEdit() is not triggered by background changes but as it turns out onChange is and the changeType is FORMAT

Here's a copy of the event object:

{"authMode":"FULL","changeType":"FORMAT","source":{},"triggerUid":"","user":{"email":"","nickname":""}}

onChange event object

But I don't think that there is a way to take advantage of this with a custom function if what you mean by custom function is found here

Try this:

function onMyChange(e) {
  if(e.changeType == 'FORMAT') {
    SpreadsheetApp.getActive().toast(SpreadsheetApp.getActiveRange().getA1Notation());
  }
}

Upvotes: 1

Related Questions