Come convertire Google Fogli CSV in JSON utilizzando uno script di app

Google Sheets è uno strumento potente per archiviare e manipolare dati, ma a volte hai bisogno di dati in un formato diverso, come JSON, per l'uso in applicazioni web o analisi dati. In questa guida, ti guideremo attraverso il processo di utilizzo di un file personalizzato Google App Script per convertire il tuo Google Fogli in formato CSV in JSON.

Di cosa avrai bisogno:

• A Google account
• Conoscenza di base con Google Lenzuola
• I tuoi dati in un Google Fogli di calcolo

Passaggio 1: prepara il tuo Google Dati CSV del foglio

1. Apri il tuo Google Foglio contenente i dati che si desidera convertire.
2. Assicurati che i tuoi dati siano ben strutturati, con le intestazioni nella prima riga.
3. Blocca la prima riga dell'intestazione (clicca su Visualizza > Blocca > 1 riga).
4. Assicurati che non ci siano colonne vuote tra i tuoi dati.

Passaggio 2: aprire l'editor di script delle app

1. nella vostra Google Foglio, clicca su “Strumenti” nel menu in alto.
2. Selezionare “Editor di script” dal menu a discesa.
3. Si aprirà una nuova scheda con il Google Editor di script per app.

Passaggio 3: Copia e incolla lo script

1. Eliminare qualsiasi codice esistente nell'editor di script.
2. Copiare l'intero script fornito qui sotto.

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. Incolla il codice sopra nell'editor.
4. Fare clic su File > Salva e assegnare un nome al progetto, ad esempio "Convertitore da CSV a JSON".

Passaggio 4: personalizza lo script (facoltativo)

È possibile personalizzare lo script modificando i valori predefiniti:

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

• FORMAT_PRETTY: restituisce un JSON formattato con indentazione
• LANGUAGE_JS: genera JSON standard (utilizza LANGUAGE_PYTHON per output compatibili con Python)
• STRUCTURE_LIST: restituisce i dati come un array di oggetti (utilizzare STRUCTURE_HASH per gli oggetti con chiavi ID)

Passaggio 5: esegui lo script

1. Chiudi la scheda dell'editor di script e torna al tuo Google Foglio.
2. Aggiorna la pagina. Ora dovresti vedere una nuova voce di menu chiamata "Esporta JSON".
3. Fare clic su "Esporta JSON" e scegliere "Esporta JSON per questo foglio" o "Esporta JSON per tutti i fogli".

Passaggio 6: visualizzare e copiare l'output JSON

1. Apparirà una finestra di dialogo con i tuoi dati in formato JSON.
2. Controllare l'output per assicurarsi che sia corretto.
3. Copia i dati JSON da utilizzare nel tuo progetto.

Utilizzo avanzato:

1. Gestione dei valori separati da virgole: Lo script converte automaticamente i valori separati da virgole nelle celle in array JSON. Ad esempio, una cella contenente "rosso, verde, blu" diventerà ["rosso", "verde", "blu"] nell'output JSON.
2. Esportazione di più fogli: Se si sceglie "Esporta JSON per tutti i fogli", lo script creerà un oggetto JSON in cui ogni chiave è un nome di foglio e il valore sono i dati di quel foglio.
3. Formattazione personalizzata: Lo script offre tre opzioni di formattazione: One-line, Multi-line e Pretty. Puoi modificare la funzione makeJSON_ per personalizzare ulteriormente il formato di output.

Risoluzione dei problemi:

1. Se il menu "Esporta JSON" non viene visualizzato, prova ad aggiornare la pagina o a riaprire il foglio di calcolo.
2. Assicurati che il foglio abbia le intestazioni nella prima riga; in caso contrario, lo script potrebbe non funzionare correttamente.
3. Se l'esportazione non riesce, controllare eventuali messaggi di errore nei registri dell'editor di script (Visualizza > Registri).
4. Utilizza uno strumento online gratuito come JSONLint per convalidare il tuo codice JSON.

Best Practices

1. Sempre eseguire il backup dei dati prima di eseguire gli script sui fogli di calcolo.
2. Testare prima lo script su una copia dei dati, soprattutto se si lavora con set di dati di grandi dimensioni.
3. Sii consapevole di Googlequote e limitazioni per Apps Script, in particolare per fogli di calcolo di grandi dimensioni.

Incartare

Questo potente Google Apps Script ti consente di convertire facilmente il tuo Google Fogli dati CSV in formato JSON, semplificando l'utilizzo dei dati in varie applicazioni e ambienti di programmazione. Seguendo questa guida, puoi semplificare il processo di conversione dei dati e integrare il tuo Google Fogli di dati con altri sistemi in modo più efficiente.