Template code to sync values and formulas between two Google Sheets using App Script

sync_gsheets_app_script.js

function copySheets() {
  sheetIDsToUpdate = [
    // [sourceID, destID]
    ["source_google_sheets_id", "dest_google_sheets_id"]
  ]

  for (let i = 0; i < sheetIDsToUpdate.length; i++) {
    sourceID = sheetIDsToUpdate[i][0]
    destID = sheetIDsToUpdate[i][0]

    importRange(sourceID, "source_range", destID, "dest_range"); // example: Sheet1!A1:BL
  }
}


function importRange(sourceID, sourceRange, destID, destionationRangeStart ) {
  const sourceSS = SpreadsheetApp.openById(sourceID);
  const sourceRng = sourceSS.getRange(sourceRange);
  const sourceVal = sourceRng.getValues();
  const sourceFormula = sourceRng.getFormulas();

  const destSS = SpreadsheetApp.openById(destID);
  const destStartRange = destSS.getRange(destionationRangeStart);
  const destSheet = destSS.getSheetByName(destStartRange.getSheet().getName());

  const destRange = destSheet.getRange(
    destStartRange.getRow(),
    destStartRange.getColumn(),
    sourceVal.length,
    sourceVal[0].length
  )

  var data = sourceVal.map(function(row, i) {
    return row.map(function(col, j) {
      return sourceFormula[i][j] || col;
    });
  });

  destRange.setValues(data);
}