Sync Google Sheets Data Across Multiple Sheets
There is a requirement to synchronize all sheets from one Google Spreadsheet to another spreadsheet (including all sub-sheets).
This is implemented using Google Sheets → Extensions → Apps Script.
Why synchronization is needed
The source spreadsheet is read-only by default.
By synchronizing its data into another spreadsheet, the copied sheets can then be edited freely.
sourceSpreadsheetId and targetSpreadsheetId are the IDs from the Google Sheets URL —
they are the long strings after /d/ in the address bar.
Apps Script Code Example
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
| function synchronizeSheetsToAnotherFile() {
const sourceSpreadsheetId = "id1";
const targetSpreadsheetId = "id2";
const sourceSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetId);
const targetSpreadsheet = SpreadsheetApp.openById(targetSpreadsheetId);
const sourceSheets = sourceSpreadsheet.getSheets();
sourceSheets.forEach(sheet => {
const sheetName = sheet.getName();
let targetSheet = targetSpreadsheet.getSheetByName(sheetName);
if (!targetSheet) {
targetSheet = targetSpreadsheet.insertSheet(sheetName);
} else {
targetSheet.clear();
}
const range = sheet.getDataRange();
const displayValues = range.getDisplayValues();
const formats = range.getBackgrounds();
const fontWeights = range.getFontWeights();
const targetRange = targetSheet.getRange(
1,
1,
displayValues.length,
displayValues[0].length
);
targetRange.setValues(displayValues);
targetRange.setBackgrounds(formats);
targetRange.setFontWeights(fontWeights);
});
}
|