Rename tabs to one cell from another in the same Sheet

Question

I'd like to have a google script, where: 1-Tab name changes are applied to A1 to A15 from Sheet1 2-Values are cells from B1 to B15 from the same Sheet1

I currently have this script that works perfectly, but the changes are required directly from cells A1 to A15, so the purpose of what I want, it don't work:

function onEdit(e) {
  var planilha = SpreadsheetApp.getActiveSpreadsheet();
  var aba = planilha.getActiveSheet();
  var celula = e.range;
  var linha = celula.getRow();
  var coluna = celula.getColumn();
  if (coluna == 1) {
    var nome = celula.getValue();
    if (nome != "") {
      var abas = planilha.getSheets();
      abas[linha].setName(nome);
    }
  }
}

Hi, can you please clarify what you are missing? If I understand correctly you want to limit changes to cells in Column A from row 1 to row 15, but currently are only checking for column 1. There are a few ways to approach that. Either add another `if` to check for the row as well or use `getA1Notation()` and then use a regex compare to figure out if it meets the criteria — Vytautas, Dec 15 '22 at 14:16

score 0 · Answer 1 · answered Dec 15 '22 at 14:46

To rename tabs to the names in Sheet1!B1:B when Sheet1!A1:A is modified, try this:

/**
* Simple trigger that runs each time the user hand edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  if (!e) {
    throw new Error(
      'Please do not run the onEdit(e) function in the script editor window. '
      + 'It runs automatically when you hand edit the spreadsheet. '
      + 'See https://stackoverflow.com/a/63851123/13045193.'
    );
  }
  renameTabs_(e);
}


/**
* Renames tabs to a name in Sheet1!B1:B15 when Sheet1!B1:B15 is manually edited.
* Cell B1 corresponds to the second sheet, cell B2 to the third sheet, and so on.
*
* @param {Object} e The onEdit() event object.
*/
function renameTabs_(e) {
  if (!e.value
    || e.range.columnStart !== 1
    || e.range.rowStart > 15
    || !e.range.getSheet().getName().match(/^(Sheet1)$/i)) {
    return;
  }
  const sheetName = e.range.offset(0, 1).getDisplayValue();
  if (sheetName) {
    e.source.getSheets()[e.range.rowStart]
      .setName(sheetName);
  }
}

score 0 · Answer 2 · answered Dec 15 '22 at 17:29

With this approach you can change all of the sheets names from SpreadsheetApp.getActive().getSheets()[0];

function onEdit(e) {
  e.source.toast('Entry');
  const sh = e.range.getSheet();
  const shts = e.source.getSheets();
  if (sh.getName() == shts[0].getName() && e.range.columnStart == 1) {
    e.source.toast('Gate1')
    if (e.value) {
      shts[e.range.rowStart - 1].setName(e.value);
    }
  }
}

Please note this only works for user edits. It will not work if column one is changed via formula or another script.

Rename tabs to one cell from another in the same Sheet

2 Answers2