วิธีการแปลง Google แปลงแผ่นงาน CSV เป็น JSON โดยใช้สคริปต์ Apps

Google Sheets เป็นเครื่องมือที่มีประสิทธิภาพสำหรับการจัดเก็บและจัดการข้อมูล แต่บางครั้งคุณอาจต้องการข้อมูลในรูปแบบอื่น เช่น 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 to JSON Converter"

ขั้นตอนที่ 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 แผ่นข้อมูลเชื่อมต่อกับระบบอื่นได้อย่างมีประสิทธิภาพมากขึ้น