Что такое Google Apps Script и зачем он нужен для Google Sheets?
Google Apps Script (GAS) — это облачный язык сценариев, основанный на JavaScript, который позволяет автоматизировать задачи и расширять функциональность приложений Google Workspace, включая Google Sheets. Он позволяет создавать пользовательские функции, автоматизировать рутинные операции, интегрировать Google Sheets с другими сервисами Google и сторонними API.
Для Google Sheets, GAS предоставляет возможность программно управлять таблицами, листами, ячейками и данными. Это открывает широкие возможности для автоматизации отчетности, обработки данных, интеграции с CRM и другими системами.
Преимущества использования Apps Script для автоматизации задач
Использование Apps Script предоставляет следующие преимущества:
Автоматизация рутинных задач: Сокращение времени и усилий, затрачиваемых на повторяющиеся операции.
Расширение функциональности: Добавление пользовательских функций и возможностей, недоступных в стандартном интерфейсе Google Sheets.
Интеграция с другими сервисами: Обмен данными между Google Sheets и другими приложениями Google (Docs, Drive, Calendar, Gmail) и сторонними сервисами (CRM, ERP и т.д.).
Централизованное управление: Скрипты хранятся в облаке, обеспечивая доступ к ним с любого устройства.
Совместная работа: Возможность совместной разработки и использования скриптов несколькими пользователями.
Как открыть редактор Apps Script из Google Sheets
Чтобы открыть редактор Apps Script из Google Sheets, выполните следующие действия:
Откройте Google Sheets.
Выберите Инструменты > Редактор скриптов.
Откроется новая вкладка с редактором Apps Script, где вы можете писать и запускать свой код.
Основные функции для работы с данными в Google Sheets
Получение доступа к таблице и листам: SpreadsheetApp и Spreadsheet
Для работы с Google Sheets через Apps Script необходимо получить доступ к таблице и листам. Это делается с помощью классов SpreadsheetApp и Spreadsheet:
/**
* @OnlyCurrentDoc
*/
function accessSpreadsheet() {
// Получаем активную таблицу
const ss: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// Получаем активный лист
const sheet: GoogleAppsScript.Spreadsheet.Sheet = ss.getActiveSheet();
// Или получаем лист по имени
const sheetByName: GoogleAppsScript.Spreadsheet.Sheet = ss.getSheetByName("ИмяЛиста");
Logger.log(sheet.getName());
}SpreadsheetApp – это основной класс, предоставляющий доступ к таблицам. getActiveSpreadsheet() возвращает активную таблицу, а getSheetByName() позволяет получить лист по его имени.
Чтение данных из ячеек и диапазонов: getValue(), getValues(), getRange()
Для чтения данных из ячеек и диапазонов используются методы getValue(), getValues() и getRange():
function readData() {
const ss: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet: GoogleAppsScript.Spreadsheet.Sheet = ss.getActiveSheet();
// Получаем значение из ячейки A1
const cellValue: any = sheet.getRange("A1").getValue();
Logger.log("Значение ячейки A1: " + cellValue);
// Получаем значения из диапазона A1:B5
const rangeValues: any[][] = sheet.getRange("A1:B5").getValues();
Logger.log("Значения диапазона A1:B5: " + rangeValues);
}getRange() возвращает объект Range, представляющий ячейку или диапазон. getValue() возвращает значение одной ячейки, а getValues() возвращает двумерный массив значений из диапазона.
Запись данных в ячейки и диапазоны: setValue(), setValues()
Для записи данных в ячейки и диапазоны используются методы setValue() и setValues():
function writeData() {
const ss: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet: GoogleAppsScript.Spreadsheet.Sheet = ss.getActiveSheet();
// Записываем значение в ячейку A1
sheet.getRange("A1").setValue("Новое значение");
// Записываем значения в диапазон A1:B2
const data: any[][] = [["Значение 1", "Значение 2"], ["Значение 3", "Значение 4"]];
sheet.getRange("A1:B2").setValues(data);
}setValue() записывает одно значение в ячейку, а setValues() записывает двумерный массив значений в диапазон.
Работа с форматированием: setFontWeight(), setBackground(), и другие
Apps Script позволяет управлять форматированием ячеек и диапазонов. Вот примеры:
function formatData() {
const ss: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet: GoogleAppsScript.Spreadsheet.Sheet = ss.getActiveSheet();
// Устанавливаем жирный шрифт для ячейки A1
sheet.getRange("A1").setFontWeight("bold");
// Устанавливаем цвет фона для ячейки B2
sheet.getRange("B2").setBackground("#FF0000");
// Устанавливаем размер шрифта для диапазона C3:C5
sheet.getRange("C3:C5").setFontSize(12);
}Доступны и другие методы для управления форматированием, такие как setFontFamily(), setTextRotation(), setHorizontalAlignment() и другие. Для получения более детальной информации следует обратиться к официальной документации Google Apps Script.
Функции для автоматизации задач с листами
Создание, удаление и переименование листов: insertSheet(), deleteSheet(), setName()
Apps Script позволяет автоматизировать управление листами в таблице:
function manageSheets() {
const ss: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// Создаем новый лист
const newSheet: GoogleAppsScript.Spreadsheet.Sheet = ss.insertSheet("Новый лист");
// Переименовываем лист
newSheet.setName("Переименованный лист");
// Удаляем лист
// ss.deleteSheet(newSheet); // Внимание: удаление необратимо!
}Копирование листов: copyTo()
Для копирования листов используется метод copyTo():
function copySheet() {
const ss: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet: GoogleAppsScript.Spreadsheet.Sheet = ss.getActiveSheet();
// Копируем лист в другую таблицу
const newSS: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.create("Копия таблицы");
sheet.copyTo(newSS);
}Защита листов и диапазонов: protect(), remove()
Apps Script позволяет защищать листы и диапазоны от изменений:
function protectSheet() {
const ss: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet: GoogleAppsScript.Spreadsheet.Sheet = ss.getActiveSheet();
// Защищаем лист
const protection: GoogleAppsScript.Spreadsheet.Protection = sheet.protect().setDescription("Защита листа");
// Защищаем диапазон
const range: GoogleAppsScript.Spreadsheet.Range = sheet.getRange("A1:B10");
const rangeProtection: GoogleAppsScript.Spreadsheet.Protection = range.protect().setDescription("Защита диапазона");
// Удаляем защиту
// protection.remove();
// rangeProtection.remove();
}Использование триггеров для автоматического выполнения скриптов
Типы триггеров: onOpen(), onEdit(), onChange(), onFormSubmit()
Триггеры позволяют автоматически запускать скрипты при определенных событиях в Google Sheets:
onOpen(): Запускается при открытии таблицы.
onEdit(): Запускается при изменении данных в таблице.
onChange(): Запускается при любом изменении структуры таблицы (добавление/удаление листов, изменение форматирования и т.д.).
onFormSubmit(): Запускается при отправке данных через Google Forms, связанную с таблицей.
Настройка триггеров через интерфейс Apps Script
Триггеры можно настроить через интерфейс редактора Apps Script: Редактировать > Триггеры текущего проекта.
Создание триггеров программно
Триггеры также можно создавать программно:
function createTrigger() {
// Создаем триггер, который запускается при изменении данных
ScriptApp.newTrigger('myFunction')
.forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet().getId())
.onEdit()
.create();
}
function myFunction() {
Logger.log('Триггер сработал!');
}Примеры практического использования функций Apps Script в Google Sheets
Автоматическая отправка email-уведомлений при изменении данных
Можно настроить отправку уведомлений по email при изменении определенных ячеек или диапазонов. Например, когда статус заказа меняется на "Отправлен".
function onEdit(e: GoogleAppsScript.Events.SheetsOnEdit) {
const sheet: GoogleAppsScript.Spreadsheet.Sheet = e.range.getSheet();
const editedCell: GoogleAppsScript.Spreadsheet.Range = e.range;
const editedRow: number = editedCell.getRow();
const editedColumn: number = editedCell.getColumn();
// Проверяем, что изменение произошло в столбце статуса (например, столбец 5)
if (editedColumn === 5) {
const status: any = editedCell.getValue();
// Если статус изменился на "Отправлен", отправляем уведомление
if (status === "Отправлен") {
const orderId: any = sheet.getRange("A" + editedRow).getValue(); // Предполагаем, что ID заказа в столбце A
const email: any = sheet.getRange("B" + editedRow).getValue(); // Предполагаем, что email клиента в столбце B
const subject: string = "Заказ №" + orderId + " отправлен";
const body: string = "Ваш заказ №" + orderId + " отправлен. Ожидайте доставку.";
MailApp.sendEmail(email, subject, body);
}
}
}Создание пользовательских функций (UDF) для Google Sheets
Можно создавать собственные функции, которые можно использовать непосредственно в ячейках Google Sheets. Например, функция для расчета ROI (Return on Investment):
/**
* Calculates the Return on Investment (ROI).
* @param {number} investment The amount of the investment.
* @param {number} gain The amount of gain from the investment.
* @return The ROI percentage.
* @customfunction
*/
function ROI(investment: number, gain: number): number {
// Проверка на ноль, чтобы избежать деления на ноль
if (investment === 0) {
return 0;
}
return (gain - investment) / investment;
}После сохранения скрипта, функцию ROI можно использовать в Google Sheets как =ROI(A1, B1), где A1 – инвестиции, B1 – прибыль.
Автоматизация импорта данных из внешних источников
С помощью Apps Script можно автоматически импортировать данные из API различных сервисов, например, данные из рекламных кабинетов Google Ads или Яндекс.Директ. Например, импорт данных о расходах из Google Ads:
function importGoogleAdsData() {
const ss: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet: GoogleAppsScript.Spreadsheet.Sheet = ss.getActiveSheet();
// Замените на ваши учетные данные и настройки Google Ads API
const ACCOUNT_ID: string = 'YOUR_ACCOUNT_ID';
const DATE_RANGE: string = 'LAST_30_DAYS';
const REPORT_QUERY: string = 'SELECT CampaignName, Clicks, Cost FROM CAMPAIGN_PERFORMANCE_REPORT DURING ' + DATE_RANGE;
// Получаем токен доступа
const accessToken: string = getGoogleAdsAccessToken();
// Запрашиваем данные из Google Ads API
const url: string = 'https://googleads.googleapis.com/v11/customers/' + ACCOUNT_ID + '/googleAds:query';
const options: any = {
'method': 'post',
'headers': {
'Authorization': 'Bearer ' + accessToken,
'Content-Type': 'application/json',
'developer-token': 'YOUR_DEVELOPER_TOKEN', // Обязательный developer-token
'login-customer-id': 'YOUR_LOGIN_CUSTOMER_ID' // Обязательный login-customer-id
},
'payload': JSON.stringify({ 'query': REPORT_QUERY }),
'muteHttpExceptions': true // Важно для обработки ошибок
};
const response: GoogleAppsScript.URL_Fetch.HTTPResponse = UrlFetchApp.fetch(url, options);
const json: any = JSON.parse(response.getContentText());
if (response.getResponseCode() === 200) {
// Обрабатываем данные и записываем их в таблицу
const data: any[][] = [["Campaign Name", "Clicks", "Cost"]];
if(json.results) {
json.results.forEach(function(row: any) {
const campaignName = row.campaign.campaignName;
const clicks = row.metrics.clicks;
const cost = row.metrics.costMicros / 1000000; // Convert from micros to currency
data.push([campaignName.value, clicks.value, cost.value]);
});
// Записываем данные в таблицу
const range: GoogleAppsScript.Spreadsheet.Range = sheet.getRange(1, 1, data.length, data[0].length);
range.setValues(data);
} else {
Logger.log("No results found");
}
} else {
// Обрабатываем ошибку
Logger.log("Error fetching data: " + response.getContentText());
}
}
/**
* Fetches access token using OAuth2 service
* Requires OAuth2 library to be added to the project
*/
function getGoogleAdsAccessToken(): string {
// Replace with your client ID and client secret
const clientId: string = 'YOUR_CLIENT_ID';
const clientSecret: string = 'YOUR_CLIENT_SECRET';
const service: any = OAuth2.createService('googleads')
.setClientId(clientId)
.setClientSecret(clientSecret)
.setTokenUrl('https://oauth2.googleapis.com/token')
.setAuthorizationBaseUrl('https://accounts.google.com/o/oauth2/auth')
.setCallbackFunction('authCallback') // This must be a globally defined function in your script
.setPropertyStore(PropertiesService.getUserProperties())
.setScope('https://www.googleapis.com/auth/adwords');
if (service.hasAccess()) {
return service.getAccessToken();
} else {
const authorizationUrl: string = service.getAuthorizationUrl();
Logger.log('Authorize the script by visiting: ' + authorizationUrl);
// In a real application, you would prompt the user to visit the authorization URL
// and then run the `authCallback` function to complete the OAuth2 flow.
throw new Error('Authorization required. Open logs for URL.');
}
}
function authCallback(request: any) {
const service: any = OAuth2.getService('googleads');
const authorized: boolean = service.handleCallback(request);
if (authorized) {
return HtmlService.createHtmlOutput('Success!');
} else {
return HtmlService.createHtmlOutput('Denied.');
}
}Важно: Для работы с Google Ads API требуется настроить OAuth 2.0 и получить соответствующие учетные данные. Также, потребуется добавить библиотеку OAuth2 в проект Apps Script.
Создание интерактивных элементов управления (кнопок, меню)
Apps Script позволяет создавать пользовательские меню и диалоговые окна для взаимодействия с пользователем. Например, добавление пользовательского меню:
function onOpen() {
const ui: GoogleAppsScript.UI.Ui = SpreadsheetApp.getUi();
ui.createMenu('Пользовательское меню')
.addItem('Выполнить функцию', 'myFunction')
.addToUi();
}
function myFunction() {
Browser.msgBox('Функция выполнена!');
}Этот код добавит в Google Sheets меню "Пользовательское меню" с пунктом "Выполнить функцию", при нажатии на который будет выводиться сообщение.
Использование Apps Script открывает широкие возможности для автоматизации и расширения функциональности Google Sheets, позволяя создавать мощные инструменты для обработки данных, интеграции с другими сервисами и автоматизации бизнес-процессов.