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","")),"")

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:

• map()

CodePudding user response：

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.