ºÇ½ª¹¹¿·¡§ orika_ex_miyako 2024ǯ01·î02Æü(²Ð) 14:43:21ÍúÎò
¡¡¥¹¥×¥ì¥Ã¥É¥·¡¼¥ÈÆâ¤Ë¥á¥Ë¥å¡¼¤òÄɲä·¡¢»ØÄꤷ¤¿¥¿¥¤¥×¤Î Json ¥Õ¥¡¥¤¥ë¤ò½ñ¤½Ð¤¹¤³¤È¤¬½ÐÍè¤Þ¤¹¡£
¤³¤³¤Ç¤Ï£²¤Ä¤Î¥¿¥¤¥×¤ËÂбþ¤·¤Æ¤¤¤Þ¤¹¡£
¡ã¥á¥Ë¥å¡¼¡ä
¡ãÄ̾ï¤ÎJson¡ä
¡ãÆâÉô¤ÎÇÛÎó¤ò»ý¤Ä Json¡ä
¡¡Json ·Á¼°¤Î½ÐÎϤò¹Ô¤¤¤¿¤¤¥¹¥×¥ì¥Ã¥É¥·¡¼¥È¤ò³«¤¡¢¡Ö³ÈÄ¥µ¡Ç½ ¢ª Apps Script¡×¤òÁªÂò¤·¤Þ¤¹¡£
Apps Script ¤¬¿·¤·¤¤¥¿¥Ö¤ä¥¦¥¤¥ó¥É¥¦¤Ç³«¤¤Þ¤¹¤Î¤Ç¡¢¡Ö¡Ü¡×¥Ü¥¿¥ó¤ò²¡¤·¤Æ¡¢¥¹¥¯¥ê¥×¥È¤òºîÀ®¤·¤Þ¤¹¡£
¡¡
dialogJson.gs
function generateJson() { const jsonKey = 'data'; // json¤Îkey̾ // ¥Ç¡¼¥¿¤Î¼èÆÀ const sheet = SpreadsheetApp.getActiveSpreadsheet(); //.getSheetByName('¥·¡¼¥È̾'); const lastRow = sheet.getLastRow(); const lastColum = sheet.getLastColumn(); const keys = sheet.getSheetValues(1, 1, 1, lastColum).flat(); const values = sheet.getSheetValues(2, 1, (lastRow-1), lastColum); // ¥Ç¡¼¥¿¤ÎÀ°·Á const data = values.map(currentValue => { return keys.reduce((obj, currentKey, index) => { obj[currentKey] = currentValue[index]; return obj; }, {}); }); const json = JSON.stringify(data); return json; } function onOpen() { const ui = SpreadsheetApp.getUi(); ui.createMenu('json') .addItem('json·Á¼°¤Ç¥À¥¦¥ó¥í¡¼¥É', 'downloadDialog') .addToUi(); }; function downloadDialog() { const html = HtmlService.createTemplateFromFile("dialog").evaluate(); SpreadsheetApp.getUi().showModalDialog(html, 'json·Á¼°¤Ç¥À¥¦¥ó¥í¡¼¥É'); }
¡¡Â³¤¤¤Æ¡Ö¡Ü¡×¥Ü¥¿¥ó¤ò²¡¤·¡¢HTML ¤òÁªÂò¤·¤Æ¥Õ¥¡¥¤¥ë¤òºîÀ®¤·¤Þ¤¹¡£
dialog.html
<!DOCTYPE html> <html> <head> <base target="_top"> </head> <body> <button onclick="dl()">¥À¥¦¥ó¥í¡¼¥É¤¬»Ï¤Þ¤é¤Ê¤¤¾ì¹ç¤Ï¥¯¥ê¥Ã¥¯</button> <script type='text/javascript'> function dl() { const json = <?= generateJson(); ?>; const blob = new Blob([json], { "type" : "application/json" }); const link = document.createElement('a'); link.href = window.URL.createObjectURL(blob); link.download = 'data.json'; link.click(); } dl(); </script> </body> </html>
¡¡¤â¤¦£±¤Ä¤Î½ÐÎÏÊý¼°¤òºîÀ®¤·¤Þ¤¹¡£
¡Ö¡Ü¡×¥Ü¥¿¥ó¤ò²¡¤·¤Æ¥¹¥¯¥ê¥×¥È¤òºîÀ®¤·¤Þ¤¹¡£
exportToJson.gs
function exportToJson() { // ¥·¡¼¥È̾¤ò»ØÄê const sheet = SpreadsheetApp.getActiveSpreadsheet(); //.getSheetByName('¥·¡¼¥È14'); // ¥·¡¼¥È̾¤ÏŬÀÚ¤ËÊѹ¹ // ¥Ç¡¼¥¿¤ò¼èÆÀ const data = sheet.getDataRange().getValues(); // ¥Ø¥Ã¥À¡¼¹Ô¤ò¼èÆÀ const headers = data[0]; // ¥Ç¡¼¥¿¤òJSON·Á¼°¤ËÊÑ´¹ const jsonData = []; for (let i = 1; i < data.length; i++) { const row = data[i]; const obj = {}; obj['tag'] = row[0]; // 'jp', 'kr', 'en' ¤ÎÎó¤òÇÛÎó¤Ë¤Þ¤È¤á¤ë obj['languageDatas'] = [row[1], row[2], row[3]]; jsonData.push(obj); } // JSON¥Ç¡¼¥¿¤ò½ÐÎÏ const jsonOutput = JSON.stringify(jsonData, null, 2); // HtmlService¤Î¥À¥¤¥¢¥í¥°¤òÊĤ¸¤ë¤¿¤á¤Ë½ÐÎÏ return jsonOutput; } function onOpen() { const ui = SpreadsheetApp.getUi(); ui.createMenu('Custom Menu') .addItem('JSON·Á¼°¤Ç¥À¥¦¥ó¥í¡¼¥É', 'downloadDialog') .addToUi(); } function downloadDialog() { const html = HtmlService.createTemplateFromFile('combinedDialog').evaluate(); SpreadsheetApp.getUi().showModalDialog(html, 'JSON·Á¼°¤Ç¥À¥¦¥ó¥í¡¼¥É'); } function getSheetName() { const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); return sheet.getName(); }
¡¡downloadDialog() ´Ø¿ôÆâ¤Î°ú¿ô¤Î»ØÄê¤òÊѹ¹¤·¤Þ¤¹¡£
¡¡¤Þ¤¿¿·¤·¤¯ getSheetName() ´Ø¿ô¤ò¤³¤Á¤é¤Ë¤âÄɲä·¤Þ¤¹¡£
function downloadDialog() { const html = HtmlService.createTemplateFromFile('combinedDialog').evaluate(); SpreadsheetApp.getUi().showModalDialog(html, 'JSON·Á¼°¤Ç¥À¥¦¥ó¥í¡¼¥É'); } function getSheetName() { const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); return sheet.getName(); }
¡¡¥¹¥×¥ì¥Ã¥É¥·¡¼¥È¤Ë¤Ï£±¤Ä¤Î HTML ¥Õ¥¡¥¤¥ë¤·¤«ÀßÄê¤Ç¤¤Ê¤¤¤¿¤á¡¢
Àè¤Û¤ÉºîÀ®¤·¤¿Ä̾ï¤Î Json ¥Õ¥¡¥¤¥ë¤Î¥À¥¦¥ó¥í¡¼¥Éµ¡Ç½¤Ë¡¢º£²ó¤Î°ìÉô¤òÇÛÎ󲽤·¤Æ Json ¥Õ¥¡¥¤¥ë¤È¤·¤Æ¥À¥¦¥ó¥í¡¼¥Éµ¡Ç½¤òÄɲä·¤Þ¤¹¡£
¡¡
¡¡¤Þ¤¿ºîÀ®¤µ¤ì¤ë Json ¥Õ¥¡¥¤¥ë¤Î̾¾Î¤âưŪ¤Ë¥·¡¼¥È̾¤ËÀßÄꤵ¤ì¤ë¤è¤¦¤ËÊѹ¹¤â¤·¤Æ¤¤¤Þ¤¹¡£
¡¡¤½¤Î¤¿¤á¡¢HTML ¤Î̾¾Î¤âÊѹ¹¤·¤Þ¤·¤ç¤¦¡£
combinedDialog.html
<!-- combinedDialog.html --> <!DOCTYPE html> <html> <head> <base target="_top"> </head> <body> <button onclick="exportJson()">JSON·Á¼°(°ìÉôÇÛÎó)¤Ç¥À¥¦¥ó¥í¡¼¥É</button> <button onclick="dl()">Ä̾ï¤ÎJSON·Á¼°¤Ç¥À¥¦¥ó¥í¡¼¥É</button> <script type='text/javascript'> function getSheetName() { return google.script.run.getSheetName(); } function exportJson() { google.script.run.withSuccessHandler(function(sheetName) { const json = <?= exportToJson(); ?>; const blob = new Blob([json], { type: 'application/json' }); const link = document.createElement('a'); link.href = window.URL.createObjectURL(blob); link.download = `${sheetName}.json`; // ¥·¡¼¥È̾¤ò¥Õ¥¡¥¤¥ë̾¤ËÀßÄê link.click(); }).getSheetName(); } function dl() { google.script.run.withSuccessHandler(function(sheetName) { const json = <?= generateJson(); ?>; const blob = new Blob([json], { type: 'application/json' }); const link = document.createElement('a'); link.href = window.URL.createObjectURL(blob); link.download = `${sheetName}.json`; // ¥·¡¼¥È̾¤ò¥Õ¥¡¥¤¥ë̾¤ËÀßÄê link.click(); }).getSheetName(); } </script> </body> </html>
¡¡³Æ¥Õ¥¡¥¤¥ë¤òÊݸ¸å¡¢¥¹¥×¥ì¥Ã¥É¥·¡¼¥È¤ÎºÆÆɤ߹þ¤ß¤ò¹Ô¤Ã¤Æ¤¯¤À¤µ¤¤¡£
¾åÉô¤Î¥á¥Ë¥å¡¼¤Ë Json ¤òÁªÂò¤¹¤ë¤È¡¢¥¦¥¤¥ó¥É¥¦Æâ¤Ë¿·¤·¤¤¥á¥Ë¥å¡¼¤¬Äɲ䵤ì¤Æ¤¤¤Þ¤¹¡£
¡ã¥á¥Ë¥å¡¼¡ä
¡¡Î¾Êý¤È¤â¼Â¹Ô¤·¤Æ¡¢¤½¤ì¤¾¤ì¤Î Json ·Á¼°¤Ç¤Î½ÐÎϤ¬¹Ô¤ï¤ì¤ë¤³¤È¤ò³Îǧ¤·¤Æ¤ß¤Æ¤¯¤À¤µ¤¤¡£
¡ãÄ̾ï¤ÎJson¡ä
¡ãÆâÉô¤ÎÇÛÎó¤ò»ý¤Ä Json¡ä
¡¡°Ê¾å¤Ç´°À®¤Ç¤¹¡£
- ¥«¥Æ¥´¥ê¡§
- ¿Ê³Ø/¥¹¥¯¡¼¥ë
- ¥×¥í¥°¥é¥ß¥ó¥°
¥³¥á¥ó¥È¤ò¤«¤¯