Copy Values and Formulas Between Spreadsheets

/ Snippets, Tutorials

A question on StackOverflow made me realize that there seems to be no method to copy values and formulas between ranges in different spreadsheets. 

Using setValues() just transfers values, including the final result of formulas. On the other hand, using setFormulas() just transfers formulas, leaving blank cells that don’t have formulas.

If the ranges are from the same spreadsheet, then we can rely on copyTo() to copy values, formulas, and even formatting. However, using this method with ranges from different spreadsheets gives us Exception: Target range and source range must be on the same spreadsheet.

Here’s a simple workaround for this situation:

  1. Use getValues() and getFormulas() to get values and formulas in the source range.
  2. Set values to the destination range with setValues().
  3. Using the result of getFormulas(), replace the contents of cells in the destination range that should have formulas instead of values.

Sample implementation:

function copyValuesAndFormulas(sourceRange, destinationRange) {
  const values = sourceRange.getValues();
  const formulas = sourceRange.getFormulas();

  destinationRange.setValues(values);

  for (let i = 0; i < formulas.length; i++) {
    for (let j = 0; j < formulas[i].length; j++) {
      if (formulas[i][j]) {
        destinationRange.getCell(i+1, j+1).setFormula(formulas[i][j]);
      }
    }
  }
}

Note that this workaround works for the majority of cases, but there is a couple of caveats:

  • It can be slow if there are many cells with formulas since they would all be accessed individually instead of in a batch.
  • It won’t work if ARRAYFORMULA is used, since the copied values would not be overwritten by ARRAYFORMULA, making it throw an error. A more specific approach is required in this case to transfer only the formula, and not the values created by it.