Как создать множество документов на основе единого шаблона при помощи скрипта внутри гугл таблицы

Иногда бывает необходимо создать множество повторяющихся документов, которые отличаются лишь номером, датой и ещё парой текстовых строк. Очень грустно тратить на их создание своё время - ведь требуется совершить множество одинаково повторяющихся действий. Ещё можно понять затраты времени на создание 5 документов, но если их надо создать, например 500 штук?

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

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

Создайте шаблон для вашего документа

Первым шагом является создание шаблона. Пусть образцом для этой статьи станет протокол сварки. Его можно создать заново или перенести в гугл таблицы. Вот ссылка на этот пример.

Шаблон протокола на одну страницу А4. Красным выделены поля, которые надо изменять

Красным выделены поля, которые надо изменять в процессе создания.

Даты были заданы заранее, как и их порядковые номера. Даты для работы скрипта надо записать в виде массива:

const values = ["2022-12-21", "2022-12-21", "2022-12-21", "2022-12-21", "2022-12-21", "2022-12-21", "2022-12-22", "2022-12-22"];

Для столбца из дат можно сделать это, например, через макросы в Notepad++:
(знаете способ лучше - напишите в комментарии к статье)

Запись макроса и его дальнейший запуск

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

Гугл скрипты в помощь

Google Apps Script — это простой язык сценариев, который использует синтаксис JavaScript.

Для получения погоды (а это одно из полей, которые требует протокол) можно воспользоваться бесплатным Free Weather API, которое даже не требует получения ключа для работы с ним. Координаты местности задаются через параметры latitude=58.08&longitude=55.76:

// https://habr.com/ru/articles/728840/
// Михаил Шардин https://shardin.name/

function temperature(date) { //получаем погоду
    // date = "2022-10-04"
    // console.log(`temperature. date = ${date}`)
    const url = `https://archive-api.open-meteo.com/v1/archive?latitude=58.08&longitude=55.76&start_date=${date}&end_date=${date}&timezone=Asia%2FYekaterinburg&daily=temperature_2m_mean,precipitation_sum,windspeed_10m_max`
    CacheServis(url);
    try {
        const response = UrlFetchApp.fetch(url)
        const json = JSON.parse(response.getContentText());
        const temperature_2m_mean = json.daily.temperature_2m_mean[0]
        // console.log(`Погода по координатам на ${date}: ${temperature_2m_mean}°C.`)
        const precipitation_sum = json.daily.precipitation_sum[0]
        // console.log(`Погода по координатам на ${date}: ${precipitation_sum} мм.`)
        const windspeed_10m_max = json.daily.windspeed_10m_max[0]
        // console.log(`Погода по координатам на ${date}: ${windspeed_10m_max} км/ч.`)
        // console.log(`Погода по координатам на ${date}: ${temperature_2m_mean}°C, ${precipitation_sum} мм осадков, ветер ${windspeed_10m_max} км/ч.`)
        return `${temperature_2m_mean}°C\n${precipitation_sum} мм осадков\nветер ${windspeed_10m_max} км/ч`
    } catch (error) {
        console.log(`temperature. Ошибка на ${date}: ${error}.`)
        return ""
    }
}

Поскольку даты повторяются пришлось воспользоваться сервисом кеширования, для того чтобы сократить время работы этой погодной функции:

// https://habr.com/ru/articles/728840/
// Михаил Шардин https://shardin.name/

function CacheServis(url) { //кэширование результата запроса по url
    var cache = CacheService.getScriptCache();
    var cached = cache.get(url);
    if (cached != null) {
        // Logger.log("CacheServis.\nУже было записано для " + url);
        return cached;
    }
    try {
        var result = UrlFetchApp.fetch(url);
    } catch (error) {
        Logger.log("CacheServis.\nОшибка обращения при кэшировании по url " + url + "\n\n");
        return ""
    }
    var contents = result.getContentText();
    cache.put(url, contents, 21600); // cache for 6 hours
    Logger.log("CacheServis.\nЗаписали по новой для " + url);
    return contents;
}

Далее самая важная часть гугл скрипта - создание множества листов на основе одного шаблона:

// https://habr.com/ru/articles/728840/
// Михаил Шардин https://shardin.name/

