Автоматическая подстановка реквизитов по инн в таблицах google

Простой инструмент, который может экономить время.

Так уж вышло, что мы реализовали почти 500 проектов в «Google Таблицах» и очень хорошо разбираемся в них. Можно прочитать про нас на сайте Helpexcel.pro.

Рабочие процессы многих компаний связаны с таблицами. И все их используют для разных целей. Кто-то для подготовки отчётов, кто-то ведёт реестр продаж, кто-то совершает расчёты и готовит смету для клиента. Разных бизнес-задач в «Google Таблицах» множество.

Одна из частых проблем — автозаполнение документов. Договоры заключаются в каждой компании, и от структуры договора или пакета документов зависят трудозатраты. Как правило, это подстановка данных в шаблоны. То есть пользователь вставляет в определённые места типового документа переменные параметры. Решить задачу можно довольно просто.

Как работает

Заполняем строку таблицы переменными договора

Наименование организации, сумма, условия. У каждого эти поля будут свои.

Создаём копию шаблона

В соответствующей ячейке шаблона меняем номер договора

На соседнем листе формируется договор

Выводим на печать или сохраняем в нужном формате, например в PDF

Решается сразу несколько проблем:

Как сделать автозаполнение самостоятельно

Все решается за счёт двух формул. Просто нужно посмотреть на процесс немного иначе.

Создаем таблицу реестра документов

В нём должны быть переменные поля документов, которые нам нужно заполнять.

Переносим договор для автозаполнения в «Google Таблицу»

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

Формулой IMPORTRANGE нужно подтянуть весь реестр в таблицу шаблона

Формулой ВПР подтянуть переменные величины в соответствующие поля договоры

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

Папка с описанными таблицами. Скопируйте их на свой «Google Диск» (в меню: Файл → Создать копию)

Всем, кто дочитал до этого момента, отправим пошаговую инструкцию с описанием формул и реализации этого кейса.

Успехов!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Следующее поле, которое нужно заполнить – это сумма договора.
Вводим формулу: =’Данные’!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(» «);
}

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

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

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

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

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

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

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

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

Плюсов в работе с google таблицами много, вот только те немногие которые мне очень понравились:

1. Возможность легко расшарить документ
2. Разграничение прав
3. Бесплатно
4. Разграничение прав вплоть до каждой ячейки!
5. Поддержка макросов
6. Возможность написания собственных скриптов
7. Доступно с любой платформы, хоть с древней нокии можно открыть и посмотреть документ.

Про собственные скрипты остановимся поподробнее и напишем простой скрипт для автоматической установки даты в определенные ячейки.

Задача:

 Подскажите пожалуйста формулу для автоматической подстановки даты или времени в необходимую мне ячейку. к примеру. в ячейке А1 я пишу Иванов, в ячейке В1 автоматически подставляется текущая дата или время. далее пишу в ячейке А2 Петров, в ячейке В2 автоматически пишется дата текущая или время.

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

Если вкратце, то нужно создать функцию которая срабатывает в момент редактирования определенной ячейки. Для примера, предположим что нам нужно при добавлении записи в ячейку 17 автоматически подставлять текущую дату в ячейку номер 2, а так же текущее время в ячейку номер 3.  Ещё немного усложним задачу и сделаем проверку, если в соседней ячейке уже есть какая-то запись, то данные не обновлять и не менять дату и время.

function onEdit(e) {
var sheet = e.source.getActiveSheet();
var idCol = e.range.getColumn();
var idRow = e.range.getRow();
if ( idCol == 17 && sheet.getName() =='Страница1' ) {         // тут у меня имя листа, впишите сюда своё
var Value = e.range.offset(0, -13).getValues();            // Смотрим что в ячейке с лева на 12
if ( Value == "" ) {

var vartoday = getDate();
var varnow = getTime();


sheet.getRange(idRow, 2).setValue( vartoday );
sheet.getRange(idRow, 3).setValue(varnow);
}
}
}

// Returns YYYYMMDD-formatted date.
function getDate() {
var today = new Date();
today.setDate(today.getDate());
//return Utilities.formatDate(today, 'PST', 'yyyy.MM.dd');
return Utilities.formatDate(today, 'GMT+05:00', 'yyyy.MM.dd');
}

function getTime() {
var today = new Date();
today.setDate(today.getDate());
//return Utilities.formatDate(today, 'PST', 'yyyy.MM.dd');
return Utilities.formatDate(today, 'GMT+05:00', 'HH:mm');
}

Если кому-то не понятен сей скрипт, пишите в комменты, постараюсь помочь.

Пример работы с подсказками DaData в Google Taблицах