Google Apps Script: Copy/Paste Ranges

Walden Systems Geeks Corner News Google Apps Script: Copy/Paste Ranges Rutherford NJ New Jersey NYC New York City North Bergen County

When working with google spreadsheets i often find myself copying and pasting various values from one sheet range to another. There are multiple ways to copy data from one range to another, and will depend on the type of work you need to perform. I will outline a few copy functions that I have been using. Of course things can get complicated depending on the data being moved around, but the following few can get most of the job done quickly.

Let's start with a simple rangeFrom.copyTo(rangeTo) function. This function will copy all the data from a range of cells to another range of cells. The copy will include all the values and formatting. It is pretty straight forward, you supply the range from which to copy and the range to copy to. 


function copyRangeToRange(rangeFrom, rangeTo) {
  rangeFrom.copyTo(rangeTo);
};


The more complicated version of the same copy command can include the ‘copyPasteType’ and the ‘transposed’ flag. There are various ‘copyPasteType’ available, but I will use and list the ones that I find most useful: 


SpreadsheetApp.CopyPasteType.PASTE_NORMAL SpreadsheetApp.CopyPasteType.PASTE_FORMULA SpreadsheetApp.CopyPasteType.PASTE_VALUES


Our new copy function takes four parameters this time, to account for the ‘copyType’ and ‘transposed’ flag. For example, the transposed flag will take data from rows with multiple columns, and transpose it to a single column with multiple rows. 


function copyRangeToRangeWithType(rangeFrom, rangeTo, copyType, transposed) {
  rangeFrom.copyTo(rangeTo, copyType, transposed);
};

The following is a full example of calling the functions outlined above. 


function copyRangeToRange(rangeFrom, rangeTo) {
  rangeFrom.copyTo(rangeTo);
};

function copyRangeToRangeWithType(rangeFrom, rangeTo, copyType, transposed) {
  rangeFrom.copyTo(rangeTo, copyType, transposed);
};

function mainCopyRange() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); 
  var rangeFrom = sheet.getRange(1,1,1,5);
  var rangeTo = sheet.getRange(10,1);
  copyRangeToRange(rangeFrom, rangeTo );
  copyRangeToRangeWithType(rangeFrom, rangeTo, SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  copyRangeToRangeWithType(rangeFrom, rangeTo, SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
  copyRangeToRangeWithType(rangeFrom, rangeTo, SpreadsheetApp.CopyPasteType.PASTE_VALUES, true);
};