function index() {
    var startTime = new Date();

    const values = ["2022-12-21", "2022-12-21", "2022-12-21", "2022-12-21", "2022-12-21", "2022-12-21", "2022-12-22", "2022-12-22"];
    
    let n = 0
    let time = 7;

    values.forEach((date, index) => {
        if (index === 0 || date !== values[index - 1]) {
            time = 7;
        } else {
            time += 1;
        }
        n += 1
        console.log(`Выполняю копирование №${n} для ${date}.`)
        duplicate(date, n, time);

    var endTime = new Date();
    var duration = (endTime - startTime) / 60000; 
    Logger.log("Время выполнения скрипта: " + duration.toFixed(2) + " минут");
    });
}

И сам код, который создаёт дубликаты вкладок и меняет их содержимое:

// https://habr.com/ru/articles/728840/
// Михаил Шардин https://shardin.name/

function duplicate(date, n, time) {

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var lastTabName = ss.getSheets().pop().getSheetName(); 
    var sheet = ss.getSheetByName('Шаблон').copyTo(ss);

    sheet.setName(n);
    sheet.getRange("B2").setValue(temperature(date));
    sheet.getRange("B5").setValue(n);
    sheet.getRange("B33").setValue(date);
    sheet.getRange("B34").setValue(date);
    sheet.getRange("C33").setValue(`${time}:${Math.floor(Math.random() * 3) + 1}:00`);
    sheet.getRange("C34").setValue(`${time+1}:${Math.floor(Math.random() * 6) + 4}:00`);

    // ss.setActiveSheet(sheet);
}

Я знаю что этот код написан не самым оптимальным образом и вряд ли стоит рассчитывать на скорость работы, но этот код может за 6 бесплатных минут работы гугл скрипта (а именно такое ограничение любого гугл аккаунта), создать около 300 дубликатов вкладки. А ещё обращение к внешнему сервису за погодой сильно замедляет его работу.

Как пользоваться?

Для того чтобы запустить этот скрипт из гугл таблицы и предоставить все необходимые разрешения для его работы надо выполнить несколько шагов:

Шаг 1. Откройте таблицу Google Протоколы_шаблон и скрипт:

• Войдите в свою учетную запись Google и откройте электронную таблицу, содержащую этот скрипт.

Шаг 2. Создайте собственную копию этой гугл таблицы примера:

• Нажмите на меню «Файл», затем выберите «Создать копию...».

• В окне «Создать копию» введите имя для своей копии электронной таблицы и выберите место для нее.

• Нажмите «ОК», чтобы создать копию.

• Ваша новая копия электронной таблицы откроется в новой вкладке, теперь вы можете начать работать с ней.

• Все данные и форматирование исходной электронной таблицы будут перенесены в новую копию.

• Если вы сделали копию общей электронной таблицы, у вас будут свои собственные отдельные данные, и вы не испортите данные исходной электронной таблицы, которой с вами поделились.

Шаг 3. Откройте редактор в вашей таблице скриптов:

• Щелкните меню «Расширения», затем выберите «Apps Script».

• Это откроет редактор скриптов в новом окне.

Шаг 4. Запустите скрипт:

• В редакторе сценариев выберете вкладку replication, а в ней функцию index и щелкните кнопку «Выполнить».

• Вам потребуется авторизовать сценарий, нажав «Просмотреть разрешения», а затем нажав «Разрешить», чтобы предоставить сценарию разрешение на доступ к информации вашей учетной записи для того, чтобы начать использовать этот скрипт. Вы в любое время можете посмотреть список выданных вами разрешений на специальной странице и в один клик их отозвать.

Шаг 5. Проверьте результат:

• Если сценарий выполняется успешно, выходные данные функции будут отображаться на вкладке «Журнал выполнения» в нижней части окна редактора сценариев.

Выполнение работы скрипта

• Также во время работы скрипта вы можете открыть таблицу и наблюдать как появляются новые вкладки.

Вот и всё! Следуя этим простым шагам, вы можете запустить этот гугл скрипт из своей электронной таблицы и убедиться, что всё работает. После этого можно скачать готовую книгу как файл Экселя или pdf и передать её в дальнейшую работу.

Вновь созданные вкладки

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

Что в итоге

Использование связки гугл таблица + гугл скрипт для создания однотипных документов на основе единого шаблона является действенным способом автоматизации создания документов.

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

Настраивая свои шаблоны и сценарии в соответствии со своими потребностями, вы сможете создавать свои собственные профессионально выглядящие документы.

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

Автор: Михаил Шардин,

14 апреля 2023 г.