How I taught the accounting department to draw up gift agreements at the speed of 1 agreement in 4 seconds
In the article I analyze a specific example – how, using Google Apps Script, based on a template created in Google Docs, you can massively create gift agreements from Google Sheets rows.
You can create standard contracts en masse when you have a template document with ready-made formatting and key fields that are replaced for each new line of the table are marked in it. After autocorrect, a copy of the completed template is saved with a specified name as a Google Document and can immediately be automatically converted to a PDF or Microsoft Word file.
Auto-filling of gift agreements
In many organizations, accountants are responsible for drafting gift agreements from the organization. This task can be repetitive, time-consuming, and error-prone when done manually. All data is most likely stored in 1C, but you can export it to Excel and then copy it to Google Sheets:
Using Google Apps script, this process can be fully automated. The script retrieves data from a Google spreadsheet containing employee information:
Then a similar Google document template is created for all employees:
After this, the Google script runs time.
Contract auto-completion script
Below is a script for auto-filling gift agreements, written in Google Apps Script. This is a scripting programming language that is used to automate work with Google services such as Gmail, Google Docs, Sheets, Slides, Forms and others. It allows you to create custom functions, macros, and applications that can interact with the data within these services. This is a powerful tool for increasing productivity and improving communication between the various components of the Google ecosystem:
function generateDocumentsFromSpreadsheet() {
// Подробнее: https://habr.com/ru/articles/843488/
console.log(`Функция generateDocumentsFromSpreadsheet начала работу в ${(new Date()).toLocaleString("ru-RU")}`);
// Доступ к таблице и получение данных
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName("Автозаполнение"); // Измените на название вашего листа
const data = sheet.getDataRange().getValues(); // Получаем все данные с таблицы
// Открытие шаблона Google Документа
const templateDocId = "1y2XQhZBХХХХХХХХХХХХХzyfnXh8yUQ"; // Замените на настоящий идентификатор вашего шаблона
const templateDoc = DriveApp.getFileById(templateDocId);
const templateFolder = templateDoc.getParents().next(); // Получаем папку, в которой находится шаблон
// Проходим по каждой строке (начиная со второй, чтобы пропустить заголовки)
for (let i = 1; i < data.length; i++) {
const [rawDate, fullName, position, rawAmount, gift, passport, tin] = data[i]; // Получаем данные из строки таблицы
console.log(`Работаем с ${fullName}.`);
let formattedAmount="";
formattedAmount = rawAmount + " (" + FloatToSamplesInWordsRus(rawAmount) + ")" // Форматируем сумму как текст (включая пропись)
// Форматируем дату
let formattedDate="";
if (rawDate instanceof Date) {
formattedDate = Utilities.formatDate(rawDate, Session.getScriptTimeZone(), "dd.MM.yyyy"); // Преобразуем дату в формат "дд.мм.гггг"
}
// Создание нового документа на основе шаблона
const copyDoc = templateDoc.makeCopy(`Заполненный - ${fullName}`); // Копируем шаблон и называем новый документ по ФИО
const copyDocId = copyDoc.getId();
// Открываем новый документ
const copyDocAsDoc = DocumentApp.openById(copyDocId);
const copyDocBody = copyDocAsDoc.getBody();
// Замена шаблонных полей на данные из таблицы
copyDocBody.replaceText('Дата', formattedDate);
copyDocBody.replaceText('ФИО', fullName);
copyDocBody.replaceText('Должность', position);
copyDocBody.replaceText('Сумма', formattedAmount);
copyDocBody.replaceText('Подарок', gift);
copyDocBody.replaceText('Паспорт', passport);
copyDocBody.replaceText('ИННномер', tin);
// Замена текста полей на соответствующие данные
// Сохранение и закрытие заполненного документа
copyDocAsDoc.saveAndClose();
// Преобразование документа в PDF, если надо
// const pdfBlob = DriveApp.getFileById(copyDocId).getAs("application/pdf");
// Сохранение копии PDF на Google Диске
// const pdfFile = templateFolder.createFile(pdfBlob).setName(`Заполненный - ${fullName}.pdf`);
// Дополнительно: удалите промежуточный Google Документ, если нужен только PDF
// DriveApp.getFileById(copyDocId).setTrashed(true);
}
console.log(`Функция generateDocumentsFromSpreadsheet закончила работу в ${(new Date()).toLocaleString("ru-RU")}`);
}
// Карта для преобразования чисел в слова (рубли) отсюда https://habr.com/ru/articles/104057/
var mapNumbers = {
0: [2, 1, "ноль"],
1: [0, 2, "один", "одна"],
2: [1, 2, "два", "две"],
3: [1, 1, "три"],
4: [1, 1, "четыре"],
5: [2, 1, "пять"],
6: [2, 1, "шесть"],
7: [2, 1, "семь"],
8: [2, 1, "восемь"],
9: [2, 1, "девять"],
10: [2, 1, "десять"],
11: [2, 1, "одиннадцать"],
12: [2, 1, "двенадцать"],
13: [2, 1, "тринадцать"],
14: [2, 1, "четырнадцать"],
15: [2, 1, "пятнадцать"],
16: [2, 1, "шестнадцать"],
17: [2, 1, "семнадцать"],
18: [2, 1, "восемнадцать"],
19: [2, 1, "девятнадцать"],
20: [2, 1, "двадцать"],
30: [2, 1, "тридцать"],
40: [2, 1, "сорок"],
50: [2, 1, "пятьдесят"],
60: [2, 1, "шестьдесят"],
70: [2, 1, "семьдесят"],
80: [2, 1, "восемьдесят"],
90: [2, 1, "девяносто"],
100: [2, 1, "сто"],
200: [2, 1, "двести"],
300: [2, 1, "триста"],
400: [2, 1, "четыреста"],
500: [2, 1, "пятьсот"],
600: [2, 1, "шестьсот"],
700: [2, 1, "семьсот"],
800: [2, 1, "восемьсот"],
900: [2, 1, "девятьсот"]
};
var mapOrders = [{
_Gender: true,
_arrStates: ["рубль", "рубля", "рублей"]
},
{
_Gender: false,
_arrStates: ["тысяча", "тысячи", "тысяч"]
},
{
_Gender: true,
_arrStates: ["миллион", "миллиона", "миллионов"]
},
{
_Gender: true,
_arrStates: ["миллиард", "миллиарда", "миллиардов"]
},
{
_Gender: true,
_arrStates: ["триллион", "триллиона", "триллионов"]
}
];
var objKop = {
_Gender: false,
_arrStates: ["копейка", "копейки", "копеек"]
};
function Value(dVal, bGender) {
var xVal = mapNumbers[dVal];
if (xVal[1] == 1) {
return xVal[2];
} else {
return xVal[2 + (bGender ? 0 : 1)];
}
}
function From0To999(fValue, oObjDesc, fnAddNum, fnAddDesc) {
var nCurrState = 2;
if (Math.floor(fValue / 100) > 0) {
var fCurr = Math.floor(fValue / 100) * 100;
fnAddNum(Value(fCurr, oObjDesc._Gender));
nCurrState = mapNumbers[fCurr][0];
fValue -= fCurr;
}
if (fValue < 20) {
if (Math.floor(fValue) > 0) {
fnAddNum(Value(fValue, oObjDesc._Gender));
nCurrState = mapNumbers[fValue][0];
}
} else {
var fCurr = Math.floor(fValue / 10) * 10;
fnAddNum(Value(fCurr, oObjDesc._Gender));
nCurrState = mapNumbers[fCurr][0];
fValue -= fCurr;
if (Math.floor(fValue) > 0) {
fnAddNum(Value(fValue, oObjDesc._Gender));
nCurrState = mapNumbers[fValue][0];
}
}
fnAddDesc(oObjDesc._arrStates[nCurrState]);
}
function FloatToSamplesInWordsRus(fAmount) {
var fInt = Math.floor(fAmount + 0.005);
var fDec = Math.floor(((fAmount - fInt) * 100) + 0.5);
var arrRet = [];
var iOrder = 0;
var arrThousands = [];
for (; fInt > 0.9999; fInt /= 1000) {
arrThousands.push(Math.floor(fInt % 1000));
}
if (arrThousands.length == 0) {
arrThousands.push(0);
}
function PushToRes(strVal) {
arrRet.push(strVal);
}
for (var iSouth = arrThousands.length - 1; iSouth >= 0; --iSouth) {
if (arrThousands[iSouth] == 0) {
continue;
}
From0To999(arrThousands[iSouth], mapOrders[iSouth], PushToRes, PushToRes);
}
if (arrThousands[0] == 0) {
// Handle zero amount
if (arrThousands.length == 1) {
PushToRes(Value(0, mapOrders[0]._Gender));
}
var nCurrState = 2;
PushToRes(mapOrders[0]._arrStates[nCurrState]);
}
if (arrRet.length > 0) {
// Capitalize first letter
arrRet[0] = arrRet[0].match(/^(.)/)[1].toLocaleUpperCase() + arrRet[0].match(/^.(.*)$/)[1];
}
arrRet.push((fDec < 10) ? ("0" + fDec) : ("" + fDec));
From0To999(fDec, objKop, function () {}, PushToRes);
return arrRet.join(" ");
}Execution process:
Filling speed: 3-4 seconds per 1 contract.
Result
After executing the script, the required number of gift agreements are created in the table location folder:
How to use this code?
To insert and run a Google Apps script into a Google Sheet, follow these steps:
Open Google Sheets: Start by opening your Google Spreadsheet.
Open the script editor: Click Extensions in the menu, then select Application Script. This will open the Application Script Editor in a new tab.
Paste the script: In the script editor, remove the placeholder code. Then paste the Google Apps script above into the editor.
Save the script: Click the floppy disk icon or press “Ctrl+S” to save the script. Give it a meaningful name if prompted.
Authorize the script: If your script interacts with Google services (e.g. Sheets, Calendar), you need to grant it permission. Click the “Run” button (play icon) and follow the authorization instructions.
Run the script: After authorization, click “Run” again. The script will be launched and will perform actions to automatically complete gift agreements.
From now on, you can easily run the script from the script editor, or create a custom menu or button in your table to run it.
Where else can it be used?
Similar scripts can be used for other purposes:
Automated payroll for HR departments
The accounting department of micro-enterprises (large ones already use something else) can create payroll statements for each employee. Using such a script, they will be able to automatically enter information such as last name, position, salary from the main table, generating salary documents.Creation of legal documents
Legal departments often process a large volume of standardized contracts and agreements in which only a few details change from client to client. By extracting data from a spreadsheet (client name, contract amount, dates), this script can automatically generate relevant documents in seconds.Student report cards or certificates
Schools and universities can use a script like this to generate report cards or certificates for students. By storing students' names, grades, and achievements in a spreadsheet, the script can generate a personalized report or certificate for each student.Personalized Invitations and Event Signs
Event organizers often need to send personalized invitations or create personalized badges for attendees. Using a script like this, you can extract participant data from a spreadsheet and generate invitation letters or plaques in PDF format, ready for printing.
Results
Accountants no longer have to manually create each agreement, saving time and eliminating potential data entry errors. Instead, they can rely on a script to create and store all agreements in an organized folder structure in Google Drive, ready for viewing and sharing. This automation not only streamlines administrative workflows, but also ensures consistency and easy access to records.
Author: Mikhail Shardin
September 30, 2024
