Cómo convertir Google Hojas de cálculo CSV a JSON mediante un script de aplicaciones

Google Sheets es una herramienta poderosa para almacenar y manipular datos, pero a veces necesitas que tus datos estén en un formato diferente, como JSON, para usarlos en aplicaciones web o análisis de datos. En esta guía, te guiaremos a través del proceso de uso de una hoja de cálculo personalizada. Google Apps Script para convertir tu Google Hojas de cálculo con formato CSV a JSON.

Lo que necesitarás:

• A Google .
• Conocimiento básico de Google hojas
• Sus datos en un Google Hojas de cálculo

Paso 1: Prepare su Google Datos CSV de la hoja

1. Abra su Google Hoja que contiene los datos que desea convertir.
2. Asegúrese de que sus datos estén bien estructurados con encabezados en la primera fila.
3. Congelar la primera fila de encabezado (haga clic en ver > Congelar > 1 fila).
4. Asegúrese de que no haya columnas vacías entre sus datos.

Paso 2: Abra el editor de scripts de aplicaciones

1. En su Google Hoja, haga clic en “Herramientas” en el menú superior.
2. Seleccione “Editor de scripts” en el menú desplegable.
3. Se abrirá una nueva pestaña con el Google Editor de aplicaciones Script.

Paso 3: Copiar y pegar el script

1. Elimina cualquier código existente en el editor de scripts.
2. Copie el script completo que se proporciona a continuación.

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. Pegue el código anterior en el editor.
4. Haga clic en Archivo > Guardar y asigne un nombre a su proyecto, por ejemplo, “Convertidor de CSV a JSON”.

Paso 4: Personaliza el script (opcional)

Puede personalizar el script modificando los valores predeterminados:

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

• FORMAT_PRETTY: Genera salida JSON con formato y sangría
• LANGUAGE_JS: genera JSON estándar (use LANGUAGE_PYTHON para obtener una salida compatible con Python)
• STRUCTURE_LIST: genera datos como una matriz de objetos (use STRUCTURE_HASH para objetos con claves de identificación)

Paso 5: ejecute el script

1. Cierre la pestaña del editor de scripts y vuelva a su Google Hoja.
2. Actualice la página. Ahora debería ver un nuevo elemento de menú llamado “Exportar JSON”.
3. Haga clic en “Exportar JSON” y elija “Exportar JSON para esta hoja” o “Exportar JSON para todas las hojas”.

Paso 6: Ver y copiar la salida JSON

1. Aparecerá un cuadro de diálogo con sus datos en formato JSON.
2. Revise la salida para asegurarse de que sea correcta.
3. Copie los datos JSON para usarlos en su proyecto.

Uso avanzado:

1. Manejo de valores separados por comas:El script convierte automáticamente los valores separados por comas de las celdas en matrices JSON. Por ejemplo, una celda que contenga “rojo, verde, azul” se convertirá en [“rojo”, “verde”, “azul”] en la salida JSON.
2. Exportación de varias hojas:Si elige “Exportar JSON para todas las hojas”, el script creará un objeto JSON donde cada clave es un nombre de hoja y el valor son los datos de esa hoja.
3. Formateo personalizado:El script ofrece tres opciones de formato: Una línea, Varias líneas y Formato. Puedes modificar la función makeJSON_ para personalizar aún más el formato de salida.

Solución de Problemas:

1. Si no aparece el menú “Exportar JSON”, intente actualizar la página o volver a abrir la hoja de cálculo.
2. Asegúrese de que su hoja tenga encabezados en la primera fila; de lo contrario, el script podría no funcionar correctamente.
3. Verifique si hay mensajes de error en los registros del editor de scripts (Ver > Registros) si falla la exportación.
4. Utilice una herramienta gratuita en línea como JSONLint para validar su código JSON.

BUENAS PRÁCTICAS

1. Siempre Haga una copia de seguridad de sus datos antes de ejecutar scripts en sus hojas de cálculo.
2. Primero pruebe el script en una copia de sus datos, especialmente cuando trabaje con conjuntos de datos grandes.
3. Ser consciente de GoogleCuotas y limitaciones de 's para Apps Script, especialmente para hojas de cálculo grandes.

Envolver

Esta potente Google Apps Script te permite convertir fácilmente tu Google Convierta los datos CSV de Sheets en formato JSON, lo que facilita el uso de sus datos en diversas aplicaciones y entornos de programación. Si sigue esta guía, podrá optimizar el proceso de conversión de datos e integrar sus datos en su entorno de programación. Google Hojas de datos con otros sistemas de forma más eficiente.