Copy Google Spreadsheet Data to another Sheet with Apps Script
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);
}
Amit Agarwal
Google Developer Expert, Google Cloud Champion
Amit Agarwal is a Google Developer Expert in Google Workspace and Google Apps Script. He holds an engineering degree in Computer Science (I.I.T.) and is the first professional blogger in India.
Amit has developed several popular Google add-ons including Mail Merge for Gmail and Document Studio. Read more on Lifehacker and YourStory
