How to copy a formula from "Parent Tab" to "Child Tab" on Google Sheet

Question

I'm doing RolePlay Character Sheets on a "Parent tab" I've called "MODEL", where I masterize my formulas.

I've created a second tab "Character1" and a third one "Character2". But when I try to use =QUERY or =TEXTFORMULA or whatever. It doesn't make the formulas to calculate on the actual spreadsheet, it just get the data from the "MODEL" tab.

My only way is actually to copy/past all my formulas, but if I do a mistake, I'll have to correct it in every spreadsheet every time.

Is that possible to have a formula which take the cell at:

• MODELE!AE58

And automatically generate the same formulas in every tabs:

• CHARACTER1!AE58
• CHARACTER2!AE58
• etc...

Sorry if its blur, I'm doing my best to explain.

Answer 1

simple

Try

function onEdit(e) {
  var sh = e.source.getActiveSheet()
  var rng = e.source.getActiveRange()
  if (rng.getFormula() != '' && sh.getName() == 'MODEL') {
    var excl = ['MODEL', 'OTHER'];//excluded sheets
    SpreadsheetApp.getActiveSpreadsheet().getSheets().forEach(sh => {
      if (!~excl.indexOf(sh.getSheetName())) {
        sh.getRange(rng.getA1Notation()).setFormula(rng.getFormula())
      }
    })
  }
}

when you change a formula in MODEL, this will also change in other tabs excepts excluded ones

multiple

If you edit the formulas by dragging them into the MODEL sheet, use this one which allows you to edit all the formulas at once

function onEdit(e) {
  var sh = e.source.getActiveSheet()
  if (sh.getName() != 'MODEL') return;
  for (var i = e.range.rowStart; i <= e.range.rowEnd; i++) {
    for (var j = e.range.columnStart; j <= e.range.columnEnd; j++) {
      if (sh.getRange(i, j).getFormula() != '') {
        var excl = ['MODEL', 'OTHER'];//excluded sheets
        SpreadsheetApp.getActiveSpreadsheet().getSheets().forEach(child => {
          if (!~excl.indexOf(child.getSheetName())) {
            child.getRange(sh.getRange(i, j).getA1Notation()).setFormula(sh.getRange(i, j).getFormula())
          }
        })
      }
    }
  }
}

global

Il you need to reset all formulas, enable google sheets api and try

function onOpen() {
  SpreadsheetApp.getUi().createMenu('⇩ M E N U ⇩')
    .addItem('👉 Apply all formulas from MODEL to all tabs', 'spreadFormulas')
    .addToUi();
}
function spreadFormulas() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName('MODEL')
  if (sh.getName() != 'MODEL') return;
  var data = [];
  var formulas = sh.getRange(1, 1, sh.getLastRow(), sh.getLastColumn()).getFormulas()
  for (var i = 0; i < formulas.length; i++) {
    for (var j = 0; j < formulas[0].length; j++) {
      if (formulas[i][j] != '') {
        var excl = ['MODEL', 'OTHER'];//excluded sheets
        SpreadsheetApp.getActiveSpreadsheet().getSheets().forEach(child => {
          if (!~excl.indexOf(child.getSheetName())) {
            data.push({
              range: `${child.getName()}!${columnToLetter(+j + 1) + (+i + 1)}`,
              values: [[`${formulas[i][j]}`]],
            })
          }
        })
      }
    }
  }
  if (data.length) {
    var resource = {
      valueInputOption: 'USER_ENTERED',
      data: data,
    };
    try { Sheets.Spreadsheets.Values.batchUpdate(resource, ss.getId()); } catch (e) { console.log(JSON.stringify(e)) }
  }
}
function columnToLetter(column) {
  var temp, letter = '';
  while (column > 0) {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}