CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
J. Doe
J. Doe

Reputation: 271

GSheet - Update the Sheet-Formel

I have create a simply formel to check the backroundcolor from a Cell:

function myTest(row, col) {
  return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(row, col).getBackgrounds();
  }

This script works fine when I change the backroundcolor for the first time. After that I must delete the formel from the cell an add it again.

What do I have to do if all formulas in the sheet are to be constantly updated?

Here is my fileexample: https://docs.google.com/spreadsheets/d/1CqZWoLy-y7bui8UnnV4B3n-vRHZ_usUyw_A74SYxtNE/edit?usp=sharing

Who has a tipp for me?

Thanks

Upvotes: 0

Views: 53

Answers (1)

Kate
Kate

Reputation: 1533

Custom functions wont automatically recompute unless a change to one of their input values occurs.

Your function is written as =mytest(ROW(A1);COLUMN(A1)), so it wont recompute unless the value of ROW(A1) or COLUMN(A1) changes -- which will never happen, given A1's row is always 1, and its column is always 1.

In general, I don't think there's a way to hook into a formatting change event in Google Sheets at this time. However, you do have some options for workarounds that can get the color codes to update after a small delay, including:

  • A: Set up a time-driven trigger for a function so it runs periodically (e.g., every 30 minutes) to manually compute the result and set the result values into column B based on the background color it observes in A when it runs.
  • B: Set up a time-driven trigger for a function so it runs periodically (e.g., every 30 minutes) to clear and then immediately re-set the formulas in column B, which will make the cells behave like the first time it ran each time you re-set them.
  • C: Figure out how to change your function signature to use something that will change in your input workbook each time you make a change to the background color. (I'm not sure how you'd be able to detect the background color automatically from inside sheets, so this seems like it'd be more complicated than it's worth for your use case.)

My sense is (A) or (B) is the simplest option to wire up given what you have.

A:

function refreshBackgroundColorCodes(event) {
  var sheet = (event ? event.source.getSheetByName("Tabellenblatt1") : null) || SpreadsheetApp.getActiveSheet();
  sheet.getRange("B:B").setValues(sheet.getRange("A:A").getBackgrounds());
}

B:

function refreshBackgroundColorCodes(event) {
  var sheet = (event ? event.source.getSheetByName("Tabellenblatt1") : null) || SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("B:B");
  var formulas = range.getFormulas();
  range.clearContent();
  SpreadsheetApp.flush();
  range.setFormulas(formulas);
}

You can also use either on-demand via a custom menu option by adding the following code:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Background Colors').addItem('Refresh color codes', 'refreshBackgroundColorCodes').addToUi();
}

Upvotes: 1

Related Questions