Как конвертировать Google Таблицы CSV в JSON с использованием скрипта приложений

Google Таблицы — это мощный инструмент для хранения и обработки данных, но иногда вам нужны данные в другом формате, например JSON, для использования в веб-приложениях или анализа данных. В этом руководстве мы проведем вас через процесс использования пользовательского Google Скрипт приложений для преобразования вашего Google Формат таблиц CSV в JSON.

Что вам нужно:

• A Google аккаунт
• Базовые знания Google Листы
• Ваши данные в Google Таблицы электронных таблиц

Шаг 1: Подготовьте Google Данные CSV листа

1. Откройте Google Лист, содержащий данные, которые вы хотите преобразовать.
2. Убедитесь, что ваши данные хорошо структурированы и содержат заголовки в первой строке.
3. Закрепите первую строку заголовка (нажмите «Вид» > «Закрепить» > «1 строка»).
4. Убедитесь, что между вашими данными нет пустых столбцов.

Шаг 2: Откройте редактор скриптов приложений.

1. В вашей Google Лист, нажмите «Инструменты» в верхнем меню.
2. В раскрывающемся меню выберите «Редактор скриптов».
3. Откроется новая вкладка с Google Редактор скриптов приложений.

Шаг 3: Скопируйте и вставьте скрипт

1. Удалите весь существующий код в редакторе скриптов.
2. Скопируйте весь скрипт, представленный ниже.

