Этот пост будет актуальным для тех, кто хочет писать скрипты в гугл таблице (макросы) для себя или зарабатывать на этом. На фрилансерских сайтах бывают задачи по написанию скриптов и это умение может приносить доход. Готовые фрагменты помогут быстрее справиться с разными задачами по написанию макросов.

Время от времени пост будет обновляться и дополняться новыми материалами, поэтому сохраните ссылочку на него 🙂

Рассмотрим такие вопросы:

1. Операции с таблицей
2. Операции с листом
3. Операции с ячейкой
4. Операции с диапазоном
5. Создание своего меню
6. Работа с событиями гугл таблицы
7. Готовые решения, наработки, примеры кода

.

Операции с таблицей

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

Обратимся к активной таблице (в которой работаем в текущий момент). Для этого используем конструкцию:

let ss = SpreadsheetApp.getActiveSpreadsheet();

В результате мы сможем обращаться к нашей таблице, сославшись на переменную ss. Например, обратиться к активному листу (что открыт у вас в текущий момент):

let activeSheet = ss.getActiveSheet();

У нас в переменной activeSheet будет ссылка на активный лист таблицы

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

Я предпочитаю использовать идентификатор. Как узнать идентификатор гугл-таблицы, можно прочитать ЗДЕСЬ.

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

let ss = SpreadsheetApp.openById('идентификатор таблицы'); //используем идентификатор

let ss = SpreadsheetApp.openByUrl('ссылка на таблицу'); //используем ссылку

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

Как можно заметить, во всех вышеперечисленных случаях мы используем специальный класс SpreadsheetApp. Он имеет много различных методов и свойств, например можно создать новую таблицу (SpreadsheetApp.create(«имя нового файла»);) и т.п.

Информацией о том, что и как я использовал в своих проектах, а также разные полезности (создание, удаление и пр.), описаны ЗДЕСЬ, а сейчас продолжим.
.

Операции с листом.

Прежде, чем работать непосредственно с данными, нам нужно обратиться к листу, где собственно эти данные находятся. Здесь также имеем несколько способов.

1. SpreadsheetApp.getActiveSpreadsheet().getSheets()[0]

Обращаемся к коллекции листов в активной таблице и берем первый (нумерация начинается с нуля)

2. SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()

Обращаемся к активному листу

3. SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Лист1")

Обращаемся к листу по имени (в примере «Лист1»).

Естественно, что подобными способами вы можете обращаться к листам в другой (не активной) таблице. Но есть один нюанс, который касается способа номер 2 (обратиться к активному листу).

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

Дополнительные материалы по работе с листами (удаление, переименование, копирование и пр.) — в отдельной статье.
.

Операции с ячейкой.

Обращаемся к ячейке.

1. let myCell = sheet.getRange("A1");

2. let myCell = sheet.getRange(1,1);

3. let myCell = sheet.getActiveCell();

4. let myCell = sheet.getActiveRange();

В первом варианте мы обращаемся с ячейке «A1», указав явно ее адрес (А1), во втором используем номер строки и колонки (1-я строка, 1-я колонка). Сначала указываем номер строки, затем номер колонки.

Т.е., чтобы обратиться к первой ячейке в третьей строке, нужно указать (3,1).

Предпоследний и последний способ — получить ячейку, активную (выделенную) в текущий момент.

