Google Apps Script: Clone Data Up to Column J

Note: Google Apps Script to Clone Data Up to Column J

Use the following function to replicate all sheets from a source Google Spreadsheet to a target Spreadsheet, copying only columns A-J. Existing sheets in the target with the same name will be replaced, and any columns beyond J will be deleted.

function replicateSheetsUpToColumnJ() {
 const sourceSheetId = '1smwCvKnRCMRHrlt2NzmIRb0_9q8Oo2aNff9pfyEaGws'; // Your source spreadsheet ID
 const targetSheetId = '1MYeY0A_58IDAz4e6q98kEZCXx_W0KbACT5qoCIzhbnU'; // Your target spreadsheet ID

 const sourceSpreadsheet = SpreadsheetApp.openById(sourceSheetId);
 const targetSpreadsheet = SpreadsheetApp.openById(targetSheetId);

 const sourceSheets = sourceSpreadsheet.getSheets();

 for (const sheet of sourceSheets) {
 const sheetName = sheet.getName();

 // Get last row with data
 const lastRow = sheet.getLastRow();
 if (lastRow === 0) continue; // Skip empty sheets

 // Get data from columns A-J only
 const dataRange = sheet.getRange(1, 1, lastRow, 10);
 const data = dataRange.getValues();

 // Remove existing sheet with same name in target
 let targetSheet = targetSpreadsheet.getSheetByName(sheetName);
 if (targetSheet) {
 targetSpreadsheet.deleteSheet(targetSheet);
 }

 // Create new sheet in target
 targetSheet = targetSpreadsheet.insertSheet(sheetName);

 // Set values in target sheet
 targetSheet.getRange(1, 1, data.length, data[0].length).setValues(data);

 // Delete extra columns beyond J if any
 const maxColumns = targetSheet.getMaxColumns();
 if (maxColumns > 10) {
 targetSheet.deleteColumns(11, maxColumns - 10);
 }
 }

 SpreadsheetApp.flush();
 Logger.log("Replication up to column J complete");
}

Usage:

A.M. Rinas

Written by A.M. Rinas

Full-Stack Software Engineer working on web applications, DevOps automation, and databases. More about me on the about page, or say hi through the contact page and LinkedIn.

← Back to all articles