{"id":5038,"date":"2019-02-08T19:34:21","date_gmt":"2019-02-08T19:34:21","guid":{"rendered":"https:\/\/max-drake.cc\/?p=5038"},"modified":"2019-02-08T19:58:25","modified_gmt":"2019-02-08T19:58:25","slug":"creating-embedded-form-with-alternative-choices-in-google-docs","status":"publish","type":"post","link":"https:\/\/max-drake.cc\/?p=5038","title":{"rendered":"Creating embedded form with alternative choices in Google Docs"},"content":{"rendered":"<p>After the success of my previous automated quoting service <a href=\"https:\/\/max-drake.cc\/2019\/02\/07\/creating-free-embedded-automated-quote-for-service-with-google-docs\/\" target=\"_blank\" rel=\"noopener\"><strong>Creating free embedded automated quote for service with Google Docs<\/strong><\/a> I found a couple of things that I wanted to try to extend this process.<\/p>\n<p>The first is multi sections in forms and the second is, instead of using the Google Sheets <strong>Document Studio Addon<\/strong> I would try the <strong>AutoCrat addon<\/strong> instead. To evaluate different addons.<\/p>\n<h3><strong>1. Multi sections in forms<\/strong><\/h3>\n<p>The video below demonstrates the process, in a multi choice question how, depending on which is your choice, it jumps you to a different section to answer questions specific to that choice.<\/p>\n<p><iframe data-src=\"https:\/\/www.youtube.com\/embed\/cJvjvJgjlDU\" width=\"560\" height=\"315\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" class=\"lazyload\" data-load-mode=\"1\"><\/iframe><\/p>\n<p>I am offering 3 services to quote, so depending on which button you select it will take you to a different section to answer the question.<\/p>\n<p>So, in the multi choice question:<\/p>\n<p><img decoding=\"async\" class=\"wp-image-5043 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/02\/2019-02-09-11_17_41-Sheet-Sets-Quote-Form-Google-Forms-300x157.jpg\" alt=\"\" width=\"1806\" height=\"945\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/02\/2019-02-09-11_17_41-Sheet-Sets-Quote-Form-Google-Forms-300x157.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/02\/2019-02-09-11_17_41-Sheet-Sets-Quote-Form-Google-Forms-768x401.jpg 768w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/02\/2019-02-09-11_17_41-Sheet-Sets-Quote-Form-Google-Forms-1024x535.jpg 1024w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/02\/2019-02-09-11_17_41-Sheet-Sets-Quote-Form-Google-Forms.jpg 1747w\" data-sizes=\"(max-width: 1806px) 100vw, 1806px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1806px; --smush-placeholder-aspect-ratio: 1806\/945;\" \/><\/p>\n<p>I thought I could use checkboxes to get multiple cell answers but it just made a string in the same cell, so I had to restructure form to make individual Yes\/No answers.<\/p>\n<h3>2. Results to Sheet with Vlookup on 2nd sheet<\/h3>\n<p>I had to play around with some of the formulae so that I got what I wanted, but in the end got the results. These formulae need more testing but I have a basic setup ready for using with the &#8220;Quote Template&#8221;.<\/p>\n<p>I kept getting an errors in that it wouldn&#8217;t read the string &#8220;Yes&#8221; so I had to use the exact() function to force it to do so.<\/p>\n<h3>3. Quote Sheet<\/h3>\n<p>I filled out the &#8220;Quote sheet template&#8221; as if I was using &#8220;Document Studio&#8221;(DS). I have now made a copy for using AutoCrat, which is pretty similar to DS, but instead of &#8220;{{}}&#8221; it uses &#8220;&lt;&lt;&gt;&gt;&#8221; around variables.<\/p>\n<p>So I&#8217;ve written the template for DS but will use Fin d\/replace\u00a0 to change &#8220;{{&#8221; to\u00a0&#8220;&gt;&gt;&#8221; and\u00a0 &#8220;}}&#8221; to &#8220;&gt;&gt;&#8221;\u00a0 then it should be ready for testing.<\/p>\n<h3>4.autoCrat<\/h3>\n<p>I followed this video, the programme did pretty much what it said in the video, no surprises. I&#8217;d been a bit relaxed in my setup<\/p>\n<p><iframe data-src=\"https:\/\/www.youtube.com\/embed\/xapZEiDHTEE\" width=\"560\" height=\"315\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" class=\"lazyload\" data-load-mode=\"1\"><\/iframe><\/p>\n<p>I really like the mapping process, it is a good check to see if you are mapping the correct variables to the correct places.<\/p>\n<p><img decoding=\"async\" class=\"alignnone wp-image-5044 lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/02\/2019-02-09-19_18_30-Sheet-Sets-Quote-Form-Responses-Google-Sheets-300x134.jpg\" alt=\"\" width=\"1762\" height=\"787\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/02\/2019-02-09-19_18_30-Sheet-Sets-Quote-Form-Responses-Google-Sheets-300x134.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/02\/2019-02-09-19_18_30-Sheet-Sets-Quote-Form-Responses-Google-Sheets-768x344.jpg 768w\" data-sizes=\"(max-width: 1762px) 100vw, 1762px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1762px; --smush-placeholder-aspect-ratio: 1762\/787;\" \/><\/p>\n<p>Sheet 2:<\/p>\n<p><img decoding=\"async\" class=\"wp-image-5047 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/02\/2019-02-09-20_20_13-Sheet-Sets-Quote-Form-Responses-Google-Sheets-300x127.jpg\" alt=\"\" width=\"1620\" height=\"686\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/02\/2019-02-09-20_20_13-Sheet-Sets-Quote-Form-Responses-Google-Sheets-300x127.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/02\/2019-02-09-20_20_13-Sheet-Sets-Quote-Form-Responses-Google-Sheets-768x324.jpg 768w\" data-sizes=\"(max-width: 1620px) 100vw, 1620px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1620px; --smush-placeholder-aspect-ratio: 1620\/686;\" \/><\/p>\n<h3>5. Output- Email, Storage directory &amp; PDF<\/h3>\n<p>The email was pretty standard, same as Document Studio although I&#8217;m not sure if you can put HTML into it.<\/p>\n<p>It is interesting that it stores the &#8220;Quotes&#8221; output as a Sheet, rather than as a PDF. This has pros and cons.<\/p>\n<p><strong>Cons.<\/strong><\/p>\n<p>You need to go back to the email to get the explicit document that you sent, that you know the client has<\/p>\n<p><strong>Pros.<\/strong><\/p>\n<p>If client wants adjustments you can do them easily on the fly in the spreadsheet and generate a modified quote quickly.<\/p>\n<p><img decoding=\"async\" class=\"wp-image-5046 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/02\/2019-02-09-20_10_10-SheetSetQuotes-Google-Drive-300x84.jpg\" alt=\"\" width=\"1621\" height=\"454\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/02\/2019-02-09-20_10_10-SheetSetQuotes-Google-Drive-300x84.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/02\/2019-02-09-20_10_10-SheetSetQuotes-Google-Drive-768x214.jpg 768w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/02\/2019-02-09-20_10_10-SheetSetQuotes-Google-Drive-1024x286.jpg 1024w\" data-sizes=\"(max-width: 1621px) 100vw, 1621px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1621px; --smush-placeholder-aspect-ratio: 1621\/454;\" \/><\/p>\n<p>Actual output PDF. I need to tweek this a bit or else make it clear how they filled in the form , the &#8220;Yes&#8221; in the Quantity column is confusing, although the Quantity is in the number of rooms it should be more clear in the quote.<\/p>\n<h3><img decoding=\"async\" class=\"wp-image-5045 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/02\/2019-02-09-20_04_48-Sheet-Set-Quote-2019-7-max15-drake-09_02_2019-19_56_37-1.pdf-Adobe-Acrobat-P-243x300.jpg\" alt=\"\" width=\"1467\" height=\"1811\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1467px; --smush-placeholder-aspect-ratio: 1467\/1811;\" \/> End comment<\/h3>\n<p>This process was a little bit more elaborate than my panorama form, but it went well. I really thought I&#8217;d be struggling with the formulae a lot more but my familiarity with the process in the previous exercise seems to have paid off. The process has gone well (so far), but still needs testing.<\/p>\n<p>The formulae were slightly different than in the previous process and I do need to test them to check that they are robust.<\/p>\n<p>The free <strong>autoCrat<\/strong> addon instead of <strong>Document Studio<\/strong> setup went well, I quite like it, and it seems to be a &#8220;FREE&#8221; addon without constraints. I will possibly try it out in the next automation process (3dPDF&#8217;s quote) and see how it does on that.<\/p>\n<p>As I have a sheet as a record maybe I should look at a &#8220;Zapier&#8221; or IFTTT process to take the attachment of some of my emails (I wonder if I can filter them?) to save them to this sub-directory so that i have the sheet &amp; the pdf for a more robust record.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>After the success of my previous automated quoting service Creating free embedded automated quote for service with Google Docs I found a couple of things that I wanted to try to extend this process. The first is multi sections in forms and the second is, instead of using the Google Sheets Document Studio Addon I [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":5045,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[204,265,42,29,27],"tags":[277,276,268,199,255,48],"class_list":["post-5038","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-automation","category-google-docs","category-productivity","category-web","category-wordpress","tag-autocrat-addon","tag-branch-logic","tag-free-automation","tag-google-docs","tag-google-sheets","tag-productivity"],"featured_image_src":"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/02\/2019-02-09-20_04_48-Sheet-Set-Quote-2019-7-max15-drake-09_02_2019-19_56_37-1.pdf-Adobe-Acrobat-P.jpg","featured_image_src_square":"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/02\/2019-02-09-20_04_48-Sheet-Set-Quote-2019-7-max15-drake-09_02_2019-19_56_37-1.pdf-Adobe-Acrobat-P.jpg","author_info":{"display_name":"Max Drake","author_link":"https:\/\/max-drake.cc\/?author=1"},"_links":{"self":[{"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/posts\/5038","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=5038"}],"version-history":[{"count":0,"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/posts\/5038\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/media\/5045"}],"wp:attachment":[{"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5038"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5038"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5038"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}