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:

• Replace the sourceSheetId and targetSheetId with your actual spreadsheet IDs.
• Run the function in Google Apps Script editor.
• All sheets and their data up to column J will be cloned to the target spreadsheet.

Written by A.M. Rinas