В последнем случае в переменную попадет выделенный диапазон (если вы выделите именно диапазон (блок ячеек). Про диапазоны и работу с ними будет чуть дальше.

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

Теперь, когда мы имеем в переменной myCell нашу ячейку, мы можем делать с ней ряд действий, например.

1. Вписать в ячейку нужное значение:

myCell.setValue("Новое значение");

Вписываем в нашу ячейку строку «Новое значение»

2. Получить текущее значение ячейки:

let a1 = myCell.getValue();

Сохраняем в переменную a1 значение ячейки A1

Обратите внимание, что в первом случае используется метод setValue (от англ. установить значение), во втором — getValue (от англ. взять значение).

В дополнение к сказанному. Есть метод «getDisplayValue», который возвращает значение ячейки в том виде, в каком их видно на экране. Это значит, что если вы например применили к числу форматирование и видите на экране в ячейке что-то типа «1 078 234,13», то вы это же и получите в переменной, если используете «getDisplayValue». Это также касается и использования форматирования для ячеек с датой и временем. Однако, не все так просто и могут быть нюансы.

Как и что использовать, решать вам. Я же предпочитаю получать значения, как есть, с помощью «getValue», а уже потом, после обработки, при выводе полученных значений на лист форматировать так, как необходимо для задачи.

Продолжим. С помощью специальных методов можно установить фон заливки ячейки:

myCell.setBackground("yellow"); //закрасить в желтый

и узнать фон заливки ячейки:

Logger.log(myCell.getBackground()); //получить цвет заливки и вывести полученное значение в консоль

В данном примере я использовал директиву Logger.log для вывода информации в консоль. Это иногда очень удобно при отладке программного кода. Хочу обратить ваше внимание, что цвет заливки выводится в формате #ffff00 (соответствует желтому цвету).

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

Список всех доступных методов открывается, когда вы в редакторе кода после переменной с ссылкой на нашу ячейку ставите точку.

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

Пояснение. К примеру, если вы ранее в коде в переменную myVar сохранили ссылку на лист, то можно получить список всех доступных методов и свойств листа, поставив в редакторе после имени переменной точку ( myVar. ).

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

В завершении текущего блока хочу поделиться небольшим «лайфхаком».

Один из способов что-то узнать, если забыл (или просто не знаешь),  использовать встроенный инструмент «Записать макрос».

Вы записываете макрос с нужными вам действиями (что хотите реализовать с помощью скрипта, но не знаете как)  и затем сохраняете его.

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

Примечание. Чтобы открыть редактор скриптов, необходимо воспользоваться пунктом меню «Расширения -> Apps Script»:

.

Операции с диапазоном.

Обратиться к диапазону.

let myRange = sheet.getRange(1,1,2,3);

Получаем в переменной myRange нужный диапазон.

Расшифровка. В переменную myRange попадает диапазон c началом в ячейке с координатами (1,1) и окончанием в ячейке с координатами (2,3) — вторая строка, третья колонка.

Важное уточнение. В директиве getRange указывается: номер строки (начало диапазона),  номер колонки (начало диапазона), кол-во строчек (размер диапазона по вертикали, кол-во колонок (размер диапазона по горизонтали).

К примеру, если мы получаем нужный диапазон вот-так:

let myRange = sheet.getRange(3,4,10,5);

То наш диапазон имеет координаты (3 — номер строки (начало), 4 — номер колонки (начало), 12 — номер строки (конец), 8 — номер колонки (конец).

Почему не 13-я строка, а 12-я? Потому что, размерность по вертикали — 10. От строки номер 3, включая саму строчку номер 3 — 12 строка — как раз выходит 10 строчек. По горизонтали — то же самое.

Иногда, чтобы на первых порах не путаться, где начинается и заканчивается диапазон, можно для тестирования закрасить диапазон (например, в желтый), чтобы визуально убедиться, что мы определили размерность диапазона правильно:

myRange.setBackground("yellow");

Закрасим диапазон желтым и посмотрим, правильно или нет определились с размерами 🙂

Иногда необходимо обработать диапазон, у которого нижняя граница — динамическая (постоянно меняется). Например, табличка со списком клиентов, который постоянно меняется. Как нам в таком случае получить наш диапазон?

На помощь приходит метод, который позволяет получить номер последней строки, где на листе есть данные —  «getLastRow» (получить последнюю строчку).  Как вы помните, в методе getRange предпоследним параметром идет кол-во строк (размер диапазона по вертикали).

Если в нашей условной табличке со списком клиентов данные начинаются со второй строки (в первой — оглавление таблицы), то кол-во строк в таблице равно getLastRow() — 1.

Для определения правой границы таблицы можна воспользоваться getLastColumn — номер последнего столбца с данными (крайний правый).

Еще вариант, как получить динамический диапазон, если вы знаете его границы по ширине:

К примеру, у вас таблица заканчивается (правая граница) на колонке F. В таком случае получить диапазон:

let myRange = sheet.getRange("A2:F" + sheet.getLastRow()).getValues();

В результате мы в переменной myRange получим все значения нашего динамического диапазона (учитываем, что в первой строке идут названия столбцов таблицы, поэтому начинаем с A2).

В отличии от работы с ячейкой, работа с диапазон имеет ряд нюансов. Рассмотрим подробнее.

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

В зависимости от задачи, мы можем работать с диапазоном напрямую (в нашем примере с помощью переменной myRange), либо сразу считать значения из диапазона в массив и затем работать со значениями.

Если мы работаем напрямую с диапазоном (с помощью объекта Range), мы имеем по сути массив, каждым елементом которого есть ячейка, которая входит в наш диапазон.

Соответственно, мы имеем доступ ко всем свойствам ячейки — значение, цвет заливки и пр. Например, чтобы получить значение левой верхней ячейки, необходимо написать:

let a1 = myRange.getCell(1,1).getValue();

В результате в переменной a1 имеем значение ячейки с координатами 1,1 (левой верхней ячейки)

Соответственно, что-то записать в левую верхнюю ячейку можно так:

myRange.getCell(1,1).setValue("Новое значение");

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

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

let myRange = sheet.getSelection().getActiveRange();

На всякий случай напоминаю, что в переменной sheet лежит ссылка на наш активный лист.

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

Вначале нам необходимо узнать размеры нашего диапазона — кол-во строчек и столбцов. Для этого есть методы «getNumRows» (получить кол-во строк) и «getNumColumns» (получить кол-во столбцов).

Напишем код:

let myRange = sheet.getSelection().getActiveRange(); //получаем выделенный диапазон
  let k = 1; //просто переменная для тестирования
  for (let i = 1; i <= myRange.getNumRows(); i++) { //перебор всех строчек 
    for (let j = 1; j <= myRange.getNumColumns(); j++) { //перебор всех столбцов
      myRange.getCell(i,j).setValue(k); //задаем значение очередной ячейки диапазона
      k += 1; //увеличиваем нашу переменную на единицу (еще один из способов)
    }
  }

В приведенном коде мы перебираем подряд все ячейки выделенного диапазона и каждой присваиваем значение — от 1 до общего кол-ва ячеек в нашем диапазоне.

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

Иногда приходиться работать с большими диапазонами, сравнивать несколько диапазонов и прочее. В таких случаях более оптимальным решением есть сохранить содержимое диапазонов в массивы и работать с массивами (по сути обрабатывать данные в оперативной памяти).

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

Так получается намного быстрее. А учитывая тот факт, что время работы скрипта ограничено 6-ю минутами, иногда это важно для решения определенных задач.

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

Например, сохраним в массив выделенный диапазон:

let myArray = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getSelection().getActiveRange().getValues();

Или более читабельный вариант (учитывая созданные ранее переменные)

let myRange = sheet.getSelection().getActiveRange(); //получаем выделенный диапазон
let myArray = myRange.getValues(); //сохраняем значения диапазона в массив

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

Создание своего меню

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

В данном разделе коснемся создания своего меню.

В гугл таблицах пользовательское меню бывает двух типов:

• пользовательское меню — создается, как отдельное меню
• дополнительное меню — пункт меню, который можно добавить в уже существующее на листе меню «Дополнения»

Скриншот обычного меню:

Скриншот дополнительного меню:

В качестве «донора» для скриншотов была использована таблица «Ведение семейного бюджета» — расширенная версия.

Когда и какие виды меню использовать — зависит от поставленных задач. От себя добавлю, что меню создается с помощью скрипта. Часто скрипт, который создает требуемое меню, запускается при открытии гугл таблицы — используя специальное событие «открытие таблицы».

Более детально об использовании различных меню с примерами кода можно посмотреть ЗДЕСЬ.

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

Работа с событиями таблицы

Когда что-то происходит с таблицей или данными, то возникает так называемое событие.

Например, когда вы открываете таблицу, либо редактируете данные, возникает событие «Открытие таблицы» или «Редактирование таблицы» соответственно.

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

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

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

Подробнее об использовании событий с примерами кода читайте ЗДЕСЬ.
.

Готовые решения, наработки, примеры кода

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

Надеюсь, что данный пост будет полезен.

Если есть вопросы и пожелания — пишите в комментариях.

Рекомендую почитать:

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