jeudi 30 septembre 2021

Google Sheets Apps Script - How to add an Arrayformula and multiple associated IF functions within a script (Without showing the formula within UI)

I was wondering if someone is able to assist?

I'm trying to add an Arrayformula consisting of two IF functions, so I'm wanting to merge the following two formulas into one cell:

  1. =ARRAYFORMULA(IF(D13:D104="","",(IF(K13:K104,K13:K104*20,"$0"))))
  2. =ARRAYFORMULA(IF(D105:D="","",(IF(K105:K,K105:K*C4,"$0"))))

So the first section of the sheet needs to be multiplied by 20, and then the figure has changed and needs to be multiplied by 25 (which is cell C4)

  1. Is it possible to merge these into one cell containing an Arrayformula+the two IF functions (or is there another/easier way for this)
  2. Is it possible to add this into Google Apps Script so that it works in the backend (so not just a script that applies the formula into a cell - but doesn't show in the frontend or sheet)
  3. More of a general question - When using Arrayformula with IF; and for example the output is specific text e.g. "Test Complete" associated to the range F2:F (checking if E2:E contains a particular phrase e.g. "Done") - for the empty cells in between (due to setting the False outcome as "") is it possible to somehow randomly add data into these blank cells without interrupting the formula? (so I have to option for automated text if the cell to the left states a particular term/word, but still have the option to manually add random data into blank cells)

Any assistance would be greatly appreciated

Aucun commentaire:

Enregistrer un commentaire