Как писать скрипты, макросы и код в Google Scripts — часть 1

Доброго времени суток, дорогие читатели!

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

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

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

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

С чего начинается любой скрипт? Очевидно, что с какого-то кода, но с какого? Вполне может так быть, что у вас уже появляются такие мысли при виде этого, а точнее того, белого экрана. Но это нормально. Когда мало опыта и много непонятного, хочется все бросить и лечь спать. Но не отчаивайтесь! И отойдите от кровати! Если я не буду писать это в некотором ироничном виде, то от моей заунывной статьи вы уже на третьей строчке усне..

Пишем и используем Google Scripts: Line Zero, - Beginning

Так вот, с чего нам начать? Я сейчас не имею в виду алгоритм действий, который вы держите в голове, или блок-схемы на листочке, нет. Нам следует начать с... объявления функции.

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

Объявление функции имеет определенный формат вида:

function FUNCTIONNAME () {

};

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

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

Итак, вы определились с вашей первой функцией, теперь что? А теперь нужно объявить переменные. Для вашей позиции в торговом автомате переменными могут быть: номер ряда, номер колонки, картинка позиции, её цена.

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

var variablename;

И ой, она уже объявлена. А для чего это нужно?

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

Грубо говоря, если вы напишете e = m * c ^2 без объявления переменных, то компилятор перед запуском кода выдаст ошибку о том, что он вообще не имеет ни малейшего понятия кто такие e, m, с и что им всем от него нужно. А вот если вы напишете:

var e = 0, m = 1, c = 2;

То он вам посчитает безо всякого труда, потому что он уже знает что e, m, c, - это числа, имеющие значения 0, 1, 2 соответственно.

Однако, если вы напишете:

var e = "энергия", m = "масса", c = 300000;

То он вам, конечно, посчитает выражение  e = m * c ^2, но его ответом будет e = 2, потому что у вас несогласованны типы (m - это строка, c - это число) и он просто отбросит строки (VBA, например, здесь выдаст ошибку что у вас неправильные типы данных).

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

Что касается самих выражений, здесь не совсем обычная математика (а кто говорил, что будет легко?). Если там выражение e = m*c^2 будет эквивалентно выражению m*c^2 = e, то здесь не так. Здесь вообще оператор под названием "равно" выглядит иначе и обозначается как "==". А то, что написано выше, это не "е равно эм * це квадрат", это читается как "е присвоить эм*це квадрат" и данный оператор называется оператором присваивания. Вот этот момент очень важен и здесь не следует путать.

Давайте подробнее остановимся на операторах. Есть несколько категорий:

• Арифметические. Для примера x = 5:
  

  Оператор	Формула	Значение x	Значение y
  +	y = x + 2	x = 5	y = 7
  -	y = x - 3	x = 5	y = 2
  *	y= x * 7	x = 5	y = 35
  ^	y = x ^ 2	x = 5	y = 25
  %	y = x % 3	x = 5	y = 2
  ++	y = ++x y = x++	x = 6 x = 6	y = 6 y = 5
  --	y = --x y = x--	x = 4 x = 4	y = 4 y = 5

• Операторы присваивания - позволяют присвоить переменной какое-то значение. Возьмем x = 10.
  

  Оператор	Формула	Аналогичная формула	Результат	Комментарий
  =	x = 3		3	=3
  +=	x += 3	x = x + 3	13	Это не "икс равно икс плюс три". Это "икс присвоить икс плюс 3", т.е. добавить три к тому, что было икс.
  -=	x -= 3	x = x - 3	7
  *=	x *= 4	x = x * 4	40
  /=	x /= 2	x = x / 2	5
  %/	x %= 3	x = x % 7	1

• Строковые операторы - в общем-то они просто склеивают строки в бОльшие строки. Например у вас есть text1 ="Добрый", text2 = "день".
  

  Оператор	Формула	Значение text1	Значение text2	Значение text3
  =	text3 = text1 + text2	"Добрый "	"день"	"Добрый день"
  +=	text1 += text2	"Добрый день"	"день"

  В чем же разница между первым и вторым оператором? (ответ в следующей строке, выделите мышкой)

  В первом случае у нас значение переменных text1 и text2 присваиваются переменной text3, во втором же случае нам не нужна дополнительная переменная, мы просто обновляем значение переменной text1.

