Копирование определенного листа таблицы во всех файлах папки

Question

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

Пример: Файлы в папке: 001 002 003 и так далее.

В каждом файле есть лист с названием "ПУСТОЙ"

Нужно что бы в каждом файле в папке скрипт скопировал лист "ПУСТОЙ", в этот же файл, изменил название на определённое и записал в определенную ячейку новое название листа. Из дополнительных фишек, необходимо защитить лист и исключить отдельные ячейки (изменения может вносить только владелец таблицы)

На данный момент переработал данный скрипт:

  var as = SpreadsheetApp.getActiveSpreadsheet();
  var template = as.getSheetByName('ЛИСТ КОТОРЫЙ КОПИРУЕМ');
  var names = as.getRange('ЛИСТ ОТКУДА БЕРЕМ ИМЯ ЛИСТА!A1:A1').getValues().forEach(function(r, i){
    var name = r[0];
    if(!as.getSheetByName(name)) template.copyTo(as).setName(name);
  });
}

Но этот скрипт копирует лист внутри файла, из массива названий который в этом же файле. Массив названий необходим в отдельном файле, находящийся вне указанной папки.

Answer 1

Вам нужно что-то вроде этого

function doMyJob() {
  const sheetNames = SpreadsheetApp.openById('ID Таблицы с именами листов')
    .getRange('Sheet1!A:A').getValues()
    .filter(row => row[0] !== '').map(row => row[0]);
  const fileIterator = getFiles_();
  while (fileIterator.hasNext()) {
    const file = fileIterator.next();
    const book = SpreadsheetApp.openById(file.getId());
    doJobForBook_(book, { sheetNames })
  }
}

/**
 * @returns {globalThis.DriveApp.FileIterator}
 */
function getFiles_() {
  return DriveApp
    .searchFiles('"ID папки, где лежат Таблицы для изменения" in parents and mimeType="application/vnd.google-apps.spreadsheet"');
}

/**
 * @param {globalThis.SpreadsheetApp.Spreadsheet} book
 * @param {{
 *   sheetNames: string[]; 
 * }} options
 */
function doJobForBook_(book, options) {
  var template = book.getSheetByName('Лист, который копируем');
  if (!template) {
    console.info('Шаблон не найден. Пропуск');
    return;
  };
  options.sheetNames.forEach(function (name) {
    if (!as.getSheetByName(name)) template.copyTo(as).setName(name);
  });
}

Все изменения производите в/из функции doJobForBook_
Если времени на изменение всех файлов не хватит, то необходимо подумать о перемещении измененных файлов в другую папку. Сделайте это сразу после вызова doJobForBook_ в doMyJob

Answer 2

Для тех у кого будет похожая задача, выкладываю часть кода, который решает описанную мной задачу. Отдельное спасибо за помощь contributorpw!

function doMyJob() {
  const sheetNames = SpreadsheetApp.openById('ID Таблицы с именами листов')
    .getRange('Sheet!A1:A').getValues()
    .filter(row => row[0] !== '').map(row => row[0]);
  var fileIterator = DriveApp.getFolderById('"ID папки, где лежат Таблицы для изменения" in parents and mimeType="application/vnd.google-apps.spreadsheet"').getFiles()
    
  while (fileIterator.hasNext()) {
    const file = fileIterator.next();
    const book = SpreadsheetApp.openById(file.getId());
    doJobForBook_(book, { sheetNames })
  }
}
 
function doJobForBook_(book, options) 
{
  var template = book.getSheetByName('Лист, который копируем');
   if (!template) 
  {
    console.info('Шаблон не найден. Пропуск');
    return;
  };
  options.sheetNames.forEach(function (name)
  {
    if (!book.getSheetByName(name)) 
    {
    const SetActiveSheet_ = template.copyTo(book).setName(name);
    SetActiveSheet_.activate();
    book.moveActiveSheet(0);
    book.getRange("CELL").clear().setValue(name).setBackgroundColor("COLOR"); //Заполнение ключа для дальнейших действий
    
    var protection = book.getActiveSheet().protect(); // Настройка прав доступа и исключений
    var unprotected = book.getRange('RANGE');
    protection.setUnprotectedRanges([unprotected]);
    
    var me = Session.getEffectiveUser();
    protection.addEditor(me);
    protection.removeEditors(protection.getEditors());
    if (protection.canDomainEdit()) 
     {
     protection.setDomainEdit(false);
     }
    }
  });
}

БЛОГ НА HUSL

Копирование определенного листа таблицы во всех файлах папки

Ответы (2 шт):