Google Таблицы – это мощный облачный инструмент для работы с электронными таблицами, который позволяет делать почти все, что вы можете делать в Microsoft Excel. Но настоящая сила Google Таблиц – это встроенная в них функция Google Scripting.
Сценарии Google Apps – это инструмент для создания фоновых сценариев, который работает не только с в Google Таблицах, но и с Google Docs, Gmail, Гугл Аналитика и почти всеми другими облачными службами Google. Он позволяет автоматизировать эти отдельные приложения и интегрировать каждое из них друг с другом.
В этой статье вы узнаете, как начать работу со скриптами Google Apps, создать базовый скрипт в Google Sheets для чтения и записи данных ячеек, а также наиболее эффективные расширенные функции скрипта Google Sheets.
Как создать скрипт Google Apps
Вы можете прямо сейчас начать создавать свой первый скрипт Google Apps прямо из Google Таблиц.
Для этого выберите в меню Инструменты , затем Редактор скриптов .
Откроется окно редактора скриптов, в котором по умолчанию используется функция myfunction() . Здесь вы можете создать и протестировать свой скрипт Google.
Чтобы попробовать, попробуйте создать функцию-скрипт Google Таблиц, которая будет считывать данные из одной ячейки, выполнять вычисления и выводить объем данных в другую ячейку.
Функция получения данных из ячейки — это функции getRange() и getValue() . Вы можете идентифицировать ячейку по строке и столбцу. Итак, если у вас есть значение в строке 2 и столбце 1 (столбец A), первая часть вашего скрипта будет выглядеть так:
function myFunction() {
var sheet = SpreadsheetApp.getActiveSheet();
var row = 2;
var col = 1;
var data = sheet.getRange(row, col).getValue();
}
Значение из этой ячейки сохраняется в переменной data . Вы можете выполнить расчет данных, а затем записать эти данные в другую ячейку. Итак, последняя часть этой функции будет:
var results = data * 100;sheet.getRange(row, col+1).setValue(results); }
Завершив написание функции, выберите значок диска для сохранения.
При первом запуске новой функции сценария Google Таблиц, подобной этой (выбрав значок запуска), вам потребуется предоставить авторизацию для запуска сценария в вашей учетной записи Google.
Разрешите разрешения, чтобы продолжить. После запуска скрипта вы увидите, что он записал результаты вычислений в целевую ячейку..
Теперь, когда вы знаете, как написать базовую функцию сценария Google Apps, давайте рассмотрим некоторые более сложные функции.
Используйте getValues для загрузки массивов
Вы можете вывести концепцию выполнения вычислений над данными в электронной таблице с помощью сценариев на новый уровень, используя массивы. Если вы загружаете переменную в скрипт Google Apps с помощью getValues, эта переменная будет массивом, который может загружать несколько значений из листа.
function myFunction() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
Переменная data представляет собой многомерный массив, содержащий все данные листа. Чтобы выполнить расчет данных, вы используете цикл for . Счетчик цикла for будет работать с каждой строкой, а столбец останется постоянным в зависимости от столбца, из которого вы хотите получить данные.
В нашем примере электронной таблицы вы можете выполнять вычисления над тремя строками данных следующим образом.
for (var i = 1; i < data.length; i++) {
var result = data[i][0] * 100;
sheet.getRange(i+1, 2).setValue(result);
}
}
Сохраните и запустите этот скрипт, как вы это делали выше. Вы увидите, что все результаты занесены в столбец 2 вашей электронной таблицы.
Вы заметите, что обращение к ячейке и строке в переменной массива отличается от обращения к функции getRange.
data[i][0] относится к измерениям массива, где первое измерение — это строка, а второе — столбец. Оба они начинаются с нуля.
getRange (i+1, 2) относится ко второй строке, когда i = 1 (поскольку строка 1 является заголовком), а 2 - второй столбец, где хранятся результаты.
Используйте AppendRow для записи результатов
Что, если у вас есть электронная таблица, в которой вы хотите записать данные в новую строку, а не в новый столбец?
Это легко сделать с помощью функции appendRow . Эта функция не будет влиять на существующие данные на листе. Он просто добавит новую строку к существующему листу.
В качестве примера создайте функцию, которая будет считать от 1 до 10 и отображать счетчик, кратный 2, в столбце Счетчик .
Эта функция будет выглядеть так:
function myFunction() {
var sheet = SpreadsheetApp.getActiveSheet();
for (var i = 1; i<11; i++) {
var result = i * 2;
sheet.appendRow([i,result]);
}
}
Вот результаты запуска этой функции.
Обработка RSS-каналов с помощью URLFetchApp
Вы можете объединить предыдущую функцию сценария Google Таблиц и URLFetchApp , чтобы получать RSS-канал с любого веб-сайта и записывать в электронную таблицу строку для каждой статьи, недавно опубликованной на этом веб-сайте.
>.По сути, это самодельный метод создания собственной таблицы для чтения RSS-каналов!
Сценарий для этого тоже не слишком сложен.
function 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("channel").getElement("title").getText();
var items = doc.getElement().getElement("channel").getElements("item");
// Parsing single items in the RSS Feed
for (var i in items) {
item = items[i];
title = item.getElement("title").getText();
link = item.getElement("link").getText();
date = item.getElement("pubDate").getText();
desc = item.getElement("description").getText();
sheet.appendRow([title,link,date,desc]);
}
}
Как видите, Xml.parse извлекает каждый элемент из RSS-канала и разделяет каждую строку на заголовок, ссылку, дату и описание.
Используя функцию appendRow , вы можете поместить эти элементы в соответствующие столбцы для каждого отдельного элемента в RSS-канале.
Вывод на вашем листе будет выглядеть примерно так:
Вместо того, чтобы встраивать URL-адрес RSS-канала в сценарий, вы можете создать на своем листе поле с URL-адресом, а затем создать несколько листов — по одному для каждого веб-сайта, который вы хотите отслеживать.
Объединить строки и добавить возврат каретки
Вы можете усовершенствовать электронную таблицу RSS, добавив некоторые функции манипулирования текстом, а затем использовать функции электронной почты, чтобы отправить себе электронное письмо со сводкой всех новых сообщений в RSS-канале сайта.
Для этого в сценарий, который вы создали в предыдущем разделе, вам нужно добавить несколько сценариев, которые будут извлекать всю информацию из электронной таблицы.
Вам потребуется создать строку темы и тело текста электронного письма, анализируя вместе всю информацию из того же массива «items», который вы использовали для записи данных RSS в электронную таблицу.
Для этого инициализируйте тему и сообщение, поместив следующие строки перед циклом For «items».
var subject = ‘Latest 10 articles published at mysite.com’
var message = ‘’
Затем в конце цикла for «items» (сразу после функции AppendRow) добавьте следующую строку.
message = message + title + '\n' + link + '\n' + date + '\n' + desc + '\n' + '\n \n';
Символ «+» объединяет все четыре элемента вместе, а после каждой строки следует символ «\n» для возврата каретки. В конце каждого блока данных заголовка вам понадобятся два возврата каретки для красиво отформатированного тела электронного письма.
После обработки всех строк переменная body содержит всю строку сообщения электронной почты. Теперь вы готовы отправить электронное письмо!
Как отправить электронное письмо с помощью скрипта Google Apps
Следующим разделом вашего скрипта Google будет отправка «темы» и «тела» по электронной почте. Сделать это с помощью Google Script очень просто.
var emailAddress = [email protected];
MailApp.sendEmail(emailAddress, subject, message);
MailApp — это очень удобный класс внутри сценариев Google Apps, который дает вам доступ к службе электронной почты вашего аккаунта Google для отправки и получения электронных писем. Благодаря этому единственная строка с функцией sendEmail позволяет вам отправить любое электронное письмо использовать только адрес электронной почты, строку темы и основной текст..
Вот как будет выглядеть полученное электронное письмо.
Сочетание возможности извлекать RSS-канал веб-сайта, сохранять его в Google Sheet и отправлять себе с включенными URL-ссылками делает очень удобным отслеживание новейшего контента любого веб-сайта.
Это лишь один пример возможностей сценариев Google Apps для автоматизации действий и интеграции нескольких облачных сервисов.
.