Syncing Google Sheets without losing formatting

Question

Im trying to sync sheets from a master spreadsheet to individual sheets saved together in a google folder (such that any changes in mastersheet will be displayed in the individual sheets and these would be accessible while keeping the mastersheet private). I tried using ImportRange and also using apps scripts to sync them, but only the data gets brought over and the formatting is lost. Any way to go about this?

Referenced this from Is there a way to keep two sheets synchronized? what can i add to retain the formatting

function onOpen(e){
  SpreadsheetApp.getUi().createMenu('Sync')
    .addItem('Sync with accessible sheets', 'importData')
    .addToUi();
}

var sourceSpreadsheetID = "id1";
var sourceWorksheetName = "name";

var destinationSpreadsheetID = "id2";
var destinationWorksheetName = "Sheet1";

function importData() {
  var thisSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetID);
  var thisWorksheet = thisSpreadsheet.getSheetByName(sourceWorksheetName);
  var thisData = thisWorksheet.getDataRange();
  var toSpreadsheet = SpreadsheetApp.openById(destinationSpreadsheetID);
  var toWorksheet = toSpreadsheet.getSheetByName(destinationWorksheetName);
  var toRange = toWorksheet.getRange(1, 1, thisData.getNumRows(), thisData.getNumColumns())
  toRange.setValues(thisData.getValues()); 
}

Answer 1

If what you're aiming for is to have changes in the private source spreadsheet copied to the exposed sheets, overwriting whatever is currently in the target sheets, then one approach would be to delete and re-copy each sheet with copyTo. This allows you to duplicate an entire sheet from one spreadsheet to another. Usage would look something like:

const fromSpreadsheet = SpreadsheetApp.openById(sourceID);
const toSpreadsheet = SpreadsheetApp.openById(targetID);
const fromSheet = fromSpreadsheet.getSheets()[0]; // You could loop through each sheet and reference them by index here
fromSheet.copyTo(toSheet);