Как создать копию листа в таблице Google Sheets через макрос, чтобы имена новых листов брались из диапазона ячеек?

Question

[Toomaas]

В документе Google-таблиц на листе "Список" есть список наименований, с которыми необходимо создать новые листы копированием листа "Пример".
Нужен макрос для привязки к кнопке, чтобы по её нажатию лист "Пример" копировался в новый в конец документа (последним листом в документе) и ему задалось имя из ячейки A1 листа "Список", затем лист "Пример" копировался в новый и ему задалось имя из ячейки A2 листа "Список"...и так до тех пор, пока не создадутся копированием все листы из списка в столбце А. Список может быть с разным количеством.
Документ для примера
На данный момент сделал только создание нового листа копированием и именованием заданным именем:

function CopySheet() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getSheetByName('Пример');
spreadsheet.getSheetByName('Пример').getRange('A1').activate();
spreadsheet.duplicateActiveSheet();
spreadsheet.moveActiveSheet(3);
spreadsheet.getActiveSheet().setName('ПримерНовый');
};

Как изменить макрос так, чтобы:
1) при копировании лист перемещался в конец документа (проверялось количество листов и новый помещался за последний),
2) имя назначалось из списка A1, A2, A3 и т.д., пока есть данные в ячейках в столбце А листа "Список" (может быть 10 значений, а может быть и 20),
3) можно ли как-то сделать это в фоне (например, в Excel можно было в функции указать "Application.ScreenUpdating = False", чтобы каждый лист не открывался.
4) если возможен пункт 3, то можно ли как-то сделать, чтобы по итогу выводилось сообщение примерно как "Все листы созданы" или (в идеале) "Создано листов: ** (количество созданных макросом листов)"?

Answer 1

[Sergey В.]

1)

function CopySheet() {
var ss = SpreadsheetApp.getActive();

var sheet = ss.getSheetByName("Список");
var ranges = sheet.getDataRange();
var lastRow = ranges.getLastRow();

var names = sheet.getRange(`A1:A${lastRow}`).getDisplayValues(); // get new sheet names array
names = names.filter(name => (name.length > 0)); // delete empty values 

// need check by doubles

var sheetSample = ss.getSheetByName('Пример');

names.forEach(name => { // create new sheets 
  var sheetTemp = sheetSample.copyTo(ss);
  sheetTemp.setName(name);
});

Browser.msgBox('Info', 'Completed! Create: ' + names.length + ' sheets', Browser.Buttons.OK);
};

Работу скрипта не проверял, если будут ошибки, надеюсь справитесь. Скрипт не проверяет список названий листов на дубликаты. Также возможно потребуется перемещение листа в конец книги;

2) в столбце "А" может быть любое количество новых названий;

3) нельзя. Единственное, что можно сделать - при создании нового листа скрыть его, а в конце выполнения скрипта - сделать цикл для отображения всех новых листов. Это имеет смысл делать, если на листах выполняются вычисления (например много формул);

4) реализовано

Comments

[Toomaas]

Отлично, то, что нужно! Огромное спасибо!

Пара вопросов:

1) После добавления и именования последнего листа появляется ошибка "Exception: Укажите название листа". То есть когда значения в ячейках заканчиваются, скрипт всё равно пытается назвать лист пустым значением. Можно это как-то поправить? До сообщения о выполнении не доходит

2) Можно ли как-то вместе с копированием листа перенести и заданную безопасность листа-Примера? Например, на листе стоит защита с исключением одной ячейки (Q2)

[Sergey В.]

Toomaas,
1) возможно в массив попадает null значение. Тогда измените строку с filter на
names = names.filter(name => { return (name && name.length > 0) }); // delete empty and null values
рекомендую также в forEach добавить строку:
console.log(name);
чтобы видеть в консоли - на каком названии скрипт спотыкается.
2) безопасность придётся назначать заново. Можно записать макрос и этот код (немного доработав) вставить в forEach (после создания листа)

[Toomaas]

Sergey В.,
Поменял строку с filter, но результат тот же:


Прошу помощи.

Кстати, добавлю, макрос выполняется всё-таки в фоне, каждый создаваемый лист не открывается (это прекрасно)

[Sergey В.]

Toomaas, кажется понял, надо перевести 2D массив в одномерный. Так:
var names = sheet.getRange(`A1:A${lastRow}`).getDisplayValues().flat();
вместо
var names = sheet.getRange(`A1:A${lastRow}`).getDisplayValues();

[Toomaas]

Sergey В.,
Я уже решил этот момент немного изменив условие фильтра:
names = names.filter(name => { return (name > '') }); // значение больше чем null
вместо
names = names.filter(name => { return (name && name.length > 0) }); // delete empty and null values
А так, с одномерным массивом тоже работает, спасибо!

Ещё добавил настройку безопасности после создания каждого листа, о которой спрашивал:
var sp = SpreadsheetApp.getActive().getSheetByName(name);
var protection = sp.protect().setDescription('SimpleProtection');
var unprotected = sp.getRange('Q2');
protection.setUnprotectedRanges([unprotected]);

а также значение ключевой ячейки на листе для запроса данных из названия листа после его именования:
sp.getRange('A3').setFormula('=GetThisSheetName()');

В итоге все поставленные задачи выполняются. Выполняются в фоне, после чего выводится итоговое сообщение.

Огромная благодарность за помощь!

Вероятно, это уже будет вопрос в отдельный пост, но всё же вдруг есть быстрый ответ: после создания листа (для того же документа) возможно ли макросом задать чередование цветов (условное форматирование) для определённого диапазона, чтобы оно работало только на строчках, где есть данные? Например, зададим общий диапазон А5:M35, но на каждом листе после создания будет от 10 до 30 строк, и надо, чтобы чередовались цвета только тех строк, в которых есть данные.

[Sergey В.]

Toomaas, не за что!
По последнему абзацу - к сожалению не подскажу (

[Alexander Ivanov]

Toomaas, возможно. Просто сошлитесь на свой созданный лист и используйте вас же код https://qna.habr.com/q/1252870