With Document Studio, you can create a customized price quotation, proforma invoices, purchase orders and professional sales proposals in Google Sheets and send the generated document to your customer as a PDF attachment in an email message.
For this example, we have a sales quotation Google Form where the customer fills-in their business name, mailing address and products list (along with the quantity) in a Google Form. When they submit the form, the workflow is triggered, the prices of the selected items are pulled in from the price sheet and the proposal is generated.
The quotation file gets automatically saved to your Google Drive for easy collaboration.
Create Quotation Template in Google Sheets
Create a new Google Sheet and add a new workbook titled
Catalogue. Here enter your product names and their unit prices. We'll use this prices sheet later with
IMPORTRANGE to calculate the prices of the products selected by the customer in the Google Form.
In the same sheet, add a new template for the quotation. This price quotation template should include room for item descriptions, line item totals, sales tax and the total due amount.
The Unit Prices and the Total Price columns are calculated automatically using the vlookup formula. For instance, here's the price lookup formula for cell G18 that you will have to copy to all other cells in the Unit Price column:
=IFNA ( VLOOKUP ( B18, Catalogue!$A$2:$B$21, 2, FALSE ) )
It is recommended that you hide the
Catalogue sheet from the list of sheets in the template since we do not want to show it to the customer in the generated PDF quotation.
Create Quotations from Google Form Responses
Install the Document Studio add-on and launch the add-on inside your Google Forms. Give your workflow a descriptive name, like
Sales Quotation Generator, and click on
Continue to move to the conditions page.
Since we are looking to generate sales proposals for all responses, choose the
Process all responses option to not skip any response.
Configure Document Studio
Continue and then choose
Create File from the list of available tasks. Here you need to pick the Google Sheets template that you have created in the previous step for the Document Template field.
Specify a folder in Google Drive where the generated files are uploaded. You can also chose to save the spreadsheet files in custom folders that are dynamically created from user's answers in the Google Forms.
Attach this file in email option and add another Email task to send the file in a personalized email to the customer. The documents can be exported as PDF, Office Excel or Google Sheets format. The Sheets format is recommended if you wish to share the proposal through the built-in sharing option of Google Drive.
You may also use Scriptlets in any of the input text field to generate dynamic content.
Activate the Workflow
Preview button to generate a dummy quotation based on data in the second row of the response Google Sheet. You can open your Google Drive to view the generated file.
Done to locally apply your changes. On the next screen, turn on the
Run on Form Submit option and the workflow will run whenever a new Google Form submission is received.
Save button and the workflow will now appear in the workflow dashboard where you can edit, delete or manually run the workflow.
Here's a sample sales quotation generated by Document Studio: