Скрипты для новичков. Часть 1 | @google_sheets
Привет! Открываем серию, в которой мы планируем вам показывать простые приёмы работы с Таблицей (и не только) скриптами, также будем слега касаться JS (языка, на основе которого написан Google Apps Script, язык, на котором мы пишем скрипты в Google Docs)
В этой статье: обращаемся к текущей Таблице, к выбранному листу, забираем диапазон заполненных ячеек и разбираемся, что нам возвращается.
Обращаемся к Таблице, Листу, забираем данные
//определяем активную таблицу
const ss = SpreadsheetApp.getActive();
//определяем в ней Лист1
const sheet = ss.getSheetByName(‘Лист1’);
//определяем на Лист1 диапазон с данными и забираем его значения
const data = sheet.getDataRange().getValues();
//возвращаем эти значения
Запускаем функцию в редакторе скриптов – функция выводит в лог заполненные ячейки.
Помимо этого функция возвращает массив заполненных ячеек ( return data) и его можно использовать в другой функции, что нам пригодится дальше.
Что нам вообще возвращается
Функция возвращает несколько массивов (array) в массиве.
Строк с данными несколько, они отделены от друг-друга запятыми и объединены внешним массивом.
Если бы у вас было только две строки с данными по две ячейки в каждой, то эта конструкция выглядела бы так:
А ЕЩЕ наша функция сможет работать как пользовательская функция, её можно вызвать просто на листе, не запуская скрипт (смотрите скриншот)
Итак, у нас есть прекрасный массив с данными, как обращаться к нему, как посчитать количество строк с данными, как найти последнюю строку вообще, последнюю строку и выбранном столбце, последний столбец – мы разберёмся в следующей главе.
Как писать скрипты, макросы и код в Google Scripts — часть 1
Доброго времени суток, дорогие читатели!
Как некоторые могут помнить, у нас была одна статья на тему программирования в Google таблицах. Она была больше как эксперимент, который показал, что в общем-то это направление довольно популярно (да-да, про эксель я не забыл, он тоже будет. рано или поздно, но будет).
Тем, кто уже знаком с такими понятиями, как: функция, переменная, оператор, массив и тому подобное; будет (по всей видимости) скучновато, так что в таком случае рекомендую просмотреть статью по диагонали, вдруг что интересное для себя найдете.
Итак, вы уже знаете где находится редактор скриптов (если кто забыл, смотрите предыдущую статью), открываете его и. идете заваривать чай (кофе, какао, глинтвейн), а я пока расскажу немножко теории ( которую заодно надо бы освежить в памяти ).
С чего начинается любой скрипт? Очевидно, что с какого-то кода, но с какого? Вполне может так быть, что у вас уже появляются такие мысли при виде этого, а точнее того, белого экрана. Но это нормально. Когда мало опыта и много непонятного, хочется все бросить и лечь спать. Но не отчаивайтесь! И отойдите от кровати! Если я не буду писать это в некотором ироничном виде, то от моей заунывной статьи вы уже на третьей строчке усне..
Так вот, с чего нам начать? Я сейчас не имею в виду алгоритм действий, который вы держите в голове, или блок-схемы на листочке, нет. Нам следует начать с. объявления функции.
Объявление функции имеет определенный формат вида:
Где вместо FUNCTIONNAME вы можете ввести любое название, кроме уже зарезервированных системой гуглом, а в скобочках вы можете указать параметры, которые бы хотели передать в эту функцию.
Часто можно обойтись одной функцией, особенно, если у вас небольшая задачка. Однако при достаточно большом куске кода лучше всего делить функцию на несколько меньших функций, где одна вызывается из другой. Но об этом в следующей серии.
Итак, вы определились с вашей первой функцией, теперь что? А теперь нужно объявить переменные. Для вашей позиции в торговом автомате переменными могут быть: номер ряда, номер колонки, картинка позиции, её цена.
Переменными в общем случае может быть любой набор символов любого типа. Объявляются они очень просто, достаточно сказать котелок вари
И ой, она уже объявлена. А для чего это нужно?
Когда компилятор (такая штука, которая превращает удобочитаемый код для нас в машинный набор нулей и единиц для компьютера) так вот, когда компилятор начинает последовательно читать ваш код (а делает это он либо при сохранении вами кода, либо при запуске) то встречая неизвестные ему названия он первым и последним делом ищет где вы объявили ему (рассказали что это такое и что с этим делать) эти названия.
Однако, если вы напишете:
Если же вы объявите переменную, но не напишете чему она равна, то компилятор будет считать, что значение не определено ( undefined ).
Давайте подробнее остановимся на операторах. Есть несколько категорий:
В чем же разница между первым и вторым оператором? (ответ в следующей строке, выделите мышкой)
false
true
| X | Y | X&Y | X+Y | !X | !Y | !X&!Y | !X+!Y |
| 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0 |
| 1 | 0 | 0 | 1 | 0 | 1 | 0 | 1 |
| 0 | 1 | 0 | 1 | 1 | 0 | 0 | 1 |
| 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1 |
Итак, в общем-то с основами разобрались. Теперь немного практики. Задача:
У нас есть некоторый набор значений в ячейках (cкажем, три ряда по четыре колонки случайных чисел), нам надо отдельно вывести сумму каждого ряда и его среднее значение (учитывая что мы не знаем о встроенных функциях, а пишем сами).
Таблица для нашего примера:
| A | B | C | D | |
| 1 | 2 | 3 | 4 | 1 |
| 2 | -4 | 12 | 3 | 5 |
| 3 | 1 | 1 | 2 | 5 |
Итак, с чего начать? Нет, не с написания кода, сначала надо прикинуть план действий (хотя бы очень грубо).
Путем нехитрого гугления мы достаточно быстро выясним как вытащить значение ячейки из таблицы. Но какой смысл копировать код, ничего в нем не понимая? Правильно, никакого. С точки зрения.. ммм.. гугла таблица представляется собой объект (класс), состоящий из множества подклассов (типа матрешки). Иерархия там примерно такая:
Таким образом, мы можем получить значение как одной ячейки, так и диапазона ячеек. Воспользуемся вторым вариантом. Для этого пишем соотв. код:
Создаем нашу функцию:
Почти всегда первая строчка функции начинается со ссылки на таблицу. И здесь мы ссылаемся именно на текущую таблицу (ведь мы можем ссылаться и на другой лист, на другой файл и тп.).
Теперь достанем наши значения из ячеек:
Да, не очень красиво, но работает. Кстати проверить результат можно несколькими способами:
У кого не получилось, код целиком ниже:
Итак, первый пункт выполнен. Теперь приступаем ко второму. Учитывая, что мы (теперь) знаем сумму ряда и знали с самого начала кол-во чисел в ряду, добавляем еще три переменные и проверяем (значком я обозначаю место, где можно поставить Breakpoint для быстрого просмотра результатов):
При этом, мы должны указать данные точно в таком же формате, как мы их получили. Присваивание значений массивам в общем-то достаточно простая задача. Допустим у нас есть значения 1, 2, 3, 4, 5 и нам нужен массив. Объявляем:
Выглядит криповато, но массив можно записать еще таким образом:
А это уже другое дело! Хотя тем, кто на этих массивах уже не первую собаку съел, первый способ может быть удобней, т.к. быстрее.
Теперь же осталось дело за малым. Записать сумму и среднее для каждого ряда:
Запускаем и смотрим что у нас происходит на листе:
Послесловие
Итак, дабы не перегружать вас (дорогих читателей) информацией, первая часть на этом радостном событии завершена. Небольшой итог:
Теперь вы имеете представление что такое функция, переменная, зачем они нужны, какие операторы бывают и что делают, а также усвоили (я надеюсь) базовые навыки по работе с таблицами.
В следующей серии поговорим о том, как можно улучшить код, дабы не плодить кучу переменных и иметь возможность работать с таблицей значений переменной длинны (т.е. когда мы заранее не знаем сколько рядов и строк нам надо). А также немножко разобьем все это дело на несколько функций. Stay Tunned!
Пишем скрипты для автоматизации работы с приложениями Google
Содержание статьи
Google Apps Script — это язык для автоматизации работы с онлайн-приложениями, появившийся в 2009 году. Его основа — классический JavaScript, обогащенный расширениями для работы с сервисами Google. После прочтения этой статьи ты овладеешь основами использования этого языка, выучишь пару приемов манипуляции с почтой и документами, а также получишь представление о необозримых возможностях Google Apps Script.
Основы использования
Хакер #187. Обходим Blizzard Warden
Теперь в нашем меню есть пункт под названием ExampleFunc, при клике на который открывается однострочное подменю «Моя единственная функция».
Декларированные функции можно использовать в формулах, которые вводятся внутрь ячеек электронных таблиц (см. рис. 3). Теперь перейдем к более практически полезным примерам.
Рис. 2. Редактор кода, заполненный автогенерируемым сырьевым материалом для работы с электронной таблицей 
Рис. 3. Вызов пользовательской функции с аргументом
WARNING
Работа с Google Docs
Когда встает задача автоматизации работы с офисными документами, первым делом на ум приходит VBA, одно упоминание которого оказывает на многих тотальное антиэкстатическое воздействие, вызывая болезненные воспоминания из школьного и университетского прошлого. Google Script однозначно удобнее и доступнее для понимания. Особенно для веб-разработчиков, ведь это же родной, привычный и любимый JS! Разберем пару примеров скриптов для Google Docs. Приведенный код заполняет левую верхнюю ячейку первого листа активной таблицы:
А этот код создает копию текстового документа и кладет его в определенное место:
А вот так можно провести замену строк в текстовом документе:
Следующий пример кода подсвечивает определенные слова в тексте:
Рис. 4 Вот здесь можно узнать ID документа
Если пишешь скрипт, включающий в себя функции поиска/замены текста, помни о том, что GS поддерживает регулярные выражения.
Работа с почтой
Письмо отправляется одной короткой строкой: MailApp.sendEmail(«irairache@gmail.com», «тема письма», «текст письма») Если добавить к ней еще немного кода, то можно организовать рассылку по списку адресов из электронной таблицы (исходник ищи в приложении):
Помимо рассылок, с помощью GS можно производить автоматизированную обработку содержимого почтового ящика. Пример — удаление всех писем от адресата, на которого ты обиделся:
WARNING
Имей в виду, что Gmail не только защищает от входящего спама, но и ограничивает рассылку исходящего. Больше 500 писем за сутки с помощью Google Apps Script не выйдет.
Работа с Google Translate
С помощью Google Apps Script можно переводить текстовые строки с одного языка на другой. Пример:
Коды для языков можно посмотреть в адресной строке сервиса Google Translate.
Работа с Google Drive
Google Apps Script может работать с файлами пользователя, размещенными на Google Drive. Этот скрипт выводит в консоль имена всех файлов пользователя:
К файлам можно применять несколько десятков различных методов. Вот некоторые из них:
Работа с Google Contacts
Адресная книга также может быть подвергнута автоматизированной обработке. Приведенный ниже код копирует все контакты из группы «Редакция» в лист Google Spread Sheet:
Работа с Google Tasks
С помощью Google Apps Scripts можно работать с сервисом Google Task — создавать новые задачи и парсить уже имеющиеся.
Этот код создает новое дело в списке:
А таким образом можно вывести список нумерованных задач в консоль:
Задачи можно перемещать из одного списка в другой с помощью метода move, дополнять с помощью метода update и удалять с помощью метода delete.
Всего есть несколько десятков методов для работы с задачами. Полный их список доступен вGoogle Apps Script References для Google Tasks.
Работа с календарем
Создавать события в календаре тоже можно автоматически (и так же, как в случае с рассылкой, формировать информацию о них из строк таблицы). Код для создания события:
Формы обмена скриптами
Есть два основных способа поделиться своим скриптом с другим человеком (без учета непосредственного обмена исходным кодом) — ссылка и гаджет. В первом случае все просто: пользователь получает ссылку на программу, переходит по ней, и скрипт немедленно начинает выполняться (при условии, что человек авторизирован в своем Google-аккаунте).
События
Можно настроить скрипт так, чтобы он выполнялся после определенного события. К примеру, после открытия/редактирования электронной таблицы или отправки данных формы. Подробности о работе с Events
Работа с базами данных
Для этого существует сервис для работы с базами данных Google Cloud SQL. По сути — классический MySQL в облаке. Может взаимодействовать с Google Apps Script по стандарту Java Database Connectivity. Вот пример кода, который производит чтение записей из таблицы:
Стоимость использования сервиса — 88 долларов в год за 10 Гб свободного места. С другими базами данных Google Apps Script, к сожалению, работать не может. Если ты запланировал написать скрипт, который должен взаимодействовать с данными, не стоит сразу расстраиваться или истощать свой бюджет пожертвованиями на закупку квадроциклов для жителей Кремниевой долины. Есть два способа выкрутиться из этой ситуации:
Приложения, с которыми может взаимодействовать Google Apps Script
Я думаю, что далеко не каждый из наших читателей успел опробовать все онлайн-сервисы Google. В целях расширения кругозора и стимуляции творческого воображения приведем краткий обзор возможностей приложений, работу которых можно автоматизировать с помощью Google Apps Script.
По старой доброй традиции, которой уже почти три месяца, мы выложили несколько исходничков на GitHub:
Advanced Google Services
У Google есть множество API для разработчиков, которые можно внедрять в программы, написанные на Google Apps Script. Для этого надо подключить в редакторе скриптов эту возможность (в меню Resources, далее Advanced Google services). После этого можно будет задействовать возможности следующих сервисов:
Google Docs, Google Drive, Google Scripts: как писать скрипты, макросы и код — часть 0
Я думаю, что очень многие из Вас умеют пользоваться Excel ‘ем или его аналогом, а некоторые, может, даже и гугловскими таблицами, про которые писали здесь.
Те, кто пользуется диском Google (Google Drive ), наверное уже использовали Таблицы (Spreadsheets ) и заметили, что по функционалу они немного уступают Экселю, но тем не менее это всё ещё мощный инструмент.
Я заранее Вас предупреждаю о возможной сложности дальнейшего примера, т.к. он не столько обучающий, сколько.. Мм.. Так сказать, конечный факт, которым Вы можете пользоваться и.. И развивать, если это Вам знакомо.
Соберитесь в комочек мозга.. И приступим 🙂
Создание таблицы Google Drive / Scripts и наполнение её контентом
Рассмотрим такую простенькую задачку:
У нас есть две колонки, в первой мы пишем названия фруктов, а во второй цвет, который соответствует этому фрукту. И мы хотим, чтобы при вводе цвета в колонке цветов автоматически менялся бы цвет названия фрукта.
Теперь добавляем на первый лист наши фрукты и цвета:
Примечание! Для того, чтобы считались фрукты, введите в ячейку А1 формулу:
Теперь создадим макрос. Для этого идем в меню » Инструменты » и выбираем » Управление скриптами «. Появится всплывающее меню, где мы жмем на кнопку » Создать «.
В появившемся окошке выбираем » Пустой проект «.
Откроется редактор, который на первый взгляд (да и на второй) может вызвать ступор.
Собственно, что дальше? А дальше мы начинаем писать наш собственный макрос ручками (да, всё самостоятельно). Как будет выглядеть наш макрос? Нужно составить схемку сего процесса (иначе этот процесс займет у Вас очень много времени).
Итак.. Вроде бы всё просто.. Если знать, как это делать, конечно 🙂
шКоддинг
Перейдем к самому коду:
Теперь я постараюсь Вам его объяснить. Функция onOpen добавляет меню » Скрипты » к таблице при открытии оной. И выглядит это дело так:
Эта строчка добавляет в переменную sheet идентификатор открытого нами документа, чтобы потом по нему обращаться к документу.
Эта переменная-массив содержит список названий менюшек и функций, которые выполняются при клике на эти менюшки.
Этот метод добавляет к нашему документу меню » Скрипты «.
Функция MakeMeHappy, собственно, и будет нашей главной функцией, которая красит фрукты.
Сначала я объявляю переменные:
Соответственно, в переменной sheet находится идентификатор нашего документа. В переменной range находится выделенная нами область (например, ячейки B2:B6 ), в переменной data находятся значения этих ячеек в виде массива.
В этом условии мы проверяем, что выбранный диапазон ячеек соответствует второй колонке (в которой цвета фруктов).
В этом цикле мы проходимся по каждой ячейке из диапазона B2:B
..Будет эквивалентно функции:
Методы setFontColor и setBackgroundColor задают цвета текста и фона в виде #rrggbb (r-red, g-green, b-blue, диапазоны цветов) соответственно.
В общем-то на этом всё. Но не совсем.
Скрипты и макросы таблиц Google, дополнение
Но можно сделать небольшое дополнение, чтобы не приходилось каждый раз запускать функции вручную.
Они будут приходить в случае, если скрипт выполнялся с ошибками.
Конечный результат действа:
Послесловие
Как и всегда, будем рады вопросам и всему такому прочему в комментариях. Следующая часть этого материала, более детально обучающая процессу, теперь обитает по этой вот ссылке, а следующая по этой.
Продолжения раз-два-готовим и три. Ну и комментарии конечно содержат много вкусного.
Как написать скрипт для гугл таблицы
Этот пост будет актуальным для тех, кто хочет писать скрипты в гугл таблице (макросы) для себя или зарабатывать на этом. На фрилансерских сайтах бывают задачи по написанию скриптов и это умение может приносить доход. Готовые фрагменты помогут быстрее справиться с разными задачами по написанию макросов.
Время от времени пост будет обновляться и дополняться новыми материалами, поэтому сохраните ссылочку на него ?
Рассмотрим такие вопросы:
Операции с таблицей
Для того, чтобы програмно работать с таблицей, нужно к ней обратиться. Рассмотрим несколько способов.
Обратимся к активной таблице (в которой работаем в текущий момент). Для этого используем конструкцию:
В результате мы сможем обращаться к нашей таблице, сославшись на переменную ss. Например, обратиться к активному листу (что открыт у вас в текущий момент):
У нас в переменной activeSheet будет ссылка на активный лист таблицы
Также в гугл-таблице можна обратиться по ее идентификатору или по ссылке на таблицу.
Я предпочитаю использовать идентификатор. Как узнать идентификатор гугл-таблицы, можно прочитать ЗДЕСЬ.
Это может пригодиться, если вы работаете в одной таблице и вам нужно воспользоваться данными из другой (что-то взять, изменить и пр.). Привожу пример кода:
Точно, как и в предыдущем случае, в переменной ss находится ссылка на таблицу и в дальнейшем можно ее использовать.
Как можно заметить, во всех вышеперечисленных случаях мы используем специальный класс SpreadsheetApp. Он имеет много различных методов и свойств, например можно создать новую таблицу (SpreadsheetApp.create(“имя нового файла”);) и т.п.
Информацией о том, что и как я использовал в своих проектах, а также разные полезности (создание, удаление и пр.), описаны ЗДЕСЬ, а сейчас продолжим.
.
Операции с листом.
Прежде, чем работать непосредственно с данными, нам нужно обратиться к листу, где собственно эти данные находятся. Здесь также имеем несколько способов.
Обращаемся к коллекции листов в активной таблице и берем первый (нумерация начинается с нуля)
Обращаемся к активному листу
Обращаемся к листу по имени (в примере “Лист1”).
Естественно, что подобными способами вы можете обращаться к листам в другой (не активной) таблице. Но есть один нюанс, который касается способа номер 2 (обратиться к активному листу).
Если вы обращаетесь к другой таблице (открываете по идентификатору или ссылке) и обращаетесь к активному листу (getActiveSheet()), вы получаете первый лист, даже если при этом таблица будет открыта у вас в соседней вкладке на нужном вам листе. Поэтому, лучше четко обозначать лист, к которому вы обращаетесь (либо по имени, либо по номеру).
Дополнительные материалы по работе с листами (удаление, переименование, копирование и пр.) – в отдельной статье.
.
Операции с ячейкой.
Обращаемся к ячейке.
В первом варианте мы обращаемся с ячейке “A1”, указав явно ее адрес (А1), во втором используем номер строки и колонки (1-я строка, 1-я колонка). Сначала указываем номер строки, затем номер колонки.
Т.е., чтобы обратиться к первой ячейке в третьей строке, нужно указать (3,1).
Предпоследний и последний способ – получить ячейку, активную (выделенную) в текущий момент.
В последнем случае в переменную попадет выделенный диапазон (если вы выделите именно диапазон (блок ячеек). Про диапазоны и работу с ними будет чуть дальше.
Как вы могли догадаться, в переменной sheet лежит ссылка на лист, которую мы можем получить одним из способов, указанных выше.
Теперь, когда мы имеем в переменной myCell нашу ячейку, мы можем делать с ней ряд действий, например.
1. Вписать в ячейку нужное значение:
Вписываем в нашу ячейку строку “Новое значение”
2. Получить текущее значение ячейки:
Сохраняем в переменную a1 значение ячейки A1
Обратите внимание, что в первом случае используется метод setValue (от англ. установить значение), во втором – getValue (от англ. взять значение).
В дополнение к сказанному. Есть метод “getDisplayValue”, который возвращает значение ячейки в том виде, в каком их видно на экране. Это значит, что если вы например применили к числу форматирование и видите на экране в ячейке что-то типа “1 078 234,13”, то вы это же и получите в переменной, если используете “getDisplayValue”. Это также касается и использования форматирования для ячеек с датой и временем. Однако, не все так просто и могут быть нюансы.
Как и что использовать, решать вам. Я же предпочитаю получать значения, как есть, с помощью “getValue”, а уже потом, после обработки, при выводе полученных значений на лист форматировать так, как необходимо для задачи.
Продолжим. С помощью специальных методов можно установить фон заливки ячейки:
и узнать фон заливки ячейки:
В данном примере я использовал директиву Logger.log для вывода информации в консоль. Это иногда очень удобно при отладке программного кода. Хочу обратить ваше внимание, что цвет заливки выводится в формате #ffff00 (соответствует желтому цвету).
Подобными способами можно изменить форматирование нашей ячейки, размер шрифта и все остальное, что возможно сделать с ячейкой в гугл-таблице.
Список всех доступных методов открывается, когда вы в редакторе кода после переменной с ссылкой на нашу ячейку ставите точку.
Таким образом можно получить доступные методы и свойства для всех объектов, которым соответствует ранее назначенная переменная.
Пояснение. К примеру, если вы ранее в коде в переменную myVar сохранили ссылку на лист, то можно получить список всех доступных методов и свойств листа, поставив в редакторе после имени переменной точку ( myVar. ).
Специально для читателей блога я записал несколько методов, которыми пользуюсь чаще остальных. Ознакомиться с описанием и примерами кода можно в отдельной статье.
В завершении текущего блока хочу поделиться небольшим “лайфхаком”.
Один из способов что-то узнать, если забыл (или просто не знаешь), использовать встроенный инструмент “Записать макрос”.
Вы записываете макрос с нужными вам действиями (что хотите реализовать с помощью скрипта, но не знаете как) и затем сохраняете его. 
Теперь вы можете открыть код макроса и подсмотреть, что и как реализуется. Я иногда пользуюсь таким способом, чтобы быстро что-то узнать ?
Операции с диапазоном.
Обратиться к диапазону.
Получаем в переменной myRange нужный диапазон.
Расшифровка. В переменную myRange попадает диапазон c началом в ячейке с координатами (1,1) и окончанием в ячейке с координатами (2,3) – вторая строка, третья колонка.
Важное уточнение. В директиве getRange указывается: номер строки (начало диапазона), номер колонки (начало диапазона), кол-во строчек (размер диапазона по вертикали, кол-во колонок (размер диапазона по горизонтали).
К примеру, если мы получаем нужный диапазон вот-так:
То наш диапазон имеет координаты (3 – номер строки (начало), 4 – номер колонки (начало), 12 – номер строки (конец), 8 – номер колонки (конец).
Почему не 13-я строка, а 12-я? Потому что, размерность по вертикали – 10. От строки номер 3, включая саму строчку номер 3 – 12 строка – как раз выходит 10 строчек. По горизонтали – то же самое.
Иногда, чтобы на первых порах не путаться, где начинается и заканчивается диапазон, можно для тестирования закрасить диапазон (например, в желтый), чтобы визуально убедиться, что мы определили размерность диапазона правильно:
Закрасим диапазон желтым и посмотрим, правильно или нет определились с размерами ?
Иногда необходимо обработать диапазон, у которого нижняя граница – динамическая (постоянно меняется). Например, табличка со списком клиентов, который постоянно меняется. Как нам в таком случае получить наш диапазон?
На помощь приходит метод, который позволяет получить номер последней строки, где на листе есть данные – “getLastRow” (получить последнюю строчку). Как вы помните, в методе getRange предпоследним параметром идет кол-во строк (размер диапазона по вертикали).
Если в нашей условной табличке со списком клиентов данные начинаются со второй строки (в первой – оглавление таблицы), то кол-во строк в таблице равно getLastRow() – 1.
Для определения правой границы таблицы можна воспользоваться getLastColumn – номер последнего столбца с данными (крайний правый).
Еще вариант, как получить динамический диапазон, если вы знаете его границы по ширине:
К примеру, у вас таблица заканчивается (правая граница) на колонке F. В таком случае получить диапазон:
В результате мы в переменной myRange получим все значения нашего динамического диапазона (учитываем, что в первой строке идут названия столбцов таблицы, поэтому начинаем с A2).
В отличии от работы с ячейкой, работа с диапазон имеет ряд нюансов. Рассмотрим подробнее.
Во первых, диапазон – это, как правило, несколько значений. Поэтому и работа с ними отличается от работы с ячейкой.
В зависимости от задачи, мы можем работать с диапазоном напрямую (в нашем примере с помощью переменной myRange), либо сразу считать значения из диапазона в массив и затем работать со значениями.
Если мы работаем напрямую с диапазоном (с помощью объекта Range), мы имеем по сути массив, каждым елементом которого есть ячейка, которая входит в наш диапазон.
Соответственно, мы имеем доступ ко всем свойствам ячейки – значение, цвет заливки и пр. Например, чтобы получить значение левой верхней ячейки, необходимо написать:
В результате в переменной a1 имеем значение ячейки с координатами 1,1 (левой верхней ячейки)
Соответственно, что-то записать в левую верхнюю ячейку можно так:
Подобным способом можно работать и с остальными свойствами ячеек в нашем диапазоне.
Получить диапазон также можно и неявным способом (не зная конкретных размеров). Например, если пользователь выделил произвольный диапазон на листе, то получить его можно с помощью метода:
На всякий случай напоминаю, что в переменной sheet лежит ссылка на наш активный лист.
Перебрать все элементы нашего диапазона (читай, ячейки) можно с помощью цикла.
Вначале нам необходимо узнать размеры нашего диапазона – кол-во строчек и столбцов. Для этого есть методы “getNumRows” (получить кол-во строк) и “getNumColumns” (получить кол-во столбцов).
В приведенном коде мы перебираем подряд все ячейки выделенного диапазона и каждой присваиваем значение – от 1 до общего кол-ва ячеек в нашем диапазоне.
Конечно же, вместо присвоения ячейке нового значения можно воспользоваться другим методом работы с текущей ячейкой (исходя из вашей задачи).
Иногда приходиться работать с большими диапазонами, сравнивать несколько диапазонов и прочее. В таких случаях более оптимальным решением есть сохранить содержимое диапазонов в массивы и работать с массивами (по сути обрабатывать данные в оперативной памяти).
По факту мы вначале загружаем все необходимые диапазоны с данными в массивы, затем выполняем определенные операции и по завершению обработки выгружаем в таблицу (или куда-то передаем) полученный результат.
Так получается намного быстрее. А учитывая тот факт, что время работы скрипта ограничено 6-ю минутами, иногда это важно для решения определенных задач.
Дополнительные возможности при работе с диапазонами и массивами описаны в отдельной статье. Здесь же я отмечу, что для того, чтобы считать значения диапазона в массив, необходимо использовать метод “getValues”.
Например, сохраним в массив выделенный диапазон:
Или более читабельный вариант (учитывая созданные ранее переменные)
Данным кодом мы в переменную myArray получили значения выделенного диапазона на активном листе активной таблицы.
.
Создание своего меню
Запускать тот или иной скрипт удобно с помощью пользовательского меню. Конечно же, можно запустить скрипт непосредственно из редактора скриптов, либо при наступлении определенного события, клике на картинке и пр.
В данном разделе коснемся создания своего меню.
В гугл таблицах пользовательское меню бывает двух типов:
Скриншот обычного меню:
Скриншот дополнительного меню:
В качестве “донора” для скриншотов была использована таблица “Ведение семейного бюджета” – расширенная версия.
Когда и какие виды меню использовать – зависит от поставленных задач. От себя добавлю, что меню создается с помощью скрипта. Часто скрипт, который создает требуемое меню, запускается при открытии гугл таблицы – используя специальное событие “открытие таблицы”.
Более детально об использовании различных меню с примерами кода можно посмотреть ЗДЕСЬ.
Далее коснемся вопроса работы с событиями гугл таблицы.
.
Работа с событиями таблицы
Когда что-то происходит с таблицей или данными, то возникает так называемое событие.
Например, когда вы открываете таблицу, либо редактируете данные, возникает событие “Открытие таблицы” или “Редактирование таблицы” соответственно.
Следовательно, вы можете использовать событие – запустить нужный вам скрипт.
Что здесь имеется ввиду. Например, когда вы открываете таблицу – срабатывает событие, которое в свою очередь запускает нужный скрипт, например создание пользовательского меню.
Для того, чтобы в определенное событие запустился определенный скрипт, необходимо либо создать специальную функцию с четко установленным именем (которое определено разработчиками гугл таблиц), либо создать специальный триггер, который сработает при возникновении события и запустит заданную вами функцию.
Подробнее об использовании событий с примерами кода читайте ЗДЕСЬ.
.
Готовые решения, наработки, примеры кода
На нашем блоге есть обзорная статья, где собраны различные наработки, связанные с работой в гугл таблицах, программные решения и прочая полезная информация. Переходите по ссылке и пользуйтесь ?
Надеюсь, что данный пост будет полезен.
Если есть вопросы и пожелания – пишите в комментариях.
Теперь вы знаете какие однокоренные слова подходят к слову Как написать скрипт для гугл таблицы, а так же какой у него корень, приставка, суффикс и окончание. Вы можете дополнить список однокоренных слов к слову "Как написать скрипт для гугл таблицы", предложив свой вариант в комментариях ниже, а также выразить свое несогласие проведенным с морфемным разбором.