Введение в JSON и Google Apps Script
Что такое JSON и его структура
JSON (JavaScript Object Notation) — это легковесный формат обмена данными. Он основан на подмножестве JavaScript и представляет данные в виде пар «ключ-значение» или упорядоченных списков. Основные компоненты JSON:
- Объекты: Заключены в фигурные скобки
{}и содержат неупорядоченный набор пар «ключ»:»значение». Ключи всегда являются строками, значения могут быть примитивными типами (строка, число, булево значение, null), другими объектами или массивами. - Массивы: Заключены в квадратные скобки
[]и представляют собой упорядоченные списки значений. Значения могут быть любого типа, допустимого в JSON. - Примитивы: Строки (в двойных кавычках), числа (целые или с плавающей точкой), булевы значения (true/false) и null.
Пример JSON:
{
"name": "Example Product",
"price": 25.99,
"inStock": true,
"tags": ["electronics", "featured"]
}
Зачем читать JSON файлы в Google Apps Script
Чтение JSON файлов в Google Apps Script позволяет:
- Интегрироваться с внешними API: Многие веб-сервисы предоставляют данные в формате JSON. Apps Script может получать и обрабатывать эти данные.
- Импортировать данные в Google Sheets/Docs: JSON можно использовать для структурированного хранения данных, которые затем импортируются в таблицы или документы Google.
- Конфигурировать приложения: JSON файлы могут хранить параметры конфигурации для приложений Apps Script.
- Автоматизировать задачи: Обрабатывать данные из различных источников в едином формате для автоматизации отчетности, анализа и других задач.
Предварительные требования: Настройка Google Apps Script
Для работы с JSON в Google Apps Script никаких специальных настроек не требуется. Достаточно иметь доступ к Google Apps Script editor. Рекомендуется использовать редактор кода Apps Script с включенной поддержкой TypeScript для улучшения читаемости и поддержки кода. Для доступа к внешним ресурсам (например, чтение JSON с URL) может потребоваться включение соответствующих служб в настройках проекта Apps Script.
Чтение JSON из Google Sheets
Получение данных из Google Sheets как JSON
Хотя Google Sheets не хранит данные непосредственно в формате JSON, можно преобразовать данные из таблицы в JSON. Это полезно, когда нужно экспортировать данные для дальнейшей обработки в Apps Script.
/**
* Получает данные из Google Sheets и преобразует их в JSON.
* @param {string} spreadsheetId ID Google Sheets.
* @param {string} sheetName Имя листа.
* @return {string} JSON представление данных.
*/
function getJsonFromSheet(spreadsheetId: string, sheetName: string): string {
const ss = SpreadsheetApp.openById(spreadsheetId);
const sheet = ss.getSheetByName(sheetName);
if (!sheet) {
throw new Error(`Sheet with name '${sheetName}' not found.`);
}
const dataRange = sheet.getDataRange();
const values = dataRange.getValues();
// Предполагаем, что первая строка содержит заголовки
const headers = values[0];
const jsonData = [];
for (let i = 1; i < values.length; i++) {
const rowData = {};
for (let j = 0; j < headers.length; j++) {
rowData[headers[j]] = values[i][j];
}
jsonData.push(rowData);
}
return JSON.stringify(jsonData);
}
Обработка JSON данных из Google Sheets в Apps Script
После получения JSON строки из таблицы, ее необходимо распарсить с помощью JSON.parse():
/**
* Обрабатывает JSON данные.
* @param {string} jsonString JSON строка.
* @return {object[]} Массив объектов, полученных из JSON.
*/
function processJsonData(jsonString: string): object[] {
try {
const jsonData = JSON.parse(jsonString);
return jsonData;
} catch (e) {
Logger.log(`Error parsing JSON: ${e}`);
return [];
}
}
Пример: Запись данных из JSON в Google Sheets
Следующий код читает JSON строку, полученную из Google Sheets, и записывает определенные поля в другую таблицу:
/**
* Записывает данные из JSON в Google Sheets.
*/
function writeJsonToSheet() {
const sourceSpreadsheetId = "YOUR_SOURCE_SPREADSHEET_ID";
const sourceSheetName = "Sheet1";
const targetSpreadsheetId = "YOUR_TARGET_SPREADSHEET_ID";
const targetSheetName = "Sheet2";
const jsonString = getJsonFromSheet(sourceSpreadsheetId, sourceSheetName);
const jsonData = processJsonData(jsonString);
const targetSs = SpreadsheetApp.openById(targetSpreadsheetId);
const targetSheet = targetSs.getSheetByName(targetSheetName);
if (!targetSheet) {
throw new Error(`Target sheet with name '${targetSheetName}' not found.`);
}
// Очищаем целевой лист
targetSheet.clearContents();
// Записываем заголовки
if (jsonData.length > 0) {
const headers = Object.keys(jsonData[0]);
targetSheet.appendRow(headers);
// Записываем данные
jsonData.forEach(item => {
const row = headers.map(header => item[header]);
targetSheet.appendRow(row);
});
}
}
Чтение JSON из внешних источников (URL)
Использование UrlFetchApp для получения JSON
Сервис UrlFetchApp позволяет получать данные из веб-ресурсов, включая JSON API. Для отправки запросов к внешним ресурсам может потребоваться авторизация, если API требует её.
/**
* Получает JSON данные из URL.
* @param {string} url URL адрес.
* @return {string} JSON строка, полученная из URL.
*/
function getJsonFromUrl(url: string): string {
try {
const response = UrlFetchApp.fetch(url);
const content = response.getContentText();
return content;
} catch (e) {
Logger.log(`Error fetching URL: ${e}`);
return "";
}
}
Разбор JSON данных, полученных из URL
После получения JSON строки, её необходимо распарсить, как и в случае с данными из Google Sheets:
// (Используйте функцию processJsonData, описанную выше)
Обработка ошибок при чтении JSON из внешнего источника
Важно обрабатывать ошибки, которые могут возникнуть при чтении JSON из внешнего источника:
- Сетевые ошибки:
UrlFetchApp.fetch()может выбрасывать исключения, если сервер недоступен или произошла сетевая ошибка. - Некорректный JSON: API может возвращать некорректный JSON, который не удастся распарсить.
- Ошибки авторизации: Если API требует авторизации, необходимо правильно настроить параметры авторизации в
UrlFetchApp.fetch().
try {
const response = UrlFetchApp.fetch(url, {"muteHttpExceptions": true});
const content = response.getContentText();
if (response.getResponseCode() === 200) {
return content;
} else {
Logger.log(`Error fetching URL: HTTP Status ${response.getResponseCode()}`);
return "";
}
} catch (e) {
Logger.log(`Error fetching URL: ${e}`);
return "";
}
Пример: Получение и отображение данных о погоде из JSON API
Следующий код получает данные о погоде из открытого API и выводит их в лог:
/**
* Получает и отображает данные о погоде из API.
*/
function getWeather() {
const apiKey = "YOUR_API_KEY"; // Замените на ваш API ключ
const city = "London";
const url = `https://api.openweathermap.org/data/2.5/weather?q=${city}&appid=${apiKey}&units=metric`;
const jsonString = getJsonFromUrl(url);
if (jsonString) {
const weatherData = processJsonData(jsonString)[0];
Logger.log(`Temperature in ${city}: ${weatherData.main.temp}°C`);
Logger.log(`Weather condition: ${weatherData.weather[0].description}`);
}
}
Чтение JSON из Google Cloud Storage (GCS)
Настройка доступа к Google Cloud Storage
Для чтения файлов из Google Cloud Storage (GCS) необходимо:
- Включить API Google Cloud Storage в проекте Google Cloud.
- Настроить сервисный аккаунт с правами доступа к GCS bucket.
- Активировать Advanced Google Service для Cloud Storage API в Apps Script.
Чтение JSON файла из GCS с помощью Apps Script
/**
* Читает JSON файл из Google Cloud Storage.
* @param {string} bucketName Имя GCS bucket.
* @param {string} fileName Имя JSON файла.
* @return {string} JSON строка из файла.
*/
function getJsonFromGcs(bucketName: string, fileName: string): string {
try {
const file = Storage.Objects.get(bucketName, fileName);
const content = Utilities.newBlob(file.body).getDataAsString();
return content;
} catch (e) {
Logger.log(`Error reading from GCS: ${e}`);
return "";
}
}
Пример: Загрузка и обработка конфигурационного JSON файла из GCS
/**
* Загружает и обрабатывает конфигурационный JSON файл из GCS.
*/
function loadConfigFromGcs() {
const bucketName = "YOUR_BUCKET_NAME";
const fileName = "config.json";
const jsonString = getJsonFromGcs(bucketName, fileName);
if (jsonString) {
const configData = processJsonData(jsonString)[0];
Logger.log(`Config loaded: ${JSON.stringify(configData)}`);
// Используйте configData для настройки приложения
}
}
Практические примеры и советы
Обработка сложных JSON структур (вложенные объекты, массивы)
Для обработки сложных JSON структур используйте рекурсию или итеративные подходы. При работе с глубоко вложенными объектами, старайтесь избегать слишком длинных цепочек обращений к свойствам (например, data.level1.level2.level3). Вместо этого, используйте промежуточные переменные для хранения результатов.
/**
* Рекурсивно обходит JSON структуру и выводит значения.
* @param {object} obj Объект для обхода.
*/
function traverseJson(obj: object) {
for (const key in obj) {
if (obj.hasOwnProperty(key)) {
const value = obj[key];
if (typeof value === 'object' && value !== null) {
traverseJson(value);
} else {
Logger.log(`${key}: ${value}`);
}
}
}
}
Советы по оптимизации чтения больших JSON файлов
- Используйте потоковую обработку (если возможно): Для очень больших файлов, рассмотрите возможность потоковой обработки, чтобы избежать загрузки всего файла в память.
- Оптимизируйте API запросы: Если получаете JSON из API, используйте параметры для фильтрации и уменьшения объема возвращаемых данных.
- Кэшируйте данные: Если данные не меняются часто, кэшируйте их в Script Properties или User Properties для быстрого доступа.
Распространенные ошибки и способы их устранения
SyntaxError: Unexpected token:: Ошибка синтаксиса в JSON. Убедитесь, что JSON строка валидна (например, с помощью онлайн-валидаторов).TypeError: Cannot read property '...' of undefined: Попытка доступа к несуществующему свойству объекта. Проверьте наличие свойства перед его использованием.UrlFetchApp: Request failed for ... returned code 403: Ошибка доступа к URL. Проверьте права доступа и настройки API.Service invoked too many times for one day: Превышен лимит использования сервиса. Оптимизируйте код для уменьшения количества запросов.
В заключение, чтение JSON файлов в Google Apps Script является мощным инструментом для интеграции с внешними сервисами, обработки данных и автоматизации задач. Правильное использование UrlFetchApp, JSON.parse() и обработки ошибок позволит создавать надежные и эффективные приложения.