Google Apps Scriptãé«éåãããã¯ããã¯ãŸãšããGASã
Google Apps Scriptã¯å€§å€äŸ¿å©ãªã¹ã¯ãªãããªã®ã§ãããé£ç¶çšŒåæéã«æå€§6åãšããå¶éãããããããè¶ ãããšãèµ·åæéã®æå€§å€ãè¶ ããŸããããšã®ã¡ãã»ãŒãžãåºãŠã¹ã¯ãªããã¯åæã«æ¢ãŸã£ãŠããŸããŸãïŒG Suite Businessã ãš30åïŒããããåé¿ãããã¯ããã¯ãšããŠã¯ã¹ã¯ãªããããªã¬ãŒãå©çšããçªç Žæ¹æ³ãããã®ã§ãããå®è£ ã¯çµæ§å€§å€ã§ãããäœããæ©ãçµããã«è¶ããããšã¯ãããŸãããããããã¹ã¯ãªãããé«éåããçºã«ã¯Google Apps Scriptæµã®æžãæ¹ãšãããã®ãããã®ã§ãä»åããããŸãšããŠã¿ãŸããã
ãªãã¹ãæ®æ®µããæèããŠæžãããã«ãããšãæ©èœè¿œå æã«çãç®ãã¿ãã«æžã¿ãŸãïŒå®éãåçŽãªã·ãŒãããŒã¿ãéããã¹ã¯ãªããã§ãéããã·ãŒããå¢ããŠãã£ãšããéã«ã¹ã¯ãªãããç Žç¶»ããŠå°ã£ãæãåºããããŸãïŒãä»å䜿çšããã¹ãã¬ããã·ãŒãã®ãããŒããŒã¿ã¯Mockarooãå©çšããããŸããã
ç®æ¬¡
- 1 ä»å䜿çšããã¹ãã¬ããã·ãŒã
- 2 APIã®åŒã³åºãåæ°ãæžãã
- 3 ããŒã¿ã®ååŸãšæäœ
- 4 ããŒã¿ã®æžèŸŒãšæ€çŽ¢
- 5 UrlfetchAppã§ãŸãšããŠãªã¯ãšã¹ã
- 6 Sheets API v4ãå©çšãã
- 7 V8 Runtimeãå©çšãã
- 8 HTML ServiceãæŽ»çšãã
- 9 ã·ãŒã颿°çã§åºæ¥ãäºã¯ãªãã¹ããããã
- 9.1 ããŒã¿ã¯é¢æ°ã§éèšããŠãã
- 9.2 è€æ°ã®ã·ãŒãã«ããªã
- 9.3 æ¡ä»¶ä»ãæžåŒèšå®ã掻çšãã
- 9.4 setFormulaãæŽ»çšããã
- 9.5 垳祚é¡ãçæããæã®æ³šæç¹
- 9.6 ã¹ã¯ãªããããªã¬ãŒã§äºãèªååŠçããããŠãã
- 9.7 onEditã§ã¡ãã£ãšããåŠçã¯éæå®è¡ãããŠããŸã
- 10 ãã®ä»ã®ãã€ã³ã
- 11 é¢é£ãªã³ã¯
ä»å䜿çšããã¹ãã¬ããã·ãŒã
APIã®åŒã³åºãåæ°ãæžãã
ããã§ããAPIãšã¯ãSpreadsheetAppã§ãã£ãããDocumentAppã§ãã£ããããã¢ã¬ã§ããããã«ã¶ãäžããgetSheetByNameã§ãã£ãããgetRangeãgetValuesãªã©ãåãã§ããããGoogle Apps Scriptç¹æã®APIã®åŒã³åºãã¯å¯èœãªéãåãäºããããé«éåã§ã¯çã£å ã«ãããããããšã§ããã©ãããŠãããã°ã©ãã³ã°åå¿è æä»£ã«ã¯ããã£ãŠããŸããã¡ãªã®ã§ãããVBAãªã©ã§ã¯ããã»ã©é床äœäžã¯ããªãã®ã§ãããGoogle Apps Scriptã§APIã®åŒã³åºããé »ç¹ã«è¡ããšç©åãé ããªããŸãã
äŸãã°ãSpreadsheetApp.getActiveSpreadsheet().getSheetByName().getRange().getValues()ãšããïŒæããã£ãå Žåãããã ãã§4åAPIãåŒã³åºããŠãäºã«ãªããŸããåŒã³åºãããããªãã¹ãååŸããå€ã䜿ãããã«ããã®ãä»åã®ãã¢ã§ããä»åã¯äºäŸãšããŠãç¹å®ã®IDã«åèŽããã¬ã³ãŒããã·ãŒãããæ¢ãåºãããã®IDã«å«ãŸããŠãäŒæ¥åãè¿ããã®ãäœã£ãŠã¿ãŸãããç¹å®ã®IDã¯äžçªæåŸã®IDã䜿ãäºã«ããŸãããïŒã·ãŒãã®ã¬ã³ãŒãæ°ã¯1500ã§ãããæå¹ãªã¬ã³ãŒãã¯1000ã§ããä»ã¯ç©ºçœè¡ã§ããçºïŒã
â»å®è¡æéã¯æ¯åå€ãããŸãããŸããåãåŠçã®å®è¡ã§ã2床ç®ã®å€ãè¯ããªãã®ã¯ãã£ãã·ã¥ãããŠãçºãšæãããŸãã
â»ãã¹ãå®è¡ã¯ãã¹ãã¬ããã·ãŒãã®ã¡ãã¥ãŒããããã¹ããâãããŒã¿ãã§ãã¯ãâãææªãªäºäŸãããã³ãAPIåŒã³åºããæ¹è¯ããå®è¡ããŸãã
é ãã³ãŒãã®äºäŸ
ãããšãããææªãªã³ãŒãã§æžããŠã¿ãŸãããïŒã€ãã€ã»ã«ã®å€ãååŸããŠã¯æ¬¡ã®ã»ã«ãžãšãã£ããããªå¹çã®æªãæ€çŽ¢ãè¡ã£ãŠããŸãã
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 |
//APIãåŒã³åºããã¹ãã®æªãäºäŸ function badrequestapi(){ //éå§æéãååŸãã var sttime = new Date(); //çµäºæéçšã®å€æ° var edtime = ""; //æ€çŽ¢å€ãæ ŒçŽãã var word = 1000; //ã«ãŠã³ã¿ãçšæãã var cnt = 2; //ã¹ãã¬ããã·ãŒããååŸãã var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Dummy"); //Aåã®å€ãååŸããŠwordãšæ¯èŒããŠäžèŽãããæãã for(var i = 0;i<ss.getLastRow();i++){ //ã»ã«ã®å€ãååŸãã var tempid = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Dummy").getRange("A" + cnt).getValue(); //ã»ã«ã®å€ãšæ€çŽ¢å€ãäžèŽããããã§ã㯠if(tempid == word){ //çµäºæéãååŸãã edtime = new Date(); }else{ cnt = cnt + 1; } } //éå§æéãšçµäºæéã®å·®ãç®åºããïŒç§ã«å€æïŒ var kinoko = (edtime - sttime)/1000; //ãã€ã¢ãã°è¡šç€º var message = "çºèŠãŸã§ã«æãã£ãæéïŒç§ïŒïŒ" + kinoko; SpreadsheetApp.getUi().alert(message); |
å®è¡çµæã¯ã160ç§ã»ã©ãæ¿é ã§ãã
æ¹è¯ããã³ãŒãã®äºäŸ
å°ãæŽçããŠãAPIã®åŒã³åºããæžãããŸãããæ¹è¯ç¹ã¯ä»¥äžã®éãã§ãã
- getLastRowã§æçµè¡ã®å€ãååŸããŠå€æ°ã«å ¥ããŠã«ãŒããåããŠããŸããå€ã¯lengthã«æ ŒçŽããŠããŸããã19è¡ç®ã
- ã«ãŒãå ã§ã®å€ã®ååŸãAPIåŒã³åºãããŠããŸãããå°ãæ¹è¯ãããŸãããã24è¡ç®ã
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 |
//APIãåŒã³åºããã¹ãã®æ¹è¯äºäŸ function goodrequestapi(){ //éå§æéãååŸãã var sttime = new Date(); //çµäºæéçšã®å€æ° var edtime = ""; //æ€çŽ¢å€ãæ ŒçŽãã var word = 1000; //ã«ãŠã³ã¿ãçšæãã var cnt = 2; //ã¹ãã¬ããã·ãŒããååŸãã var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Dummy"); //ã¹ãã¬ããã·ãŒãããŒã¿ã®ã¬ã³ãŒãæ°ãååŸãã var length = ss.getLastRow(); //Aåã®å€ãååŸããŠwordãšæ¯èŒããŠäžèŽãããæãã for(var i = 0;i<length;i++){ //ã»ã«ã®å€ãååŸãã var tempid = ss.getRange("A" + cnt).getValue(); //ã»ã«ã®å€ãšæ€çŽ¢å€ãäžèŽããããã§ã㯠if(tempid == word){ //çµäºæéãååŸãã edtime = new Date(); }else{ cnt = cnt + 1; } } //éå§æéãšçµäºæéã®å·®ãç®åºããïŒç§ã«å€æïŒ var kinoko = (edtime - sttime)/1000; //ãã€ã¢ãã°è¡šç€º var message = "çºèŠãŸã§ã«æãã£ãæéïŒç§ïŒïŒ" + kinoko; SpreadsheetApp.getUi().alert(message); } |
å®è¡çµæã¯ã80ç§ã»ã©ãããã§ããŸã ãŸã é ãã§ãããæ¹è¯ã®äœå°ã¯ãŸã ãŸã ãããŸãã
ããŒã¿ã®ååŸãšæäœ
ããŠããŸãAPIã®åŒã³åºããæžããæ¹æ³ã§è¥å¹²ã¹ããŒãã¢ããããŸããããããããŸã å®çšã¬ãã«ã«è³ã£ãŠããŸãããæ¹è¯ãããšèšã£ãŠããæ¹è¯ã³ãŒãã®å 容ã«ã¯ãŸã ãŸã 課é¡ãæ®ã£ãŠããŸããäž»ã«ãã®èª²é¡ç¹ãäžæ°ã«åæããŠã¿ãããšæããŸãã
â»ãã¹ãå®è¡ã¯ãã¹ãã¬ããã·ãŒãã®ã¡ãã¥ãŒããããã¹ããâãããŒã¿ãã§ãã¯ãâãäžçªè¯ãäºäŸããå®è¡ããŸãã
ã»ã«åäœã§å€ãèªããããªæžãæ¹ã¯è¡ããªã
äž»ã«VBAãªã©ã®ã³ãŒãã§ããèŠãããã®ããããã°ã©ã ãšèšãããã人éã®è¡åãå¿ å®ã«æžããŠããããªãã¯ãçãªãœãŒã¹ã³ãŒãã§ããã€ãŸãã1åã»ã«ã®å€ãèªãã§ãã§ãã¯ããæ¬¡ã®ã»ã«ã«ç§»åããŠãŸãå€ãèªã¿èŸŒãã§ãã§ãã¯ãããããããVBAã®ããã«ããŒã«ã«ã§åããã·ã³ã§ããã°ããã§ãè¯ãã®ã§ãããGoogle Apps Scriptã§ã¯ãã®åºŠã«APIãçºè¡ããªããã°ãªããŸããããã£ãŠããã®éšåã¯ããŒã¿ãšããŠã¬ãããšååŸããŠãé åããŒã¿ããã§ãã¯ããæ¹åŒã«å€ããŠãããå¿ èŠããããŸããäŸãã°ã
1 2 3 4 5 6 7 8 |
//ããŒã¿ãååŸãã var data = ss.getRange("A2:A").getValues(); //ã¹ãã¬ããã·ãŒãããŒã¿ã®ã¬ã³ãŒãæ°ãååŸããïŒé
åã¯0ããå§ãŸãçºïŒ var length = ss.getLastRow() - 1; //é
åããå€ãåãåºã var tempid = data[i][0]; |
ãããªå ·åã«ãªããŸããgetValuesã§ã·ãŒãããŒã¿ãé åã§ååŸåºæ¥ãŸãããŸããA2:Aã®ç¯å²ã§ååŸããŠãã®ã§ãgetLastRowã®å€ãã-1ããŠããŸããé åããå€ãåãåºãæã¯ãforæã«ããiã䜿ãã®ã§ãcntãšããŠå®£èšããŠãã£ãã«ãŠã³ã¿ã¯äžèŠã«ãªããŸãã
forã«ãŒãå ãæé©å
forã«ãŒãå ã«ã課é¡ãæ®ã£ãŠããŸããä»åã®ã·ãŒãã¯ããŒã¿èªäœã¯999è¡ã§æ®ãã¯ãã ã®ç©ºçœè¡ã§ãããã®çºã以äžã®åé¡ç¹ãæ®ã£ãŠãäºã«ãªããŸãã
- 該åœã®ã¬ã³ãŒããèŠã€ããŠãã«ãŒãã¯æåŸã®è¡ãŸã§èŠã«ããããã«ãªã£ãŠãïŒgetLastRowã§ã¯ããŒã¿ã®æå¹ç¯å²ãèªåã§å€æããŠãããã®ã§ã999è¡ã§ååŸåºæ¥ãŸãïŒã
- ãªãããã®æ¡ä»¶ã§ç©ºçœè¡ãŸã§èªã¿ã«ãããããªå Žåã1500è¡å šéšãèªã¿ã«è¡ã£ãŠããŸãã
ããã§ããã¡ããšããŒã¿ãèŠã€ãããããã§ã«ãŒããè±åºãããŠããããŸãã空çœè¡ãæ¥ããæ¢ãããããªåŠçœ®ã远å ããŠããããšäœèšãªåŠçãããã«æžã¿ãŸããã«ãŒãè±åºã¯break;æãå ¥ããŠãããã ãã空çœãã§ãã¯ã¯æ®éã«tempid == ââã§ãã§ãã¯åºæ¥ãŸãã
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
//Aåã®å€ãååŸããŠwordãšæ¯èŒããŠäžèŽãããæãã for(var i = 0;i<length;i++){ //ã»ã«ã®å€ãååŸãã var tempid = data[i][0]; //空çœè¡ã®å Žåãããã§ããã°ã©ã çµäº if(tempid == ""){ break; } //ã»ã«ã®å€ãšæ€çŽ¢å€ãäžèŽããããã§ã㯠if(tempid == word){ //çµäºæéãååŸãã edtime = new Date(); break; }else{ } } |
ãããŸã§ã®å 容ãåæ ããã³ãŒãã ãšãå®è¡çµæã¯0.35ç§ã§ãããAPIã®åŒã³åºããæžãããé åããå€ããã§ãã¯ããæ¹åŒã«å€ãããšãããŸã§åçã«é床ãåäžããŸãã
ãã®ä»
ãã®ä»ãããé«éã«ãªãããã«æ¹åããäžã§ã®ãã€ã³ãã¯
- getDataRangeãgetRangeByNameã䜿ããšAPIã®äœ¿çšãããå°ãªãããäºãå¯èœã§ãã
- ã·ãŒãã幎床æ¯ã«åå²ãããã幎床ããŒã¿èç©å°çšã®å¥ã®ã·ãŒããçšæããŠãã
getDataRangeã¯ããŒã¿ãæå¹ãªè¡ãèªåå€å®ããŠããŒã¿ç¯å²ãæå®ããŠãããã¡ãœããã§ãã察ããŠgetRangeByNameã¯äºãã¹ãã¬ããã·ãŒãã«ãååä»ãç¯å²ããèšå®ããŠãæã«ãã®èšå®ã®ç¯å²ãæŸã£ãŠããã¡ãœããã§ããæ§æã¯ãSpreadsheetApp.getActiveSpreadsheet().getRangeByName(âååä»ãç¯å²åâ).getValues()ãšãã£ãæãã«ãªããŸããååä»ãç¯å²ã¯ãããŒã¿ã®å¢æžã®ãªããåºå®çãªã·ãŒãã«å¯ŸããŠèšå®ãè¡ããŸãã
ãŸããïŒïŒã«ã€ããŠã§ããã1æã®ã·ãŒãã«äœå¹ŽãããŒã¿ãæºã蟌ãã§ãå Žåãå¿ ããããã§ã«çµãã£ãåã®å¹Žã®ããŒã¿ã¯å¿ èŠãªãã£ããããŸãããããã¯å¹ŽåºŠã®åæ¿æã«ã§ããå¥ã®ã·ãŒãã«éé¿ãããã°ãåç §ããã¬ã³ãŒãæ°ãæžãããã®ã§ãçµæçã«é«éåã«ç¹ãããŸããèªåã®å Žåã幎床åäœã§éå»ãã°ã·ãŒããžç§»åãããã©ãŒã çã§æ¿èªæžã¿ã¬ã³ãŒãã¯éå»ãã°ã·ãŒããžéé¿ããããã«ã¹ã¯ãªãããæžããŠããŸãã
ããŒã¿ã®æžèŸŒãšæ€çŽ¢
é åã®ããŒã¿ãå€é¡ãã¹ãã¬ããã·ãŒããžæžã蟌ãã·ãŒã³ãå€ãããããšæããŸããäŸãã°ãåãã¹ã¿ã€ã«ã®ã·ãŒãã®ããŒã¿ãããã€ãååããŠäžçºãã«ããŠåéããã ãšããã·ãŒãããŒã¿ãã³ããŒããŠãããªã©ãªã©ãããã§ã¹ã¯ãªããã®é床ã«å€§ããªå·®ãçãŸããç¹ããããŸããä»åã¯ã䜿çšããã¹ãã¬ããã·ãŒãã®Dummyã·ãŒãããcopymanã·ãŒããžããŒã¿ãã³ããŒããäºäŸã§ãã
â»ãã¹ãå®è¡ã¯ãã¹ãã¬ããã·ãŒãã®ã¡ãã¥ãŒããããã¹ããâãæžèŸŒãã¹ããâãïŒè¡ãã€æ¿å ¥ãããã³ããŸãšããŠæ¿å ¥ããå®è¡ããŸãã
é ãã³ãŒãã®äºäŸ
ãã§ã«ã»ã«åäœã§ããŒã¿ãæžã蟌ãç䌌ã¯ããŸãããAPIã®åŒã³åºãåæ°ãæžããã®é ç®ã§ããã¯ç«èšŒãããŸããããšããäºã§æžèŸŒã§æ¬¡ã«äœ¿çšãããã§ãããã¡ãœããããappendRowãã1次å é åã®ããŒã¿ãã¹ãã¬ããã·ãŒãã®æåŸã®è¡ã«èªåã§è¿œå ããŠãããåªããã®ã§ããã©ãŒã ãªã©1è¡ã®ã¬ã³ãŒããæ¿å ¥ããå Žåã«å¹æçã§ãã
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 |
//appendRowã§ããŒã¿ãã³ããŒãã function copydataman(){ //éå§æéãååŸãã var sttime = new Date(); //çµäºæéçšã®å€æ° var edtime = ""; //ã³ããŒå
ã¹ãã¬ããã·ãŒããååŸãã var sheet = SpreadsheetApp.getActiveSpreadsheet(); var ss = sheet.getSheetByName("Dummy").getRange("A2:F").getValues(); //ã³ããŒå
ã¹ãã¬ããã·ãŒããååŸãã var cpsheet = sheet.getSheetByName("copyman"); //ã³ããŒå
ããŒã¿ãå
šã¯ãªã¢ãã cpsheet.getRange("A2:F").clearContent(); //ã³ããŒå
ããŒã¿ã®ã¬ã³ãŒãæ°ãååŸãã var length = ss.length; //ããŒã¿ã1è¡ãã€ã³ããŒããŠãã for(var i = 0;i<length;i++){ //空çœè¡ãã©ãããã§ãã¯ãã var tempid = ss[i][0]; if(tempid == ""){ break; } //1è¡ååŸããŠæ¿å
¥ãã var temprec = ss[i]; cpsheet.appendRow(temprec); } //çµäºæéãååŸãã edtime = new Date(); //éå§æéãšçµäºæéã®å·®ãç®åºããïŒç§ã«å€æïŒ var kinoko = (edtime - sttime)/1000; //ãã€ã¢ãã°è¡šç€º var message = "æ¿å
¥å®äºãŸã§ã«æãã£ãæéïŒç§ïŒïŒ" + kinoko; SpreadsheetApp.getUi().alert(message); } |
倧éã®ããŒã¿ãæ¿å ¥ããå ŽåãappendRowã§ã¯1è¡ãã€æ¿å ¥ããäºã«ãªãã®ã§ãé ãã§ããå®è¡çµæã¯ã88ç§ã§ããã
æ¹è¯ããã³ãŒãã®äºäŸ
ïŒè¡ãã€ã§ã¯ããªãæéãæããäžã«ãããŒã¿éã«æ¯äŸããŠåŠçæéã䌞ã³ãŠããŸããŸããããã§ãã®æ¹æ³ãé åãŸãããšäžçºã§æžãèŸŒãæ¹æ³ãåããŸããè€æ°ã®ã¬ã³ãŒããappendRowãšåããæçµè¡ã«è¿œå ããã¹ã¿ã€ã«ã§ãã
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 |
//倧éããŒã¿ãäžçºã§æžèŸŒãã function arraydataman(){ //éå§æéãååŸãã var sttime = new Date(); //çµäºæéçšã®å€æ° var edtime = ""; //ã³ããŒå
ã¹ãã¬ããã·ãŒããååŸãã var sheet = SpreadsheetApp.getActiveSpreadsheet(); var ss = sheet.getSheetByName("Dummy").getRange("A2:F").getValues(); //ã³ããŒå
ã¹ãã¬ããã·ãŒããååŸãã var cpsheet = sheet.getSheetByName("copyman"); //ã³ããŒå
ããŒã¿ãå
šã¯ãªã¢ãã cpsheet.getRange("A2:F").clearContent(); //ã³ããŒå
ããŒã¿ã®ã¬ã³ãŒãæ°ãååŸãã var length = ss.length; //æžèŸŒçšé
åãçšæãã var array = []; //ããŒã¿ã1è¡ãã€ã³ããŒããŠãã for(var i = 0;i<length;i++){ //空çœè¡ãã©ãããã§ãã¯ãã var tempid = ss[i][0]; if(tempid == ""){ break; } //æžèŸŒçšé
åã«pushãã array.push(ss[i]); } //é
åããŒã¿ãæçµè¡ã«äžçºæžèŸŒ var endrow = Number(cpsheet.getLastRow()) + 1; //æçµè¡ã®äœçœ®ãååŸ var lastColumn = array[0].length;ãããããããã//ã«ã©ã ã®æ°ãååŸãã var lastRow = array.length; ãããããããããã//è¡ã®æ°ãååŸãã cpsheet.getRange(endrow,1,lastRow,lastColumn).setValues(array); //çµäºæéãååŸãã edtime = new Date(); //éå§æéãšçµäºæéã®å·®ãç®åºããïŒç§ã«å€æïŒ var kinoko = (edtime - sttime)/1000; //ãã€ã¢ãã°è¡šç€º var message = "æ¿å
¥å®äºãŸã§ã«æãã£ãæéïŒç§ïŒïŒ" + kinoko; SpreadsheetApp.getUi().alert(message); } |
衚瀺ã¯ããŒã¿éã«ãã£ãŠã¢ã¿ãããŸãããå®è¡çµæèªäœã¯0.80ç§ã§ãããæ¬æ¥ãååŸããã¹ãã¬ããã·ãŒãèªäœãé åãªã®ã§ããã®ãŸãŸæžèŸŒãåºæ¥ãŸããæ¡ä»¶ãäžç·ã«ããããã«ãæžã蟌ã¿çšé åã«ããããpushãããŠããŸãã
UrlfetchAppã§ãŸãšããŠãªã¯ãšã¹ã
éåžžãå€éšã®HTTPã«ãªã¯ãšã¹ããéãçºã®UrlfetchAppã¯åäžã®URLã«å¯ŸããŠãªã¯ãšã¹ããéä¿¡ããå€ãåãåã£ããããçºã«å©çšããŸããããããã®ã¡ãœããã¯ã10ç§éã«ããã3åçšåºŠãããªã¯ãšã¹ãåºæ¥ããQuotaã«ã¯è¡šèšãç¡ãã®ã§ããïŒå以äžãªã¯ãšã¹ããéããšã429ãšã©ãŒïŒToo Many RequestïŒã§éä¿¡ããªãžã§ã¯ããããŠããŸããŸãã
æ ã«10ç§éã«3åãã€éãããã«ãŠã§ã€ãïŒSleepïŒããåŠçãå ¥ããŠãåŠçãé ããããããããŠããŠã䜿ãã®ãéåžžã§ããããããUrlfetchAppã«ã¯ããäžã€ãUrlfetchApp.fetchAllããšåŒã°ããã¡ãœãããçšæãããŠãããè€æ°ã®ãªã¯ãšã¹ãããŸãšããŠéã£ãŠãçµæããŸãšããŠåãåãäºãå¯èœã§ãããããã50%çšåºŠé床ãåäžããŸããç¹ã«PDFçæãªã¯ãšã¹ãçã§å¹æãåºããšæããŸãã
â»äœããäžåºŠã«ããããã®ãªã¯ãšã¹ããåæã«éã£ãŠãããããNGã«ãªãã®ã§ãããããæ°åçšåºŠã«ããã¡ãŠããã¹ãã§ãããã
ãªã¯ãšã¹ãããæ¹æ³ã¯ä»¥äžã®éãã§ãã
1 2 3 4 5 6 7 8 9 10 |
//äžåºŠã«3ã€ã®ãªã¯ãšã¹ããæãã function multirequest(){ //3ã€ã®ãªã¯ãšã¹ããïŒåºŠã«éã let url1 = "ãªã¯ãšã¹ãURL"; let url2 = "ãªã¯ãšã¹ãURL2"; let url3 = "ãªã¯ãšã¹ãURL3"; //fetchAllã§äžåºŠã«ãªã¯ãšã¹ã var response = UrlFetchApp.fetchAll([url1, url2, url3]); } |
OAuth2.0èªèšŒçã®èªèšŒãå¿ èŠãªãã®ã¯1åãã€ãªã¯ãšã¹ããã©ã¡ãŒã¿ãçšæããŠããªã¯ãšã¹ãã®é åã«å ããã°è¯ããQuotaã«ããçŸåšã®å¶éãè¶ ããªããµã€ãºã§ãªã¯ãšã¹ããå¿ èŠãªã®ã§èŠæ³šæã
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
//pdfãã¡ã€ã«ãè€æ°åæã«ãªã¯ãšã¹ã function makeallpdf(token,payload){ //1åç®ã®ãªã¯ãšã¹ãã¡ãœãã let req1 = { "url" : "1ã€ç®ã®URL", "method" : "GET", 'headers': {"Authorization": " Bearer " + token} } //2ã€ç®ã®ãªã¯ãšã¹ãã¡ãœãã let req2 = { "url" : "1ã€ç®ã®URL", "method" : "POST", 'headers': { "Authorization": " Bearer " + token, 'contentType': "application/json" }, "payload" : payload } //ãªã¯ãšã¹ãã®å®è¡ let response = UrlFetchApp.fetchAll([req1,req2]); } |
ãšããããšã§ã5ã€ã®ã·ãŒããæã€ãã¡ã€ã«ãã5ã€ã®PDFãäžæ¬ã§çæããã³ãŒããäœæããã¹ãããŠã¿ããšããã16ç§çšåºŠã§çæããäºãã§ããŸããããã¡ãã®ã³ãŒãã¯ãã¡ãã®ãã¡ã€ã«ã®äžã«ããmultimake.gsã®äžã«èšè¿°ããŠãããŸãã
fetchAllã®çµæããŸããé åã§ãŸãšããŠè¿ã£ãŠããã®ã§ãããããgetBlobã§ååŸãããã®ãæå®ã®ãã©ã€ãã®ãã©ã«ãå ã«createFileã§çæããã ãã§ããåçºãªã¯ãšã¹ãã ãš24ç§ãè¶ ããŠããŸããŸãã
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 |
//ããŒãžããšã«PDFåããŸãšããŠå®è¡ function makeAllPdf() { //ã¢ã¯ã»ã¹ããŒã¯ã³ãååŸ let token = ScriptApp.getOAuthToken(); //ã·ãŒãåã®äžèЧ let ss = SpreadsheetApp.getActive(); let sheets = ss.getSheets(); //ãªã¯ãšã¹ãããã let headers = {'Authorization': 'Bearer ' + token}; //ãªã¯ãšã¹ãURLãé
åã«ãŸãšãã let array = [] let sheetman = [] let fileid = ss.getId(); for(let i = 0;i<sheets.length;i++){ //ã·ãŒãåãåãåºã let sheetname = sheets[i].getName(); sheetman.push(sheetname) //sheetidãåãåºã let sheetid = sheets[i].getSheetId(); //URLãçµã¿ç«ãŠ let url = "https://docs.google.com/spreadsheets/d/" + fileid + "/export?gid=" + sheetid + "&format=pdf&portrait=false&size=A4&gridlines=false&fitw=true" //ãªã¯ãšã¹ããªãã·ã§ã³ let req = { "url" : url, "method" : "GET", 'headers': headers } //é
åã«å ãã array.push(req); } //ãªã¯ãšã¹ããå®è¡ let response = UrlFetchApp.fetchAll(array); //ä¿åå Žæãæå® let folder = DriveApp.getFolderById("ããã«çæå
ã®ãã©ã«ãã®IDãå
¥ãã"); //responseããPDFãçæãã for(let j = 0;j<sheetman.length;j++){ //BlobãååŸ let blob = response[j].getBlob(); //ãã¡ã€ã«ãçæïŒã·ãŒãåã§ãã¡ã€ã«åãã»ããïŒ folder.createFile(blob).setName(sheetman[j] + ".pdf"); } } |
Sheets API v4ãå©çšãã
ãã¡ãã®ãµã€ãã«ãŠãSpreadsheetAppãããSheets APIã䜿ã£ãã»ããå šç¶æ©ãããšããè³å¯ããªæ å ±ãæµããŠããŠãæ€èšŒããŠã¿ãŸããããè€æ°ã®ã·ãŒãããã³å€§éã®ããŒã¿ã®èªã¿æžãã«é¢ããŠã¯ãSheets APIãå©çšããã»ããå§åçã«æ©ãäºãããããŸããïŒããã2.8åã»ã©æ©ãïŒããã®ãšã³ããªãŒã«é¢ããŠã¯ä»¥äžã®ããŒãžã«è©³çŽ°ã«æžããŠããŸãã
éåžžã®SpreadsheetAppãå©çšãããããæç¶ããããã§ãã
Google Apps Scriptã§Sheets APIã䜿ã£ããçéã ã£ããGASã
V8 Runtimeãå©çšãã
2020幎2æ6æ¥ãããGoogle Apps Scriptã®åºç€ãšãªã£ãŠãJavaScriptãšã³ãžã³ãV8ãšã³ãžã³ã䜿ããããã«ãªããŸãããV8ãšã³ãžã³ã«åãæ¿ããŠã¹ããŒããã³ãããŒã¯ãåã£ãŠã¿ãæãå šäœçã«18%çšåºŠã®ã¹ããŒãã¢ããããŠãããããªãããªããšããçµæãåŸãããŸããã
V8ãšã³ãžã³ã«ã€ããŠã®è©³çްã«ã€ããŠã¯ãã¡ãã®ãšã³ããªãŒãåç §ããŠãã ããã以äžã®æé ã§å€æŽã§ããŸãã
- ã¹ã¯ãªãããšãã£ã¿ãéã
- ã¡ãã¥ãŒã®ã衚瀺ãâããããã§ã¹ããã¡ã€ã«ã衚瀺ããã¯ãªãã¯
- appsscript.jsonãéãã1è¡ä»¥äžã®ãããªãã®ãã远å ãã
- ä¿åããã°ãããã«äœ¿ããããã«ãªããŸãã
- ããšã«æ»ãå Žåã«ã¯ããã®è¡ãåé€ããã°ãªãã±ãŒã§ããïŒDEPRECATED_ES5ãæå®ããŠãå¯ïŒ
1 2 |
//远å ããè¡ã¯ãã¡ã "runtimeVersion":"V8" |
å³ïŒç°¡åã«äœ¿ããããã«ãªããŸã
æ€èšŒçµæãšããŠã¯ãä»åã®ãµã³ãã«ã§èšãã°ã»ã»ã»
ææªãªã³ãŒãã§æžããå Žåã®æ€èšŒçµæã¯ãã97.078ïŒç§ïŒãã§ãããïŒ18%ã»ã©æ©ãã£ãïŒ
äžçªè¯ãäºäŸã§æžããå Žåã®æ€èšŒçµæã¯ãã0.424ïŒç§ïŒãã§ãããïŒ19%ã»ã©æ©ãã£ãïŒ
ãšãã£ãæãã§ããã20%è¿ãé床ã¢ãããèŠèŸŒããã®ã§ãV8ã«ããŠãè¯ãã§ããããŸã ãªãªãŒã¹ããã°ãããªã®ãšã泚æç¹ãããã®ã§ããããèæ ®ããŠå€æŽããŸãããã
HTML ServiceãæŽ»çšãã
ãã®ã»ã¯ã·ã§ã³ã§ç޹ä»ããæ¹æ³ã¯ãé«éåãããšèšãããã¯ã6åã®å®è¡ãªããããåé¿ããŠäœæ¥ãè¡ãããäºã®ã§ãããã¯ããã¯ã§ããHTML Serviceã§çæããHTMLã¯ã¯ã©ã€ã¢ã³ãã®ãã·ã³äžã§åäœããŠãã®ã§ãJavaScriptã®å®è¡æéã«å¶éã¯ãããŸããããã ãããã®åŠçãäžçºã§è¡ãããã«ã¯ãåæåŠçãå¿ èŠã«ãªãã®ã§ãäžæãåã®åŠçãçµãã£ããæžèŸŒã«ãŒãã³ãå®è¡ããããã«ããªããšãããŒã¿ã®ååãçµãã£ãŠããªãã®ã«ãGASåŽãžæããããŠããŸãã®ã§æ³šæãå¿ èŠã§ãã
â»ãã¹ãå®è¡ã¯ãã¹ãã¬ããã·ãŒãã®ã¡ãã¥ãŒããããã¹ããâãããŒã¿ãã§ãã¯ãâãHTML Serviceã§ååŸãããããã³ãVisualization APIã§ååŸããå®è¡ããŸãã
åçŽãªäœ¿çšäŸ
- GASåŽããã·ãŒãã®ãªã¹ããååŸããã
- Google Apps ScriptåŽã§åŠçãæ åœãã颿°ã¯é£ç¶åŠçã§ã¯ãªãåäžã®åŠçãè¡ãã ãã§ããïŒããã·ãŒãã®ããŒã¿ã®å¡ãååŸããŠHTMLåŽãžè¿ãã ããšãã
- æ ã«ãHTMLåŽã«ã·ãŒãã®IDãææãããªã¹ãã«ãããã£ãŠ2.ãžIDãæããè¿ã£ãŠããå€ãé åã«pushïŒãããã¯ãArray.prototype.push.applyïŒããã³ãŒããèšè¿°ããã
- æçµçã«äžå¡ã«ãªã£ãé åããŒã¿ãGASåŽã«éãè¿ããŠã·ãŒãã«æžã蟌ãŸãã
ãããªå ·åã§ããåžžã«GASåŽãšããåããããŠããŸãããGASåŽã§ã®å®éã®åŠçã¯ãåäžã·ãŒãããŒã¿ã®ååŸãšå¡ã®æžèŸŒã§ãã®ã§ãïŒã€ïŒã€ã6åã«å°éããäºããªããã°åé¡ãªãé£ç¶åŠçãæããäºãå¯èœã§ããä»åã®ãµã³ãã«ã§ã¯ããããŒã®ã·ãŒãïŒ10ã«ã©ã 1000ã¬ã³ãŒãã10æïŒãããã32ç§ã§ååŸåºæ¥ãŠããŸãã
â»ããŒã¿ã®åŠçãGASåŽã§ãããããHTML Serviceã§çæããHTMLäžã«ãŠãJavaScriptã«ããããã®ããã€ã³ãã§ãã
â»google.script.run.withSuccessHandlerã䜿çšããŠããŸãããã®é¢æ°ã®å®è¡ã¯éåæã§å®è¡ãããã®ã§ã䞊ååŠçã£ãœãäœæ¥ãè¡ããŸãã
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 43 44 45 46 47 48 49 |
//ãã¡ã€ã«ãªã¹ããååŸãã颿° function fileman(){ var sheet = SpreadsheetApp.getActiveSpreadsheet(); var ss = sheet.getSheetByName("filelist").getRange("A2:A").getValues(); Logger.log(ss); return JSON.stringify(ss); } //ããŒã¿ååŸãã€ã¢ãã°ã衚瀺ãã function datapakupaku(){ var html = HtmlService.createHtmlOutputFromFile('bigdata') .setSandboxMode(HtmlService.SandboxMode.IFRAME) .setWidth(200) .setHeight(100); SpreadsheetApp.getUi() .showModalDialog(html, 'ã·ãŒãããŒã¿ååå'); } //ãã¡ã€ã«IDãå
ã«ããŒã¿ã®å¡ãååŸããŠè¿ã颿° function tokyotower(id){ var sheet = SpreadsheetApp.openById(id); var data = sheet.getSheetByName("apple").getRange("B2:J").getValues(); return JSON.stringify(data); } //ããŒã¿ã®å¡ãã¹ãã¬ããã·ãŒãã«æžã蟌ã function insertarray(array){ var sheet = SpreadsheetApp.getActiveSpreadsheet(); var ui = SpreadsheetApp.getUi(); var ss = sheet.getSheetByName("dataset"); //ã·ãŒããã¯ãªã¢ãã ss.getRange("A2:I").clearContent(); //ããŒã¿ã®æžèŸŒããã var endrow = Number(ss.getLastRow()) + 1; //æçµè¡ã®äœçœ®ãååŸ var lastColumn = array[0].length;ãããããããã//ã«ã©ã ã®æ°ãååŸãã var lastRow = array.length; ãããããããããã//è¡ã®æ°ãååŸãã ss.getRange(endrow,1,lastRow,lastColumn).setValues(array); //ã¡ãã»ãŒãžè¡šç€º ui.alert("æžèŸŒçµäºããŸããã"); } |
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 |
<html> <head> <script type="text/javascript"> var masterdata = []; //ã·ãŒãããŒã¿ãæ ŒçŽãã倿° var filelist = ""; //ãã¡ã€ã«ãªã¹ããæ ŒçŽãã倿° var lengthman = 0; var cnt = 0; //ãã¡ã€ã«ãªã¹ãã®ååŸ google.script.run.withSuccessHandler(onSuccess).fileman(); function onSuccess(data){ filelist = JSON.parse(data); lengthman = Number(filelist.length); document.getElementById('laputa').disabled = null; } //ããŒã¿ååŸçšã®ã³ãã³ã function disp(value){ //ãã¿ã³ãšãªã¢ãæžãæã if(value == 0){ var html = "<img src='https://officeforest.org/wp/library/ProgressSpinner.gif' width='48' height='48'><p><font color='red'><b>ããŒã¿ååŸäžïŒïŒïŒ</b></font></p>"; document.getElementById("spring").innerHTML = html; } //cntã«valueãæ ŒçŽãã cnt = value; //lengthãšcntãæ¯èŒããŠæ¡ä»¶åå² if(cnt<lengthman){ //IDãååŸãã var fileid = filelist[cnt][0]; //GASåŽã«ããŒã¿ãåãããŠååŸãã google.script.run.withSuccessHandler(imparray).tokyotower(fileid); }else{ //ããŒã¿ãååŸãçµããã®ã§çµäºåŠçãå®è¡ google.script.run.insertarray(masterdata); } } //ããŒã¿ãé
åã«æ¬¡ã
ã«å
¥ããã³ãã³ã function imparray(data){ var json = JSON.parse(data); Array.prototype.push.apply(masterdata, json); cnt = cnt + 1; disp(cnt); } </script> </head> <body> <div align="center" id="spring"> <button type="button" id="laputa" onClick="disp(0);" disabled> <img src="https://officeforest.org/wp/library/icons/spinner.gif" width="32" height="32" ><br> <font size="2">ããŒã¿é ããŸã</font> </button> </div> </body> </html> |
Promiseãšã¿ã€ããŒã§åæåŠç
åé ã®åçŽãªäºäŸã¯GASåŽã¯ããŒã¿ã®ååŸãè¡ã£ãŠHTMLåŽã§åŠçãããããšãç®çãšãããã®ã§ãããéã«HTMLåŽã¯ã¿ã€ããŒç®¡çãè¡ãããšã«åŸ¹ããŠãåŠçã¯GASåŽã§è¡ãããå Žåã«ã¯Promiseã«ããåæåŠçãå®çŸããå¿ èŠããããŸããããããgoogle.script.runã¯éåæã«ãã³ãã³å®è¡ãããŠããŸãã®ãšãã«ãŒãåŠçããŸãåããªã®ã§äžæã«ã«ãŒããåãã€ã€ãæžã蟌ã¿ãèæ ®ããŠã¿ã€ããŒãæããé ç¹°ãã«google.script.runãå®è¡ããããã«ãããšãGASåŽã§é£ç¶åŠçããããšãåãäºãå¯èœã§ãã
- HTMLåŽã¯PromiseãšTimerã«ãŠgoogle.script.runã®å®è¡ãš3ç§éã®ãŠã§ã€ããå®çŸãã
- GASåŽãã1000ã¬ã³ãŒããã€ååŸãã
- å¥ã®ã·ãŒãã«è¿œèšã§ã³ããŒãã
- ã«ãŒãã®åæ°åã ã2.ã3.ãå®è¡ãã
- å®äºããããã€ã¢ãã°ãéãã
- ãããæ¬æ¥éåæã§å®è¡ãããŠããŸãgoogle.script.runã®å®è¡ãåæçã«å®è¡ã§ããããã«ãªããŸãã
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 datacopy(){ var html = HtmlService.createHtmlOutputFromFile('promise') .setSandboxMode(HtmlService.SandboxMode.IFRAME) .setWidth(300) .setHeight(150); SpreadsheetApp.getUi() .showModalDialog(html, 'ã·ãŒãããŒã¿è»¢èšãã'); } //1000ãã€ããŒã¿ãã³ããŒãã颿° function recordcopy(cnt){ //åŒæ°ãã1000åç®ã®äœçœ®ã決å®ãã var endcnt = cnt + 999; //datasetã®ã¬ã³ãŒãããŒã¿ãååŸãã if(cnt == 1){ //æåã®1åç®ã ãã¯ååŸéå§äœçœ®ã1ã€ããã var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("dataset"); //2è¡ç®ä»¥éããendcntãŸã§ã®ããŒã¿ãååŸãã var range = sheet.getRange(2,1,endcnt,9).getValues(); }else{ //æå®ã®cntå€ãçšããŠããŒã¿ãååŸãã var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("dataset"); var range = sheet.getRange(cnt,1,1000,9).getValues(); } //ååŸããããŒã¿ãcpdataã«è²Œãä»ãã var cpsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("cpdata"); var endrow = Number(cpsheet.getLastRow()) + 1; //æçµè¡ã®äœçœ®ãååŸ var lastColumn = 9ãããããããã//ã«ã©ã ã®æ°ãååŸãã var lastRow = range.length; ãããããããããã//è¡ã®æ°ãååŸãã cpsheet.getRange(endrow,1,1000,lastColumn).setValues(range); //çµæãè¿ã return endcnt; } |
- 1000ä»¶åã£ãŠã³ããŒããããå®äºããã«ãŒãã³ã§ãã
- åæå€ã®cntã¯HTMLåŽããåãåããŸãããããã¬ã³ãŒãååŸéå§äœçœ®ã«ããªããŸãã
- ïŒåç®ã¯ã¿ã€ãã«è¡ãããã®ãèæ ®ããŠãrangeã®ååŸæ¹æ³ãå€ããŠããŸãã
- ããŒã¿ã®èªã¿æžãã¯äžçºã§è¡ã£ãŠããŸãã
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 |
<!DOCTYPE html> <html> <head> <base target="_top"> <script> var cnt = 1; // ã«ãŒãå
åŠçïŒããŒã¿ã®ååŸïŒ function action(i) { return new Promise( (res, rej) => { setTimeout( () => { console.log(cnt); //ã¬ã³ãŒã転èšããã»ã¹å®è¡ google.script.run.withSuccessHandler(function(ret) { //åŠçããã°ã¬ã¹è¡šç€º document.getElementById("console").innerHTML = cnt + 'ã¬ã³ãŒãç®ãåŠçäž'; console.log(i + 'åç®ã§ã') //ã«ãŠã³ã¿ã«1000ãå ç®ãã cnt = cnt + 1000; res(); }).recordcopy(cnt); },3000) }) } //ã«ãŒãåŠç function loop( fn, i, end) { console.log(i) return fn(i) .then( () => { if ( i < end ) { return loop( fn, i+1, end) } else { google.script.host.close(); return Promise.resolve('end'); } }); } //ããŒã¿è»¢èšã¡ã€ã³ããã»ã¹ function disp(){ //ã«ãŠã³ã¿åæå cnt = 1; //ã«ãŒãåŠçå®è¡(2000ã¬ã³ãŒãã ãååŸ) loop(action, 0, 1); } </script> </head> <body> <div align="center" id="spring"> <button type="button" id="laputa" onClick="disp();"> <img src="https://officeforest.org/wp/library/icons/itigo-jm.gif" width="32" height="32" ><br> <font size="2">ããŒã¿è»¢èšããŸã</font> </button> </div> <div id="console"></div> </body> </html> |
- ä»åãPromiseãšã«ãŒãããããŠ3ç§ã®ã¿ã€ããŒãå®çŸããã³ãŒãã¯ãã¡ãã®ãµã€ããããåãããŸããã
- ãã¿ã³ã¯ãªãã¯ã§dispãå®è¡ãããä»å2000ã¬ã³ãŒããªã®ã§ã«ãŒãåæ°ã1ãšããŠæå®ïŒã€ãŸã2åå®è¡ïŒã
- loop颿°ã¯åŒæ°ã®å®è¡é¢æ°åãåæã«ãŠã³ã¿ãã«ãŒãåæ°ãåãåããifæã§å€å®ããŠååž°çã«èªåèªèº«ãå®è¡ããŠããŸãã
- ã«ãŒãåæ°ã«éãããšgoogle.script.host.closeã§ãã€ã¢ãã°ãéããŠããŸãã
- å®éã«åŠçãè¡ãaction颿°ã«ãŠãsetTimeoutã§ã¹ãªãŒããïŒmsã§æå®ïŒãã¬ã³ãŒã転èšããã»ã¹ã«google.script.run.withSuccessHandlerãcallbackãã圢ã§çµã¿èŸŒãã§ããŸãã
- GASåŽã§åŠçãå®äºãcallbackããããã°ããŒãã«å€æ°ã®ã«ãŠã³ã¿ã«1000ãå ããŠããã
- GASåŽé¢æ°å®è¡æã«åŒæ°ãšããŠã°ããŒãã«å€æ°ã®ã«ãŠã³ã¿ãæž¡ããŠããŸãã
- foræãªã©ã®ã«ãŒãã䜿ããšéåæã«æ¬¡ã ã«å®è¡ãããŠããŸãã®ã§ãä»åã®ããã«ã«ãŠã³ã¿ã䜿ã£ãå€ã®æ¯èŒã§åŠçãå®è¡ãããŸãã
ãããã®å¿çšäŸãšããŠä»¥äžã®ãããªãšã³ããªãŒãäœæããŠã¿ãŸããã
Google Apps Scriptã§ããã¥ã¡ã³ããäžæ¬ã§PDFã«ãã
Visualization APIã䜿çšãã
ããŒã¿ã®å¡ãååãããããªäœæ¥ã«æŒããŠãGASã§ãããããšã¿ã€ã ãªãŒããŒããŠããŸããããªã±ãŒã¹ã§ããHTML Serviceäžã§Visualization APIã䜿çšããäºã§ã¿ã€ã ãªãŒããŒãšé¢ä¿ãªãåŠçãè¡ãããäºãå¯èœã§ãã
- GASåŽããã·ãŒãã®ãªã¹ããååŸããã
- Visualization APIãå©çšããŠããŒã¿ã®å¡ãååŸããåŠçãé£ç¶åŠçããããååŸããŒã¿ã¯é åã«æ ŒçŽãã
- æçµçã«äžå¡ã«ãªã£ãé åããŒã¿ãGASåŽã«éãè¿ããŠã·ãŒãã«æžã蟌ãŸããã
ãããªå ·åã§ããGASåŽã§ãã£ãŠãäºã¯æžèŸŒåŠçã ãã§ãã®ã§ããã»ã©å€§ééããããŒã¿ã§ãªããã°ã¿ã€ã ã¢ãŠãããªãã¯ãã§ããVisualization APIã¯HTMLåŽã§åäœããŠããŸãã®ã§ãããŒã¿ã®ååŸã§ã¿ã€ã ã¢ãŠãã¯çºçããŸãããå®è¡ããŠã¿ãçµæã¯ã26ç§ã§ããã
â»äœæ ããã¡ãã®ããæ¹ã®å ŽåãHTMLåŽããGASåŽãžé åããŒã¿ãæž¡ãæã«ãFailed due to illegal value in propertyããšãããšã©ãŒãåºãã®ã§ãHTMLåŽã§JSON.stringify(masterdata)ãšããŠæž¡ããŠäžããŠãGASåŽã§JSON.parse(array)ãšããŠåãåãããŠãŸããä»ã¯å šãåãã³ãŒãã§ãã
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 |
//ãã¡ã€ã«ãªã¹ããååŸãã颿° function fileman(){ var sheet = SpreadsheetApp.getActiveSpreadsheet(); var ss = sheet.getSheetByName("filelist").getRange("A2:A").getValues(); return JSON.stringify(ss); } //ããŒã¿ååŸãã€ã¢ãã°ã衚瀺ãã function datapakupaku2(){ var html = HtmlService.createHtmlOutputFromFile('bigdata2') .setSandboxMode(HtmlService.SandboxMode.IFRAME) .setWidth(200) .setHeight(150); SpreadsheetApp.getUi() .showModalDialog(html, 'ã·ãŒãããŒã¿åååã»åŒåŒ'); } //ããŒã¿ã®å¡ãã¹ãã¬ããã·ãŒãã«æžã蟌ã function potato(data){ var json = JSON.parse(data); var sheet = SpreadsheetApp.getActiveSpreadsheet(); var ui = SpreadsheetApp.getUi(); var ss = sheet.getSheetByName("dataset"); //ã·ãŒããã¯ãªã¢ãã ss.getRange("A2:I").clearContent(); //ããŒã¿ã®æžèŸŒããã var endrow = Number(ss.getLastRow()) + 1; //æçµè¡ã®äœçœ®ãååŸ var lastColumn = json[0].length;ãããããããã//ã«ã©ã ã®æ°ãååŸãã var lastRow = json.length; ãããããããããã//è¡ã®æ°ãååŸãã ss.getRange(endrow,1,lastRow,lastColumn).setValues(json); //ã¡ãã»ãŒãžè¡šç€º ui.alert("æžèŸŒçµäºããŸããã"); } |
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 85 86 87 88 89 90 91 92 93 94 95 |
<html> <head> <!-- Google Visualization APIé¢ä¿ --> <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script> <script type="text/javascript"> var masterdata = []; //ã·ãŒãããŒã¿ãæ ŒçŽãã倿° var filelist = ""; //ãã¡ã€ã«ãªã¹ããæ ŒçŽãã倿° var lengthman = 0; var cnt = 0; var gidval = "apple"; //åç
§ããã·ãŒãå var url = ""; //queryã«æããURL //ããã±ãŒãžã®ããŒããšããŒã¿ã®ååŸ google.charts.load("current", {packages:["table"], 'language': 'ja'}); //ãã¡ã€ã«ãªã¹ãã®ååŸ google.script.run.withSuccessHandler(onSuccess).fileman(); function onSuccess(data){ filelist = JSON.parse(data); lengthman = Number(filelist.length); document.getElementById('laputa').disabled = null; } //ããŒã¿ååŸçšã®ã³ãã³ã function disp(value){ //ãã¿ã³ãšãªã¢ãæžãæã if(value == 0){ var html = "<img src='https://officeforest.org/wp/library/ProgressSpinner.gif' width='48' height='48'><p><font color='red'><b>ããŒã¿ååŸäžïŒïŒïŒ</b></font></p>"; document.getElementById("spring").innerHTML = html; } for(var i = 0;i<lengthman;i++){ //URLã®çµã¿ç«ãŠ var spreadman = "https://docs.google.com/spreadsheets/d/" + filelist[i][0] + "/"; var urlData = spreadman + "gviz/tq?sheet="; url = String(urlData + gidval); //æ€çŽ¢å¯Ÿè±¡ã®ã¹ãã¬ããã·ãŒãURLã®èšå® var query = new google.visualization.Query(url); //Queryãã¶ãæãã query.send(imparray); } } //ããŒã¿ãé
åã«æ¬¡ã
ã«å
¥ããã³ãã³ã function imparray(response){ //ã¯ãšãªãŒãšã©ãŒãã©ããåŠç if (response.isError()) { alert('ã¯ãšãªãŒãšã©ãŒ: ' + response.getMessage() + ', ' + response.getDetailedMessage()); return; } //ããŒã¿ããŒãã«ãšã¬ã³ãŒãæ°,ã«ã©ã æ°ãååŸãã var data = response.getDataTable(); var length = data.getNumberOfRows(); var clength = data.getNumberOfColumns(); //é
åãçšæãDataTableã®äžèº«ãåãåºã for(var i = 0;i<length;i++){ var array = []; for(var j = 1;j<clength;j++){ array.push(data.getValue(i,j)); } //é
åãmasterdataã«å ãã masterdata.push(array); } //ã«ãŠã³ã¿ãåã cnt = cnt + 1; //ã«ãŠã³ã¿ã®å€ãšlengthmanãæ¯èŒãçµäºããŠãããæžèŸŒäœæ¥ if(cnt == lengthman){ //ããŒã¿ãååŸãçµããã®ã§çµäºåŠçãå®è¡ var json = JSON.stringify(masterdata); google.script.run.potato(json); } } </script> </head> <body> <div align="center" id="spring"> <button type="button" id="laputa" onClick="disp(0);" disabled> <img src="https://officeforest.org/wp/library/icons/itigo-jm.gif" width="32" height="32" ><br> <font size="2">ããŒã¿é ããŸã</font> </button> </div> </body> </html> |
ã·ãŒã颿°çã§åºæ¥ãäºã¯ãªãã¹ããããã
ãã®é ç®ã¯ãGoogle Apps Scriptã®é«éåãšããããã¯ãããã«Google Apps Scriptã䜿ããã«æžãŸãããïŒãšããäºããŸãšããé ç®ã§ãããªãã§ãããã§ãGoogle Apps Scriptã§åŠçããããšããã®ã¯æ£ããäºã§ã¯ãããŸããããŸããããŒã¿ã®å¡ååŸã®ãããªå€§ããªäœæ¥ã®å ŽåãæšªçããŠæ¥èšããŒã¿ãã¬ãããªåã£ãŠãããããªäºããããšãçŽãã«ã¿ã€ã ãªãŒããŒã«ãªã£ãŠããŸããŸããããã§ããããGoogle Apps Scriptã§åŠçãããåæ®µéã§äœæ¥ãè¡ãããŠãScriptã®åŠçé床ãçµæçã«åäžãããããšããäºãç ç©¶ãèšè¿°ããŠããŸãã
ããŒã¿ã¯é¢æ°ã§éèšããŠãã
åçš®ããŒã¿ã®éèšãšåéãè¡ããããªã¹ãã¬ããã·ãŒãã®å ŽåãååŸããããŒã¿éãå€ãããå Žåãæ§ã ãªé«éåã®åªåãããŠããŠããã¿ã€ã ã¢ãŠãããäºããŸãŸãããŸããèªåã®å®éšã§ã¯ã10ã«ã©ã 30,000ä»¶ã®ããŒã¿åéããŠãèªåã®ã¹ãã¬ããã·ãŒãã«æžã蟌ãã«ãŒãã³ãäœã£ãæãã¿ã€ã ã¢ãŠãããŠããŸããŸãããããŒã¿ã¯æ¥èšè¡šã§éèšèªäœã¯ææ¬¡éèšãšããŠè¡ã£ãŠããŸãããã®ãããªã±ãŒã¹ã®å Žåã以äžã®æ¹åç¹ãèŠèŸŒããŸãã
- å¿ èŠãªæçµããŒã¿ã¯ææ¬¡éèšããŒã¿ã§ããã®ã§ãäºãåã·ãŒãã§éèšãããŠãçµæããã¹ã¯ãªããã¯åéããããã«å€æŽããã
- åã·ãŒãã®ããŒã¿éèšã¯ç¬èªã®é¢æ°ã§ã¯ãªããã¹ãã¬ããã·ãŒãæšæºè£ åã®é¢æ°ã䜿çšããŠè¡ããç¬èªã®é¢æ°ã䜿çšããå ŽåãåŠçãéã«åããããŒã¿ãåãæãããã¹ãã¬ããã·ãŒãæšæºã®é¢æ°ã®å Žåãã®æãããªãã
- è€éãªéèšã«ã€ããŠã¯ãQUERY颿°ã䜿çšããå Žåã«ãã£ãŠã¯Pivotæå®ãããŠã¯ãã¹éèšãè¡ãããã
ãã®æ¹åãè¡ã£ãçµæãã¿ã€ã ã¢ãŠãããŠããåŠçã¯ã20ç§ã§å®äºããç®çã®ææ¬¡éèšè¡šãäœããããã«ãªããŸãããããŒã¿ãåŠçãããšèšã£ãŠããããŒã¿ã®ãã«ã€å¡ã§ããå¿ èŠæ§ããªãã®ãªãã¹ãã¬ããã·ãŒãã®é¢æ°ã«ããããããã«ããŸãããã
è€æ°ã®ã·ãŒãã«ããªã
ïŒã€ã®ã¹ãã¬ããã·ãŒãã«ããŒã¿ãèç©ããã·ãŒããè€æ°ã«ããŠãã±ãŒã¹ããããŸããçç±ã¯åãæ¥èšè¡šã§ãã£ãŠãå ¥åããå€ãç°ãªããšããçç±ã§ã«ã©ã ã®æ°ãéã£ããããŠãçºã§ããæ ã«ã·ãŒãåã«äŸãã°ã³ãŒããæ¯ã£ãŠã101 = ã¹ãŒããŒã®å£²äžã102 = ã¬ãžã£ãŒæœèšã®å£²äžãšãã£ããããªåé¢ãããŠã人ãå€ãã§ãããã確ãã«æå ¥åãããã®ã§ããã°ãããã§ãè¯ããšæããŸããããããã·ãŒããå¢ããŠãäžã€ã¹ãã¬ããã·ãŒããå¢ãããšããããäžçºãã«ããçºã«ååããã¹ã¯ãªããã¯ãäœåºŠãAPIãå©ããªããã°ãããŸããã
ãã®ã±ãŒã¹ã®å Žåã以äžã®æ¹åãèŠèŸŒããŸãã
- ã¹ãã¬ããã·ãŒãæå ¥åããããŠãHTML Serviceã§UIãäœã£ãŠãããïŒãªãããªGUIã¢ããªãäœããåç §ïŒ
- ã·ãŒããåé¢ãããã«ïŒæã®ã·ãŒãã«ã«ã©ã åããã±ãã«ã³ãŒããå ¥ããåãèšããŠãåå± ãããŠããŸãïŒã³ãŒãã§ãã®ã¬ã³ãŒããäœã®é ç®ãªã®ãã¯åããïŒ
- éèšãè¡ãå Žåã«ã¯Query颿°ã䜿çšããPivotã«ããŠããååŸããããã«ããã
ãã®æ¹åãè¡ãäºã«ãã£ãŠãã·ãŒãããŒã¿ã®ååŸã«æããAPIã®åŒã³åºãã¯ïŒåã§æžã¿ãŸãããã£ãŠãã·ãŒãæ°ã4ã§ã¹ãã¬ããã·ãŒãã®æ°ã10ããå Žåãåèšã§40åãæããäœæ¥ã10åã§æžãäºã«ãªããŸããèªåã®å Žåãã·ãŒãæ°ã10ã§ã¹ãã¬ããã·ãŒãã®æ°ã30ãããçºãå šéšã§300åãåŒã³åºããããŠããã®ã30åã§æžãã ã®ã§å€§å¹ ã«æéã®åæžãåºæ¥ãã¿ã€ã ã¢ãŠãããªããªããŸããã
æ¡ä»¶ä»ãæžåŒèšå®ã掻çšãã
ãŠã§ãäžã®Google Apps Scriptã®é«éåã«é¢ãããµã³ãã«ã³ãŒãã«æŒããŠãã»ã«ã®è²ãè²ã åŒãäŸãåºãŠããŸããããã®ã³ãŒãã¯ãŸã Google Spreadsheetã«ãŸãšããªæ¡ä»¶ä»ãæžåŒèšå®ããªãæä»£ã®ãã®ã§ããçŸåšã¯ãæ¡ä»¶ä»ãæžåŒèšå®ã«æŒããŠæ°åŒãããããªäœ¿çšããäºãã§ããã®ã§ãã¹ã¯ãªããã§ãããã£ãäœæ¥ãè¡ãã·ãŒã³ã¯ã°ããšæžã£ãŠããŸãããã£ãŠãããžã¥ã¢ã«é¢ã§ãããããã£ãã³ãŒããå©çšããŠãå Žåãã¹ã¯ãªããã¯èŸããŠãæ¡ä»¶ä»ãæžåŒèšå®ã䜿ãããã«ããŸãããã
ãã®åãéèšåŸã«äœèšãªäœæ¥ãã¹ã¯ãªããã§ããå¿ èŠæ§ããªããªããŸãã
setFormulaãæŽ»çšããã
ããŒã¿ã®ååŸå ã®æ°ã ã®ã·ãŒãã«ã¯äŸãã°ååã³ãŒããšããã«çºããã«ã©ã ããã¹ã¿ãšããåœ¢ã§æ ŒçŽãããŠããšæãããŸãããããããããã¯æ±ºããŠå¹çã®è¯ãããæ¹ã§ã¯ãããŸããããããŸã§ãããããŠãã®ã¯äººéãèŠãæã«ããããããããçºã«ããããŠãã®ã§ãã£ãŠãããŒã¿ååŸäžã¯ã³ãŒããšæ°éã®ãããªããŒã¿ã®ã¿ã§è¯ãã¯ãã§ããããããããã ãã ãšéèšè¡šãäœã£ãæã«èŠãç®ãæ°åã®çŸ åã§åããã«ããã
ãšããäºã§æŽ»çšããã®ãsetFormulasã§ãããã®é ç®ã¯ãã§ã«ãèµ·åæã«é¢æ°ãèªåè£å®ãããã«ãŠç޹ä»ããŠããã®ã§ãããããå©çšããããšã§ä»¥äžã®æ¹åãèŠèŸŒããŸãã
- ããŒã¿ååŸå ããã¯æäœéã®ã«ã©ã ã®ã¿ã«çµã£ãŠããŒã¿ã®å¡ãååŸããã ãã§æžãã
- ååŸããããŒã¿é åã«åãå ãã圢ã§ãsetFormulasã«ãŠæ°åŒãæ¿å ¥ããŠãããŠãããã
- ããŒã¿éèšã·ãŒãã«ãã¹ã¿ãŒãèšããŠãããsetFormulasã«ãŠãvlookupãããããã«ããŠããã
- setFormulasãå ããé åããŒã¿ãã·ãŒãã«æžã蟌ãäºã§ã¹ããŒãã¢ããã«ç¹ãã
éèšåŸã¯ãvlookupåŒã«ãã£ãŠèªåçã«èªåãæã£ãŠããã¹ã¿ã®å€ãã²ã£ã±ã£ãŠããŠãããã®ã§ãããã«ãã£ãŠå šéšã®ã«ã©ã ãååŸãããã®ãåçŸããäºãã§ããŸããååŸã«ã©ã æ°ãå°ãªããã°æžã蟌ãã¹ããŒãã®åäžã«ãç¹ããã®ã§ãéèšã·ãŒãã«ããã¹ã¿ã·ãŒããæãããããã«ããŸãããã
垳祚é¡ãçæããæã®æ³šæç¹
ã¹ãã¬ããã·ãŒãã®ã¬ã³ãŒãããŒã¿ãå ã«ãäŸãã°çŽåæžã§ãã£ããç³èŸŒæžãç³è«æžé¡ããã³ãã¬ãŒãã®ã¹ãã¬ããã·ãŒãã®è©²åœã®ç®æã«setValueããŠPDFåãšãã£ããããªäœæ¥ãããŠã人ã¯çµæ§ãããšæããŸããèªåããã®æã¯å®ã«ããå©çšããŸããããããããã«ãæ¹åç¹ããããŸãã
- åã»ã«ã«å¯ŸããŠsetValueãããå Žåãå ¥åããé ç®ã®æ°ã«æ¯äŸããŠAPIã®åŒã³åºãåæ°ãå¢ããäºã«ãªãã
ãã©ãŒã ã®é ç®ã10ããå ŽåãsetValueã§ãã³ãã¬ãŒãã·ãŒãã«æžã蟌ãåæ°ã¯ã10ãšãªãããã§ããããã§ä»¥äžã®ä»æ§ã«å€æŽããäºã§ã©ããªã«ãã©ãŒã é ç®ãå¢ããããšããæžèŸŒåæ°ã1ã«ããäºãå¯èœã§ãã
- å€ããã³ãã€ã³ãã«ã»ã«ã«å ¥ããããïŒã¬ã³ãŒãå ¥ããŠãåç §ãããããã«ãã
ãšããäºã§ãã垳祚ã®ã¡ã€ã³ç»é¢ãšã¯å¥ã®ã·ãŒããçšæããããã«1ã¬ã³ãŒãå ¥ããŠãããã ããã¡ã€ã³ç»é¢ã¯ãã®ã¬ã³ãŒãããæ°åŒã§åŒã³åºãã°ããã ãã§ããã§ãã®ã§ã1ã¬ã³ãŒãããã³ãšå ¥ããã ãã§åž³ç¥šå®æã«ãªãã¹ããŒãã¢ããã«ç¹ãããŸãããã¡ãããPDFã«ããæã¯ãã©ã¡ãŒã¿ãšããŠã¡ã€ã³ç»é¢ã®ã·ãŒãã®ã¿ãæå®ããŠäžããã°åé¡ãããŸããã垳祚å°å·ç³»ã§ã¯å¿ é ã®ãã¯ããã¯ã§ããã
â»çŽåæžã®ãããªè€æ°ã¬ã³ãŒãã®å Žåã¯ããïŒã·ãŒãçšæããŠããã«ããŒã¿ãæžãå ããã¡ã€ã³ç»é¢ã§ã¯Filter颿°ã§ããŒã¿åŒã³åºãããã«ãããšè¯ãã§ãããã
ã¹ã¯ãªããããªã¬ãŒã§äºãèªååŠçããããŠãã
ã·ãŒãããŒã¿ã®ååãïŒïœ10ãŸã§ããã®ã§ã¯ãªããäºãã¹ã¯ãªããããªã¬ãŒã§æ·±å€ã«ã§ãèªåã§ãã¬åŠçãããããŠããŸããšããæ¹æ³ãã¢ãªã§ããããããŒã¿åéã·ãŒãåŽã¯ãã®çµæãæŸãã°è¯ãã®ã§ãçµæçã«ã¹ããŒãã®åäžã«ç¹ãããŸãïŒããŒã¿ã«ã®äœæ¥æéã¯å€ãããªãããã§ãããã¿ã€ã ã¢ãŠãããã«æžãã®ãšãå®éã«äœæ¥ããŠãæéã¯ã¹ã¯ãªããããªã¬ãŒã«ããèªåå®è¡ãé€ããæéã«ãªãããã§ãããïŒã
åœããåã§ãããã¹ã¯ãªããããªã¬ãŒã§ããããåŠçãã¿ã€ã ã¢ãŠãã«ãªãã®ã§ããã°ç¡æå³ã§ããã¹ã¯ãªããããªã¬ãŒã§ããããåŠçã¯æ¯èŒçå°èŠæš¡ãªã¢ãã«çããŠãããŸãããããŸããã¹ã¯ãªããããªã¬ãŒã¯ãŠã§ãã¢ããªã±ãŒã·ã§ã³ã®æãšåãã§ãgetActivveSpreadsheet()ã§ã¯ã·ãŒããåç §ã§ããªãã®ã§ãèªåèªèº«ã®ã·ãŒãIDãäºãã¹ã¯ãªããããããã£ãã°ããŒãã«å€æ°ã«é 眮ããŠããå¿ èŠæ§ããããŸãã
onEditã§ã¡ãã£ãšããåŠçã¯éæå®è¡ãããŠããŸã
Google Apps Scriptã«ã¯ãonOpenãdoGetã®ä»ã«ããonEditãšããç¹å¥ãªã€ãã³ããçšæãããŠããŸããããã¯VBAã§èšãæã®AfterUpdateã«çžåœãããã®ã§ããããããç·šéæã«å¿ ãå®è¡ãããŠããŸãé ç®ãªã®ã§ãç¹å®ã®ã»ã«ãåã§ã ãäœåããããšããäºãåºæ¥ãŸãããã·ãŒãå šäœã§ã©ããã§ç·šéããããšçºåããŠããŸããŸããæ ã«ãonEditãæŽ»çšããå Žåã«ã¯ã¡ãã£ãšããæ³šæãå¿ èŠã§ãã
ãã£ãŠãonEditã§çºåãããå 容ã«ã¯å¿ ããåç §ããŠãã»ã«ã®å 容ããå ã«ãæ¡ä»¶åå²ã§åŠçããèšè¿°ããªããã°ãªããŸããããŸããè² è·ã®é«ãåŠçãããããäºãåºæ¥ãŸãããã©ããåŒã床ã«å€§ããªåŠçãçºåããŠããŠã¯äœæ¥ã®éªéã«ãªã£ãŠããŸããŸãããã®é«éåã¯å°å³ã§å°èŠæš¡ãªãã®ãªã®ã§ãç©æ¥µçã«é«éåã®çºã«äœ¿ããšããäºã¯ããã䜿ãã·ãŒã³ãéžã³ãŸããããã³ãŒããšããŠã¯ä»¥äžã®æ§ãªæãã«ãªããŸãã
1 2 3 4 5 6 7 8 9 10 11 12 |
function onEdit(e) { //ç·šéãããã»ã«ã®ç¯å²ãååŸãã var cell = e.range; //Båã®ã»ã«ãç·šéããããçºåãã if (cell.getColumn() == 2) { ãã//å¥ã®ã·ãŒãã®Båã®å€ãšåãå€ãæã€ã¬ã³ãŒããåŠçãã ããvar row = cell.getRow(); //ã¬ã³ãŒããã³ããŒãååŸãã ã»ã»ã»ãïŒä»¥äžç¥ïŒãã»ã»ã» } } |
onEditã®åŒæ°eã«ã¯è²ã ãªã»ã«ã«é¢ããæ å ±ãè©°ãŸã£ãŠãã®ã§ããããåè§£ããŠå€å®ããããåŠçãå®è¡ããããã§ããgetColumnã§åçªå·ãgetRowã§è¡çªå·ãååŸã§ããã®ã§ãç¹å®ã®ã»ã«ã§ã®ã¿çºåãšãã£ãäºãå¯èœã§ãããã
ãããã£ãåŠçãè¡ã£ãŠããäºã§ãGoogle Apps Scriptå ã§ãã®åŠçã«è©²åœããéšåã¯åé€ãåºæ¥ãçµæçã«ã¹ããŒãã¢ããã«ç¹ãããŸãã
ãã®ä»ã®ãã€ã³ã
ãã®ä»ã®é«éåãã€ã³ããšããŠã以äžã®ãããªãã®ããããŸãã
- ã°ããŒãã«é åã«ããŒã¿ãæ ŒçŽããæ¹æ³ã¯ãã¹ããŒãã®åäžã«ã¯ãªããŸãããããå¯äœçšãšããŠãçŸæç¹ã§ã®ããŒã¿ãšååŸæã®ããŒã¿ã®éã«ä¹é¢ãçããå¯èœæ§ãããã®ã§ã䜿ãæã¯ããã»ã©å€ãã¯ãããŸããã
- JavaScriptã§ã§ããäºã¯ãã¡ãã§ããããããã«ããã
- é åå€ã®ãã§ãã¯ã«ã€ããŠãarray.indexOfããforã«ãŒãã§èª¿ã¹ãã»ããæ©ã
- ç¹å®ã®é ç®ã«ã€ããŠãã§ãã¯ãè¡ããããªã«ãŒãã³ã®å Žåãã©ã³ãã ãªããŒã¿ã®ãŸãŸæ±ãããäºãã¹ãã¬ããã·ãŒãããŒã¿ããœãŒãããŠãããŠããäœæ¥ãè¡ããããšãé床ã¢ããã«ç¹ãããŸãã
- ããŒã¿ã®ååç³»ããã°ã©ã ã§ã¯ãèªåèªèº«ã®ã·ãŒãã«importRange颿°ã«ãŠäºãä»ã®ã·ãŒãããªã³ã¯ãããŠãããšãããã¯ããã¯ãé«éåã«ãã©ã¹ã«ãªããŸãã
é¢é£ãªã³ã¯
- [GAS][ã¹ãã¬ããã·ãŒã]åŠçé床ãåäžããã«ã¯: éåŒãGoogle Apps Script
- GASã§Spreadsheetãæäœããèªåçãã¹ããã©ã¯ãã£ã¹
- ããã©ãŒãã³ã¹ã®è¯ãGASã®æžãæ¹ Best Practice
- Google Apps Scriptãéãããããã®ãã¹ããã©ã¯ãã£ã¹
- é åãé«éã«æ¢çŽ¢ããTips
- Google Visualization API Reference
- Query Language Reference (Version 0.7)
- Parallel processing in Apps Script
- GASã®åŠçé床ãé ããšè©å€ãªã®ã§èšæž¬ããŠã¿ã
- jQueryé«éåïŒåŠçé床ã10åäžãããã¯ããã¯20éž
- Googleã瀺ãJavaScriptã350åé«éåããç§èš£
- JavaScriptã®ã«ãŒãã¯ã©ããäžçªé«éãªã®ã
- 倿¬¡å é åã§ã®é£æ³é å(key:value)ãååŸããã³ãŒãäŸ
- [GAS] onEditã§ã®æ³šæç¹
- ãã€ã¢ãã°ã䜿ã£ãŠã¿ã€ã ã¢ãŠããåé¿ãã
- Google Apps Scriptã®6åã®å£ãçªç Žããããäžã€ã®æ¹æ³