Гугл таблицы ошибка неразрешенное название листа

2

2

Порядок действий имеет значение.

Раньше я сталкивался с ошибкой «Неразрешенное имя листа» при копировании листов в новую электронную таблицу.

Если sheet A относится к sheet B, убедитесь, что вы сначала скопировали лист B. В противном случае sheet A не сможет найти sheet B, и даже переписать формулу может не получиться.

Если функция использует скрипт листа Google

Условное форматирование до сих пор работает, за исключением части о том, что пустые ячейки остаются пустыми.

Круговая диаграмма Google Sheets

Создайте регулярное выражение, которое заменяет список, разделенный запятыми (например, «один, два, три») на «один, два и три», если 3+ элемента, или «один и два», если 2 элемента

Как получить имя файла из столбца URL-адресов в листах Google

ZIP-файл UrlFetchApp

Флажок нарушает правила проверки данных

Добавление разрешений листа с уведомлением

Как заставить REGEXMATCH искать несколько терминов в диапазоне ячеек, а не рассматривать одну ячейку?

Взаимодействие с рисунком

Следующая иллюстрация должна помочь:

У вас есть 2 варианта, и я не уверен, что я поклонник их увядания, но это мое мнение. Вы можете чувствовать себя иначе:

Вариант 1: принудительно запустить функцию.

Функция в ячейке не запускается, если она не ссылается на ячейку, которая изменилась. Изменение имени листа не вызывает никаких функций в электронной таблице. Но мы можем заставить эту функцию работать, передавая ей диапазон, и всякий раз, когда элемент в этом диапазоне изменяется, функция запускается.

Вы можете использовать приведенный ниже скрипт для создания пользовательской функции, которая будет получать имя:

function mySheetName() {
  var key = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
  return key;
}

и в ячейке:

=mySheetName(A1:Z)

Теперь, если какое-либо значение в ячейке в этом переданном диапазоне изменится, скрипт будет запущен. Это занимает второе место для запуска скрипта и задает сообщение в ячейке каждый раз, когда любое значение изменяется, поэтому это может стать очень раздражающим. Как уже упоминалось, это также требует изменения диапазона, чтобы заставить его запускаться, поэтому не очень полезно для довольно статического файла.

Вариант 2: использовать событие OnChange

В то время как время выполнения кажется лучше, чем приведенный выше вариант, и это не зависит от изменения значения в ячейках электронной таблицы, мне это не нравится, потому что оно заставляет туда имя. Вы можете использовать лист “Утилиты”, чтобы определить это местоположение на разных листах, если хотите. Ниже приведена основная идея, и вы можете начать работу, если вам нравится этот вариант.

Событие OnChange запускается при изменении имени листа. Вы можете сделать код ниже более сложным, чтобы проверить наличие ошибок, проверить, что идентификатор листа работает только на заданном листе и т.д. Однако основной код:

function setSheetName(e) {
  var key = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange('K1').setValue(key);
}

После того, как вы сохранили код, в редакторе сценариев установите эту функцию в текущую версию Project On Change Trigger. Он будет записывать имя листа в ячейку K1 в любом событии изменения. Чтобы установить триггер, выберите ” Текущие триггеры проекта” в меню ” Правка”.

Вот что я нашел для Google Sheets:

Чтобы получить текущее имя листа в листах Google, следующий простой сценарий может помочь вам без ввода имени вручную, выполните следующие действия:

1. Нажмите Инструменты> Редактор скриптов

2. В открывшемся окне проекта скопируйте и вставьте приведенный ниже код скрипта в пустое окно кода, см. Скриншот:

……………………….

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

Затем сохраните окно кода и вернитесь к листу, для которого вы хотите получить его имя, затем введите =sheetname() формулу: =sheetname() в ячейку и нажмите клавишу Enter, имя листа будет отображаться сразу.

Смотрите эту ссылку с добавленными скриншотами: https://www.extendoffice.com/documents/excel/5222-google-sheets-get-list-of-sheets.html