var FORMAT_ONELINE = 'One-line'; var FORMAT_MULTILINE = 'Multi-line'; var FORMAT_PRETTY = 'Pretty'; var LANGUAGE_JS = 'JavaScript'; var LANGUAGE_PYTHON = 'Python'; var STRUCTURE_LIST = 'List'; var STRUCTURE_HASH = 'Hash (keyed by "id" column)'; /* Defaults for this particular spreadsheet, change as desired */ var DEFAULT_FORMAT = FORMAT_PRETTY; var DEFAULT_LANGUAGE = LANGUAGE_JS; var DEFAULT_STRUCTURE = STRUCTURE_LIST; function onOpen() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var menuEntries = [ {name: "Export JSON for this sheet", functionName: "exportSheet"}, {name: "Export JSON for all sheets", functionName: "exportAllSheets"} ]; ss.addMenu("Export JSON", menuEntries); } function makeLabel(app, text, id) { var lb = app.createLabel(text); if (id) lb.setId(id); return lb; } function makeListBox(app, name, items) { var listBox = app.createListBox().setId(name).setName(name); listBox.setVisibleItemCount(1); var cache = CacheService.getPublicCache(); var selectedValue = cache.get(name); Logger.log(selectedValue); for (var i = 0; i < items.length; i++) { listBox.addItem(items[i]); if (items[1] == selectedValue) { listBox.setSelectedIndex(i); } } return listBox; } function makeButton(app, parent, name, callback) { var button = app.createButton(name); app.add(button); var handler = app.createServerClickHandler(callback).addCallbackElement(parent);; button.addClickHandler(handler); return button; } function makeTextBox(app, name) { var textArea = app.createTextArea().setWidth('100%').setHeight('200px').setId(name).setName(name); return textArea; } function exportAllSheets(e) { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheets = ss.getSheets(); var sheetsData = {}; for (var i = 0; i < sheets.length; i++) { var sheet = sheets[i]; var rowsData = getRowsData_(sheet, getExportOptions(e)); var sheetName = sheet.getName(); sheetsData[sheetName] = rowsData; } var json = makeJSON_(sheetsData, getExportOptions(e)); displayText_(json); } function exportSheet(e) { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet(); var rowsData = getRowsData_(sheet, getExportOptions(e)); var json = makeJSON_(rowsData, getExportOptions(e)); displayText_(json); } function getExportOptions(e) { var options = {}; options.language = e && e.parameter.language || DEFAULT_LANGUAGE; options.format = e && e.parameter.format || DEFAULT_FORMAT; options.structure = e && e.parameter.structure || DEFAULT_STRUCTURE; var cache = CacheService.getPublicCache(); cache.put('language', options.language); cache.put('format', options.format); cache.put('structure', options.structure); Logger.log(options); return options; } function makeJSON_(object, options) { if (options.format == FORMAT_PRETTY) { var jsonString = JSON.stringify(object, null, 4); } else if (options.format == FORMAT_MULTILINE) { var jsonString = Utilities.jsonStringify(object); jsonString = jsonString.replace(/},/gi, '},\n'); jsonString = prettyJSON.replace(/":${"/gi, '":\n[{"'); jsonString = prettyJSON.replace(/}$,/gi, '}],\n'); } else { var jsonString = Utilities.jsonStringify(object); } if (options.language == LANGUAGE_PYTHON) { // add unicode markers jsonString = jsonString.replace(/"([a-zA-Z]*)":\s+"/gi, '"\$1": u"'); } return jsonString; } function displayText_(text) { var output = HtmlService.createHtmlOutput("<textarea style='width:100%;' rows='20'>" + text + "</textarea>"); output.setWidth(400) output.setHeight(300); SpreadsheetApp.getUi() .showModalDialog(output, 'Exported JSON'); } function getRowsData_(sheet, options) { var headersRange = sheet.getRange(1, 1, sheet.getFrozenRows(), sheet.getMaxColumns()); var headers = headersRange.getValues()[0]; var dataRange = sheet.getRange(sheet.getFrozenRows()+1, 1, sheet.getMaxRows(), sheet.getMaxColumns()); var objects = getObjects_(dataRange.getValues(), normalizeHeaders_(headers)); if (options.structure == STRUCTURE_HASH) { var objectsById = {}; objects.forEach(function(object) { objectsById[object.id] = object; }); return objectsById; } else { return objects; } } function getColumnsData_(sheet, range, rowHeadersColumnIndex) { rowHeadersColumnIndex = rowHeadersColumnIndex || range.getColumnIndex() - 1; var headersTmp = sheet.getRange(range.getRow(), rowHeadersColumnIndex, range.getNumRows(), 1).getValues(); var headers = normalizeHeaders_(arrayTranspose_(headersTmp)[0]); return getObjects(arrayTranspose_(range.getValues()), headers); } function getObjects_(data, keys) { var objects = []; for (var i = 0; i < data.length; ++i) { var object = {}; var hasData = false; for (var j = 0; j < data[i].length; ++j) { var cellData = data[i][j]; if (isCellEmpty_(cellData)) { continue; } // Check if the cell contains a comma-separated string if (typeof cellData === 'string' && cellData.includes(',')) { // Split the string into an array and trim whitespace object[keys[j]] = cellData.split(',').map(function(item) { return item.trim(); }); } else { object[keys[j]] = cellData; } hasData = true; } if (hasData) { objects.push(object); } } return objects; } function normalizeHeaders_(headers) { var keys = []; for (var i = 0; i < headers.length; ++i) { var key = normalizeHeader_(headers[i]); if (key.length > 0) { keys.push(key); } } return keys; } function normalizeHeader_(header) { var key = ""; var upperCase = false; for (var i = 0; i < header.length; ++i) { var letter = header[i]; if (letter == " " && key.length > 0) { upperCase = true; continue; } if (!isAlnum_(letter)) { continue; } if (key.length == 0 && isDigit_(letter)) { continue; // first character must be a letter } if (upperCase) { upperCase = false; key += letter.toUpperCase(); } else { key += letter.toLowerCase(); } } return key; } function isCellEmpty_(cellData) { return typeof(cellData) == "string" && cellData == ""; } function isAlnum_(char) { return char >= 'A' && char <= 'Z' || char >= 'a' && char <= 'z' || isDigit_(char); } function isDigit_(char) { return char >= '0' && char <= '9'; } function arrayTranspose_(data) { if (data.length == 0 || data[0].length == 0) { return null; } var ret = []; for (var i = 0; i < data[0].length; ++i) { ret.push([]); } for (var i = 0; i < data.length; ++i) { for (var j = 0; j < data[i].length; ++j) { ret[j][i] = data[i][j]; } } return ret; }

3. Вставьте указанный выше код в редактор.
4. Нажмите Файл > Сохранить и дайте проекту имя, например, «Конвертер CSV в JSON».

Шаг 4: Настройте сценарий (необязательно)

Вы можете настроить скрипт, изменив значения по умолчанию:

var DEFAULT_FORMAT = FORMAT_PRETTY; var DEFAULT_LANGUAGE = LANGUAGE_JS; var DEFAULT_STRUCTURE = STRUCTURE_LIST;

• FORMAT_PRETTY: выводит отформатированный JSON с отступами
• LANGUAGE_JS: выводит стандартный JSON (используйте LANGUAGE_PYTHON для вывода, совместимого с Python)
• STRUCTURE_LIST: выводит данные в виде массива объектов (используйте STRUCTURE_HASH для объектов с ключами ID)

Шаг 5. Запустите сценарий

1. Закройте вкладку редактора скриптов и вернитесь в свой Google Лист.
2. Обновите страницу. Теперь вы должны увидеть новый пункт меню под названием «Экспорт JSON».
3. Нажмите «Экспорт JSON» и выберите «Экспорт JSON для этого листа» или «Экспорт JSON для всех листов».

Шаг 6: Просмотр и копирование выходных данных JSON

1. Появится диалоговое окно с вашими данными в формате JSON.
2. Проверьте вывод, чтобы убедиться в его правильности.
3. Скопируйте данные JSON для использования в вашем проекте.

Расширенное использование:

1. Обработка значений, разделенных запятыми: Скрипт автоматически преобразует разделенные запятыми значения в ячейках в массивы JSON. Например, ячейка, содержащая «красный, зеленый, синий», станет [«красный», «зеленый», «синий»] в выводе JSON.
2. Экспорт нескольких листов: Если вы выберете «Экспортировать JSON для всех листов», скрипт создаст объект JSON, где каждый ключ — это имя листа, а значение — данные из этого листа.
3. Пользовательское форматирование: Скрипт предлагает три варианта форматирования: однострочный, многострочный и симпатичный. Вы можете изменить функцию makeJSON_, чтобы настроить формат вывода еще больше.

Поиск и устранение неисправностей:

1. Если меню «Экспорт JSON» не отображается, попробуйте обновить страницу или повторно открыть электронную таблицу.
2. Убедитесь, что в первой строке вашего листа есть заголовки; в противном случае скрипт может работать некорректно.
3. Если экспорт не удался, проверьте наличие сообщений об ошибках в журналах редактора скриптов (Вид > Журналы).
4. Используйте бесплатный онлайн-инструмент, например JSONLint для проверки вашего JSON-кода.

Лучшие практики

1. Всегда резервное копирование данных перед запуском скриптов в электронных таблицах.
2. Сначала протестируйте скрипт на копии ваших данных, особенно при работе с большими наборами данных.
3. Быть внимательным к Googleквоты и ограничения для Apps Script, особенно для больших электронных таблиц.

Итоги

Это мощное Google Apps Script позволяет вам легко конвертировать ваши Google Таблицы CSV-данных в формате JSON, что упрощает использование ваших данных в различных приложениях и средах программирования. Следуя этому руководству, вы сможете оптимизировать процесс преобразования данных и интегрировать ваши Google Более эффективное взаимодействие данных с другими системами.