Руководство для начинающих по скриптам Google Sheets

Как

ПоРайан Дубе

Последнее обновление6 августа 2020 г.

Если вы используете Google Sheets, вам может быть интересно узнать, что Google Scripts может значительно улучшить ваши электронные таблицы.

Думайте о скриптах Google как VBA в Microsoft Excel. Это позволяет добавлять код в электронную таблицу для автоматизации ввода данных. Вы также можете использовать его для переноса внешних данных на ваши листы. Лучше всего то, что вы можете выполнять расчеты по периодическому расписанию, даже если у вас нет открытой электронной таблицы.

В этой статье вы узнаете, как включить Google Scripts и как написать и запустить простой скрипт.

Как использовать Google Scripts

В Google Sheets есть несколько вещей, которые необходимо включить, прежде чем вы сможете начать писать там сценарии Google.

Выберите меню Инструменты и выберите Редактор скриптов из меню.

Откроется редактор скриптов Google в окне проекта без названия.

Этот скрипт фактически содержится в вашей учетной записи Google в инструменте Google Scripts. Этот конкретный проект связан с таблицей, над которой вы сейчас работаете.

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

Чтобы увидеть это в действии, давайте создадим простой скрипт.

Создайте свой первый скрипт Google

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

Это пример таблицы.

Вернувшись в окно Google Scripts, вставьте следующий код в фигурные скобки функции myFunction (), например:


function myFunction () {
var ss = SpreadsheetApp.getActiveSpreadsheet ();
var sheet = ss.getSheets () [0] var range = sheet.getRange (2,2,5);
var values ​​= range.getValues ​​();
var sum = 0
for (переменная строка в значениях) {
for (var col в значениях [row]) {
сумма = значения [строка] [кол] + сумма;
}
}
sheet.getRange (7,2) .setValue (sum);
}


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

Давайте разберем этот код по одной строке за раз.

Определение переменных

1. var ss = SpreadsheetApp.getActiveSpreadsheet ();

Эта строка возвращает текущую электронную таблицу, которая активна. Поскольку вы запускаете этот сценарий только тогда, когда активна электронная таблица, для которой вы хотите выполнить вычисления, она всегда получит правильную электронную таблицу. Лист сохраняется как переменная «объект» с именем «ss».

2. var sheet = ss.getSheets () [0]

Это получает первый лист и сохраняет его как переменную объекта с именем sheet. Есть альтернативная функция, которую вы можете использовать, чтобы получить лист по имени листа.

Это было бы var sheet = ss.getSheetByName («Sheet1»);

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

3. var range = sheet.getRange (2,2,5);

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

Переменная «диапазон» становится так называемой «массив». Массив - это переменная, которая содержит несколько значений. В Google Script вы обращаетесь к любому из этих номеров, используя специальную схему адресации элементов в квадратных скобках, как вы увидите ниже.

4. var values ​​= range.getValues ​​();

Функция getValues ​​() фактически извлекает значения из объекта диапазона, который вы определили выше.

5. var sum = 0

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

Создание для циклов

6. for (переменная строка в значениях) {

Это то, что называется циклом «For». Этот вид цикла проходит через определенное количество элементов в списке. В этом случае он пройдет через массив «значений», который мы обсуждали выше. Этот первый цикл будет проходить по всем строкам массива. В этом случае будет только один.

Примечание: строки массива не совпадают со строками электронной таблицы. Когда массив загружает значения из столбца в электронной таблице, он помещает их в одну строку (или строку) данных, например: «89, 92, 78, 98, 89». Таким образом, в этом случае массив имеет одну строку (строку) и пять чисел (столбцы).

7. for (var col в значениях [row]) {

Этот второй цикл for пройдет все «строки» в массиве. В этом случае он будет проходить через все 5 значений.

8. сумма = значения [строка] [кол] + сумма;

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

9 sheet.getRange (7,2) .setValue (sum);

Это утверждение с двумя функциями. Функция getRange указывает на ячейку в 7-й строке и во 2-м столбце. Затем функция setValue помещает значение переменной sum в эту ячейку.

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

Сохранение вашего Google Script

После того, как вы закончили вводить скрипт Google, пришло время сохранить его и пройтись по коду. Сначала введите «Untitled project», чтобы дать вашему проекту имя. Затем выберите значок дискеты в меню, чтобы сохранить сценарий.

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

В этом примере в моем цикле «for» в строке 8 отсутствовал открывающий символ «(». После исправления попробуйте сохранить снова. Как только вы сможете сохранить свой код, Google сочтет синтаксис вашего кода нормальным.

Это не означает, что ваш код будет работать так, как задумано, это просто означает, что он может «хорошо скомпилироваться» без каких-либо синтаксических проблем.

Запуск вашего скрипта Google

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

Вам необходимо войти в свою учетную запись Google. А поскольку это пользовательское приложение, а не зарегистрированное в Google, вы увидите предупреждение о том, что приложение не проверено. Выберите продвинутый ссылку, а затем выберите Перейти к образцу Google Script (небезопасно) продолжать.

На следующем экране вам нужно выбрать Позволять предоставить Google Script доступ к вашей учетной записи Google. Как только вы это сделаете, скрипт запустится.

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

Написание расширенных сценариев Google

Это был очень простой скрипт Google, который просто складывал ячейки в один столбец. Если вы хотите добавить переменные к двум циклам For в коде, вы также можете выполнить цикл по всем столбцам и предоставить сводку в строке 7 для всех них.

Google Scripts предлагает всевозможные возможности, такие как отправка электронной почты из электронной таблицы. Вы даже можете подключиться к специальные API Google это позволяет вам интегрировать другие приложения Google, такие как Gmail, Календарь и даже Google Analytics. Как только вы начинаете учиться писать сценарии Google, возможности безграничны.