What formula will return the name of the Sheet in cell "A1"?

Question

Google Drive

Hi all! The task is this: 1. There is a Google Spreadsheet
in which "Sheet 1" is created . if I change the name of the sheet, so that the value in the cell also changes.

Answer 1

Hello.
You can create your own function based on Google Apps Script .
Code.gs

function sheetName() {
  return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
}

function sheetsName() {
  return SpreadsheetApp.getActiveSpreadsheet().getSheets().map(function(s){return s.getName()});
}

In the Table, you must specify a formula to get the name of the active sheet.
=sheetName(E5:E13)
To get a list of all sheets
=sheetsName(E5:E13)
where E5:E13special, necessarily active, i.e. user input dependent range. Without changing this range, the function will be of little use.
Attention , time functions (TODAY(), NOW()) cannot be passed as arguments.
Sample Table with script goo.gl/w97FNi
PS: To avoid anger and any misinterpretation, this script became possible after the question was posted and the first comments on this post. Proof https://code.google.com/p/google-apps-script-issue...

Answer 2

Hello,
below is another solution to your question, it does not need ranges.
Just give a sheet number from 1 to N, where N is the last sheet.
The instruction is very simple and short:
- Copy the code below (all with all brackets, slashes and commas):

//функция возврата имени листа по порядковому номеру
function getSheetName(num) {
  
  //переменная 'num' в строке выше - номер вашего листа и для обычного пользователя первый лист num = 1,
  //но с точки зрения программирования он имеет другой номер на 1 меньше (пользователи, не прьтесь почему так - сейчас не важно),
  //то есть то что для пользователя =1 для программы =0, поэтому ниже мы сдвигаем значение cellNumber на -1. 
  var cellNumber = num-1;         
  return SpreadsheetApp.getActiveSpreadsheet().getSheets()[cellNumber].getSheetName();
  
}

- Open the table where you want to use the function;
- Select "Tools" > "Script Editor.." from the menu of the opened Google Sheets editor;
- The Script Editor will open in a new tab:
- Paste the copied code into the editor:
- From the editor menu, select "Resources" > "Current Project Triggers" and give your project a name:
- Then click "Triggers not configured. Click here to add a trigger. " to create the first trigger:
- In the dialog that opens, select the values ​​marked in the image below:
- The editor will require authorization and perform actions in your account, select "Next", "Next" ...
Check, select cell, enter value =getSheetName(1) result "Sheet1"