Улучшил макрос через Google Apps Script для решения задачи

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

Эта таблица реализована через google таблицы для того чтобы скрыть элемент интеллектуальной собственности (формулы). 

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

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

У клиента 300 таких проектов. Каждый проект можно построить в 13 типовых вариантах комплектации. Получается 3 900 разных цен. 

Представьте что нужно сделать, если себестоимость какого-либо материала изменилась (а такое происходит довольно часто) и нужно пересчитать все вариации во всех проектах. 

В ручную, физически такое сделать оочень сложно и уж точно, абсолютно нецелесообразно. 

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

Получается, ты в одной таблице изменил цену на материал и все 3900 цен сразу, автоматические пересчитались.

Далее через отдельный скрипт написанный программистом, цены автоматически меняются на сайте.

Согласитесь очень удобно.

Такое можно проделать и с  комплектациями. 

И вот всё шло хорошо, пока не понадобилось изменить саму логику ценообразования, саму финансовую модель. Для этого в каждой таблице, в каждой комплектации есть коэффициент. И вот его-то и понадобилось изменить. То есть сделать 3 900 изменений в трех ста таблицах. 

Передо мной стояла большая монотонная работа. 

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

Начало работы с макросом

Чтобы привязать коэффициент к материнской таблице нужно сделать несколько простых действий:

  1. Скопировать и вставить ячейку с ссылкой на материнскую таблицу, и ячейку с диапазоном. 
  2. В ячейке с диапазоном изменить адрес на нужную ячейку
  3. Далее в месте где стоит диапазон, вставить функцию impotrange

Вот и всё. 

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

А так как в душе я программист и люблю строить автоматизированные системы, то решил это всё усовершенствовать. 

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

Вот как выглядит код этого макроса:

Авто подставка названия листа

Первое что не работает в этом коде, это то, что макрос в код записал название листа на котором я записывал макрос «spreadsheet.getCurrentCell().setValue(‘Лист 1!i3:i3′);«. А нам нужно чтобы название листа менялось и соответствовало текущему листу.
 
Для этого я ввожу новую переменную «name» которой присваиваю значение имя листа «getName()«, активного листа «getActiveSheet()«. Далее переменную «name» подставляю туда где должно быть имя листа.
Теперь код выглядит так:

Выполнение скрипта на нужных листах

Теперь макрос работает хорошо, но его надо активировать на каждом листе каждой таблицы. То есть в одной таблице нужно 13 раз активировать макрос. 

Моя лень подталкивает оптимизировать и этот процесс. 

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

Для этого ввожу новую переменную «sh» на которую назначаю функцию выбора определённого листа «SpreadsheetApp.getActiveSpreadsheet().getSheetByName(«Имя нужного листа»);»

Далее активирую лист функцией «spreadsheet.setActiveSheet(sh);» с аргументом «sh» на котором висит имя нужного нам листа. 

Далее выполняется скрипт нужных нам действий. 

Код выглядит так: 

Ну и теперь нужно просто повторить этот код 13 раз, меняя название нужных листов и имена переменных. 

Теперь весь код выглядит так:

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

Теперь, чтобы заменить коэффициенты во всех трехста таблицах, я открываю каждую, захожу в «расширения»/»Apps script», вставляю код, сохраняю. 

Далее захожу в «расширения»/»макросы»/»импортировать макрос», выбираю единственный с названием «ZamenaKof2» и нажимаю галочку. 

Далее опять захожу в «расширения»/»макросы» и выбираю появившийся макрос «ZamenaKof2». 

Он начинает выполняться, но в целях безопасности гугл не даёт и просит предоставить разрешение к аккаунту.

Даешь разрешение, заново активируешь макрос и он выполняется по всем листам. 

И так теперь с каждым файлом проекта. То есть 300 раз. 

Да, дело всё ещё трудозатратное, но в 10 раз быстрее чем делать 3900 изменений вручную. 

Что ещё можно было улучшить?

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

Но если честно у меня не хватило на это терпения. 

Во-первых я погугли пример такого скрипта в РУ-нете  и ничего не нашёл. Да, можно было поковыряться в мануале и найти нужные команды. 

Но во-вторых, у меня ни как не получилось развернуть даже тестовый скрипт через библиотеку Apps Script. Ни как не найти нужные идентификаторы. И статей с примерами опять же не нашёл. И именно это, в большей степени, меня остановился от дальнейшей оптимизации.

В общем сделал работу хоть и не до конца в автоматическом режиме, но всё-таки значительно облегчив монотонные ручные правки и самое главное — потенциальные ошибки из-за ручного труда. 

Данную статью пишу для тех, кто будет что-то искать по теме Google Apps Script и возможно моя статья поможет. 

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

Добавить комментарий