CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets-formula
HSHO
HSHO

Reputation: 525

Using GETBACKGROUNDCOLOR function to write in cell But it is slow

I am using this functions to write values in cell.

For example if cell has specific color then write value to cell and this function is slow. Is there any fast function to write values in cell if it has specific color.

There is one more problem that is these formatting changes according to Conditional Formatting Rules but when it changes then formula does not change the Value accordingly. It should be dynamic it cell color changes then formula must observe.

I have tried with below function but it is slow.

=IF($B2<>"",IF(GETBACKGROUNDCOLOR(cell("address", Data!P3)) = "#f4cccc", "MISSING",IF(Data!P3="P","Pending","")),"")

Sheet Link

function GETBACKGROUNDCOLOR(cell){
  return SpreadsheetApp.getActiveSheet().getRange(cell).getBackground();


}

Upvotes: 1

Views: 119

Answers (2)

kishkin
kishkin

Reputation: 5325

You can change your function a bit (notice the s at the end) to work with ranges instead of a single cell:

function GETBACKGROUNDCOLORS(range){
  return SpreadsheetApp.getActiveSheet().getRange(range).getBackgrounds();
}

And then use ARRAYFORMULA with it:

=ARRAYFORMULA(
  IF(
    B2:B27 = "",,
      IFS(
        GETBACKGROUNDCOLORS("Data!P3:AM28") = "#f4cccc",
          "MISSING",
        Data!P3:AM28 = "P",
          "Pending",
        True,
      )
  )
)

Both those steps will increase performance significantly.

See the array solution sheet in your sample spreadsheet.

Upvotes: 1

Tanaike
Tanaike

Reputation: 201398

I believe your goal is as follows.

  • You want to reduce the process cost for achieving the result situation by the formula like =IF($B2<>"",IF(GETBACKGROUNDCOLOR(cell("address", Data!P3)) = "#f4cccc", "MISSING",IF(Data!P3="P","Pending","")),"").
  • You are required to dynamically update the values when the cell values of Data sheet are changed.

In this case, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor and save it. And, when your sample Spreadsheet is used, please put a custom function of =SAMPLE(Data!P3:AM,"Data!P3:AM",B2:B) to the cell "G2" of "Formula" sheet. By this, the values are put.

In this sample script, 3 arguments of Data!P3:AM,"Data!P3:AM",B2:B are used. Those are used as the cell values of "Data", the range for retrieving the background colors, and the cell values of "Formula". Data!P3:AM and B2:B are used. By this, when those cells are changed, the custom function is recalculated. I thought that this might be the result you expect.

function SAMPLE(srcValues, srcRange, dstValues) {
  const srcBackgrounds = SpreadsheetApp.getActiveSpreadsheet().getRange(srcRange).getBackgrounds();
  return srcValues.map((r, i) => r.map((c, j) => {
    if (dstValues[i][0] != "") {
      if (srcBackgrounds[i][j] == "#f4cccc") {
        return "MISSING";
      }
      return c == "P" ? "Pending" : "";
    }
    return "";
  }));
}

Result:

When this script is used for your sample Spreadsheet, the following result is obtained.

enter image description here

Note:

  • In this sample script, the custom function is =SAMPLE(Data!P3:AM,"Data!P3:AM",B2:B). And, the arguments of Data!P3:AM,"Data!P3:AM",B2:B. The 2nd argument is the string type. Please be careful about this.
  • When I tested my proposed script using your sample Spreadsheet, the result values are shown soon. But I'm not sure about your actual situation. When the processing speed is not fast, please reopen the Spreadsheet and test it again.
  • This sample script is for your sample Spreadsheet. When your actual Spreadsheet is different from your sample Spreadsheet, the script might not be able to be used. Please be careful this.

Reference:

Upvotes: 1

Related Questions