Copy Google Spreadsheet Data to another Sheet with Apps Script

CodeCanyon · May 2

The cloneGoogleSheet() function will copy data (all rows and columns, but no formatting styles) from one Google Spreadsheet to any other Google Spreadsheet under the same Google Drive.

You need specify the file IDs of the source and destination Google Spreadsheets as arguments in the formula and also change the source and target sheet names inside the method body.

This function can be invoked via a time-based trigger or run it manually from the Apps Script editor. However, if you would like to keep the two spreadsheet in sync with each other always, you an consider using the IMPORTRANGE() Google formula that automatically imports a range of cells from a specified spreadsheet into the currently selected cell / range /sheet.

// copy data from Google Sheet A to Google Sheet B
// Credit: @chrislkeller

function cloneGoogleSheet(ssA, ssB) {
  // source doc
  var sss = SpreadsheetApp.openById(ssA);

  // source sheet
  var ss = sss.getSheetByName('Source spreadsheet');

  // Get full range of data
  var SRange = ss.getDataRange();

  // get A1 notation identifying the range
  var A1Range = SRange.getA1Notation();

  // get the data values in range
  var SData = SRange.getValues();

  // target spreadsheet
  var tss = SpreadsheetApp.openById(ssB);

  // target sheet
  var ts = tss.getSheetByName('Target Spreadsheet');

  // Clear the Google Sheet before copy
  ts.clear({ contentsOnly: true });

  // set the target range to the values of the source data
  ts.getRange(A1Range).setValues(SData);
}