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

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

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

• Методы и свойства объектов;
• Условия и циклы.

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

Методы и свойства объектов Google Scripts

Для тех, кто запамятовал, на данный момент наш код выглядит следующим образом:

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];
    var avg1 = sum1/4;
    var avg2 = sum2/4;
    var avg3 = sum3/4;
    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]
    ];
    sheet.getRange("E1:F3").setValues([
    [sum1, avg1],
    [sum2, avg2],
    [sum3, avg3]
    ]);
};

Как вы могли заметить, у нас есть конструкции вида SpreadsheetApp.getActiveSheet() или sheet.getRange().getValues , которые многим кажутся очень непонятными (да, это нормально).

Разберем обе конструкции подробнее.

У нас есть объект SpreadsheetApp - он представляет собой возможность работы с таблицами в целом и является родительским (головным, основным, классом верхнего уровня, называйте как хотите) для таблиц. В общем, если вы хотите работать с таблицами гугла, то вам нужен этот объект.

Любой объект у гугла имеет определенный синтаксис (правила написания) вида <название объекта>.<метод/свойство объекта>, где.

• Метод - это функция, которая принадлежит данному объекту (например объект - человек, методы: ходить, говорить, думать, читать и тп. В общем, какие-то действия).
• Свойство - это характеристики (переменные) данного объекта (например объект - человек, свойства: имя, фамилия, дата рождения и тп).

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

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

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

Как его узнать? Ну.. мы конечно можем узнать его каким-то способом и сообщить гуглу, но если глянуть в методы SpreadsheetApp, то у него есть один замечательный метод под названием getActiveSheet(). Он, грубо говоря, говорит гуглу: "Эй, чувак, я хочу узнать ссылку на текущую таблицу". И гугл дает нам идентификатор, поэтому важно записать его в какую-нибудь переменную, чтобы дальше не вызывать снова этот метод, а использовать уже готовую ссылку (как с ярлыками для папок в винде).

Так как метод - это функция, то любой метод оканчивается на круглые скобочки (). Эти скобочки как раз и указывают, что нам нужен метод, а не свойство. В таких скобочках для некоторых функций можно передавать значения (аргументы) для дальнейшей работы метода.  Например, мы могли бы получить ссылку на текущую таблицу через метод openByUrl(<адрес текущего файла>), но это не очень удобно :)

Что касается второй конструкции (sheet.getRange().getValues), то здесь тоже самое, но мы вызываем метод getValues для объекта Range, который в свою очередь получаем, обращаясь к методу getRange объекта Sheet, на который ведет ссылка sheet (да, это может быть сложновато для восприятия). Напишем конструкцию иначе:

var dataRange = sheet.getRange("A1:D3");
var dataArray = dataRange.getValues();

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

var sheet = SpreadsheetApp.getActiveSheet();
var dataRange = sheet.getRange("A1:D3");
var dataArray = dataRange.getValues();

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

var dataArray = SpreadsheetApp.getActiveSheet().getRange("A1:D3").getValues();

Здесь мы обратились к родительскому классу SpreadsheetApp, потом мы обратились к классу ниже Sheet, затем к классу еще ниже Range и для этого класса уже вызвали метод getValues().

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

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

Циклы и условия Google Scripts

Вот у нас есть такие строки кода:

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];

Код вроде бы удобен, но что делать, если, например, у нас увеличится кол-во рядов или строк? Переписывать каждый раз заново код? Не очень удобно, не так ли? :)

Здесь-то нам и пригодятся циклы. Если кратно, то цикл - это повторение какой-то операции до тех пор, пока условия цикла не будут выполнены. Основных вариантов цикла существует целых два - for и while. Их различие в том, что цикл for выполняется до тех пор, пока переменная цикла не достигнет определенного значения, а цикл while будет выполняться до тех пор, пока не будет выполнено определенное действие. При этом с циклами стоит быть несколько осторожными, т.к. очень легко создать бесконечный цикл (который будет выполняться пока ему не надоест или пока не сработает тайм-аут).

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

for (i=0; i < 100; i++){
    sum += i;
}

Данные строчки означают следующее:

У нас есть переменная цикла i (т.н. счетчик), которая каждый шаг цикла (итерацию) увеличивается на 1 (i++) и цикл будет длиться до тех пор, пока i не станет больше или равно 100. При этом каждую итерацию у нас в переменную sum будет прибавляться значение i (т.е. после такого цикла sum будет равно 0+1+2+3+4+...+99 = 4950).

Аналогичный примеру выше цикл while:

while (i < 100)
    sum += i;
    i++;
}

Дословно означает "Пока i меньше 100, делать следующее: прибавить к сумме значение i, затем увеличить i на 1".

С условиями все еще проще, конструкция условия выглядит таким образом:

if (условие) {
    действие
} else if (условие 2) {
    другое действие
}

Например, условие - если у вас 5 яблок, то увеличить их вдвое (вот бы так с деньгами, да?), иначе уменьшить их кол-во на 3:

if (apple == 5) {
    apple += apple;
} else {
    apple -= 3;
}

Итак, как с помощью этого нам упростить свой код?

