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

Так что приглашаю вас познакомиться с постом «Скрипты Google Sheets (макросы): операции с таблицей, листом, ячейкой, диапазоном, меню, событиями и готовые решения»

Эту публикацию оставил без изменений и вы также можете продолжить знакомство с ней.

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

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

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

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

В связи с этим пришла идея сделать что-то наподобие памятки по написанию скриптов для гугл таблиц.

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

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

Итак, приступим 🙂

• Обратиться к открытой таблице:

var MySpreadSheet = SpreadsheetApp.getActive();

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

• Обратиться к другому листу открытой таблицы:

1. var MySpreadSheet = SpreadsheetApp.getActive().getSheetByName("Лист2").activate();
2. var MySpreadSheet = SpreadsheetApp.getActive().getSheetByName("Лист2");
3. MySpreadSheet.activate();

Расшифровка:
1. обращаемся к заданному листу (Лист2) и активируем его.
2. обращаемся к заданному листу (Лист2) . При этом фокус не передаем (не делаем его активным).
3. активируем лист, определенный переменной MySpreadSheet.

Работа с ячейками

• Получить значение определенной ячейки:

var a1 = MySpreadSheet.getRange('D4').getValue();

В переменную a1 записывается значение ячейки «D4».

• Записать значение в определенную ячейку:

MySpreadSheet.getRange('F12').setValue('Qwerty');

В ячейку «F12» записываем слово «Qwerty».

• Узнать координаты активной (выделенной) ячейки

var MySpreadSheet = SpreadsheetApp.getActive();
1. var MyCell = MySpreadSheet.getActiveCell();
2. var MyCell = MySpreadSheet.getActiveRange();

Расшифровка:
В переменную MySpreadSheet сохраняем ссылку на активный лист.
1. первый способ взять активную (выделенную) ячейку
2. второй способ взять активную (выделенную) ячейку

Ячейка (Cell) является частным случаем от диапазона (Range), при условии что диапазон (Range) указывает на одну ячейку. Если вы выделите несколько ячеек, то в этом случае во втором варианте (getActiveRange) в переменной MyCell будет храниться ссылка на выделенный диапазон.

var IndexRow = MyCell.getRowIndex();
var IndexColumn = MyCell.getColumn();

В переменной IndexRow будет номер строки выделенной ячейки
В переменной IndexColumn будет номер столбца (колонки) выделенной ячейки.

• Сдвиг на соседнюю от активной ячейку (перемещение фокуса):

var MyCell = MySpreadSheet.getActiveRange();
var CountRows = 2;
var CountColumns = 1;
var NewCell = MyCell.offset(CountRows,CountColumns);

CountRows — на сколько строк смещаться вниз от активной ячейки
CountColumns — на сколько колонок смещаться вправо от активной ячейки

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

• Форматирование ячеек:

var MyCell = MySpreadSheet.getActiveCell()
.setFontColor('#ff0000')
.setFontStyle('italic')
.setBackground('#ffff00')
.setFontSize(20)
.setFontWeight('bold');

Форматирование применяется к активной ячейке или диапазону.

MySpreadSheet.getRange('C1:E2').activate()
.setFontColor('#ff0000')
.setFontStyle('italic')
.setBackground('#ffff00')
.setFontSize(20)
.setFontWeight('bold');

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

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

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

MySpreadSheet.getRange('C1:E2').activate()
.merge()
.setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP)
.setHorizontalAlignment('center')
.setVerticalAlignment('middle');

• Вставка изображения с интернет-ресурса в заданную ячейку:

var LinkLogo = "https://zhyttia-prekrasne.com/IRPIN-kids-Fest_1.png"; //ссылка на логотип
var AddresLogo = "=IMAGE(" + '"' + LinkLogo + '"' + ")"; //генерируем формулу для вставки изображения в ячейку
var MySpreadSheet = SpreadsheetApp.getActive();
var MyCell = MySpreadSheet.getActiveCell();
MyCell.setFormula(AddresLogo);

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

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

• Изменение размера ячейки:

var MyCell = MySpreadSheet.getActiveCell();
var IndexRow = MyCell.getRowIndex(); //номер строки
var IndexColumn = MyCell.getColumn(); //номер колонки
var NewHeight = 150; //новая высота ячейки
var NewWidth = 150; //новая ширина ячейки
MySpreadSheet.setRowHeight(IndexRow, NewHeight); //устанавливаем высоту ячейки
MySpreadSheet.setColumnWidth(IndexColumn, NewWidth); //устанавливаем ширину ячейки

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

var MyRange = MySpreadSheet.getRange('F6:G7').activate().merge();
var IndexRow = MyRange.getRowIndex(); //номер строки
var IndexColumn = MyRange.getColumn(); //номер колонки
var NewHeight = 200;
var NewWidth = 250;
MySpreadSheet.setRowHeight(IndexRow, NewHeight); //высота ячейки
MySpreadSheet.setColumnWidth(IndexColumn, NewWidth); //ширина ячейки
MyRange
.setBackground('#ffff00') //заливка желтым
.setBorder(true, true, true, true, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID); //обводка черной рамкой

Работа с диапазонами

• Активируем (выделяем) и сохраняем в переменную заданный диапазон:

var First = MySpreadSheet.getRange('C1:C20').activate();

• Аналогично можно сохранить в переменную диапазон, выделенный пользователем:

var range = MySpreadSheet.getActiveRange();

• Выделяем (активируем) диапазон от активной ячейки до конца данных (последняя ячейка из нашего диапазона, где имеются данные) и сохраняем в переменную:

MySpreadSheet.getRange('C3').activate();
var myrange = MySpreadSheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate(); //от активной ячейки вниз
var myrange = MySpreadSheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.NEXT).activate(); //от активной ячейки вправо

var count = myrange.getNumRows(); //кол-во строк в выделенном диапазоне (выделение вниз)
var count = myrange.getNumColumns(); //кол-во колонок в выделенном диапазоне (выделение вправо)

• Выделяем диапазон из нескольких колонок:

var MySpreadSheet = SpreadsheetApp.getActive();
MySpreadSheet.getRange('B1:C1').activate();
var myrange = MySpreadSheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();

• Перебор всех ячеек в выделенном диапазоне (в цикле):

for (i = 1; i <= myrange.getNumRows(); i++) {
for (j = 1; j <= myrange.getNumColumns(); j++) {
var CurrentCell = myrange.getCell(i, j).getValue();
}}

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

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

• Создаем новый лист в таблице:

var MySpreadSheet = SpreadsheetApp.getActive(); //обращаемся к таблице
MySpreadSheet.insertSheet(1); //вставляем новый лист после 1-го
MySpreadSheet.getActiveSheet().setName("Новый лист"); //при необходимости изменяем имя нового листа

• Удаляем лист из таблицы:

var MySpreadSheet = SpreadsheetApp.getActive().getSheetByName("Имя листа для удаления").activate();
MySpreadSheet.deleteActiveSheet();

• Несколько полезных функция при работе с листами:

function IsSheet(SheetName) {
try {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName(SheetName), true);
return true;} catch(e) {return false;}
};

Данная функция возвращает true (истина), если лист (заданный переменной SheetName) существует, или false (ложь), если указанного листа нет.

function SmartDeleteSheet(SheetName) {
if (IsSheet(SheetName)) {
var MySpreadSheet = SpreadsheetApp.getActive(); //текущая таблица (активная)
MySpreadSheet.setActiveSheet(MySpreadSheet.getSheetByName(SheetName), true);
MySpreadSheet.deleteActiveSheet();
}
};

Данная функция удаляет заданный лист (SheetName), если он существует.

Обработка некоторых событий таблицы.

• Открытие таблицы в обозревателе:

function onOpen() { //функция, которая запускается при открытии таблицы в обозревателе
var MySpreadSheet = SpreadsheetApp.getActive(); //текущая таблица (активная)
MySpreadSheet.addMenu("Главный пункт меню",[
{name: "Подпункт 1", functionName: "FunctionName1"}, //запуск функции FunctionName1
{name: "Подпункт 2", functionName: "FunctionName2"}, //запуск функции FunctionName2
{name: "Подпункт 3", functionName: "FunctionName3"} //запуск функции FunctionName3
]);
}

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

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

• Событие, которое возникает после редактирования ячейки:

function onEdit(e) {
var MySpreadSheet = SpreadsheetApp.getActive();
var range1 = e.range; //ячейка, которая редактировалась
var NewValue = range1.getValue(); //новое значение
var IndexRow = range1.getRow(); //номер строки ячейки, которая редактировалась
var IndexColumn = range1.getColumn(); //номер колонки ячейки, которая редактировалась
}

Дополнительно хочу поделиться фишкой, которой я часто пользуюсь, если что-то не знаю (помимо Google).

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

После достаточно зайти в редактор скриптов и посмотреть, какие команды записал макрос 🙂

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

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

Кстати, если наш сайт принес вам пользу и вы хотите сказать нам «Спасибо» и поддержать нас 🙏, то это можно сделать ЗДЕСЬ.

Готовое бесплатное решение учета клиентов и заказов в гугл таблице

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

Готовое платное решение учета клиентов и заказов в гугл таблице

Недавно у нас вышла платная версия готовой гугл таблицы по учету клиентов и заказов с расширенными возможностями. Детальную информацию по таблице смотрите ЗДЕСЬ.

Заказать макросы (скрипты) для Гугл таблиц

Вы также можете заказать макросы для Гугл таблиц, если хотите сэкономить время или нет возможности сделать их самостоятельно.

С тем, как можно сделать заказ и сколько они стоят, смотрите ЗДЕСЬ.

И еще на ютуб в отдельном плейлисте буду выкладывать некоторые примеры работы макроса.

Это один из примеров работы макроса.

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

• «Доход на партнерских/реферальных программах: примеры и полезные советы»
• «Цифровые ресурсы без ограничений: фото, видео, мелодии, видеошаблоны, графика, презентации и т.д»
• «Как разрешить запуск скрипта для Гугл Таблиц (Google Sheets)»