I made a read-only version that updates automatically once a day and when opened by someone with write-access to this copy.
https://docs.google.com/spreadsheets/d/153VRoGi9QiZnbTEV2mTlxMbDW7QHSGKe5WFwAL_DFgw
Most of the formatting is copied over automatically, but there may be some tweaking needed as the formatting is fine-tuned.
Updating script:
function OverwriteAll() {
var targetSpreadsheet = SpreadsheetApp.getActive();
var sourceSpreadsheet = SpreadsheetApp.openById("1GGsGcjFBCr5xFSfN4-WmiFCYvqg7qci7_r6hxa4gvKM");
var sheetNames = ["FE1", "FE2", "FE3", "BSFE", "FE4", "FE5", "FE6", "FE7", "FE8", "FE9", "FE10", "FE11", "FE12", "FE13", "FE14", "FE15", "FE16", "FE17", "Tactile/FEXNA", "Lex Talionis", "SRPG", "FEXP", "OTHER"];
sheetNames.forEach(function(sheetName) {
var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
var targetSheet = targetSpreadsheet.getSheetByName(sheetName);
if (!sourceSheet || !targetSheet) {
Logger.log("Sheet not found: " + sheetName);
return;
}
// Copy data and formatting
copySheetDataAndFormatting(sourceSheet, targetSheet);
SpreadsheetApp.flush();
});
}
function copySheetDataAndFormatting(sourceSheet, targetSheet) {
var sourceRange = sourceSheet.getDataRange();
var targetRange = targetSheet.getRange(1, 1, sourceRange.getNumRows(), sourceRange.getNumColumns());
targetRange.clear(); // removes merged cells formatting I think
// Copy values
// includes font colours, underlines, bold, italics, strikethrough, size
var values = sourceRange.getRichTextValues();
targetRange.setRichTextValues(values);
targetRange.setShowHyperlink(true);
// Copy additional formatting
targetRange.setBackgrounds(sourceRange.getBackgrounds());
targetRange.setHorizontalAlignments(sourceRange.getHorizontalAlignments());
targetRange.setVerticalAlignments(sourceRange.getVerticalAlignments());
}
I think this could help prevent accidental vandalism, and encourage sorting without disrupting the view of the main spreadsheet (eg. they must file->make a copy instead to sort).