Free Google Docs online ordering automated

I saw this video on automating process for ordering online in Google Docs. I decided to see if I could replicate the process (to a point, not the PayPal integration).

The video above has a ‘cake Shop” with an online form  that can take orders for cakes asking for name, location, email and preferred cake type. The form data goes to a spreadsheet workbook. In a second sheet in the workbook there is a table with name of cake type and price. There is a formula on the first form that looks up the cake ordered and calculates cost of cake, taxes and total.  Another Google Worksheet is created to create an invoice. This is filled out with variables created in the form. The Add-on to Google Sheets “Document Studio” merges the data from Invoice with new order data from form that is in spreadsheet and an email is sent to orderer and to owner with new order and invoice. The PDF invoice is saved to specific client named folder in google drive. There is a further part of the process that uses a Paypal account.

Copying the process

1. setup form

I started the process using the same process as the Cake Shop example, setting out a form as per their example. I used the services that I was offering rather than products with embellishments. This caused

2. Spreadsheet setup

I created Spreadsheet & lookup tables.

The spreadsheet is linked to the form so gets filled in by that. The lookup price is from the next sheet and gets price, to drag these cells down to make it work for all new rows required the following formula:

=ArrayFormula(IF(NOT(ISBLANK(B2:B)),VLOOKUP(B2:B,Prices!A2:D5,4,FALSE),))

Which says don’t display unless there is a valid entry in Column B. So if there is an empty row then Column H will show nothing on that row.

3. Invoice Setup

I then filled in the Invoice form (using one of the Google Sheet Template files (as per the video, as I’m just testing the process))

Using variables (in double curly brackets) to show which ones the  form needs to get information from.

4. Document Studio Addon to Sheets

I then filled out the process as per the video. Watch the video for the process.

Note that for the free version you are only allowed 20 merges per day. I’ve done 2 so 18 remaining. For my type of service I wouldn’t reach this limit, so that is fine, but for others it may be insufficient.

5. Results

The email was sent to orderer  with Invoice Attachment and I can’t see an email sent to supplier (I’d need to check I’d set that up correctly) and a new sub-directory was created for the order under the name of the orderer, Mr Jones. In fact, 2 sub-directories were created. I also got about 2-4 emails (I may need to check this again) as I have been playing with adding other accounts to one of my gmail accounts (so I have 2 inboxes in Thunderbird for the same account). Anyway, the process worked as shown in video.

From the order form a price was calculated and the invoice sent out and copy put into a sub -directory. What happens if the same name puts in another order? Is a new directory created? Can you concatenate variables to create unique directory names or should, instead of name the date be used as the directory?

Stepping through the Process

As I was working on the process, for my use I’d need some variations on this basic process.

1. setup form

Form setup for more complex information capture for pricing

As I was setting up form, I realised that the multi-choice answer was insufficient for my needs,. If they said yes to some services, there needed to be more questions, specific to those services, that needed further questions to be answered to be able to calculate a price for that service.

Eg Scan to BIM. This service needs to be scaled, a 200 m2 building would take far less effort than a 2,000 m2 building. So an area component is needed to be able to help calculate a price. Also, a different level of sophistication, if its a shed or if its a hospital, there is a different level of complexity and that would need to be taken into account for pricing.

For something like setting up a file for panorama and images with a plan link this would depend on the number of photos that needed to be linked to a plan. If it is multi-story than that will also be another component for costing. So that service would need a different set of questions to ascertain a price for that specific service.

After a bit of research, there is a method based on Skip or Branch Logic in Google Forms. See video below. So this allows for different results in the spreadsheet.

Aesthetics of form

I think the forms are a bit dull, I’d like to be able to do something to them to make them look more interesting.

This lass’s videos show how to simplify or modify forms so they still work in sending data to google sheets. They require a bit of HTML editing.

2. Spreadsheet setup

With the added complexity of the results from the linked Google Form the spreadsheet VLookup will need to be more sophisticated, in fact , there may need to be multiple VLookups to be able to manage the possible variations for ascertaining prices for the specific service. These may be best managed on different sheets in the workbook so each process can be calculated in a different sheet and the final result of each gets sent back to the main sheet with the calculated cost/price 9that way, if one service needs modifications you only need to deal with a specific sheet in the workbook)

3. Invoice Setup

A couple of things I would need to figure out is how to add say 5 days onto the Date of the Form so that the invoice Due date is shown say 5 days later. I suppose it would be better to do this in the Google Sheets and just make a new column, then use that variable.

Also invoice number, That could be a count in the main google sheet so that every new row is a number added to the previous. To ensure its unique maybe add the date to the invoice as well as name & make a concatenated field , then you are only calling a single variable for that.

4. Document Studio Addon to Sheets

The limit of 20 merges a day is fine for my needs. I would need to test this more thoroughly to see If there are other features to use to refine the process more. My concern is on having a job ID that I can then use to create a Google Drive folder to share with client for sharing information.

I think you need this add-on to create the merge function and create the PDF and attach it to the email. I’m not absolutely sure as I’m not that familiar with Google Docs.

End comment

Overall I think there is the flexibility within Google Forms to get the process that I would want for a form on my website that will do the process of creating a quote for work.