Что такое Apps Script и его возможности для Google Sheets
Apps Script — это облачный язык сценариев, позволяющий автоматизировать задачи и расширять функциональность Google Workspace, включая Google Sheets. Он предоставляет доступ к различным сервисам Google, таким как Sheets API, Maps API, Drive API и другим. С помощью Apps Script можно создавать собственные функции, автоматизировать рутинные операции, интегрировать Google Sheets с другими сервисами и, что важно для нас, создавать интерактивные веб-приложения прямо внутри таблиц.
Зачем создавать интерактивные карты в Google Sheets?
Интерактивные карты в Google Sheets позволяют визуализировать данные о местоположении наглядно и интерактивно. Вместо статического списка адресов вы получаете карту, на которой можно увидеть расположение объектов, получить дополнительную информацию о них при клике на маркер, фильтровать данные и многое другое. Это особенно полезно для:
Анализа географического распределения данных.
Отображения точек продаж, филиалов, складов.
Визуализации данных о доставке или логистике.
Создания карт для туристических маршрутов.
Анализа эффективности рекламных кампаний, ориентированных на определенные регионы.
Обзор необходимых инструментов и сервисов
Для создания интерактивной карты нам понадобятся:
Google Sheets: Для хранения и управления данными.
Apps Script: Для написания кода, взаимодействующего с Google Sheets и Google Maps API.
Google Maps API: Для создания и отображения карты (необходимо получить API ключ).
HTML Service: Для отображения HTML-страницы с картой внутри Google Sheets.
Подготовка данных для карты
Форматирование данных местоположений (адреса, координаты)
Для корректного отображения на карте необходимо правильно отформатировать данные о местоположении. Можно использовать адреса (например, "Москва, ул. Тверская, 10") или координаты (широта и долгота). Использование координат предпочтительнее, так как они обеспечивают более точное позиционирование.
Создание таблицы с данными: название, описание, координаты
Создайте Google Sheet с заголовками столбцов, например: Название, Описание, Адрес (или Широта, Долгота). Заполните таблицу данными о местоположении, названии и дополнительном описании.
Проверка и очистка данных
Перед тем, как приступить к созданию карты, убедитесь, что данные введены корректно. Проверьте правильность адресов и координат, удалите дубликаты и исправьте опечатки. Если вы используете адреса, можно воспользоваться сервисом Geocoding API от Google Maps, чтобы преобразовать их в координаты.
Написание Apps Script для создания интерактивной карты
Настройка триггеров для автоматического обновления карты
Можно настроить триггеры Apps Script для автоматического обновления карты при изменении данных в Google Sheets. Например, можно настроить триггер, который будет запускаться при редактировании ячейки или при отправке формы.
Получение данных из Google Sheets с помощью Apps Script
/**
* Функция для получения данных из Google Sheets.
* @param {string} sheetName - Название листа в Google Sheets.
* @return {Array<Array>} - Двумерный массив данных.
*/
function getDataFromSheet(sheetName: string): string[][] {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(sheetName);
if (!sheet) {
throw new Error(`Sheet with name '${sheetName}' not found.`);
}
const dataRange = sheet.getDataRange();
const data = dataRange.getValues();
// Remove header row
data.shift();
return data;
}Эта функция получает данные из указанного листа Google Sheets и возвращает их в виде двумерного массива. Первая строка массива обычно содержит заголовки столбцов.
Использование Google Maps API для создания маркеров
Для создания маркеров на карте необходимо использовать Google Maps API. Сначала необходимо получить API ключ Google Maps JavaScript API и включить его в HTML-код.
Пример создания маркера:
/**
* Функция для создания HTML кода карты с маркерами.
* @param {Array<Array>} data - Двумерный массив данных о местоположении.
* @return {string} - HTML код карты.
*/
function createMapHtml(data: string[][]): string {
let html = `\n\n\n Interactive Map \n \n #map { height: 400px; }\n \n\n\n \n \n function initMap() {\n const map = new google.maps.Map(document.getElementById("map"), {\n center: {lat: 0, lng: 0}, // Set initial center\n zoom: 2 // Set initial zoom\n });
`;
data.forEach(row => {
const name = row[0];
const description = row[1];
const latitude = parseFloat(row[2]);
const longitude = parseFloat(row[3]);
html += `
const marker = new google.maps.Marker({\n position: {lat: ${latitude}, lng: ${longitude}},\n map: map,\n title: '${name}'\n });
const infowindow = new google.maps.InfoWindow({\n content: '${name}
${description}'\n });
marker.addListener('click', function() {\n infowindow.open(map, marker);\n });
`;
});
html += `
}\n \n \n\n`;
return html;
}Замените YOUR_API_KEY на свой API ключ Google Maps JavaScript API.
Настройка информационных окон для маркеров
Как показано в примере выше, для каждого маркера можно настроить информационное окно, которое будет отображаться при клике на маркер. В информационное окно можно добавить название объекта, описание и другую полезную информацию.
Визуализация карты в Google Sheets
Добавление HTML Service для отображения карты
Чтобы отобразить карту в Google Sheets, необходимо использовать HTML Service. Этот сервис позволяет создавать HTML-страницы и отображать их внутри Google Sheets.
/**
* Функция для отображения карты в Google Sheets.
*/
function showMap() {
const data = getDataFromSheet("Sheet1"); // Replace "Sheet1" with your sheet name
const htmlOutput = HtmlService
.createHtmlOutput(createMapHtml(data))
.setWidth(800)
.setHeight(600);
SpreadsheetApp.getUi()
.showModalDialog(htmlOutput, 'Interactive Map');
}Интеграция карты с данными Google Sheets
HTML-код, созданный функцией createMapHtml, интегрируется с данными, полученными из Google Sheets, динамически создавая маркеры для каждой записи.
Настройка отображения карты: размеры, центрирование
Размеры карты и начальное центрирование можно настроить в HTML-коде. Например, можно задать ширину и высоту карты в пикселях, а также координаты центра карты и уровень масштабирования.
Расширенные возможности и примеры использования
Добавление фильтров и поиска по карте
Можно добавить фильтры и поиск по карте, чтобы пользователи могли легко находить нужные объекты. Например, можно добавить фильтр по типу объекта или поиск по названию.
Кластеризация маркеров для больших объемов данных
При отображении большого количества маркеров на карте может возникнуть проблема с перекрытием маркеров. Для решения этой проблемы можно использовать кластеризацию маркеров, которая объединяет близлежащие маркеры в кластеры.
Интеграция с другими сервисами Google (например, Google Forms)
Интерактивную карту можно интегрировать с другими сервисами Google, например, Google Forms. Например, можно создать форму, в которой пользователи смогут добавлять новые объекты на карту.
Примеры использования: отображение филиалов, доставка, туризм
Интерактивные карты в Google Sheets могут использоваться в различных сферах:
Отображение филиалов: Позволяет клиентам легко находить ближайший филиал компании.
Доставка: Позволяет отслеживать местоположение курьеров и оптимизировать маршруты доставки.
Туризм: Позволяет создавать интерактивные карты туристических маршрутов с информацией о достопримечательностях.