Если вы ссылаетесь на лист с другого листа, вы можете получить имя листа с помощью функции CELL. Затем вы можете использовать регулярные выражения, чтобы извлечь имя листа.

=REGEXREPLACE(CELL("address",'SHEET NAME'!A1),"'?([^']+)'?!.*","$1")

обновление: формула автоматически обновит “ИМЯ ЛИСТА” с будущими изменениями, но вам нужно будет ссылаться на ячейку (например, А1) на этом листе при первоначальном вводе формулы.

Вот мое предложение для скрипта, который возвращает имя листа с его позиции в списке листов в параметре. Если параметр не указан, возвращается имя текущего листа.

function sheetName(idx) {
if (!idx)
return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
else {
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var idx = parseInt(idx);
if (isNaN(idx) || idx < 1 || sheets.length < idx)
throw "Invalid parameter (it should be a number from 0 to "+sheets.length+")";
return sheets[idx-1].getName();
}
}

Затем вы можете использовать его в ячейке, как любая функция

=sheetName() // display current sheet name
=sheetName(1) // display first sheet name
=sheetName(5) // display 5th sheet name

Как описано в других ответах, вам нужно добавить этот код в скрипт с:

Tools > Script editor

У меня есть лист, предназначенный для использования другими, и у меня довольно много косвенных() ссылок, поэтому мне нужно формально обрабатывать измененное имя вкладки листа.

Я использовал формулу из JohnP2 (ниже), но у меня возникли проблемы, потому что она не обновлялась автоматически при изменении имени листа. Вам нужно перейти к фактической формуле, внести произвольные изменения и обновить, чтобы запустить ее снова.

=REGEXREPLACE(CELL("address",'SHEET NAME'!A1),"'?([^']+)'?!.*","$1")

Я решил это с помощью информации, найденной в этом решении, о том, как заставить функцию обновляться. Возможно, это не самое элегантное решение, но оно заставило Sheets обратить внимание на эту ячейку и регулярно обновлять ее, чтобы она уловила обновленный заголовок листа.

=IF(TODAY()=TODAY(), REGEXREPLACE(CELL("address",'SHEET NAME'!A1),"'?([^']+)'?!.*","$1"), "")

Благодаря этому Sheets знает, что нужно обновлять эту ячейку каждый раз, когда вы вносите изменение, в результате чего адрес обновляется всякий раз, когда пользователь переименовывает его.

Старый поток, но полезный… так что здесь дополнительный код.

Во-первых, в ответ на замечание Крейга о том, что регулярное выражение является чрезмерно жадным и не подходит для имен листов, содержащих одну кавычку, это должно сработать (замените “SHEETNAME”! A1 своим собственным листом и ссылкой на ячейку):

=IF(TODAY()=TODAY(), SUBSTITUTE(REGEXREPLACE(CELL("address",'SHEETNAME'!A1),"'?(.+?)'?!$.*","$1"),"''","'", ""), "")

Он использует ленивое совпадение (“. +?”), Чтобы найти строку символов (включая кавычки), которая может или не может быть заключена в кавычки, но определенно завершается символом доллара (“! $”), За которым следует любое количество символов, Google Sheets на самом деле защищает сквоты внутри имени листа, добавляя еще одну сквот (как в ”), поэтому необходимо заменить SUBSTITUTE, чтобы вернуть их обратно к одиночным сквотам.

Формула также учитывает имена листов, которые содержат челки (“!”), Но не будут работать с именами, использующими доллары взрыва (“! $”) – если вам действительно нужно, чтобы имена ваших листов выглядели как полные абсолютные ссылки на ячейки, поместите разделяющий символ между ударом и долларом (например, пробел).

