Google Apps Script: Copy and Paste Formulas Only

3
votes

I need to copy and paste Formulas only in a spreadsheet using Google Apps Script

var range = activeSheet.getRange(targetRow-1, 1, 1, activeSheet.getLastColumn());
range.copyTo(activeSheet.getRange(targetRow, 1, 1, activeSheet.getLastColumn()), {contentsOnly:false});

This give me the full information, including the formulas and data. However, I wish to only copy the formulas (without data).

CopyFormulasToRange unfortunately does not exists.

2
javascriptgoogle-apps-scriptgoogle-sheets

2 Answers

6
votes

The following function will copy formulas only. It has user settings for what row and column to start getting the formulas from.

function copyFormulas() {
  var activeSheet,numberOfSourceColumnsToGet,sourceColumnStart,sourceFormulas,sourceRange,
      sourceRowStart,targetColumn,targetRange,targetRowStart;

  //USER INPUT

  sourceRowStart = 1; //Row to start getting formulas from
  sourceColumnStart = 2; //Column to start getting formulas from
  numberOfSourceColumnsToGet = 1; //Number of columns to get formulas from

  targetRowStart = 1; //Row to start copying formulas to
  targetColumn = 3; //Column to start copying formulas to

  //END OF USER INPUT

  activeSheet = SpreadsheetApp.getActiveSheet();
  sourceRange = activeSheet.getRange(sourceRowStart, sourceColumnStart, activeSheet.getLastRow(), numberOfSourceColumnsToGet);

  sourceFormulas = sourceRange.getFormulas();//Get only formulas from the source range

  targetRange = activeSheet.getRange(targetRowStart,targetColumn,sourceFormulas.length,sourceFormulas[0].length);

  targetRange.setFormulas(sourceFormulas);//Copy the formulas to the target range
}
8
votes

Use getFormulasR1C1 and setFormulasR1C1 to copy formulas and preserve relative references. For example:

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");

  var currentRow = sheet.getLastRow();

  var sourceRange = sheet.getRange(currentRow, 3, 1, 4);
  var sourceFormulas = sourceRange.getFormulasR1C1();

  currentRow++;

  var targetRange = sheet.getRange(currentRow, 3, 1, 4);
  targetRange.setFormulasR1C1(sourceFormulas);

https://developers.google.com/apps-script/reference/spreadsheet/range#getformular1c1 https://developers.google.com/apps-script/reference/spreadsheet/range#setformulasr1c1formulas