I have a script (from here) that changes tab name based on cell value. This works great on the active sheet but I would like it to check and change all tabs based on cell B2 of each tab.

function onEdit() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var oldName = sheet.getName();
  var newName = sheet.getRange(1,1).getValue();
  if (newName.toString().length>0 && newName !== oldName) {

I've researched and cannot quite figure out how to make a script run across all tabs.

  • Not clear why you need this functionality in a script that runs on every edit. If there is no edit to a sheet, then its B2 is same as it was, so it doesn't have to be checked. – user135384 Mar 16 '18 at 21:27
  • This is a file that takes specs off a blueprint and the sheets work in pairs. e.g., Sheet 1 is named by user in cell B2 and could be named Slab. Sheet 2 looks at sheet 1!B2 and names the sheet Calc4Slab. Sheet 1 is the input from the user and Sheet 2 performs the calculations for total cost of project. – Naomi Mar 17 '18 at 14:30
  • Since sheet 2 name change is not an actual "on edit", the way I have this written does not change the tab name for Sheet 2 (sheet 2 is never actually the active sheet). There are several "pairs" of tabs. In Excel when there is a change to "B2" on any sheet the macro updates all sheets. I am a very new user of google sheets and apps script and cannot figure out how to make the script update all sheets instead of just one. – Naomi Mar 17 '18 at 14:49

Spreadsheet, the class representing the workbook, has a function that returns an array of all tabs:

   function onEdit() {
      var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
      for(var i = 0; i <sheets.length; i++){
        var oldName = sheets[i].getName();
        var newName = sheets[i].getRange("b2").getValue();
        if (newName.toString().length>0 && newName != oldName) {
  • Thank you very much for answering my question. Works perfectly! – Naomi Apr 3 '18 at 14:54
  • This does change and refreshes the sheet names in cell B2 but now all the INDIRECT formulas return a #REF! error, stating, " It is not a valid cell/range reference". How can I get the INDIRECT formulas to work when the source in B2 changes? – Naomi Apr 3 '18 at 16:20
  • You could use the script to update the formulas. You could use B2 as the source for the sheet name in the indirect function. can you use the standard sheet!A1 notation instead of indirect? support.google.com/docs/answer/… – Jared Pinkham Apr 4 '18 at 0:36
  • Thank you for your assistance Jared. I was originally using cell B2 as source & INDIRECT formulas would not work. =INDIRECT(B2&"!C65"). It is like the formulas will not refresh. I've done a rather lengthy work around. 1) Created a Table of Contents tab listing all sheets. This updates when a sheet name changes (added to on edit script) . 2)In a cell on each of the 'linked sheets' I created a source cell using a vlookup referencing the B2 cell in the Table of Contents. 3) I use this 'source cell' in the INDIRECT formula & they now work. PHEW! been a long day in world of data havoc! – Naomi Apr 4 '18 at 1:20
  • How do I restrict the onEdit trigger to run only when there is a change in cell B2 on any sheet? I know it has to do with setting e.values-- OnEdit(e) but I don't know where to begin. – Naomi Apr 4 '18 at 23:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.