Автозаполнение договоров и типовых документов

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

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

Будем оптимизировать работу с договором на оказание услуг
(изменяемые поля выделены желтым).

В качестве изменяемых полей будут значения из нижеприведенной таблицы 1:

Создаем папку «Договора» и в ней создаем таблицу с названием «Шаблон» и «Реестр» ( кликните на выделенные слова "шаблон" и "реестр", чтобы перейти по ссылке в таблицы)

На первом листе таблицы «Реестр» создаем таблицу с полями:

Заходим в таблицу «Шаблон» и на первом листе создаем форму ввода данных. Просто рисуем и форматируем ячейки так, как будет удобно. Далее значения из нашей формы будут подставляться в шаблон договора.

Далее в таблице «Шаблон» создаем лист «Договор» и переносим туда содержимое договора.
Изменяемые поля размещаем в отдельных ячейках и временно выделяем для удобства желтым цветом.

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

А так же применять инструменты для выравнивания текста:

И инструменты для форматирования:

Теперь самое главное!
Сперва определим алгоритм документооборота:
У нас есть таблица «Реестр», в ней в каждой строке содержатся данные для одного договора. Пользователь заполняет таблицу реестра:

И открывает ее.
В копии заполняется только поле «Номер договора». Данные автоматически подставляются в соответствующие места договора.

Далее создает копию шаблона:

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

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

Переходим на лист «Реестр» в таблице «Шаблон». Встаем курсором на ячейку А1 и вводим следующую формулу:
=importrange("https://docs.google.com/s..";"Реестр!B:O")
Получаем массив данных реестра:

Далее с помощью функции ВПР будем подтягивать по номеру договору, который введет пользователь, данные реестра.

Вид функции: =ВПР(Искомое значение; диапазон в котором осуществляется поиск; номер столбца из которого возвращается значение; точность совпадений (в 99,99% случаев указываем «0» (нуль))

На листе «Данные» встаем на ячейку С4 и вводим формулу:
=ВПР(C3;'Реестр'!A:N;2;0)

В ячейке С4 появится значение:

Н/Д – нет данных. Значение отображается потому что в таблице не найдено пустого значения. Что бы все было красиво и без ошибок включим формулу ЕСЛИОШИБКА
Вид функции: =ЕСЛИОШИБКА(Значение если нет ошибка; Значение если ошибка)
Таким образом наша формула примет следующий вид:
=ЕСЛИОШИБКА(ВПР(C3;'Реестр'!A:N;2;0);"")

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

Теперь введем номер одного из имеющихся в реестре договоров и получим заполненную форму:

Теперь осталось подставить полученные данные в шаблон договора.
В ячейке для номера договора вводим формулу: ='Данные'!C3
Таким образом ссылаемся на соответствующее поле на листе «Данные».
В поле даты договора вводим формулу: =СЕГОДНЯ()
В шаблоне всегда будет фигурировать текущая дата.
В ячейку юридического статуса вводим формулу: ='Данные'!C8
А вот ячейка для ФИО состоит из трех полей на листе «Данные». Поэтому воспользуемся простой текстовой функцией СЦЕПИТЬ и получим такую формулу:

=СЦЕПИТЬ('Данные'!C11;" ";'Данные'!C12;" ";'Данные'!C13)

Следующее поле, которое нужно заполнить – это сумма договора.
Вводим формулу: ='Данные'!C4

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

Заходим в редактор скриптов:

И вставляем фрагмент нижеприведенного кода:

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

Далее сохраняем и задаем название проекта:

Чтобы перевести числовую сумму в текстовый эквивалент, необходимо в таблице воспользоваться функцией =FloatToSamplesInWordsRus() и в скобках указать адрес ячейки, в которой у нас хранится числовая сумма.
Введем формулу со ссылкой на сумму:

=FloatToSamplesInWordsRus(A57)
Получим искомое значение:

Поле наименование содержит данные из двух ячеек формы: юридический статус и наименование.
Поле «юридический статус» в реквизитах нам нужно указать в формате сокращенной аббревиатуры, поэтому предусмотрим все возможные вариации на листе «Данные» и воспользуемся логической функцией ЕСЛИ

Функция ЕСЛИ проверяет ячейку на совпадение с задаваемыми условиями и возвращает одно значение в случае совпадения и другое в противном случае.
Вид функции: =ЕСЛИ(условие; значение если истина; значение если ложь)
В нашем случае функция будет иметь следующий вид:
=ЕСЛИ(C8="ИП";"ИП";ЕСЛИ(C8="общество с ограниченной ответственностью";"ООО";C8))

Вставим ее рядом с полем «Юридический статус» на листе «Данные»:

Вернемся к реквизитам.
Теперь в поле наименования можно ввести формулу со ссылкой на две ячейки листа «Данные»:
=СЦЕПИТЬ('Данные'!D8;" ";'Данные'!C9)
И прописываем ссылки на остальные реквизиты. Получаем искомый результат:

Осталось заполнить место для подписи фамилией и инициалами. Для этого воспользуемся функцией ЛЕВСИМВ
Функция ЛЕВСИМВ отображает заданное количество знаков с левого края значения ячейки.
Вид функции: =ЛЕВСИМВ(значение; количество знаков)
Для того, что бы получить нужное нам значение, нужно воспользоваться функцией СЦЕПИТЬ в связке с ЛЕВСИМВ.
Таким образом формула будет иметь вид (рис.26):
=СЦЕПИТЬ(ЛЕВСИМВ('Данные'!C12;1);".";ЛЕВСИМВ('Данные'!C13;1);". ";'Данные'!C11)

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

Делаем фон однотонно – белым:

Теперь, не снимая выделения с табличной области, сделаем границы ячейки белыми:

Проверяем расположение границ при выводе на печать: