{"id":5029,"date":"2019-02-07T18:39:55","date_gmt":"2019-02-07T18:39:55","guid":{"rendered":"https:\/\/max-drake.cc\/?p=5029"},"modified":"2019-02-08T12:18:10","modified_gmt":"2019-02-08T12:18:10","slug":"creating-free-embedded-automated-quote-for-service-with-google-docs","status":"publish","type":"post","link":"https:\/\/max-drake.cc\/?p=5029","title":{"rendered":"Creating free embedded automated quote for service with Google Docs"},"content":{"rendered":"

After writing a post on following a tutorial for automated invoice for a cake shop order, Free Google Docs online ordering automated<\/a> <\/strong>I decided that, after migrating my Services to my Blog Site I’d try setting up an Automated Quote process for one of the services I offer, namely a Browser viewer for Panoramas linked to floor plans. This can be put on a flash drive and run from there via a browser.<\/p>\n

So to start, I needed a google form embedded in the web page. This is pretty simple, it asks for Name & Email and what number of plans required as well as number of panoramas or photos that need to be linked to those plans and if any Hotlinks are required. 5 questions. At the top of the form there are prices for each so the person has an indication of costs but filling out the form they will get a confirmed quote that is valid for 2 weeks. Follow link<\/strong><\/a> to page.<\/p>\n

\"\"<\/p>\n

If they fill out and submit the form they would get an email outlining the process of ordering the service and payment method and also a PDF Quote as shown below:<\/p>\n

\"\"<\/p>\n

The process follows the process of the Cake Shop example , but I ran into a few issues along the way when I tried to modify the process to fit my needs.<\/p>\n

1.Driven to distraction editing auto saving sheet<\/h3>\n

This definitely drove me cracked. The auto saving in Google Sheets was “SO SLOW” that it took ages updating the formula in each column, so trying to correct the formula in say 10 columns took a massive amount of time. I was getting really really annoyed with it. It definitely disrupted my workflow as I’m not used to working at a snails pace. and YOU CANNOT TURN IT OFF.<\/p>\n

On day 2 on this project I found out about OffLine sheets, but you need to be working in Chrome, not Firefox.<\/p>\n

I think, once I linked the\u00a0 Quote Spreadsheet to the Form Response Spreadsheet that caused it to slow down an awful lot.<\/p>\n

2. Cut\/Paste in Google Sheets.<\/h3>\n

This was very frustrating too. There seems to be the Ctrl + C\u00a0 for copy & Ctrl + V for paste, if you try using the pull down menu it just refers to the hotkeys. So only one option, and that didn’t seem to work consistently and I found that pretty frustrating. All I could do is Ctrl + Z to undo and try again when it didn’t work. I had to add a couple of steps in like using backspace to delete an element. I still don’t think I’ve got a full understanding of this and the formula in the adjacent cell I was copying from would disappear, and I’m sure I was using Ctrl + C and not Ctrl + X.<\/p>\n

3. Disappearing Functions as new row wiped clean any existing formulas<\/h3>\n

Either the Document Studio addon to Google Sheets or else transferring information from the Form, when creating a new line of data, seems to wipe out the whole row of data. So if you have a formula in empty cells on a new line in the main Google Sheet that gathers responses from the Form, they are all deleted.<\/p>\n

This held me up for quite a while.<\/p>\n

If I used this type of formula\u00a0 and dragged it down the column for empty cells:<\/p>\n

=<\/span>if<\/span>(<\/span>B2:B<\/span><><\/span>“”<\/span>,<\/span>PanoPrice!B3<\/span>,<\/span>)<\/span>)\u00a0 <\/span><\/span><\/bdo><\/strong>( a simpler way than<\/span><\/span><\/bdo>=IF(NOT(ISBLANK($A2:$A<\/span>)),PanoPrice!B3<\/span>,))<\/bdo><\/bdo><\/span><\/span><\/bdo><\/strong>)<\/bdo><\/bdo><\/span><\/span><\/bdo><\/p>\n

The formula in the cells on the same line as the new form data was created would become empty cells, so I couldn’t use formulas between cells.<\/p>\n

In the video of the Cake Shop post the designer used:<\/p>\n

=<\/span>ArrayFormula<\/span>(<\/span>if<\/span>(<\/span>B2:B<\/span><><\/span>“”<\/span>,<\/span>PanoPrice!B3<\/span>,<\/span>)<\/span>)\u00a0\u00a0 <\/span><\/span><\/bdo><\/strong>and looking up the ArrayFormula()<\/strong> function seems to be the method to overcome this process. You write it in the first line and it populates down the sheet.\u00a0 There was little explanation of this in the video and when looking at it later it seems quite a powerful function. <\/span><\/span><\/bdo>
\n<\/span><\/span><\/bdo><\/strong><\/p>\n

I subsequently found the video below that explains what was happening and how the ArrayFormula()<\/strong>\u00a0 fixes it.<\/span><\/span><\/bdo><\/p>\n