CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
jlo
jlo

Reputation: 2269

What's the most efficient way to copy a column of cells from one spreadsheet to another?

I am trying to copy one column of cells from a spreadsheet to another (append it at the bottom). The code below works, but I was wondering if it's possible to do this without a loop. Is there a faster or more efficient way of doing this?

function CopyToAnotherSheet() {

  var sourceSpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  var srcSheet = sourceSpreadSheet.getSheets()[0];

  var destinationSpreadSheet = SpreadsheetApp.openById('15-vXNpnzSEKzcqhBmJ_D173rwGyM7TOAZE1iL_wsf2A');
  var destSheet = destinationSpreadSheet.getSheets()[0]; 

  // Get the contents of a cell in srcSheet
  var range = srcSheet.getRange("xposed!A1:A")
  var values = range.getValues();

  for (var i = 0; i < values.length; i++) {
      destSheet.appendRow(values[i]);
      }
}

Cheers!

Upvotes: 0

Views: 75

Answers (1)

shabnam bharmal
shabnam bharmal

Reputation: 584

you can use the getRange() method with setValues to setValues() to set the range as the array only. Refer this documentation for getting a clear idea Document Link

function CopyToAnotherSheet() {

  var sourceSpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  var srcSheet = sourceSpreadSheet.getSheets()[0];

  var destinationSpreadSheet = SpreadsheetApp.openById('15-vXNpnzSEKzcqhBmJ_D173rwGyM7TOAZE1iL_wsf2A');
  var destSheet = destinationSpreadSheet.getSheets()[0]; 

  // Get the contents of a cell in srcSheet
  var range = srcSheet.getRange("xposed!A1:A")
  var values = range.getValues();

  //returns last row of the destination sheet
  var lastRow=destSheet.getLastRow();

  //starting from the last row, it will apend the array in the column
  //getrange(num of row to start from, num of column to start from, number of rows in array to append, num of column in array to append)
  destSheet.getRange(lastRow+1, 1, values.length,1).setValues(values);

}

Upvotes: 1

Related Questions