æ°ããGoogle Formãæ¥åã«æŽ»çšãã â ãã®â¡
G Suiteã®äž»èŠãªã¢ããªã±ãŒã·ã§ã³ã¯Microsoft365ãšã¯éããGoogle Apps Scriptã§éçºãããæ©èœã匷åããäºãå¯èœã§ãããã®Google Formãåãã§ãããªãªãžãã«ã«ã¯ãªãæ©èœãä»ãå ããäºã§ãæŽã«G SuiteãæŽ»çšåºæ¥ãã°ããã§ã¯ãªããå šèªåã§äººéãè¡ã£ãŠããäœæ¥ãããããäºãåºæ¥ãã®ã§ãå©çšè ãå€ããã°å€ãã»ã©ãåæžåºæ¥ãåŽåãæéã»ã³ã¹ãã¯å€§ããã§ãã
ä»åã¯ãååã®æŽ»çšãããã®â ã«åŒãç¶ããããã«Google Apps Scriptã§åŒ·åããè²ã ãªäœæ¥ãããããŠã¿ãããšæããŸãããã®ãã¯ããã¯ã¯Formã ãã§ãªããèªäœã®ãã©ãŒã é¡ãä»ã®ã¹ã¯ãªããã§ãå¿çšã®åºæ¥ããã®ã§ãã
ç®æ¬¡
ä»å䜿çšãããã¡ã€ã«
æ¬ãã©ãŒã ã¯èªåå¿çæ©èœä»ãGoogle FormãšããŠãµã€ãã§ãªãªãŒã¹ãããŠãããã®ãšåããã®ã§ãããã©ãŒã èªäœã®äœ¿ãæ¹ãã现ããªèšå®é¢ä¿ãäºåæºåã«é¢ããŠã¯ãã¡ãã®ããŒãžãåç §ããŠãã ããã
éä¿¡æã¹ã¯ãªããã䜿ãçºã«ã¯
ããªã¬ãŒã®èšçœ®
ä»å玹ä»ããŠããã¹ã¯ãªããé¡ã䜿ãçºã«ã¯ãå¿ ãããªã¬ãŒã®èšçœ®ãå¿ èŠã«ãªã£ãŠããŸããããªã¬ãŒèªäœã«é¢ããŠã¯ãŸãå¥ã®ãšã³ããªãŒã§è©³ãã玹ä»ããäºå®ã§ãããããã§ã¯å¿ èŠæäœéããã ãã¯ãšããããªã¬ãŒã®èšçœ®ã«é¢ãã解説ãããŠã¿ãŸãã
ä»åèªåäœæ¥ããã颿°ã¯sendForms()ãšãã颿°ã§äœæããŠããŸãã以äžã®æé ã§ã¹ã¯ãªããäœæåŸã«ããªã¬ãŒãèšçœ®ããŠãã ããã
ããã§æ³šæããªããã°ãªããªãã®ã¯ããã®ããªã¬ãŒèšçœ®ã¯ãã®ãã©ãŒã ã®ç®¡çè ã ããè¡ã£ãŠäžãããä»ã®æ¹ããã£ãŠããŸããšäºéã«ããªã¬ãŒãèšçœ®ãããŠããŸããŸãããã©ãŒã ã¯ä»ã®äººããã¯ä»ã®äººã®ããªã¬ãŒãèŠããªã仿§ãªã®ã§ãããªã¬ãŒåé€ããã®äººã§ãªããã°åºæ¥ãŸãããæ°ãä»ããŸãããã
- ãã©ãŒã ã®å³äžã®ãïžããã¯ãªãã¯ãããã¹ã¯ãªãããšãã£ã¿ããéããŸãã
- ã¡ãã¥ãŒããããç·šéãâãçŸåšã®ãããžã§ã¯ãã®ããªã¬ãŒããéããŸãã
- ããªã¬ãŒã空ãªã®ã§ããªã³ã¯ãã¯ãªãã¯ããå®è¡ã«ãsendformsããã€ãã³ãã«ããã©ãŒã ããéä¿¡æãã«ãŠã»ããããŸãã
å³ïŒã¹ã¯ãªããããªã¬ãŒããªããã°åäœããŸãã
äºééä¿¡ããããšèšãããã±ãŒã¹ã«ã€ããŠ
ããæ°å¹ŽQAãµã€ãçã§ãäºéã«ã¡ãŒã«ãéä¿¡ãããããå 容ãå ¥ãéã£ãŠéä¿¡ãããã»ã»ã»ããšããæ¹ããèŠãããŸãããã®æ¹ã ã®ã³ãŒãããã¹ãã¬ããã·ãŒãåŽã§èšè¿°ããããŒã¿ãå ¥ã£ãŠãããåŠçãããããšãã£ãã³ãŒããæžãããŠããŸãããæšå¥šããŸããã
çç±ã¯
- Formãåæã«å©çšããå Žåã«ãè€æ°ã®åŠçãæ¢ãŸãããšãªãã¹ãã¬ããã·ãŒãã«å ¥ã£ãŠããã
- æä»å¶åŸ¡ãããŠããªãçºãäŸãã°ã¹ã¯ãªããããããã£ãªã©ã®å€ãäžæžããããŠãå ¥ãéããçããã
- 次ã ã«å ¥ã£ãŠããã®ã§ãåç §ãã¹ãã¬ã³ãŒããæ³å®ããã¬ã³ãŒãã§ã¯ãªããäžçªæåŸã«å ¥ã£ãŠããã¬ã³ãŒãã«ãªã£ãŠããã
- ãããããã¹ãã¬ããã·ãŒãåŽã®Forméä¿¡æã¯èªåå¿çã¡ãŒã«ãéãã®ã«é©ããŠããªãã
- Formã«èšè¿°ããææ³ãšéãéåžžã«ã³ãŒããç ©éã«ãªãïŒFormAppã¯ã¬ã³ãŒãã®äœçœ®ãªã©æ°ã«ããå¿ èŠããªãïŒ
FormAppã¯éã£ãããŒã¿ã確å®ã«ååŸããã¡ãŒã«ãéã£ãŠããã¹ãã¬ããã·ãŒãã«æžã蟌ã¿ãããŠããŸããããããã®ããã»ã¹ã¯ç¬ç«ããŠããŸããããããã¹ãã¬ããã·ãŒãåŽã«ããŠããŸããšäžŠåããŠããã€ãã®ã®åŠçãåæã«å ¥ã£ãŠããïŒããã»ã¹ã¯ç¬ç«ããŠããªãïŒã®ã§ãæ³å®å€ã®åãã«ãªããŸããæ¬ã³ãŒããè£ åãããªãã°ãFormAppã§å®è£ ããŸãããã
FormApp.getActiveForm ãåŒã³åºãæš©éããããŸãã
æè¿ãŸã仿§ãå€ãã£ããããªã®ãïŒæ¬¡é ã®èªåå¿çã®æ©èœãã€ãããã©ãŒã ãäœæãããã®ã®ããã©ãŒã ãéä¿¡ããŠãèªåå¿çãäœåããã«ãFormApp.getActiveForm ãåŒã³åºãæš©éããããŸããããšãããšã©ãŒãšãšãã«ãGoogle Apps Scriptããšã©ãŒãåããŠåæ¢ãããšããã±ãŒã¹ã«ééããŸããã
ã³ãŒããèŠãã°ãããã®ã§ãããFormAppã«é¢ããŠã®èšè¿°ã¯å ã ãããŸããã以åã¯ããã§ãåé¡ãªãåäœããŠããã®ã§ãããåå ãç©¶æããŠã¿ããšãã©ãããFormAppã¯äœ¿ã£ãŠããªããã®ã®ãFormAppã®æš©éãç¡ããšåããªããšãããå®ã¯äŒŒããããªã±ãŒã¹ã«éå»Google Pickerã®ã¢ããããŒãå®è£ ã§ãééããŠããŸãã
察å¿çãšããŠã¯ãã³ã¡ã³ãã¢ãŠãã®åœ¢ã§ãããŒã§ã//FormApp.getActiveForm()ããšããã³ãŒãããããŠãå®è¡ãæš©éæ¿èªãçŽãã°OKãPickerã®å Žåã//DriveApp.addFile("test")ãšãã£ãæžãèŸŒã¿æš©éã®ãããŒã³ãŒããå«ããããšã§ããã©ãã«ãåé¿ããŠããŸãã
æèŒããŠããæ©èœ
èªåå¿çã¡ãŒã«æ©èœ
çŸåšã®Google Formã«ã¯ãã¢ã³ã±ãŒãéä¿¡æã«ãåçã®ã³ããŒãéä¿¡ãããæ©èœãåãã£ãŠããŸãã以åã¯ãªãã£ãæ©èœã§ãããããã§èªåå¿çã¡ãŒã«çãªåœ¹å²ã¯ç°¡æçã«ã¯å¯èœã§ãããããããããŸã§ç°¡ææ©èœã§ããããã®äžã®ã¡ãã»ãŒãžã§ãã£ããããã¶ã€ã³çãã«ã¹ã¿ãã€ãºåºæ¥ãããã§ããªãã®ã§ã䟿å©ãªãã®ã§ã¯ãããŸããã
ããã§è£ åãããã®ãããã£ããããèªåå¿çã¡ãŒã«éä¿¡æ©èœãã§ããå°ãåçã®ã³ããŒãéä¿¡ã§è¡ãããå Žåã«ã¯ä»¥äžã®æé ã§ã¹ã¯ãªããç¡ãã§è£ åãå¯èœã§ãã
- å³äžã®æ¯è»ãã¯ãªãã¯ãã
- å šè¬ã«ãŠãã¡ãŒã«ã¢ãã¬ã¹ãåéãããããã§ãã¯ãã
- ãåçã®ã³ããŒãéä¿¡ãããããã§ãã¯ãã
å³ïŒãã ãã®æ©èœã¯ã¡ãã£ãšäžäŸ¿ãªãã§ãããã»ã»ã»
èªåå¿çã¡ãŒã«ã¯ãHTMLã¡ãŒã«ã®åœ¢åŒã§äœæãããããã§ããããã®çºã«ã¯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 |
function sendForms(e){ //倿°ã宣èšãã var user = ""; //ã¡ãŒã«ã¢ãã¬ã¹æ ŒçŽçš var body =""; //æ¬æçšå€æ° //ãã©ãŒã ããŒã¿ãååŸãã var formdata = e.response.getItemResponses(); //ã«ãŒãã§ã¡ã¢ãååŸïŒå
¥åå€ãæ¬æã«å ãã for (var j = 0; j < formdata.length; j++){ //å
¥åé
ç®åãååŸãã var title = formdata[j].getItem().getTitle(); //ã¡ãŒã«ã¢ãã¬ã¹ãååŸãã if(title == "ã¡ãŒã«ã¢ãã¬ã¹"){ user = formdata[j].getResponse(); } //å
¥åé
ç®ãå
¥ããïŒã¿ã€ãã«ãšå
¥åå€ïŒ body += "ã"+formdata[j].getItem().getTitle()+"ã<br>"; body += formdata[j].getResponse()+"<br><br>"; ïœ //æ¬æä»¥å€ã®é
ç®ãäœæãã var footer = '<br>' + "æ¬ä»¶ã«é¢ãããåãåããã¯ãâââéšïŒé»è©±çªå·ïŒ03-1259-96414ïŒãŸã§ãé£çµ¡ãã ããã<br>"; var top = "ãâââããåãä»ããŸããã倧å€ã¢ãªã¬ããŠããããŸãã<br>" + "ç³è«å
容ã確èªã»æ¿èªåŸãæ¹ããŠãé£çµ¡èŽããŸãã<br><br>" + "ãé£çµ¡äºé
ã<br>" + "ã»æ¬ã¡ãŒã«ã«ãŠè¿ä¿¡ããå Žåã¯ãâå
šå¡ã«è¿ä¿¡âã«ãŠéä¿¡ãã ããã<br><br><br>"; //ã¡ãŒã«å
容ãã€ãªãåããã body = top + body + footer; //ã¡ãŒã«ãéä¿¡ãã MailApp.sendEmail({ to: user, subject: "ãåãåãã確èª", htmlBody: body, name:"èªåå¿çã¡ãŒã«", }); } |
ã¡ãŒã«ã®SubjectãNameïŒã¡ãã»ãŒãžå 容ã¯ã¹ã¯ãªããå ã«çŽæ¥æžãã®ãè¯ãã®ã§ãããã¡ã³ããã³ã¹ããªãŒã«ããçºã«ããã®ããããUIãèªåã§å®è£ ããŠèª°ã§ã倿Žåºæ¥ãããã«ãããšå°è¯ãã§ããããïŒæ åœè ãå€ãã£ãçšåºŠã§ãœãŒã¹ãçŽããªããšè¡ããªãã®ã¯äžäŸ¿ã§ãããïŒã
æžé¡èªåçææ©èœ
äŸãã°ç³è«ãã©ãŒã ãªã©ã®å Žåããã®ãŸãŸã§ã¯ã¹ãã¬ããã·ãŒãã«1è¡ç³è«ããŒã¿ã远å ãããã ãã§ãããŒããŒã¬ã¹ãªãã°ããã§ãè¯ãã®ã§ãããäŒç€Ÿå ã§å°å·ç©ã§å€åãå¿ èŠãšãã£ãå Žåã«ã¯ãããŒã¿ããå¥ã«ç³è«æžãäœæããªããã°ãªããŸããããã®äœæ¥ã人éãè¡ãã®ã¯éçç£ç以å€ã®äœè ã§ããªãã®ã§ãäºãçšæããŠæŒãããã³ãã¬ãŒããã³ããŒããŠãããã«ããŒã¿ãæžã蟌ãŸããäºã§æžé¡ãèªåçæãããŠããŸãã®ãè¯ãã§ãããã
ãã®åŸã®é ç®ã®PDFåãããã«ãå¿ é ã§ããGoogleããã¥ã¡ã³ãã§ãè¯ãã®ã§ãããGoogleã¹ãã¬ããã·ãŒãã®ã»ãããã现ããªã¬ã€ã¢ãŠããäœæãæãã®ã§ãããã§ã¯ã¹ãã¬ããã·ãŒãã«ãŠè«žé¡ãèªåçæãããŠã¿ãŸãã
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 |
//芪ãã©ã«ãã®IDãè¿ãã©ããã³ã°é¢æ° function driveman(id){ ããããvar parentsfolder = DriveApp.getFileById(id).getParents(); ããããwhile (parentsfolder.hasNext()) { ããããããããvar folder = parentsfolder.next(); ããããããããvar idval = folder.getId(); ããããããããreturn idval; ãããã} } //ãã©ãŒã å
¥åå
å®¹ãæŸã£ãŠãããã¥ã¡ã³ããçæããæå®ãã©ã«ãã«æ ŒçŽããã«ãŒãã³ function docsgenerator(array) { ãããã//ãã³ãã¬ãŒããã¡ã€ã«ã®URLãååŸ ããããvar docurl = "ããã«ãã³ãã¬ãŒãã®URLã/editãŸã§å«ããŠå
¥ãã"; ãããã ãããã//æžé¡æ ŒçŽå
ãã©ã«ããæå®ãã ããããvar targetfolder = "ããã«æ ŒçŽå
ãã©ã«ãã®IDãå
¥ãã"; ãããã//åçš®å€æ°ã®å®£èšãšæ ŒçŽ ããããvar templatedocs = SpreadsheetApp.openByUrl(docurl).getId(); ããããvar movetarget = ""; ããããvar movefiles = ""; ãããã//ãã³ãã¬ãŒããã¡ã€ã«ãã³ããŒããŠIDãååŸãã ããããvar files = DriveApp.getFileById(templatedocs).makeCopy("ç³è«æž_temp"); ããããvar filesId = files.getId(); ããããvar parentsfolder = driveman(filesId); ããããvar tempparents = DriveApp.getFolderById(parentsfolder); ãããã//äœæãããã¡ã€ã«ã«ãã©ãŒã ããŒã¿ãdataã·ãŒããžæžã蟌ã ããããvar sheet = SpreadsheetApp.openById(filesId); ããããsheet.getSheetByName("data").appendRow(array); ãããã//ç§»åå
ã倿°ã«æ ŒçŽ ããããmovetarget = DriveApp.getFolderById(targetfolder); ããããmovefiles = DriveApp.getFileById(filesId); ããããtempparents.removeFile(movefiles); //å
ã®ãã©ã«ãããã¯åé€ ããããmovetarget.addFile(movefiles);ãããããããããããããã//æ ŒçŽå
ã«ãã¡ã€ã«ã远å ãããã//äœæããããã¥ã¡ã³ãã®IDãè¿ããŠããã ããããreturn filesId; } |
äºãçšæããŠãããGoogle Driveã®ãã©ã«ãã«ãã¡ã€ã«ãçæããŸããããŒã¿ã¯ã³ããŒããç³è«æžãã³ãã¬ãŒãã®ïŒã€ãã€ã®ã»ã«ã«æžã蟌ãã®ã§ã¯ãªããarrayã§åãåã£ãããŒã¿ãå¥ã®ã·ãŒãã«appendRowã§äžæ°ã«æžã蟌ã¿ãç³è«æžã·ãŒãããã¯æ°åŒã§ãããåç §ãã圢ã«ããŠããŸãããã®æ¹ãé«éã«æžã蟌ã¿ãå¯èœã§ãã
ãã¡ã€ã«ã®ç§»åã¯ã¡ãã£ãšãããããã§ãããäžåºŠçæãããã¡ã€ã«ã®èŠªãã©ã«ãããã¯removeFileããæ ŒçŽå ãã©ã«ãã«addFileããäºãå¿ èŠã§ããçæãããã¡ã€ã«ã®èŠªãã©ã«ããç¥ãçºã«ãdriveman()ãšãã颿°ãå¥ã«çšæããŠãããŸããææã«filesIdãæ¬ã«ãŒãã³ã«è¿ããŠäžããŠããŸãã
èªåPDFäœææ©èœ
èªåçæãããæžé¡ã¯ã¹ãã¬ããã·ãŒããªã©ã®ããŒã¿ã§ãããããæ¹ããã®å¯èœæ§ããã£ããã峿ã«å°å·ããããå Žåã«ã¯å°ã äžäŸ¿ã§ãããã®æžé¡ãPDFåããŠããŸããŸããããPDFåãããçºã®ã«ãŒãã³ã®è©³ãã解説ã¯ãŸãå¥ã®ãšã³ããªãŒã§ç޹ä»ããŸããããã§ã¯PDFåããçºã«ã¯ããããã«ãŒãã³ãçµããšããäºã ã玹ä»ããŸãã
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 |
//PDFãæ ŒçŽãããã©ã«ãã®IDãååŸããŠãã var folid = "ããã«Google Driveã®ãã©ã«ãã®IDãå
¥ãã"; //Access TokenãååŸãã var token = ScriptApp.getOAuthToken(); //çæããã¹ãã¬ããã·ãŒãã®IDãååŸããŠãã var fileid = "ããã«çæããã¹ãã¬ããã·ãŒãã®IDãå
¥ãã"; //çæåã«å¯Ÿè±¡ãã¡ã€ã«ã®ããŒã¿ååšç¢ºèªãã§ãã¯ãšç¡éã«ãŒã var chksheet = SpreadsheetApp.openById(fileId); var rangechk = chksheet.getSheetByName("ç³è«æž").getRange("ãã§ãã¯ããã»ã«çªå°").getValue(); while (rangechk == ""){ rangechk = chksheet.getSheetByName("ç³è«æž").getRange("ãã§ãã¯ããã»ã«çªå°").getValue(); } //ç³è«æžã·ãŒãã®ã·ãŒãIDãååŸãã var gid = SpreadsheetApp.openById(fileId).getSheetByName("ç³è«æž").getSheetId(); //PDFçæURLãçµã¿ç«ãŠã var url = "https://docs.google.com/spreadsheets/d/" + fileId + "/export?format=pdf&gid=" + gid + "&portrait=true&size=A4&gridlines=false&fitw=true"; //PDFããŒã¿ãåãåã var pdf = UrlFetchApp.fetch(url, ããããããããããããããããããã{headers: ãããããããããããããããããããããããã{'Authorization': 'Bearer ' + token} ããããããããããããããããããã}).getBlob().setName("ç³è«æžé¡.pdf"); //Google Driveã®æå®ãã©ã«ãã«ãã¡ã€ã«ãçæãã DriveApp.getFolderById(folid).createFile(pdf); |
PDFçæã¯ãç¹å¥ãªURLã«å¯ŸããŠãUrlFetchAppã¯ã©ã¹ã䜿ã£ãŠã¢ã¯ã»ã¹ãããBlob圢åŒã§PDFãšããŠååŸããŸãããã®éã«setNameã§ãã¡ã€ã«ã«ååãä»ããŠããŸãããŸãããã®åãåã£ãPDFããŒã¿ãDriveAppã¯ã©ã¹ã䜿ã£ãŠãã©ã€ãå ã«PDFãã¡ã€ã«ãçæãããŠããŸãã
éäžã®rangechkã§ãããæã ããŒã¿æžã蟌ã¿å®äºåã«PDFçæã«ã«ãŒãã³ãç§»åããŠããŸã£ãŠã空ã®ãŸãŸPDFãäœæãããŠããŸãããšãããã®ã§ãããã§æžã蟌ã¿ãã§ãã¯ãããç¡éã«ãŒããèšããŠãããŸãããã§ãã¯ããã»ã«çªå°ã¯äžçªææã«æžã蟌ãŸããæãæå®ãããšè¯ãã§ãããã
ã¡ãŒã«ã«æ·»ä»ããæã«ã¯ã以äžã®ããã«åãåã£ãpdfãattachmentã«å ããã°è¯ãã§ããè€æ°ã®ãã¡ã€ã«ãæ·»ä»ããæã«ã¯é åã§æž¡ããŠãããã°OKã§ãã
1 2 3 4 5 6 |
MailApp.sendEmail({ to: user, subject: subject, htmlBody: body, attachments:pdf, //PDFãæ·»ä»ããã }); |
èªåã§ã¹ãã¬ããã·ãŒãã«æžã蟌ã
Google Formã¯èªåã§ã¹ãã¬ããã·ãŒããžæžãèŸŒãæ©èœãæèŒãããŠããã®ã§ãããæžã蟌ãŸããæ å ±ã¯ãã©ãŒã äžã®æ å ±ã«éããããŸãæžã蟌ãŸããåã®äžŠã³é ãªã©ãåºå®ã§ããããã§ã¯ã¹ãã¬ããã·ãŒãã®äºæ¬¡å©çšãããäžã§ã¯å°ã å°ãã±ãŒã¹ããããŸãïŒã¯ãŒã¯ãããŒçãªã·ã¹ãã ãããåãåããçªå·ã§äœæ¥ããããããªã±ãŒã¹ïŒ
ããã§ãæžã蟌ã¿èªäœGoogle Formã«ããããã®ã§ã¯ãªããèªåã§ã¹ãã¬ããã·ãŒãã«æžãèŸŒãæ©èœãè£ åããäºã§ãããã«é«åºŠãªGoogle Formã®æŽ»çšãããäºãå¯èœã§ããäœããéä¿¡æ¥ä»ã«é¢ããŠã¯ãã©ãŒã éä¿¡å 容ããã¯ååŸãåºæ¥ãªãã®ã§ãnew Date()ã§èªåã§çæããŠãããå¿ èŠããããŸãã
ã¹ãã¬ããã·ãŒãæžã蟌ã¿
èªåã§æžã蟌ã¿ãããšãã£ãŠãããããŠé£ãããã®ã§ã¯ãããŸãããååŸãããã©ãŒã ã®ããŒã¿ã«ãŠããŒã¯IDãèªåçæããæžé¡ã®URLããŸãåŠçã¹ããŒã¿ã¹ã®åæå€ãªã©ãèªåã®å Žåæžã蟌ãŸããŠãŸãããåºæ¬ã¯é åãçšæããŠæµã蟌ãã ãã§ãã
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 |
function sendForms(e){ //æžã蟌ã¿çšé
åãçšæãã var array = []; //æžã蟌ã¿å
ã¹ãã¬ããã·ãŒããååŸãã var sheet = SpreadsheetApp.openById("å€ãæžã蟌å
ã¹ãã¬ããã·ãŒãID"); var ss = sheet.getSheetByName("å€ãæžã蟌ã¿å
ã·ãŒãå"); //ãã©ãŒã ããŒã¿ãååŸãã var formdata = e.response.getItemResponses(); //ã«ãŒãã§ããŒã¿ãpushãã for (var j = 0; j < formdata.length; j++){ //å
¥åé
ç®ãååŸãpushãã array.push(formdata[j].getResponse()); ïœ //uidãäžçªã¯ããã«è¿œå ãã array.unshift(uid); //æ¥ä»ãé
åã®2çªç®ã«è¿œå ãã array.splice(1, 0, new Date()); //åæã¹ããŒã¿ã¹ã远å ãã array.push("æªåŠç"); //ã·ãŒããžããŒã¿ãæžã蟌ã¿ãã ss.appendRow(array); } |
ä»åã¯ãã®æ¬¡ã®é ç®ã§ç޹ä»ãããŠããŒã¯IDãé åã®äžçªæåã«ãç³è«æ¥ãé åã®2çªç®ã«ãææã«åæã¹ããŒã¿ã¹ãšããŠãæªåŠçãã远å ããããã«ããŠããŸããããããäºã§ãèªåã§èªç±èªåšã«ããŒã¿ãæžã蟌ã¿ãããäºãå¯èœã«ãªããŸãã
ãŠããŒã¯IDãå²ãæ¯ã£ãŠããã
ãŠããŒã¯ãªIDãšã¯ã絶察ã«ãã¶ããªãé£çªã®äºã§ãäŸãã°ãåãåããçªå·ãçºè¡ããŠãã®çªå·ãå ã«ãåæ¹ã§ä»äºããããããŸãã¯ãŒã¯ãããŒçãªä»çµã¿ãæ§ç¯ããæã«ããã®ã¬ã³ãŒããç¹å®ããã«ã¯ãŠããŒã¯ãªIDãå¿ èŠã«ãªããŸãããã®ãŠããŒã¯IDã¯éåžžã¯1åç®ã«æžã蟌ã¿ãè¡ãããŸãïŒvlookup颿°ã§ãã®åŸåç §ããããããäºããããŸãããïŒã
ãã®ãŠããŒã¯IDãçºè¡ããä»çµã¿ã«ã¯ããããžã§ã¯ãããããã£ã䜿çšããŸãããåæã«éä¿¡ãããã±ãŒã¹ãèæ ®ããŠLock Serviceã䜿ãæä»åŠçãè£ åããå¿ èŠããããŸããæä»åŠçèªäœã¯ãŸãå¥ã®é ç®ã§ç޹ä»ããŠããŸããããã§ã¯ãŠããŒã¯IDçºè¡ã®ä»çµã¿ã ãã玹ä»ããŸãã
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
//ããã¯ãµãŒãã¹ã®å®£èš var lock = LockService.getPublicLock(); try{ ãã//30ç§éã®ããã¯ãéå§ ããlock.waitLock(30000); ãã//uidã®çŸåšå°ãååŸãã ããvar Properties = PropertiesService.getScriptProperties(); ããvar uid = Properties.getProperty("uid"); ãã//æ°ããuidãäœã ããuid = Number(uid) + 1; ãã//uidãæžãæ»ã ããProperties.setProperty("uid",uid); ãã//LockServiceãéæŸãã ããlock.releaseLock(); ãã }catch(e){ ãã//ããã«ãšã©ãŒçºçæã®åŠçãèšè¿°ãã } |
åçã¯ç°¡åã§
- LockServiceã§æä»åŠçãéå§
- ãããžã§ã¯ãããããã£ã®uidã®å€ãååŸãã1ãå ç®ãã
- ãããžã§ã¯ãããããã£ã«uidã®å€ãæžãæ»ã
- LockSercviceãéæŸãã
åŸã¯ã¹ãã¬ããã·ãŒãã«æžã蟌ãçºã®é åã®äžçªæåã«ãã®uidã®å€ãpushãããã¯unshiftããŠãããã°è¯ãã§ãã
1 2 3 4 5 6 7 8 9 10 |
//æ®éã«æåã«è¿œå ããæ var array = []; array.push(uid); //åŸãã1çªç®ã«è¿œå ããæ var array = []; ã»ã»ã»ãéäžã§è²ã
pushããŠã ã»ã»ã» array.unshift(uid); |
èªåã§CCè¿œå æ©èœ
æ¥åã§äœ¿çšããå Žåãèªåã§äžåžãé¢é£éšçœ²ã®äººã«CCã§éç¥ãéãããäºããããŸãããã®å Žåå¥ã®ã·ãŒãã«ãã®äžèЧãçšæããŠããããããåç §ããMailAppã®ccã«ã¯ã蟌ãã°è¯ãã§ããããå¥ã®ã·ãŒãã«ã¯ãç³è«è ã®ã¡ã¢ããCCéä¿¡å ã®2åã§çšæããã°OKã§ããã·ãŒãåã¯ãCCéä¿¡å ããšä»ããŠããŸããããŒã¿ã¯2è¡ç®ããå ¥ã£ãŠããŸãã
ãã®æããŠãŒã¶ã®ã¡ã¢ãã¯èªåã§ååŸãããã»ããæ¥œã§ãããä»åã¯Formã®ã¡ãŒã«ã¢ãã¬ã¹æ¬ã§å ¥åããããã®ã䜿ã£ãŠCCã®éä¿¡å ãæ¢çŽ¢ãããæ¹æ³ããããŠåã£ãŠã¿ãŸããCCéä¿¡å ã¯ã«ã³ãåºåãã§ã»ã«ã«å ¥åããŠããã°ãè€æ°éããŸãã
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 |
//倿°ãçšæãã var user = ""; //ãŠãŒã¶ã®ã¡ã¢ããæ ŒçŽãã var cc = ""; //CCéä¿¡å
ãæ ŒçŽãã //ã¹ãã¬ããã·ãŒãã®CCéä¿¡å
ã·ãŒããååŸãã var sheet = SpreadsheetApp.openById("ããã«ã·ãŒãã®IDãå
¥ãã"); var ss = sheet.getSheetByName("CCéä¿¡å
").getRange("A2:B").getValues(); //ãã©ãŒã éä¿¡ããŒã¿ãååŸãã var formdata = e.response.getItemResponses(); //ã«ãŒãã§ã¡ãŒã«ã¢ãã¬ã¹ãååŸãã for (var j = 0; j < formdata.length; j++){ //å
¥åé
ç®åãååŸãã var title = formdata[j].getItem().getTitle(); //ã¡ãŒã«ã¢ãã¬ã¹ãååŸãã if(title == "ã¡ãŒã«ã¢ãã¬ã¹"){ user = formdata[j].getResponse(); } ïœ //CCéä¿¡å
æ
å ±ãæ¢çŽ¢ãã for(var i = 0;i<ss.length;i++){ //CCéä¿¡å
ãããã°ãccã«ãã®ã¢ãã¬ã¹ãæ ŒçŽãã«ãŒããæãã if(user == ss[i][0]){ cc = ss[i][1]; break; } } |
ãã®åŠçã§ã¯ãã¡ãŒã«ã¢ãã¬ã¹å ¥åæ¬ãäœçªç®ã§ãã£ãŠããã¡ãŒã«ã¢ãã¬ã¹ãšããåç§°ã®å ¥åæ¬ã®å€ãååŸããŠãããŸãããŸãããã®ã¡ã¢ããå ã«CCéä¿¡å ã·ãŒãããäžèŽããã¡ã¢ããããã«å¯Ÿå¿ããCCéä¿¡å ã®å€ã倿°ccã«æ ŒçŽããŠããŸããccéä¿¡å ãã¿ã€ãã£ãããbreak;ã§æããŠãåŸã¯ãMailAppã«ãŠãccã«ãã®å€ãå ããã°è¯ãã§ãã
1 2 3 4 5 6 |
MailApp.sendEmail({ ãããto: user, ãããcc:cc, //ããã«ccãã¯ã蟌ã ãããsubject: title, ãããhtmlBody: body, }); |
ãã©ãŒã ã®åçãåé€ãã
èªåã§æžã蟌ã¿ããããæã«ããã©ãŒã ã«åçããŒã¿ãæ®ããŠãããããªãå ŽåããããŸãããã®æã«ã¯ãã«ãŒãã³ã®ææã§ãã©ãŒã ã¬ã¹ãã³ã¹ããŒã¿ãå šãŠåé€ããã³ãŒããå ¥ããŠå ¥ããŠããã°è¯ãã§ãããã
â»ãã ããåæã«å©çšè ãããå Žåããã®äººã®ããŒã¿ãŸã§åé€ãããæããããã®ã§ãå€äžãªã©ã®å©çšè ãŒãã®æéã«ããªã¬ãŒã§å®è¡ãããã»ããæãŸããã§ãã
1 2 |
//ãã©ãŒã ã«èç©ãããŠãããŒã¿ããã¹ãŠåé€ãã FormApp.openById("ããã«ãã©ãŒã ã®IDãå
¥ãã").deleteAllResponses(); |