How to create multiple documents based on a single template using a script inside Google Sheets

Sometimes it is necessary to create many duplicate documents that differ only in number, date and a couple of text lines. It is very sad to waste your time on their creation – after all, you need to perform a lot of equally repetitive actions. You can also understand the time spent on creating 5 documents, but if they need to be created, for example, 500 pieces?

And this task can also become more complicated due to the need to insert any data that must first be found on the Internet. For example, insert weather data on the date the document was created.

Luckily, Google Sheets can make it easy to create duplicate copies with a template. In this article, I will show you how you can create an unlimited number of copies based on a single template using a Google script inside a Google spreadsheet.

Create a template for your document

The first step is to create a template. Let the welding protocol be the model for this article. It can be recreated or transferred to Google Spreadsheets. Here is a link to this example.

The fields that need to be changed during the creation process are highlighted in red.

The dates were set in advance, as were their serial numbers. The dates for the script to work must be written as an array:

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"];

For a column of dates, you can do this, for example, through macros in Notepad ++:
(if you know a better way – write in the comments to the article)

The gif shows how to record a macro in Notepad ++ and then run it to the end of the file in order to make an array in Google scripts from a regular date column.

Google scripts to help

Google Apps Script is a simple scripting language that uses JavaScript syntax.

To get the weather (and this is one of the fields that the protocol requires), you can use the free Free Weather API, which does not even require obtaining a key to work with it. Terrain coordinates are set via parameters latitude=58.08&longitude=55.76:

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 ""
    }
}

Since the dates are repeated, I had to use the caching service in order to reduce the operating time of this weather function:

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;
}

Next, the most important part of the google script is the creation of many sheets based on one template:

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) + " минут");
    });
}

And the code itself, which creates duplicate tabs and changes their content:

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);
}

I know that this code is not written in the most optimal way and it is hardly worth counting on the speed of work, but this code can create about 300 duplicate tabs in 6 free minutes of the Google script (namely, such a limitation of any Google account). In addition, accessing an external service for the weather greatly slows down its work.

How to use?

In order to run this script from Google Sheets and provide all the necessary permissions for it to work, you need to follow a few steps:

Step 1. Open Google Spreadsheet Протоколы_шаблон и скрипт

– Sign in with your Google account and open the spreadsheet containing this script.

Step 2: Create your own copy of this google spreadsheet example

• Click on the “File” menu, then select “Make a copy…”.

• In the Make a Copy window, enter a name for your copy of the spreadsheet and choose a location for it.

• Click OK to create a copy.

• Your new copy of the spreadsheet will open in a new tab so you can start working on it.

• All data and formatting from the original spreadsheet will be transferred to the new copy.

• If you’ve made a copy of a shared spreadsheet, you’ll have your own separate data, and you won’t corrupt the original spreadsheet data that was shared with you.

Step 3. Open the editor in your script table

– Click the “Extensions” menu, then select “Apps Script”.

– This will open the script editor in a new window.

Step 4Run the Script

– In the script editor, select the tab replicationand in it the function index and click the Run button.

– You will need to authorize the script by clicking “View Permissions” and then clicking “Allow” to give the script permission to access your account information in order to start using this script. You can view the list of issued by you at any time. permissions on a special page and revoke them with one click.

Step 5. Check the result

– If the script runs successfully, the output of the function will be displayed in the Execution Log tab at the bottom of the Script Editor window.

– Also, while the script is running, you can open the table and watch how new tabs appear.

That’s all! By following these simple steps, you can run this google script from your spreadsheet and make sure everything works. After that, you can download the finished book as an Excel or pdf file and transfer it to further work.

Extra step. Using the function deleteRedundantSheets you can delete all automatically generated sheets in order to prepare this template with different parameters.

What is the result

Using a bunch of Google spreadsheet + Google script to create documents of the same type based on a single template is an effective way to automate the creation of documents.

This process will save time and increase productivity. With the help of the step-by-step guide presented in this article, you can easily set up this process yourself using a Google spreadsheet + Google script combination.

By customizing your templates and scripts to suit your needs, you can create your own professional looking documents.

Once everything is working well, you can take advantage of a streamlined document creation process and focus on what matters most—achieving your goals easily and efficiently.

Author: Mikhail Shardin,

April 14, 2023