Update Child Sheet When Parent Sheet Updates

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: https://docs.google.com/spreadsheets/d/1nIva4rU8DQh0E1nud90TK–t3P5jSv_xmCLbkMsh_v0/edit#gid=0
Secondary sheet: https://docs.google.com/spreadsheets/d/1qr2N3dsic67U7KXP3w61vSAV4Hth0MgIa01_dZtEiYE/edit#gid=0

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

Leave a Reply

Your email address will not be published. Required fields are marked *