Copying both values and formulas between 2 different Google Spreadsheets

Question

I'm currently trying to copy a range of cells from Spreadsheet A to Spreadsheet B. In this range I have a combination of values and formulas which I'm trying to bring across.

I've tried using the .copyTo method with: SpreadsheetA.getRange("A:E").copyTo(SpreadsheetB.getRange("A:E"), {contentsOnly:true});

But this errors as .copyto will only work within the same spreadsheet and not if they're separate. I've also tried using .setValues() and .setFormulas() but they seem to overwrite each other if one is used after another. I need a function such as below:

CopyData(SourceSpreadsheet, DestinationSpreadsheet, SourceRange, DestinationRange)

Which will copy both formulas and values depending on what they are. Thanks so much in advance if anyone has an example! You'll be a lifesaver.

Answer 1

More than setValues() and setFormulas(), I'd say the problem starts with getValues() and getFormulas(): getValues() only gets values (including the final result of formulas), and getFormulas() only get formulas, not "simple" values.

So one option would be to set the values for the whole range, then replace the values with formulas for only specific cells:

1. Get the values and the formulas separately with getValues() and getFormulas().
2. Set the values for the range using setValues().
3. Go over the cells of the formulas array and check if it has something in it (which indicates it's a formula, not a "simple" value). If it does, then you use setFormula() on the destination range's corresponding cell and pass the formula from the formulas array.

Something like this:

function copyValuesAndFormulasBetweenSpreadsheets(sourceSpreadsheet, sourceRangeA1Notation, destinationSpreadsheet, destinationRangeA1Notation) {
  const values = sourceSpreadsheet.getSheets()[0].getRange(sourceRangeA1Notation).getValues();
  const formulas = sourceSpreadsheet.getSheets()[0].getRange(sourceRangeA1Notation).getFormulas();
  const range = destinationSpreadsheet.getSheets()[0].getRange(destinationRangeA1Notation);
  
  range.setValues(values);
  for (let i = 0; i < formulas.length; i++) {
    for (let j = 0; j < formulas[i].length; j++) {
      if (formulas[i][j]) {
        range.getCell(i+1, j+1).setFormula(formulas[i][j]);
      }
    }
  }
}

The downside of this approach is that, if there are many formulas, you may need to go over many cells individually, which may take some time depending on the range.

Answer 2

Based on @mshcruz's comment I made a small improvement to not have .get/.set for every cell:

function copyValuesAndFormulasBetweenSpreadsheets(sourceSpreadsheet, sourceRangeA1Notation, destinationSpreadsheet, destinationRangeA1Notation) {
  const values = sourceSpreadsheet.getSheets()[0].getRange(sourceRangeA1Notation).getValues();
  const formulas = sourceSpreadsheet.getSheets()[0].getRange(sourceRangeA1Notation).getFormulas();
  destinationSpreadsheet.getSheets()[0].getRange(destinationRangeA1Notation).setValues(values);
  
  for (let i = 0; i < formulas.length; i++) {
    for (let j = 0; j < formulas[i].length; j++) {
      if (formulas[i][j]) {
        const cell = range.getCell(i+1, j+1);
        cell.setFormula(formulas[i][j]);
      }
    }
  }
  
}