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

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

Сценарии Google Apps — это инструмент для создания фоновых сценариев, который работает не только в Google Sheets, но и в Документах Google, Gmail, Google Analytics и почти во всех других облачных службах Google. Это позволяет автоматизировать эти отдельные приложения и интегрировать каждое из этих приложений друг с другом.

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

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

Вы можете начать прямо сейчас создавать свой первый скрипт Служб Google из Google Sheets.

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

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

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

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

function myFunction () {
   var sheet = SpreadsheetApp.getActiveSheet ();
   var row = 2;
   var col = 1;
   var data = sheet.getRange (row, col) .getValue ();
}

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

переменные результаты = данные * 100;
   sheet.getRange (row, col + 1) .setValue (результаты);
}

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

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

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

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

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

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

function myFunction () {
   var sheet = SpreadsheetApp.getActiveSheet ();
   var data = sheet.getDataRange (). getValues ​​();

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

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

для (var i = 1; i <data.length; i ++) {
   var result = data[i][0] * 100;
   sheet.getRange (i + 1, 2) .setValue (результат);
   }
}

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

Вы заметите, что ссылка на ячейку и строку в переменной массива отличается от функции getRange.

данные[i][0] относится к измерениям массива, где первое измерение — это строка, а второе — столбец. Оба они начинаются с нуля.

getRange (i + 1, 2) ссылается на вторую строку, когда i = 1 (поскольку строка 1 является заголовком), а 2 — это второй столбец, в котором хранятся результаты.

Используйте appendRow для записи результатов

Что если у вас есть электронная таблица, в которую вы хотите записать данные в новую строку вместо нового столбца?

Это легко сделать с помощью функции appendRow. Эта функция не будет беспокоить любые существующие данные на листе. Он просто добавит новую строку к существующему листу.

Например, создайте функцию, которая будет считать от 1 до 10 и отображать счетчик с кратными 2 в столбце Счетчик.

Эта функция будет выглядеть так:

function myFunction () {
   var sheet = SpreadsheetApp.getActiveSheet ();

   для (var i = 1; i <11; i ++) {
      var result = i * 2;
     sheet.appendRow ([i,result]);
   }
}

Вот результаты, когда вы запускаете эту функцию.

Обрабатывать RSS-каналы с помощью URLFetchApp

Вы можете объединить предыдущую функцию сценария Google Sheets и URLFetchApp для получения RSS-канала с любого веб-сайта и записать строку в электронную таблицу для каждой статьи, недавно опубликованной на этом веб-сайте.

Это в основном метод DIY для создания собственной таблицы чтения 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 («канал»). getElement («заголовок»). getText ();
   var items = doc.getElement (). getElement («канал»). getElements («элемент»);

// Разбор отдельных элементов в ленте RSS

for (варьируется в элементах) {
   item = items[i];
   title = item.getElement («title»). getText ();
   link = item.getElement («ссылка»). 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 в электронную таблицу.

Для этого инициализируйте тему и сообщение, поместив следующие строки перед циклом «items» For.

var subject = «Последние 10 статей, опубликованных на mysite.com»
var message = ‘’

Затем в конце цикла «items» for (сразу после функции appendRow) добавьте следующую строку.

сообщение = сообщение + заголовок + ‘ n’ + ссылка + ‘ n’ + дата + ‘ n’ + desc + ‘ n’ + ‘ n n’;

Символ «+» объединит все четыре элемента вместе, а затем « n» для возврата каретки после каждой строки. В конце каждого блока данных заголовка вам понадобятся два возврата каретки для красиво отформатированного тела письма.

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

Как отправить письмо в скрипте Google Apps

Следующим разделом вашего Google Script будет отправка «темы» и «тела» по электронной почте. Сделать это в Google Script очень просто.

var emailAddress = myemail@gmail.com;
MailApp.sendEmail (emailAddress, тема, сообщение);

MailApp — это очень удобный класс внутри скриптов Служб Google, который дает вам доступ к почтовой службе вашего аккаунта Google для отправки и получения сообщений электронной почты. Благодаря этому, единственная строка с функцией sendEmail позволяет отправлять любое электронное письмо, содержащее только адрес электронной почты, строку темы и основной текст.

Вот как будет выглядеть итоговое письмо.

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

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