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

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

Программы для Windows, мобильные приложения, игры - ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале - Подписывайтесь:)

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

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

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

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

В 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 (var row in values) {
for (var col in values[row]){
sum = values[row][col] + sum;
}
}
sheet.getRange(7,2).setValue(sum);
}


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

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

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

1. var ss = SpreadsheetApp.getActiveSpreadsheet ();

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

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

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

Это было бы var sheet = ss.getSheetByName («Лист1»);

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

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

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

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

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

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

5. var sum = 0

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

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

6. for (var row in values) {

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

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

7. for (var col in values[row]) {

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

8. сумма = значения[row][col] + сумма;

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

9 sheet.getRange (7,2) .setValue (сумма);

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

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

Сохранение вашего скрипта Google

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

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

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

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

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

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

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

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

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

Написание сложных скриптов Google

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

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