Обратите внимание, что он будет работать корректно только тогда, когда указан на листе, отличном от того, на котором находится формула! Это связано с тем, что при использовании на том же листе CELL (“адрес” возвращает только ссылку на ячейку (не имя листа). Если вам нужен лист для отображения собственного имени, поместите формулу в ячейку на другом листе, укажите ее на целевого листа, а затем ссылаться на ячейку формулы из целевого листа. У меня часто есть “Мета” лист в моих книгах для хранения настроек, общих значений, критериев соответствия базы данных и т.д., чтобы я также поместил эту формулу.

Как уже много раз говорили выше, Google Sheets заметит изменения в названии листа только в том случае, если вы установите перерасчет рабочей книги на “При изменении и каждую минуту”, который можно найти в меню “Файл | Настройки | Расчет”. Изменение может занять до целой минуты.

Во-вторых, если вам, как и мне, вам нужна совместимая формула, которая работает как в Google Sheets, так и в Excel (которая, по крайней мере, для более старых версий не имеет функции REGEXREPLACE), попробуйте:

=IF(IFERROR(INFO("release"), 0)=0, IF(TODAY()=TODAY(), SUBSTITUTE(REGEXREPLACE(CELL("address",'SHEETNAME'!A1),"'?(.+?)'?!$.*","$1"),"''","'", ""), ""), MID(CELL("filename",'SHEETNAME'!A1),FIND("]",CELL("filename",'SHEETNAME'!A1))+1,255))

При этом используется INFO (“релиз”), чтобы определить, на какой платформе мы работаем… Excel возвращает число> 0, тогда как в Google Sheets не реализована функция INFO, и генерируется ошибка, которая выводится формулой в 0 и используется для численного сравнения. Ветка кода Google такая же, как и выше.

Для ясности и полноты, это Excel-только версии (что правильно возвращает имя листа он находится на):

=MID(CELL("filename",'SHEETNAME'!A1),FIND("]",CELL("filename",'SHEETNAME'!A1))+1,255)

Он ищет терминатор имени файла “]” в выходных данных CELL (“имя файла” и извлекает имя листа из оставшейся части строки с помощью функции MID. Excel не позволяет именам листов содержать “]”, поэтому это работает для всех возможных имен листов. В интероперабельной версии Excel рада получить вызов несуществующей функции REGEXREPLACE, потому что ей никогда не удается выполнить ветвь кода Google.

      function sheetName(idx) {   if (!idx)
return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
else {
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var idx = parseInt(idx);
if (isNaN(idx) || idx < 1 || sheets.length < idx)
throw "Invalid parameter (it should be a number from 0 to "+sheets.length+")";
return sheets[idx-1].getName();   } }

=sheetName() // display current sheet name
=sheetName(1) // display first sheet name
=sheetName(5) // display 5th sheet name

Этот код работает очень хорошо для меня, но если в любом случае он возвращает все имена листов без ссылки на номер листа, я буду очень признателен, если вы поможете мне с этим.

Я получил это, чтобы наконец-то работать в полуавтоматическом режиме без использования сценариев… но для его выполнения требуется 3 ячейки. Заимствуя немного из предыдущих ответов, я начинаю с ячейки, в которой нет ничего, кроме = NOW(), чтобы показать время. Например, мы поместим это в ячейку A1…

=NOW()

Эта функция обновляется автоматически каждую минуту. В следующую ячейку поместите формулу указателя, используя собственное имя листов, чтобы указать на предыдущую ячейку. Например, мы поместим это в A2…

='Sheet Name'!A1

Помимо форматирования ячеек, ячейки A1 и A2 должны в этот момент отображать одинаковое содержимое… а именно текущее время.

И последняя ячейка – это часть, которую я заимствовал из предыдущих решений, используя выражение регулярного выражения, чтобы извлечь формулу из второй ячейки, а затем вычеркнуть имя листа из указанной формулы. Например, мы поместим это в ячейку A3…

=REGEXREPLACE(FORMULATEXT(A2),"='?([^']+)'?!.*","$1")

На этом этапе результирующее значение, отображаемое в A3, должно быть именем листа.

По моему опыту, как только название листа меняется, формула в А2 сразу обновляется. Однако этого недостаточно для запуска обновления A3. Но каждую минуту, когда ячейка A1 пересчитывает время, результат формулы в ячейке A2 впоследствии обновляется, а затем, в свою очередь, запускает A3 для обновления с новым именем листа. Это не компактное решение… но, похоже, оно работает.