How to replace all the formulas with the values they have calculated?

0
votes

Lets say I have a sheet called Sheet1, and a sheet called Sheet2

On sheet 2 I have formulas which copy values from sheet one, in the fashion of:

='Sheet1'!A1
='Sheet1'!A2
='Sheet1'!A3

There is no consistent row or column count, it might be 5X10, or it might be 20X50 rows/columns.

I then want to make a script which will take the value that has been calculated in each cell, and replace the formula with that value.

The objective is to preserve the values without creating a reference error when I later delete Sheet1. Is there a simple way to do this using a script?

1
google-apps-scriptgoogle-sheets

1 Answers

2
votes

Use getDisplayValues() and setValues()

function destroyFormulas() {
  var rng = SpreadsheetApp
    .getActive()
    .getActiveSheet()
    .getRange("A1:B3"), //or getDataRange()
    values = rng.getDisplayValues(); //or getValues()
  rng.setValues(values);
}