如何转换 Google 使用 Apps 脚本将 Sheets CSV 转换为 JSON

Google Sheets 是一款功能强大的数据存储和处理工具，但有时您需要以不同的格式（如 JSON）存储数据，以便在 Web 应用程序或数据分析中使用。在本指南中，我们将引导您完成使用自定义 Google Apps 脚本可将您的 Google 工作表 CSV 转为 JSON 格式。

你需要什么：

• A Google 帐户
• 基本熟悉 Google 表
• 您的数据 Google Sheets 电子表格

第 1 步：准备您的 Google 工作表的 CSV 数据

1. 打开你的 Google 包含要转换的数据的工作表。
2. 确保您的数据结构良好，并且第一行有标题。
3. 冻结第一个标题行（单击视图 > 冻结 > 1 行）。
4. 确保数据之间没有空列。

第 2 步：打开 Apps 脚本编辑器

1. 在您的 Google Sheet，点击顶部菜单中的“工具”。
2. 从下拉菜单中选择“脚本编辑器”。
3. 将打开一个新选项卡，其中包含 Google Apps 脚本编辑器。

步骤 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：将数据输出为对象数组（对于带有 ID 键的对象使用 STRUCTURE_HASH）

第 5 步：运行脚本

1. 关闭脚本编辑器选项卡并返回到 Google 片。
2. 刷新页面。您现在应该会看到一个名为“导出 JSON”的新菜单项。
3. 点击“导出 JSON”，然后选择“导出此工作表的 JSON”或“导出所有工作表的 JSON”。

步骤 6：查看并复制 JSON 输出

1. 包含您的数据的对话框将以 JSON 格式出现。
2. 检查输出以确保其正确无误。
3. 复制 JSON 数据以供在您的项目中使用。

高级用法：

1. 处理逗号分隔值：脚本会自动将单元格中逗号分隔的值转换为 JSON 数组。例如，包含“red, green, blue”的单元格在 JSON 输出中将变为 [“red”, “green”, “blue”]。
2. 导出多张工作表：如果选择“为所有工作表导出 JSON”，脚本将创建一个 JSON 对象，其中每个键都是工作表名称，值是该工作表中的数据。
3. 自定义格式：该脚本提供三种格式选项：单行、多行和 Pretty。您可以修改 makeJSON_ 函数以进一步自定义输出格式。

故障排除：

1. 如果没有出现“导出 JSON”菜单，请尝试刷新页面或重新打开电子表格。
2. 确保您的工作表的第一行有标题；否则，脚本可能无法正常工作。
3. 如果导出失败，请检查脚本编辑器日志（查看 > 日志）中是否有任何错误消息。
4. 使用免费在线工具，例如 JSONLint 验证您的 JSON 代码。

最佳实践

1. 总是 备份您的数据 在电子表格上运行脚本之前。
2. 首先在数据副本上测试脚本，尤其是在处理大型数据集时。
3. 注意 GoogleApps Script 的配额和限制，尤其是对于大型电子表格。

包起来

这种强大 Google Apps 脚本可让您轻松转换 Google 表格 CSV 数据转换为 JSON 格式，使您可以轻松地在各种应用程序和编程环境中使用数据。通过遵循本指南，您可以简化数据转换过程并集成您的 Google 更高效地与其他系统共享表格数据。