• Операторы сравнения - применяются при сравнении одной переменной с другой или с каким-то условием. Например возьмем x = 5 (var x = 5):
  

  Оператор	Описание	Формула	Значение	Комментарий
  ==	Равно	x == 7	false
  ===	Равно по значению и по типу	x === 5 x==="5"	true false	В первом случае мы сравниваем с типом "число" Во втором - со строкой (о чем говорят кавычки)
  !=	Не равно	x != 7	true
  !==	Не равно по значению или типу	x!== 5 x!=="5"	false true	false (по значению)+false (по типу) = false false+true = true (это дискретная логика, да)
  >	Больше	x > 5	false	Пять больше пяти? Определенно, нет.
  <	Меньше	x < 7	true
  >=	Больше или равно	x >= 5	true	Пять больше или равно пяти? Да, пять равно пяти
  <=	Меньше или равно	x <= 7	true

• Логические операторы. К примеру x = 5, y = 3:
  

  Оператор	Описание	Пример	Значение	Комментарий
  &&	Логическое "И"	(x > 3 && y < 3)	false	true*false = false
  ||	Логическое "ИЛИ"	(x > 3 || y < 3)	true	true+false = true
  !	Логическое "Не"	x != 7	true

  ВНИМАНИЕ! Дальше идет логика (дискретная). Всех, у кого этот факт вызывает неконтролируемое состояние страха, боли и потерянных годов в институте, просьба пропустить до следующего раздела, спасибо за внимание и извините за причиненные неудобства.

  У любой переменной в дискретной логике есть только два значения - 1 (true) и 0 (false). И с ней возможны три вида операций - логическое "И" (&), логическое "ИЛИ" (+) и логическое "Не" (!). Выглядит это таким образом (для переменных X и Y):

  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

  Отсюда, кстати говоря, следуют занятные комбинации: !(X&Y) это тоже самое, что !X+!Y (не (X И Y) = не X ИЛИ не Y), а !(X+Y) это тоже самое, что !X&!Y (Можете открыть учебник по дискретной логике и убедиться, что это так).

Пишем и используем Google Script: Line 1, - I Don't get it

Итак, в общем-то с основами разобрались. Теперь немного практики. Задача:

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

Таблица для нашего примера:

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

1. Как посчитать сумму? Очевидно, сложить каждое значение в ряду. Окей, кажется несложным.
2. Как получить среднее значение? Можно сложить каждое значение ряда и поделить на кол-во значений. Но зачем заново считать, если мы сумму уже посчитали на предыдущем шаге? Окей, тоже легко.
3. Остался один вопрос, - как получить значение из ячейки и как его потом туда записать? А вот тут-то и начинаются сложности.

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

• SpreadsheetApp, - самый верхний уровень, указывает, что мы хотим работать с таблицами (создавать, удалять, изменять и вообще делать с ними все, что можно. По ссылке можно изучить все свойства и методы этого класса. Т.е. все, что с ним можно сделать);
• Spreadsheet, - класс, который отвечает за действия с самим листом таблицы (копирование, переименование, защита, сокрытие, права доступа и тп);
• Sheet, - класс, который отвечает за действия на каком-то листе таблицы ( добавление ряда, добавление графиков, определение размера таблицы и многие другие). Во многих случаях можно использовать его вместо класса Spreasheet;
• Range, - класс, который отвечает за действия с каким-то определенным диапазоном ячеек на листе (включая выбор диапазона).

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

Создаем нашу функцию:

function myfunction(){

};

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

var sheet = SpreadsheetApp.getActiveSheet(); // Ссылаемся на текущий лист в соответствии с иерархией. В переменной sheet будет ссылка на наш лист

Да, можно вставлять комментарии, которые компилятор будет игнорировать (и подсветит оранжевым). Для этого перед словом/фразой/строчкой достаточно добавить два слеша // и вся оставшаяся строка будет закомментирована. Если же надо закомментировать много строк, то лучше воспользоваться вторым вариантом - поставить в начале комментария символы /*, а в конце */. Т.е. Комментарий  целиком будет выглядеть примерно так:

/*-------------
--Комментарий--
---------------*/

Теперь достанем наши значения из ячеек:

var dataArray = sheet.getRange("A1:D3").getValues(); // Получаем значения ячеек A1:D3 в переменную dataArray

При этом особенностью метода getValues является то, что на выходе мы получаем матрицу (двумерный массив или по простому - таблицу), где первый разряд означает ряды, второй - колонки. В нашем примере, например, элемент массива dataArray[0][1] будет означать... нет, не нулевой ряд, а первый ряд и вторая колонка (т.к. нумерация ячеек идет с единицы, а нумерация массивов всегда идет с нуля).

Теперь мы получили значения наших ячеек и можем с ними работать. Как мы будем суммировать? Самое простое - явно:

var sum1 = dataArray[0][0] + dataArray[0][1] + dataArray[0][2] + dataArray[0][3];
var sum2 = dataArray[1][0] + dataArray[1][1] + dataArray[1][2] + dataArray[1][3];
var sum3 = dataArray[2][0] + dataArray[2][1] + dataArray[2][2] + dataArray[2][3];

Да, не очень красиво, но работает. Кстати проверить результат можно несколькими способами:

1. Записать в журнал. Для этого добавить после var sum3 строчку Logger.log ("\nСумма 1 ряда: "+sum1+"\nСумма 2 ряда: "+sum2+"\nСумма 3 ряда: "+sum3);
   Где \n - это распространенное обозначение перевода строки (Enter). После этого в том же редакторе скриптов переходим в пункт меню "Вид - Журналы":
   
2. Вывести оповещение в браузере: Browser.msgBox("Суммы трех рядов: "+sum1+" "+sum2+" "+sum3);
   При выполнении скрипта перейдите на ваш лист и увидите всплывающее окно
   
3. Поставить Breakpoint (1) напротив var sum3, после чего нажать не на старт, а на иконку с жуком (2, означает debug). У вас напротив переменной sum3 (4) будет написано "undefined" (на скрине этот этап пропущен). Это потому, что скрипт сейчас остановился перед этой строкой и он еще не отработал её. Для того, чтобы он сделал следующее действие, нажмите на кнопку со стрелкой (3) и вуаля (4), переменная посчитана (наиболее удобный способ отладки и поиска косяков). Но это сработает, если у вас в функции код на этой строке не заканчивается (я обычно добавляю функцию Logger.log и ставлю Breakpoint на ней:
   

У кого не получилось, код целиком ниже:

function myfunction(){
var sheet = SpreadsheetApp.getActiveSheet(); // Ссылаемся на текущий лист таблицы в соответствии с иерархией. В переменной sheet будет ссылка на наш лист
/*-------------
--Комментарий--
---------------*/
var dataArray = sheet.getRange("A1:D3").getValues(); // Получаем значения ячеек A1:D3 в переменную dataArray
var sum1 = dataArray[0][0] +dataArray[0][1] + dataArray[0][2] + dataArray[0][3];
var sum2 = dataArray[1][0] +dataArray[1][1] + dataArray[1][2] + dataArray[1][3];
var sum3 = dataArray[2][0] +dataArray[2][1] + dataArray[2][2] + dataArray[2][3];
Logger.log ("\nСумма 1 ряда: "+sum1+"\nСумма 2 ряда: "+sum2+"\nСумма 3 ряда: "+sum3);
};

Итак, первый пункт выполнен. Теперь приступаем ко второму. Учитывая, что мы (теперь) знаем сумму ряда и знали с самого начала кол-во чисел в ряду, добавляем еще три переменные и проверяем (значком {B} я обозначаю место, где можно поставить Breakpoint для быстрого просмотра результатов):

var avg1 = sum1/4;
var avg2 = sum2/4;
var avg3 = sum3/4;
{B}Logger.log();

Что ж. Пункт 2 выполнен. осталось это дело сохранить, например в ячейках E, F. Мы уже знаем как достать данные из ячеек, а вот как их туда поместить? В общем-то тут никакой магии и нет, раз есть функция get, то можно догадаться, что есть функция и set.

При этом, мы должны указать данные точно в таком же формате, как мы их получили. Присваивание значений массивам в общем-то достаточно простая задача. Допустим у нас есть значения 1, 2, 3, 4, 5 и нам нужен массив. Объявляем:

var array1 = [1, 2, 3, 4, 5]; // если нам явно нужны числа
var array2 = ["1", "2", "3", "4", "5"]; // Если нам неважно числа это или символы

И всё :)

Однако это массив с одним разрядом (одномерный), нам же нужен двумерный, как сделать его? Для этого используется конструкция вида [[]], как ниже:

var array2d = [[1, 2, 3], [4, 5, 6], [7, 8, 9]];

Выглядит криповато, но массив можно записать еще таким образом:

var array2d = [
[1, 2, 3],
[4, 5, 6],
[7, 8, 9]
];

А это уже другое дело! Хотя тем, кто на этих массивах уже не первую собаку съел, первый способ может быть удобней, т.к. быстрее.

Теперь же осталось дело за малым. Записать сумму и среднее для каждого ряда:

sheet.getRange("E1:F3").setValues([
[sum1, avg1],
[sum2, avg2],
[sum3, avg3]
]);

Запускаем и смотрим что у нас происходит на листе:

Послесловие

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

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

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

P.S. За существование оной статьи отдельное спасибо другу проекта и члену нашей команды под ником “barn4k“.
P.S.2: Часть 2 живет по этой ссылке.