Googleã¹ãã¬ããã·ãŒãã®ãã¯ãæ©èœã䜿ã£ãŠã¿ã
2018幎4æ11æ¥ãGoogleã¹ãã¬ããã·ãŒãã«å€§ããªæ©èœè¿œå ããããŸããããããããã¯ãã®èšé²ãæ©èœãExcelã§ã¯ãã¯ãããªãã¿ã®ããŠãŒã¶ãæäœããå 容ãèšé²ãããã¿ã³äžçºã§ãã®æäœãåçŸããæ©èœã§ããGoogleã¹ãã¬ããã·ãŒããªã®ã§ãèšé²ããæäœã¯Google Apps Scriptã«å€æãããŠä¿åãããŸãã
ãŸããä¿åããæäœã¯ãã®ãŸãŸæ¹é ããããšãå¯èœãªã®ã§ãGoogle Apps Scriptã§ïŒããæ§ç¯ããããšã¯é£ããããã©ãã¡ãã£ãšãªãçŽãããšãããã³ããã°ã©ããªäººã«ãšã£ãŠããŸããªãã£ã¹äºåäœæ¥ã®èªååã«å€§ããè²¢ç®ããæ©èœãå®éã«äœ¿ã£ãŠã¿ãããšæããŸãã
ç®æ¬¡
ä»å䜿çšããã¹ãã¬ããã·ãŒã
â»çŸåšã®æãå°å·çšã®ã¡ãœããã¯ç¡ãã§ãããã·ã§ãŒãã«ããããŒã§äŸãã°ã¡ãã¥ãŒæäœãããŠãèšé²ã«ã¯ãããŸããã§ããã
â»åœããåã§ããããŸã ã¢ãããéããŠããŠããªãã¢ã«ãŠã³ãã§éããŠããã¡ãã¥ãŒã«ãã¯ããèšé²ã¯è¡šç€ºãããŸããããŸããG Suiteã®å Žåã管çè ãèšç»çãªãªãŒã¹ã«ããŠããå Žåãæå€§2é±éãããã¯ã¢ãããæ¥ãŠã衚瀺ãããŸããã
â»ãã¯ãã®æ©èœã§ãããChromeãšSafariã§åäœæ€èšŒããŠåããŠãããšã確èªããŠããŸãã
å®éã«äœã£ãŠã¿ã
G Suiteåãã«ãšããäºã§ãããã4æ17æ¥çŸåšãèªåã®G Suiteã¢ã«ãŠã³ãã«ã¯ã¢ãããéããŠããŠããŸããããå人ã®Googleã¢ã«ãŠã³ãã®Driveã§ã¯ãã¯ããã䜿ããããã«ãªã£ãŠããŸããã®ã§ãG Suite以å€ã®ãŠãŒã¶ãå©çšåºæ¥ãããã«ãªã£ãŠããããšã確èªããŠããŸãã
æäœãèšé²ãã
æäœéå§ãããã«ã¯ãã¡ãã¥ãŒã®ãããŒã«ãâããã¯ããâããã¯ããèšé²ããã¯ãªãã¯ããã®åŸãå®éã«æäœãããŠã¿ãŸãããã®èšé²ããå 容ã¯ããã¯ã°ã©ãŠã³ãã§ãGoogle Apps Scriptã«å€æãããŠèšé²ãããŠããŸããã§ãã®ã§ãã¹ã¯ãªãããšãã£ã¿ã§èŠããšãäžé£ã®èšé²å 容ãèŠãäºãåºæ¥ãæ§ã«ãªã£ãŠããŸãã
èšé²ãããåŸããä¿åãã¿ã³ããæŒããŠãååãä»ããã ããåºæ¬Excelã®ãã¯ããšæäœæã¯äžç·ã§ããã§ãããVBAã®ãããªè±å¯ã§ããªãããŒãªäºãåºæ¥ããïŒãšãã£ãããGoogle Apps Scriptã§å¯Ÿå¿ããŠããªãã¡ãœããã®ãã®ã¯èšé²ã¯åºæ¥ãŸãããåºæ¬çãªæäœã¯ã»ãŒå šãŠåºæ¥ãã¿ããã§ãã
絶察åç §ãšçžå¯Ÿåç §ã®ïŒã€ããããŸããã絶察åç §ã¯äŸãã°ç¯å²éžæã§ã¯ãã©ããªã·ãŒãã§ãå¿ ãåãç¯å²ãæå®ããã®ã«å¯ŸããŠãçžå¯Ÿåç §ã¯èªåã§éžæããé åå ã§èšé²ãããããã§ããéåžžã¯çµ¶å¯Ÿåç §ã§OKã§ãããã
â»äŸãã°è¡šã«çœ«ç·ãå ããã¿ã€ãã«ã«è²ãä»ããäœèšãªåãåé€ããã¿ãããªæäœã¯OKã§ãããäœããA1ã«äžåºŠã«ãŒãœã«ãæã£ãŠãã£ãŠã¢ã¯ãã£ãã«ããç¶æ ã§èšé²ãéå§ããªããšäžæããããªãã·ãŒã³ããã£ãã®ã§ããããã¡ãã£ãšçããããŸããã
å³ïŒãã¯ããèšé²äžã»ã»ã»
ãã¯ããå®è¡ãã
ãã¯ãã¯ååãã€ãããšããããŒã«ãâããã¯ããã®äžã«æ¢ã«ä»ããååã§ååšããŠããŸãã®ã§ããã®ãŸãŸå®è¡ããã ãã§ããäœããèšé²ã®ä»æ¹ã«ãã£ãŠã¯ããããªãèµ€åã§ãšã©ãŒãã§ããããŸãããŸã ã¡ãã£ãšããªããŠããªããªããšããå°è±¡ã§ããæåã®ïŒåã ãã¯ãæ¿èªããŠãã ãããŠã£ã³ããŠãåºãç¹ã¯ãGoogle Apps Scriptãšåãã§æåãåãã§ãã
å³ïŒã¯ãªãã¯ããã ãã§ãã¯ãã¯å®è¡ãããŸãã
ãã¯ãã管ç
äœæãããã¯ãã®ç®¡çïŒåé€çïŒããã·ã§ãŒãã«ããããŒã®å²åœã¯ããã¯ãã¡ãã¥ãŒã®äžã®ããã¯ãã管çããå®è¡ããŸãããã€ã¢ãã°ãã§ãŸãã®ã§ãåé€ãããå Žåã¯ã察象ã®ãã¯ãã®ãïžããã¯ãªãã¯ããŠãåé€ãéžã¹ã°èšé²ãããã¯ãã¯åé€ãããŸãããŸããã·ã§ãŒãã«ããããŒã§ãããMac OS Xã®å Žåã¯ãOption + Shiftã«ç¶ããŠæ°åã ããä»ã®ãšãã察å¿ããŠãããã§ãèªåã§èšå®ãå¯èœã§ãã
â»äœãèšé²ããGoogle Apps Scriptã¯åé€ãããªãããã§ããã¹ã¯ãªãããšãã£ã¿ãéããšæ®ã£ãŠãŸããã
å³ïŒããã«ãºã©ããšèšé²ããå 容ãã§ãŸã
ãã¯ããã€ã³ããŒã
ããã¯ãèªåã§äœæããGoogle Apps Scriptã®ã³ãŒãããã¯ããšããŠã€ã³ããŒãïŒç»é²ããæ©èœã§ãã詊ãã«ã¡ãã»ãŒãžããã¯ã¹ãåºãã ãã®é¢æ°ãèªåã§æžããŠåã蟌ãã§ã¿ãŸããã
ãã¯ãã¡ãã¥ãŒã®äžã®ãã€ã³ããŒãããå®è¡ãããšãèªåãæžãè¶³ããã³ãŒãã®ååãåºãŠããã®ã§ãã颿°ã远å ããã¯ãªãã¯ããŸããããã§ãã¯ããšããŠç»é²ãããŸããããã¯ãã§ãã®ã§ããã¯ãã®ç®¡çã§ã·ã§ãŒãã«ããããŒãå²ãåœãŠãã°ãèªåãäœæããã¹ã¯ãªããã«ã·ã§ãŒãã«ããããŒãå²ãåœãŠãããŸããããã¯ã¡ãã£ãšäŸ¿å©ã§ããã
å³ïŒãã¯ãã®ã€ã³ããŒãç»é¢
äœããããã¯ãã®æ£äœ
ã¹ã¯ãªãããšãã£ã¿ã§èŠãŠã¿ã
ãã¯ãã¯ã¹ã¯ãªãããšãã£ã¿ãéããšé²èЧã»ç·šéãå¯èœã«ãªã£ãŠããŸããå šãŠmyFunctionã«æ°åãã€ãã圢ã§Functionåãä»ããããŠããã衚ã§ãã¯ããåé€ããŠããã³ãŒãèªäœã¯æ®ãããŠããŸãããŸãããã®ã³ãŒãã®åé ã«ã¯ã/** @OnlyCurrentDoc */ããšããæååãä»ãå ããããŠããŸãã
ããŠããã¯ãã®ã¡ãã¥ãŒã«ç»é²ããŠãå Žæãªã®ã§ãããã¹ã¯ãªãããšãã£ã¿ã§éããæã«åºãŠãããappsscript.jsonãã®äžã«èšè¿°ãããŠããŸããã¡ãã¥ãŒããã衚瀺ãâããããã§ã¹ããã¡ã€ã«ã衚瀺ããã¯ãªãã¯ããã°ãåºçŸããŸãã
ãã®appssript.jsonã®äžèº«ã¯ä»¥äžã®ãããªã³ãŒããèšè¿°ãããŠããŸãã
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
{ "timeZone": "America/New_York", "dependencies": { }, "exceptionLogging": "STACKDRIVER", "sheets": { "macros": [{ "menuName": "æŽåœ¢ãã¯ã", "functionName": "myFunction2" }, { "menuName": "helloscript", "functionName": "helloscript" }] } } |
sheetsã®äžã«ããmacrosã«ååãšå®è¡ãã颿°åãç»é²ãããŠããŸãããæåã§ããã«èªåã®é¢æ°ãç»é²ãã§ãããã§ãããèªåãç»é²ããhelloscriptããã¡ããšã€ã³ããŒããããŠããŸããããã¯ã.gsã®äžã§ãªããšã倧äžå€«ã¿ããã§ããã
å³ïŒãã¯ã.gsãšããŠè¿œèšãããŠãŸã
ã¹ã¯ãªããããªã¬ãŒã䜿ãã°ã»ã»ã»
éåžžãã¯ããšãããã®ã¯ããŠãŒã¶ãã¢ã¯ã·ã§ã³ãèµ·ãããŠããå®è¡ãããŸããExcelã®ãã¯ããåºæ¬ãŠãŒã¶ããã¯ããå®è¡ãããããããã°ã©ã ãèµ°ãããã§ããããããGoogleã¹ãã¬ããã·ãŒãã®ãã¯ãã¯ããã ãã§ãªããã¹ã¯ãªããããªã¬ãŒã䜿ãäºã§ãäŸãã°ã宿çã«åæã«èªåå®è¡ããããšãã£ãäºãå¯èœã«ãªããŸããããœã³ã³ãèµ·åãããŠããå¿ èŠããããŸããã
ã¹ã¯ãªããããªã¬ãŒã§äŸãã°æ¯æ¥0æã«å®è¡ã§ããã°ã
- ã¹ã¯ãªãããšãã£ã¿ãéã
- ã¡ãã¥ãŒãããç·šéãâãçŸåšã®ãããžã§ã¯ãã®ããªã¬ãŒããéã
- ããªã¬ãŒãèšå®ãããŠããŸããã衚瀺ããããããããã¯ãªãã¯
- å®è¡ã«ãã¯ãã®é¢æ°ãæéäž»å°åã®ãæ¥ã¿ã€ããŒããšããåå0æã1æãéžæããŠä¿åãã
ããã§ãæ¯æ¥0æã«äœæãããã¯ããå®è¡ãããŸããäœããã¹ãã¬ããã·ãŒããéããŠããªãç¶æ ã§å®è¡ããäºã«ãªãã®ã§ãæçŽããå¿ èŠã«ãªãéšåãåºãŠããŸããSpreadsheetApp.getActiveScpreadsheetã§ã¯ãªããäºãã¹ãã¬ããã·ãŒãã®IDã倿°ã«å ¥ããŠãããŠãSpreadsheetApp.openById(id)ãšçœ®ãæããŠããã°åé¡ãªãã§ããããäžç«¯éããŠãã¹ãã¬ããã·ãŒããéããŠããªãç¶æ ã§ãçãã®ããªã¬ãŒã§ç¡äºåããïŒãã§ãã¯ããŠãã䜿ããŸãããã
å³ïŒãã¯ãã«ããªã¬ãŒã®åãæ
åç»è³æ
ãã¯ãã®äœæãå®è¡ããããŠãã®äžèº«ãèŠãŠã¿ãæ§åã®åç»ã§ããé³å£°ãšãããããã¯ä»ããŠããŸããããã¯ãã¯äœæåŸã¯Google Apps Scriptãã®ãã®ãªã®ã§ãå®è¡ã¯ãã£ãšããéã§ãã
é¢é£ãªã³ã¯
- æ°žåã·ã¹ãã ãExcelãã¯ããã°ãŒã°ã«ãG Suiteãã«ç§»è¡ãããµãŒãã¹éå§
- Google ã¹ãã¬ããã·ãŒãã§ã¿ã¹ã¯ãèªååãã
- Think macro: record actions in Google Sheets to skip repetitive work
- Additional details on new macro recorder and formatting options in Google Sheets
- What's New in G Suite