Google Apps Scriptã§ã¬ã³ããã£ãŒããäœããGASã
äž»ã«ã·ã¹ãã é¢ä¿è ã建èšé¢ä¿è ã®éã§ãã䜿ãããŠãå·¥çšç®¡çã®çºã®ãã£ãŒãã«ãã¬ã³ããã£ãŒããããã åçŽã«ã¿ã€ã ã©ã€ã³ã衚瀺ããŠãããã§ã¯ãªããã¿ã¹ã¯ãšã¿ã¹ã¯ã®é¢ä¿æ§ãæèããŠäœãããŠããã®ãã£ãŒãã§ãããé ã®äžã§ã ãã§ãã¡ããã¡ããšã¹ã±ãžã¥ãŒã«ç¢ºèªãã¿ã¹ã¯ç®¡çããããããã£ãšå¹æçãªãã£ãŒãã§ããèŠããåã®ä»£è¡šäŸãšãèšããŸãã
Aã¿ã¹ã¯ãçµãããªããšå®è¡ã§ããªãBã¿ã¹ã¯ãšãã£ãããã«ãä»äºã®ãããŒã衚瀺ããŠãããŸããä»åã¯ãã®ã¬ã³ããã£ãŒããã¹ãã¬ããã·ãŒã飿ºã§è¡šç€ºã§ããããã«ããã¹ã¯ãªãããäœã£ãŠã¿ãŸããããããŸã§ã®ãã£ãŒãç³»ã®ã¹ã¯ãªãããšå€§ããªéãã¯ãªãã®ã§ãããæ³šæç¹ãããã€ããããŸãã
ç®æ¬¡
䜿çšããè³æã»ã¡ãœããé¡
å®è¡ãšçµæ
ä»å䜿çšããã¹ãã¬ããã·ãŒãã®ã¡ãã¥ãŒãããâ¶ã«ã¬ã³ããŒãâãèºã®æ œå¹ãã§ãçŸåšè¡šç€ºãããŠãã¹ãã¬ããã·ãŒãããŒã¿ãã¬ã³ããã£ãŒãã«ããŠãããŸãã
å³ïŒèºã®æ œå¹ã¹ã±ãžã¥ãŒã«ãã¬ã³ããã£ãŒãå
泚æç¹ãšè§£èª¬
ã¬ã³ããã£ãŒããäœãããã°ã©ã èªäœã¯ããã»ã©é£ãããã®ã§ã¯ãããŸãããããããã¹ãã¬ããã·ãŒããèŠãŠé ããã°ããããŸãããå ¥åã«ãŒã«ãå°ãå€åçã«ãªã£ãŠããŸãããŸããã¹ã¯ãªããã«æŒããŠãã¡ãã£ãšã ãç¹æ®ãªåŠçãå ¥ã£ãŠããŸãã®ã§ãããã§ã¯ãããã®æ³šæç¹ããã³ãã£ãŒãã®è§£èª¬ãããŠã¿ãããšæããŸãã
泚æç¹
ã¹ãã¬ããã·ãŒãã®æ³šæç¹
å€åçãªå ¥åã«ãªã£ãŠããŸããã¿ã¹ã¯IDãšã¿ã¹ã¯åãŸã§ã¯æ®éã§ãããããããå ãç°ãªããŸãã
- åºæ¬çã«éå§æ¥ä»ãå ¥ããã®ã¯èŠªã¿ã¹ã¯ã§ãã
- çµäºæ¥ä»ã¯å šãŠã®ã¿ã¹ã¯ã«å ¥åãå¿ èŠã§ãã
- ã¿ã¹ã¯IDã¯éè€ããªãåç§°ãæ°å€ãå ¥ããå¿ èŠæ§ããããŸãã
- åºæ¬çã«æ¥æ°ãå ¥ããã®ã¯åã¿ã¹ã¯ã§ããããã¯çµäºæ¥ããèŠãŠäœæ¥åãŸã§ã®æéãšããæå³ã§èãããšããããããã§ãã1ã¶æåãŸã§ãªãã30ãå ¥åããæãã§ãã
- 鲿çã¯ãã®ãŸããŸçŽæ¥æ°å€ã§å ¥åããŸããæå€§å€ã¯100ã§ãã
- 芪IDã¯ãã¶ãäžãã芪ã¿ã¹ã¯ã®ã¿ã¹ã¯IDãå ¥åããŸãããã£ãŠãåã¿ã¹ã¯ã«ããå ¥åããŸãããå°ã芪IDã«ã¯ã«ã³ãåºåãã§è€æ°å ¥åãåºæ¥ãè€æ°ã®å Žåãè€æ°ã®ã¿ã¹ã¯ãå®äºããªããšå®è¡ã§ããªããšãã£ãæå³ã«ãªããŸãã
ãã£ãŒãã®æ³šæç¹
ãã£ãŒããå°ã é£ããç¹ããããŸãããã£ãŒããèŠãæã«ã«ãŒã«ã«åŸã£ãŠããªããã£ãŒãã®å Žåãããããªè¡šç€ºã«ãªã£ããããŸãã®ã§ã泚æããŸãããã
- 芪ã¿ã¹ã¯ãšåã¿ã¹ã¯ãšã®éã«æ¥æ°ã«äœè£ããªãå Žåã«ã¯ãã©ã€ã³ã®è²ãå€ããããã«ä»åãªãã·ã§ã³ãä»ããŠãŸãããããã¯ãªãã£ã«ã«ãã¹ãšèšããŸãã
- åã¿ã¹ã¯ã®éå§æ¥ïŒæ¥æ°ããéç®ïŒãã芪ã¿ã¹ã¯ã®éå§æ¥ããåã«ãªããããªççŸããã±ãŒã¹ã®å Žåãã©ã€ã³ãå£ãããããŸãã
- 芪ã¿ã¹ã¯ãšåã¿ã¹ã¯ãéè€ããå Žåã«ã¯ãæ¥æ°ã®å€ã«æ³šæããŠäžããã
- åã¿ã¹ã¯ãè€æ°ã®èŠªIDã«ã¶ãäžããå Žåã«ã¯ããããïŒã€ã®æ¥ä»ã®é¢ä¿ã«æ³šæãççŸãçããããã§ãã
- 鲿çã§ããŒã®è²ãå€ãããŸãã
ã¹ã¯ãªããã®è§£èª¬
ä»ã®ãã£ãŒãç³»ã®ã¹ã¯ãªãããšæ®ã©å€ãããªãã®ã§ããããã£ãŒãã衚瀺ãããäžã§åµããã€ã³ããããã€ããããŸãã
- GASåŽããéãããŠããããŒã¿ãJSON.parseããŠé åã«æ ŒçŽããŠããããããã®ãŸãŸdata.addRowsã§çªã£èŸŒãäºãåºæ¥ãŸãããHTMLäžã§ïŒã©ã€ã³ãã€é åãçµãã§addRowããªããã°ãªããŸããã
- æ¥æ°ã¯æ¥æ°ã§ã°ã©ããæç»åºæ¥ãŸããããã®ãããdaysToMilliseconds颿°ã§æ¥ãããªã»ã«ã³ãã«å€æããŠãããŠããŸããå¿ é ã®äœæ¥ã§ãã
- å€ã®ãã©ã³ã¯ãå ¥ã£ãŠããšãã£ãŒãã«è¡šç€ºãããŸãããnullå€ã§ãªããã°ãªããªãã®ã§ãå€å®åŒã䜿ã£ãŠãã©ã³ã¯ã®æã¯nullãä»£å ¥ããããã«ããŠããŸãã
- GASåŽã§new Date()ã§æ¥ä»åã«å€æããããŒã¿ããã®ãŸãŸå ¥ããŠãNGã§ããHTMLåŽã®ã¹ã¯ãªããã§new Date()ã§å€æããŠãããªããšåãä»ããŠãããªãããã§ãã
- addColumnã§å®çŸ©ããåãšäžäžèŽã®å Žååœç¶addRowã§ãšã©ãŒãšãªããŸããæç€ºçã«å倿ããŠãããããã«ããŸãããïŒStringãNumberãªã©ïŒã
- ãã€ã¢ãã°ã§äœ¿çšããå Žåã«ã¯ãheightãšwidthãæç€ºçã«optionã§æå®ããŠãããªããšèŠåããããåŠãªæšªã¹ã¯ããŒã«ããŒãåºããããŸãã
ãœãŒã¹ã³ãŒã
GASåŽ
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
//ã¹ãã¬ããã·ãŒãã¡ãã¥ãŒ function onOpen(){ var ui = SpreadsheetApp.getUi(); ui.createMenu('â¶ã«ã¬ã³ããŒ') .addItem('åæå', 'getMySheetId') .addItem('èºã®æ œå¹', 'berryman') .addToUi(); } //èªåèªèº«ã®IDãååŸããã³ãŒã function getMySheetId(){ var sheet = SpreadsheetApp.getActiveSpreadsheet(); var myid = sheet.getId(); var Properties = PropertiesService.getScriptProperties(); Properties.setProperty("sheetid", myid); return myid; } //æ œå¹ã«ã¬ã³ããŒã衚瀺ãã function berryman() { var html = HtmlService.createHtmlOutputFromFile('berry') .setSandboxMode(HtmlService.SandboxMode.IFRAME) .setWidth(800) .setHeight(500); SpreadsheetApp.getUi() .showModalDialog(html, 'èºæ œå¹ã«ã¬ã³ããŒ'); } //ã·ãŒãããŒã¿ãååŸãHTMLåŽãžè¿ã颿° function berrydata(){ //IDãååŸãã var prop = PropertiesService.getScriptProperties(); var ssid = prop.getProperty("sheetid") //ã·ãŒãããŒã¿ãååŸãã var sheet = SpreadsheetApp.openById(ssid); var ss = sheet.getSheetByName("gantt").getRange("A2:G").getValues(); return JSON.stringify(ss); } |
HTMLåŽ
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 |
<html> <head> <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script> <script type="text/javascript"> var dataman; google.charts.load('current', {'packages':['gantt'], 'language': 'ja'}); google.script.run.withSuccessHandler(onSuccess).berrydata(); //æ¥ãããªã»ã«ã³ããžå€æãã颿° function daysToMilliseconds(days) { return days * 24 * 60 * 60 * 1000; } //ã·ãŒãããŒã¿ãæ ŒçŽãã function onSuccess(data){ //ã°ããŒãã«é
åã«æ ŒçŽãã dataman = JSON.parse(data); drawChart(); } //ã¬ã³ããã£ãŒããæç»ãã function drawChart() { //ã«ã©ã ã®èšå® var data = new google.visualization.DataTable(); data.addColumn('string', 'Task ID'); data.addColumn('string', 'Task Name'); data.addColumn('date', 'Start Date'); data.addColumn('date', 'End Date'); data.addColumn('number', 'Duration'); data.addColumn('number', 'Percent Complete'); data.addColumn('string', 'Dependencies'); //ããŒã¿ãã»ãããã var length = dataman.length; var dataArray = []; for(var i = 0;i<length;i++){ var tempArray = []; tempArray.push(String(dataman[i][0])); tempArray.push(String(dataman[i][1])); if(dataman[i][2] == ""){ tempArray.push(null); }else{ tempArray.push(new Date(dataman[i][2])); } if(dataman[i][3] == ""){ tempArray.push(null); }else{ tempArray.push(new Date(dataman[i][3])); } tempArray.push(Number(daysToMilliseconds(dataman[i][4]))); tempArray.push(Number(dataman[i][5])); if(dataman[i][6] == ""){ tempArray.push(null); }else{ tempArray.push(String(dataman[i][6])); } data.addRow(tempArray); } //ã°ã©ããªãã·ã§ã³ var options = { height: 500, }; //æç»å ŽæãååŸ var chart = new google.visualization.Gantt(document.getElementById('chart_div')); //ã¬ã³ããã£ãŒãã衚瀺ãã chart.draw(data, options); } </script> </head> <body> <div id="chart_div" style="width:100%;"></div> </body> </html> |
ãã€ã³ã
- å ¥åã«ãŒã«ãå€åçã§ãã®ã§ãæå ¥åã§ã¯ãªããå€åã«ãŒã«ã«åŸã£ãŠå ¥åããŠãããGUIãå¥éçšæãããšæ Œæ®µã«å ¥åãæ¥œã«ãªããŸãããŸããä»ã®ã·ãŒãããã®ããŒã¿ã倿ããŠèªåçã«ãã£ãŒãåããã®ãé¢çœãã§ããã
- è²ãªã©ã¯optionéšåã§å²ããšèªç±ã«æ§ç¯ããäºãå¯èœã§ãã
- ãã£ãŒãåããæã«ãã¹ãã¬ããã·ãŒãã®ããŒã¿ã®å šãŠãã¬ã³ãåãããŸãã®ã§ãå®å šã«çµãã£ãŠãåã¿ã¹ã¯ãå®äºããŠãé ç®ã«ã€ããŠã¯ããŒã¿ããã£ã«ã¿ããŠãããåŠçããããšãèŠãç®ãåé·ã«ãªããã«æžã¿ãŸãã
- ãã€ã¢ãã°ã ãšèŠãç®ãèŠèŸãã®ã§ããŠã§ãã¢ããªã±ãŒã·ã§ã³ãšããŠå ¬éããŠéçšããã»ãã䟿å©ã§ãã
- çŸåšãã®ãã£ãŒãæ©èœã¯Î²çã§ãã仿§ãå€ããå¯èœæ§ããããŸãããŸããå€ãIEã§ã¯SVGæªå¯Ÿå¿ãªã®ã§è¡šç€ºãåºæ¥ãŸããã
- chart_divã®styleã«width:100%ãæå®ããªããšãåŠã«çž®ãŸã£ãã¬ã³ããã£ãŒãã«ãªãã®ã§æ³šæã
- ãã¡ãã®ãµã€ãã®CSSã«ã¡ãã£ãšæãå ãããšä»¥äžã®ãããªæ ããã¬ã³ããã£ãŒãã«ãªããŸãã