{"id":160,"date":"2012-11-21T16:51:49","date_gmt":"2012-11-21T07:51:49","guid":{"rendered":"http:\/\/www.tciskfactory.com\/wp\/?p=160"},"modified":"2026-10-29T16:30:40","modified_gmt":"2026-10-29T07:30:40","slug":"google","status":"publish","type":"post","link":"https:\/\/blog.coppe.jp\/en\/2012\/11\/21\/google\/","title":{"rendered":"A script that sends a confirmation email when a form created in Google Drive (formerly Google Docs) is submitted."},"content":{"rendered":"<p><img decoding=\"async\" class=\"wp-image-149 alignleft\" title=\"googsite_01home\" src=\"https:\/\/blog.coppe.jp\/app\/uploads\/2012\/11\/googsite_01home-300x225.jpg\" alt=\"\" \/>When creating a website for free, one of the options that comes to mind is<a href=\"http:\/\/www.google.com\/sites\/help\/intl\/ja\/overview.html\" target=\"_blank\" rel=\"noopener noreferrer\">Google Sites<\/a>is.<br \/>\nGoogle Sites allows you to create websites without specialized knowledge or building software.<br \/>\nWhile its design is somewhat limited, it still offers more than enough functionality for disseminating information online. By integrating with other Google applications (such as Calendar, Drive, and Docs), it's easy to display detailed information like store hours and schedules on a website, and to easily output information such as inquiries and survey forms. This groupware allows you to use all of these applications comprehensively.<a href=\"http:\/\/www.google.com\/intl\/ja\/enterprise\/apps\/business\/\" target=\"_blank\" rel=\"noopener noreferrer\">Google Apps<\/a>It's really convenient, and I use it a lot myself.<\/p>\n<p>By the way, regarding the aforementioned inquiry and survey forms, integrating them into Google Sites is probably something anyone can easily do. However, these forms lack convenience afterward.<br \/>\nIn other words, when a form is filled out and submitted, neither the submitter nor the administrator receives an email notification. Therefore, the administrator has to constantly check Google Drive to see if anything has been entered into the form. This is inconvenient. And from the submitter's perspective, simply filling out the form and pressing the [Submit] button only to be met with a simple \"Thank you\" message is quite disappointing.<\/p>\n<p>I know someone who runs a website business using Google Sites, and they asked me for help with automatically sending emails as soon as a form is submitted. I helped them integrate a script for this purpose. Recently, they asked me to do the same thing with a different form, so I integrated the script again. Regardless of the script's content, the integration process isn't that difficult, so I'll describe the method below. You can use the script by copying and pasting what's written in the text and modifying only the necessary parts.<\/p>\n<p><strong>When a form created in Google Drive (formerly Google Docs) is submitted, a confirmation email will be sent.<\/strong><\/p>\n<p><a href=\"https:\/\/blog.coppe.jp\/en\/app\/uploads\/2012\/11\/1.png\/\"><img decoding=\"async\" class=\"wp-image-130 alignnone\" title=\"1\" src=\"https:\/\/blog.coppe.jp\/app\/uploads\/2012\/11\/1-300x108.png\" alt=\"\" \/><\/a><br \/>\n1. First, create a form such as an inquiry form or survey form in Google Drive.<\/p>\n<p><a href=\"https:\/\/blog.coppe.jp\/en\/app\/uploads\/2012\/11\/Screenshot-17.png\/\"><img decoding=\"async\" class=\"alignnone wp-image-131\" title=\"Marketshot (17)\" src=\"https:\/\/blog.coppe.jp\/app\/uploads\/2012\/11\/Screenshot-17-300x189.png\" alt=\"\" \/><\/a><br \/>\n2. From the \"Tools\" menu on the menu bar, select \"Script Manager\".<\/p>\n<p><a href=\"https:\/\/blog.coppe.jp\/en\/app\/uploads\/2012\/11\/Screenshot-18.jpg\/\"><img decoding=\"async\" class=\"alignnone wp-image-132\" title=\"Fishshot (18)\" src=\"https:\/\/blog.coppe.jp\/app\/uploads\/2012\/11\/Screenshot-18-300x267.jpg\" alt=\"\" \/><\/a><br \/>\n3. Click \"New\" at the bottom of the window that appears. The above will be displayed, so select \"Empty Project\" at the top of the left column.<\/p>\n<p><a href=\"https:\/\/blog.coppe.jp\/en\/app\/uploads\/2012\/11\/Screenshot-221.png\/\"><img decoding=\"async\" class=\"alignnone wp-image-148\" title=\"Screenshot (22)\" src=\"https:\/\/blog.coppe.jp\/app\/uploads\/2012\/11\/Screenshot-221-300x212.png\" alt=\"\" \/><\/a><br \/>\n4. \u30ef\u30fc\u30af\u30b7\u30fc\u30c8\u5185\u306b\u3059\u3067\u306b\u8a18\u3055\u308c\u3066\u3044\u308b\u6570\u884c\uff08function myFunction() {}\uff09\u3092\u3059\u3079\u3066\u6d88\u3057\u3001\u4ee5\u4e0b\u306e\u30b3\u30fc\u30c9\u3092\u30b3\u30d4\u30fc\uff06\u30da\u30fc\u30b9\u30c8\u3057\u3066\u3001\u300c\u30d5\u30ed\u30c3\u30d4\u30fc\u30c7\u30a3\u30b9\u30af\u300d\u306e\u30a2\u30a4\u30b3\u30f3\u3092\u30af\u30ea\u30c3\u30af\u3057\u3066\u4fdd\u5b58\u3059\u308b\u3002\u30ab\u30b9\u30bf\u30de\u30a4\u30ba\u3084\u5909\u66f4\u306f\u3001\u4e0a\u56f3\u3092\u53c2\u8003\u306b\u3002<\/p>\n<pre class=\"toolbar-overlay:false lang:default decode:true\" title=\"sendMailByFormSubmitted\" data-no-translation=\"\">function sendMailByFormSubmitted() {\r\nLogger.log('sendMailByFormSubmitted() start');\/\/ \u4ef6\u540d\u3001\u672c\u6587\r\nvar subject = \"\u69d8 \u304a\u554f\u3044\u5408\u308f\u305b\u30fb\u304a\u7533\u8fbc\u307f \u3042\u308a\u304c\u3068\u3046\u3054\u3056\u3044\u307e\u3059\uff0fXYZ Company\";\r\nvar body = \"\u3053\u306e\u5ea6\u306f\u3001\u304a\u554f\u3044\u5408\u308f\u305b\u30fb\u304a\u7533\u8fbc\u307f\u3092\u3044\u305f\u3060\u304d\u3001\u3042\u308a\u304c\u3068\u3046\u3054\u3056\u3044\u307e\u3059\u3002n\u3053\u3061\u3089\u304b\u3089\u3001\u5225\u9014\u30e1\u30fc\u30eb\u3067\u3054\u9023\u7d61\u3044\u305f\u3057\u307e\u3059\u306e\u3067\u3001\u3057\u3070\u3089\u304f\u304a\u307e\u3061\u304f\u3060\u3055\u3044\u307e\u305b\u3002nn\u682a\u5f0f\u4f1a\u793e \uff38\uff39\uff3a\u30ab\u30f3\u30d1\u30cb\u30fcnnn=========================n\u25bc \u304a\u554f\u3044\u5408\u308f\u305b\u30fb\u304a\u7533\u8fbc\u307f\u5185\u5bb9 \u25bcn=========================n\";\/\/ \u30e1\u30fc\u30eb\u30a2\u30c9\u30ec\u30b9\u5165\u529b\u30ab\u30e9\u30e0\u540d\u306e\u6307\u5b9a\r\nvar MAIL_CLMN_NAME = '\u30e1\u30fc\u30eb\u30a2\u30c9\u30ec\u30b9';\r\nvar APPLICANT_NAME = '\u304a\u540d\u524d\uff08\u6f22\u5b57\uff09';\/\/ \u30e1\u30fc\u30eb\u9001\u4fe1\u5148(\u7ba1\u7406\u8005\u7b49)\r\nvar mail_admin = \"asdfqwerty@xyzcompany.com\";\r\nvar mail_cc = mail_admin;\r\nvar mail_reply = mail_admin;\r\nvar mail_to = '';\r\n\r\ntry{\r\n\/\/ \u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8\u306e\u64cd\u4f5c\r\nvar sh = SpreadsheetApp.getActiveSheet();\r\nvar rows = sh.getLastRow();\r\nvar cols = sh.getLastColumn();\r\nvar rg = sh.getDataRange();\r\nLogger.log(\"rows=\"+rows+\" cols=\"+cols);\r\n\r\n\/\/ \u30e1\u30fc\u30eb\u672c\u6587\u4f5c\u6210\u3068\u9001\u4fe1\u5148\u30e1\u30fc\u30eb\u30a2\u30c9\u30ec\u30b9\u53d6\u5f97\r\nfor (var j = 1; j var clmn_name = rg.getCell(1, j).getValue(); \/\/ \u30ab\u30e9\u30e0\u540d\r\nvar clmn_value = rg.getCell(rows, j).getValue(); \/\/ \u5165\u529b\u5024\r\nbody += ( clmn_name + \" : \" + clmn_value + \"n\" );\r\n\r\nif ( clmn_name === MAIL_CLMN_NAME ) {\r\nmail_to = clmn_value;\r\n}\r\nif ( clmn_name === APPLICANT_NAME ) {\r\napname = clmn_value;\r\n}\r\n\r\n}\r\n\r\n\/\/ \u30e1\u30fc\u30eb\u9001\u4fe1\r\nif ( mail_to ) {\r\nMailApp.sendEmail(mail_to, apname + subject, body, {cc : mail_cc, replyTo : mail_reply});\r\n}else{\r\nMailApp.sendEmail(mail_admin, \"error spreadsheet none mailaddress\", body);\r\n}\r\n}catch(e){\r\nMailApp.sendEmail(mail_admin, \"error spreadsheet func: sendMailByFormSubmiited()\", e.message);\r\n}\r\n}<\/pre>\n<p><a href=\"https:\/\/blog.coppe.jp\/en\/app\/uploads\/2012\/11\/Screenshot-24.png\/\"><img decoding=\"async\" class=\"alignnone wp-image-143\" title=\"Screenshot (24)\" src=\"https:\/\/blog.coppe.jp\/app\/uploads\/2012\/11\/Screenshot-24-300x186.png\" alt=\"\" \/><\/a><br \/>\n5. Next, select \"Current Script's Triggers\" under \"Resource\".<\/p>\n<p><a href=\"https:\/\/blog.coppe.jp\/en\/app\/uploads\/2012\/11\/Screenshot-26.png\/\"><img decoding=\"async\" class=\"alignnone wp-image-144\" title=\"Screenshot (26)\" src=\"https:\/\/blog.coppe.jp\/app\/uploads\/2012\/11\/Screenshot-26-300x91.png\" alt=\"\" \/><\/a><br \/>\n6. The \"Current project's triggers\" window will appear. Click \"Add a new triggers,\" change the event to \"On Form Submit,\" give it a suitable name, and click \"Save.\"<\/p>\n<p><a href=\"https:\/\/blog.coppe.jp\/en\/app\/uploads\/2012\/11\/Screenshot-272.png\/\"><img decoding=\"async\" class=\"alignnone wp-image-154\" title=\"Screenshot (27)\" src=\"https:\/\/blog.coppe.jp\/app\/uploads\/2012\/11\/Screenshot-272-300x140.png\" alt=\"\" \/><\/a><br \/>\n7. You will be asked to authenticate the first time you use it, so click \"Authorize\".<\/p>\n<p>It's finished.<br \/>\nAll that's left is to paste the form with the script embedded into your website, and you're good to go.<\/p>","protected":false},"excerpt":{"rendered":"<p>When creating a website for free, Google is often the first option that comes to mind...<\/p>","protected":false},"author":5,"featured_media":2240,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-160","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/blog.coppe.jp\/en\/wp-json\/wp\/v2\/posts\/160","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.coppe.jp\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.coppe.jp\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.coppe.jp\/en\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.coppe.jp\/en\/wp-json\/wp\/v2\/comments?post=160"}],"version-history":[{"count":3,"href":"https:\/\/blog.coppe.jp\/en\/wp-json\/wp\/v2\/posts\/160\/revisions"}],"predecessor-version":[{"id":1865,"href":"https:\/\/blog.coppe.jp\/en\/wp-json\/wp\/v2\/posts\/160\/revisions\/1865"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.coppe.jp\/en\/wp-json\/wp\/v2\/media\/2240"}],"wp:attachment":[{"href":"https:\/\/blog.coppe.jp\/en\/wp-json\/wp\/v2\/media?parent=160"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.coppe.jp\/en\/wp-json\/wp\/v2\/categories?post=160"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.coppe.jp\/en\/wp-json\/wp\/v2\/tags?post=160"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}