CODEWITHSUNDEEP
google-sheets
mediacurse
mediacurse

Reputation: 47

How to save formula value as text (automatically, via a formula)

Google Spreadsheets - I'm looking for a simple way to convert formula value into text right after the formula has been used. And it shouldn't need any involvement from me - would need it to be automatic.

Maybe replace the formula with its value right after the formula is done or just copy the result of the formula to another cell as text.

Basically I just need the formula to run only once and keep the result without launching again after a refresh. Doesn't really matter which formula we're talking about.

Options?

Upvotes: 0

Views: 1240

Answers (1)

CMB
CMB

Reputation: 5163

You can use this script running under an onEdit trigger (accessible using Tools -> Script Editor):

function onEdit(e) {
  if (e.range.getFormula()) {
    e.range.setValue(e.range.getValue());
  }
}

This replaces the formula with its computed value, and would replace only the top-left cell for formulas that return multiple values like QUERY or ARRAYFORMULA.

How to setup:

  1. Save the script and Run the script once. You should get an error TypeError that you can ignore.
  2. The script should then trigger for any user edit you do in a cell.

Upvotes: 1

Related Questions