Если приглядеться, то у нас довольно много повторяющихся строк (которые немного отличаются). У нас сейчас, по сути, для каждой строки по две переменные (sum и avg), учитывая, что строк три, получаем 6 переменных. Однако как мы можем обойтись всего двумя? Да очень просто, достаточно для каждой строки подсчитывать сумму и среднее значение, затем печатать его в соотв. ячейке и переходить к следующей строке.

Таким образом, мы обойдемся всего двумя переменными и при этом нам вообще неважно сколько строк использовать. Если рассматривать этот вопрос более детально, то что нам нужно сделать? Мы для каждой итерации (1 итерация = 1 строка) должны подсчитать сумму и среднее значение ячеек  этой строки. Однако, если мы захотим использовать больше или меньше ячеек, чем сейчас, то как нам это дело унифицировать? Тут нужен второй цикл, который будет просматривать каждую ячейку данной строки.

Теперь другой вопрос - как мы будем менять наш диапазон (строк и колонок)? Ну... тут магии нет, давайте просто создадим две переменные, которые будут отвечать за ширину и высоту диапазона (две переменные поменять всяко проще, чем кучу строк):

var rows = 3;
var cols = 4;

Т.е. у нас на данный момент три строчки (rows) по четыре значения в каждой (cols).

Прежде, чем создавать циклы, давайте несколько унифицируем эту строчку:

var dataArray = sheet.getRange("A1:D3").getValues();

Т.к. диапазон будет зависеть только от переменных rows и cols, то от явного указания области надо уходить. Ввиду того, что A1:D3 - это не очень удобное написание для нас, следует поискать более подходящий. Смотрим оф документацию и видим, ага, есть прямо то, что нам нужно:

Будем использовать метод getRange с явным указанием строк и колонок:

var dataArray = sheet.getRange(1, 1, rows, cols).getValues(); // getRange (ряд, колонка, кол-во рядов, кол-во колонок) 

Ну а дальше добавляем циклы:

for (var i = 0; i < rows; i++){ // для каждой строки:
    var sum = 0; // обнуляем переменную
    var avg = 0; // обнуляем переменную
    for (var j = 0; j < cols; j++){ // Для каждой ячейки в этой строке
       sum += dataArray[i][j]; // добавить значение ячейки в сумму
    } // перейти к следующей ячейке в строке
    avg = sum/cols; // посчитать среднее
    sheet.getRange(i+1, cols+1, 1, 2).setValues([ [sum, avg] ]); // записать значения
} // перейти к следующей строке

При этом, следует заметить, что мы используем другой формат записи getRange при записывании значений суммы и среднего. Т.к. у нас ячейка, в которую записывать, зависит от наших переменных rows, cols (если строк будет, например, 7, то записывать сумму и среднее следует в 8 и 9 строку).

Также обратите внимание, что номер ряда обозначается переменной i+1, а не i, т.к. нумерация рядов идет от единицы, а итерация идет с нуля (т.к. нумерация элементов массивов также идет с нуля).

Собираем наш код обратно и получаем такую конструкцию:

function myfunction(){
    var sheet = SpreadsheetApp.getActiveSheet(); // Ссылаемся на текущий лист таблицы в соответствии с иерархией. В переменной sheet будет ссылка на наш лист
    /*-------------
    --Комментарий--
    ---------------*/
    var rows = 3; // кол-во строк
    var cols = 4; // кол-во ячеек в строке
    var dataArray = sheet.getRange(1, 1, rows, cols).getValues(); // getRange (ряд, колонка, кол-во рядов, кол-во колонок)
    for (var i = 0; i < rows; i++){ // для каждой строки:
        var sum = 0; // обнуляем переменную
        var avg = 0; // обнуляем переменную
        for (var j = 0; j < cols; j++){ // Для каждой ячейки в этой строке
            sum += dataArray[i][j]; // добавить значение ячейки в сумму
        } // перейти к следующей ячейке в строке
        avg = sum/cols; // посчитать среднее
        sheet.getRange(i+1, cols+1, 1, 2).setValues([ [sum, avg] ]); // записать значения
    } // перейти к следующей строке
};

Теперь попробуйте увеличить кол-во строк и рядов, не забыв при этом поменять значения переменных rows и cols и запустите скрипт снова ;)

Стало куда удобней, но.. может можно еще уменьшить наши телодвижения? Программисты - это народ такой, ленивый, чем меньше надо менять значений, тем лучше :)

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

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

Это методы getLastColumn() и getLastRow(). Они занимаются тем, что возвращают нам значение последнего непустого ряда или колонки. Теперь просто заменим строчки:

    var rows = 3; // кол-во строк
    var cols = 4; // кол-во ячеек в строке

На:

    var rows = sheet.getLastRow(); // кол-во строк
    var cols = sheet.getLastColumn(); // кол-во ячеек в строке

И попробуем изменить наш диапазон, добавив или удалив строки/колонки.

Послесловие

Чтож, надеюсь кто-то до этого момента все таки дочитал :) Как и всегда, ждем Ваших вопросов, дополнений и всего такого прочего, что может появится в результате прочтения данной статьи.

Пожалуй, в следующей части я опишу как все тоже самое выглядит в скриптах VBA-экселя.

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