Рет қаралды 841
How to update the formulas on Child sheets when the Parent or Template sheet is updated.
Important Notes:
* No script in this video should be ran manually
* The script updating other Sheets files must be set on a trigger
Sheets (Make a copy from the File menu)
Main sheet: docs.google.co...
Secondary sheet: docs.google.co...
Scripts:
function updateExternalChildSheets(e){
const src = e.source.getActiveSheet();
const r = e.range;
if (src.getName() != "Template" || r.getFormula() == "") return;
const sheets = ["1qr2N3dsic67U7KXP3w61vSAV4Hth0MgIa01_dZtEiYE"];
let ss;
for (let i in sheets){
ss = SpreadsheetApp.openById(sheets[i]).getSheets()[0];
ss.getRange(r.getA1Notation()).setFormula(e.value);
}
}
function onEdit(e) {
if (!e) throw "Do not run from Editor";
updateChildSheets(e);
}
function updateChildSheets(e){
const src = e.source.getActiveSheet();
const r = e.range;
if (src.getName() != "Template" || r.getFormula() == "") return;
const sheets = SpreadsheetApp.getActive().getSheets();
let ss;
for (let i in sheets){
ss = sheets[i];
if (ss.getName() == "Template") continue;
ss.getRange(r.getA1Notation()).setFormula(e.value);
}
}
Connect with me:
• spencer.farris@gmail.com
• spencerfarris.me
• www.linkedin.com/in/spencer-farris/
• Twitter @FarrisSpencer
• Google Product Expert support.google.com/docs/profile/12305
Donate on Paypal: spencer.farris@gmail.com