5 функций скриптов Google Таблиц, которые вам нужно знать

Google Sheets - это мощный облачный инструмент для работы с электронными таблицами, который позволяет вам делать почти все, что вы могли бы делать в Microsoft Excel. Но настоящая сила Google Таблиц - это функция Google Scripting, которая идет с ней.

Скрипты Google Apps - это инструмент фонового скриптинга, который работает не только в Google Таблицах но также Google Docs, Gmail, Гугл Аналитика, и почти любой другой облачный сервис Google. Он позволяет автоматизировать эти отдельные приложения и интегрировать каждое из этих приложений друг с другом.

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

Как создать скрипт Google Apps

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

Для этого выберите Инструменты из меню, затем Редактор скриптов.

Это открывает окно редактора скриптов и по умолчанию используется функция с именем

моя функция (). Здесь вы можете создать и протестировать свой скрипт Google.

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

Функция получения данных из ячейки - это getRange () и getValue () функции. Вы можете идентифицировать ячейку по строке и столбцу. Итак, если у вас есть значение в строке 2 и столбце 1 (столбец A), первая часть вашего скрипта будет выглядеть так:

функция myFunction () {var sheet = SpreadsheetApp.getActiveSheet (); var row = 2; var col = 1; var data = sheet.getRange (строка, столбец) .getValue (); }

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

 var results = data * 100; sheet.getRange (строка, столбец + 1) .setValue (результаты); }

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

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

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

Теперь, когда вы знаете, как написать базовую функцию скрипта Google Apps, давайте взглянем на некоторые более сложные функции.

Используйте getValues ​​для загрузки массивов

Вы можете поднять концепцию выполнения вычислений над данными в вашей электронной таблице с помощью сценариев на новый уровень, используя массивы. Если вы загружаете переменную в скрипт Google Apps с помощью getValues, переменная будет массивом, который может загружать несколько значений из таблицы.

функция myFunction () {var sheet = SpreadsheetApp.getActiveSheet (); var data = sheet.getDataRange (). getValues ​​();

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

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

для (var i = 1; я 
Сохраните и запустите этот сценарий, как вы это делали выше. Вы увидите, что все результаты занесены в столбец 2 вашей таблицы.

Вы заметите, что ссылка на ячейку и строку в переменной массива отличается от ссылки на функцию getRange. 
данные [i] [0] относится к измерениям массива, где первое измерение - это строка, а второе - столбец. Оба они начинаются с нуля.
getRange (я + 1, 2) относится ко второй строке, когда i = 1 (поскольку строка 1 - это заголовок), а 2 - это второй столбец, в котором сохраняются результаты.
Используйте appendRow для записи результатов
Что делать, если у вас есть электронная таблица, в которой вы хотите записать данные в новую строку, а не в новый столбец?
Это легко сделать с appendRow функция. Эта функция не повлияет на существующие данные в таблице. Он просто добавит новую строку к существующему листу.
В качестве примера создайте функцию, которая будет считать от 1 до 10 и показывать счетчик с кратными 2 в Прилавок столбец.
Эта функция будет выглядеть так:
функция myFunction () {var sheet = SpreadsheetApp.getActiveSheet (); для (var i = 1; я <11; i ++) {var result = i * 2; sheet.appendRow ([я, результат]); } }
Вот результаты, когда вы запустите эту функцию.

Обработка RSS-каналов с помощью URLFetchApp
Вы можете объединить предыдущую функцию скрипта Google Таблиц и URLFetchApp чтобы получить RSS-канал с любого веб-сайта и записать строку в электронную таблицу для каждой статьи, недавно опубликованной на этом веб-сайте.
По сути, это метод «сделай сам» для создания собственной электронной таблицы для чтения RSS-каналов!
Сценарий для этого тоже не слишком сложен.
функция myFunction () {var sheet = SpreadsheetApp.getActiveSheet (); var item, date, title, link, desc; var txt = UrlFetchApp.fetch (" https://www.topsecretwriters.com/rss").getContentText(); var doc = Xml.parse (txt, false); title = doc.getElement (). getElement ("канал"). getElement ("название"). getText (); var items = doc.getElement (). getElement ("канал"). getElements ("элемент"); // Анализ отдельных элементов в RSS-потоке для (var i in items) {item = items [i]; title = item.getElement ("название"). getText (); link = item.getElement («ссылка»). getText (); date = item.getElement ("pubDate"). getText (); desc = item.getElement («описание»). getText (); sheet.appendRow ([заголовок, ссылка, дата, описание]); } }
Как вы видете, Xml.parse извлекает каждый элемент из RSS-канала и разделяет каждую строку на заголовок, ссылку, дату и описание. 
С использованием appendRow вы можете поместить эти элементы в соответствующие столбцы для каждого отдельного элемента RSS-канала.
Вывод на вашем листе будет выглядеть примерно так:

Вместо того, чтобы встраивать URL-адрес RSS-канала в сценарий, у вас может быть поле в вашем листе с URL-адресом, а затем несколько листов - по одному для каждого веб-сайта, который вы хотите отслеживать.
Объединение строк и добавление возврата каретки
Вы можете пойти дальше с электронной таблицей RSS, добавив некоторые функции обработки текста и затем используйте функции электронной почты, чтобы отправить себе электронное письмо со сводкой всех новых сообщений в RSS-канале сайта. подача.
Для этого в сценарии, созданном в предыдущем разделе, вам нужно добавить сценарий, который будет извлекать всю информацию из электронной таблицы. 
Вам нужно создать строку темы и текст сообщения электронной почты, проанализировав вместе всю информацию из того же массива «items», который вы использовали для записи данных RSS в электронную таблицу. 
Для этого инициализируйте тему и сообщение, поместив следующие строки перед циклом For «items».
var subject = "Последние 10 статей, опубликованных на mysite.com"
var message = ‘’
Затем в конце цикла for «items» (сразу после функции appendRow) добавьте следующую строку.
сообщение = сообщение + заголовок + '\ n' + ссылка + '\ n' + дата + '\ n' + desc + '\ n' + '\ n \ n';
Символ «+» объединит все четыре элемента вместе, за которым следует «\ n» для возврата каретки после каждой строки. В конце каждого блока данных заголовка вам понадобятся два символа возврата каретки для хорошо отформатированного тела письма.
После обработки всех строк переменная body содержит всю строку сообщения электронной почты. Теперь вы готовы отправить электронное письмо!
Как отправить электронную почту в скрипте Google Apps
Следующим разделом вашего скрипта Google будет отправка «темы» и «тела» по электронной почте. Сделать это в Google Script очень просто.
var emailAddress = [email protected];
MailApp.sendEmail (адрес электронной почты, тема, сообщение);
MailApp - это очень удобный класс внутри скриптов Google Apps, который дает вам доступ к службе электронной почты вашего аккаунта Google для отправки или получения электронных писем. Благодаря этому единственная строка с функцией sendEmail позволяет вам отправить любое электронное письмо только с адресом электронной почты, строкой темы и основным текстом.
Вот как будет выглядеть полученное электронное письмо. 

Сочетание возможности извлекать RSS-канал веб-сайта, сохранять его в Google Sheet и отправлять на с включенными URL-ссылками, позволяет очень удобно следить за последним содержанием для любого интернет сайт.
Это лишь один из примеров возможностей скриптов Google Apps для автоматизации действий и интеграции нескольких облачных сервисов.