¶ Google Sheets Templates
Maven Documents Template Builder is an add-on that you can install on a Google Doc, or Google Sheet, DOCX or XLSX file and use to build a template document with the data you have on Salesforce.
¶ Install Template Builder
Please follow the instruction below to install the "Template Builder" add-on.
-
Go to workspace.google.com/marketplace/app/maven_documents/430563853893 and click "Install"
-
Click "Continue" in the appeared message
-
In the opened window, select the Google account you want to use
-
Check out the listed permissions by clicking the "i" icon and then clicking "Allow"
-
Read the information message and click "Done"
After you follow these steps, you can now create your template using Google Sheets and use the "Template Builder" add-on.
¶ Run the Template Builder add-on
Building the template begins with running the "Template Builder" add-on on the Google Sheet.
-
Open the Google Sheet that you've added to the Document Template in the "Maven Documents" app on Salesforce and click on the "Extensions" tab
-
Hover over the "Maven Documents" item in the dropdown list and click on Template Builder
-
You'll find the "Template Builder" work pane on the right side of the Google sheet. The object and fields from the query that you built in the "Query Builder" tab are reflected in Template Builder.
¶ Meet Template Builder
Before you start building the template, let's have a glance at what we call the "Template Builder" work pane.
-
The "{} Fields" section. Here you will find the objects and the fields you added to the query in the Document Solution. All objects and fields can be inserted into the document with one click.
-
The "</> Elements" section. Here you have a picklist with the elements that could be inserted and used in the document. For detailed information on every element, you can check the "Google Sheets Elements Reference" page.
-
The "fx" or the "Expression Builder" section. Expression Builder lets you create expressions (similar to formula fields in Salesforce) without needing a formula field directly on your document. More on how Expression Builder operates you can find in the corresponding sub-section. You can also read more about expressions and their syntax on the "Expressions" page.
-
The "Refresh Nodes" button. This button serves two purposes:
- Reflect changes made on the Salesforce side. For example, you've changed the name of an object or edited the query in the "Query Builder" tab of Maven Documents. After clicking "Refresh", you would see the changes in Template Builder.
- The second purpose is to refresh the context inside the document to see fields from sub-queries. For example, if the cursor is placed inside a repeater or table, which iterates through a record, its name and included fields will also appear in the list fields in Template Builder after refreshing.
¶ Build Templates using Google Sheets and Template Builder
In Google Sheets, you can build your templates using everything that they have to offer. However, Maven Documents offers you to also put your Salesforce data in the document, and that's what Template Builder is used for. After successfully adding the document ID to the Document Template in Maven Documents, the "Fields" section in Template Builder will now show all the fields from two root queries that you created in the previous section: "Opportunity" and "Sales Agent". The "OpportunityLineItems" query fields are not available yet, because their availability depends on the context.
If the fields are not showing, click the "Refresh" button.
Now, all you need to do is click on the field you want and it will be added to the document. The fields from Template Builder will be added in the {!QueryName.FieldName} notation, and you can use that notation to add the fields manually, but in most cases, Template Builder is the easier option.
Now it's time to add some fields to the quote document. In our example we are using the cell in the second row and the B column and further examples will consider that location. But you can start with whatever cell you like.
Let's see how you can add the fields:
- From the "Account" and "Opportunity" queries, you'll use the "Name" fields in the title of the document.
- From Account, you'll use the "Billing Address" fields and you will use the "CreatedDate" field directly from Opportunity.
Pretty simple, right? All the fields you've put using Template Builder (or manually with the {!QueryName.FieldName} notation) will be dynamic, meaning they will depend on the results that the query returned.
Everything you have done up to this point has been the same as creating a document using Google Docs. Now you are going to showcase some elements (and element attributes) that are specific to Google Sheets.
In Google Docs you will use the "Table" element to show information about Opportunities'products from the "OpportunityLineItems" sub-query. In Sheets, you will use the "Repeater" element instead. In Google Sheets, you can use two types of Repeater: vertical repeater and horizontal repeater. The type of Repeater must be specified under the "Direction" attribute.
Let's use the vertical repeater to show the information about Opportunities' products from the "OpportunityLineItems" sub-query.
-
Select a cell where you want your products to be shown. Then, in Template Builder open the "Elements" tab.
-
Select the "Repeater" element. Repeater iterates through records selected in the "Value" attribute. The "Variable" attribute represents the current record in the iteration.
Referencing Variables inside Repeater requires you to place # (hash sign) before and after the name of Variables.
-
Select the following attribute values:
- Name: line-items
- Value: Opportunity.OpportunityLineItems
- Variable: oli
- Direction: vertical
- Last cell: E (Last column of the table)
-
Leave the "Hidden" attribute unselected and click "Insert Element".
Once you've inserted Repeater, you need to add field values from the Repeater Variable. Select the cell below mdoc:repeater tag and click the "Refresh" button to get the fields from the "oli" variable in Template Builder. Then insert those fields in their corresponding cells in between the opening and closing tag of mdoc:repeater element.
Next, let's add a payment details section to the "Sales Quote" Sheet.
The payment details section contains some simple info from your Salesforce org which you inserted using Template Builder.
You can also add the "Code" element that can be used directly from mobile banking applications, for example. But, don't forget to create the corresponding field in Salesforce, filled with the content required by the corresponding code type.
To insert the payment code, follow the next steps:
-
Select a cell in the document where you would like to insert your code and click the "Refresh" button in Template Builder
-
Select the "Code" element.
-
Select the following attribute values:
- Name: payment-code
- Type: PDF417
- Value: Opportunity.Payment_Code_Data__c
- Inside cell: false
The "Code" (and "Image") elements have the "Inside cell" attribute. If set to true then the element will be resized to fit inside the cell. You can read more about element attributes on the "Element Reference For Google Sheet" page.
-
Leave remaining attributes empty and click "Insert Element".
¶ Create Expressions using Expression Builder
Expression Builder lets you create expressions (similar to formula fields in Salesforce) without needing a formula field. You don't have to build formula fields which are only going to be used in your sheets. Instead, you can create them directly in your sheet!
Here are a few examples of what you can do using expressions and Expression Builder:
- display or hide text, images, tables etc. if certain criterion is met,
- format numbers (e.g. total sum of money),
- modify a date (e.g. 30 days from now),
- count and sum elements in a list...
You can read more about expressions and their syntax on the "Expressions" page.
Let's see how you can create a simple expression. You are going to add an expiration date to the quote. In the example, it expires 30 days after the quote (document) has been generated. Place your cursor where you want your date to be inserted, and follow the next steps:
- Open Expression Builder from the sidebar.
- Click on the icon in the bottom left of the expressions input field.
This will open Expression Builder.
First of all, you can see all the available functions in the "Select function" menu. The first dropdown menu filters functions by categories (date, number, string etc.), and the second dropdown menu contains (filtered) functions you can use.
You can also insert your Salesforce fields into expressions by using the dropdown in the "Insert field" menu (an arrow (>) next to a field suggests that it's a relationship field - e.g. Account > Contact > ...).
Before inserting the function or a field, verify that you've selected the text you want to replace in the text field below.
- From the "Select function" menu, choose Date and then choose addDays. Click Insert.
- You are going to replace DateTime in the expression with the current date (which is a function). From the "Select function" menu, choose Date, then now.
- Select DateTime in the text field below. Click Insert. This will replace DateTime with the now() function.
- Replace NumberOfDays with 30.
- Click Insert Expression. This inserts the expression back into the Expression Builder view. Click Insert Expression to insert it into your document.
Expressions can also be used within elements, on all attributes which support them. If an attribute has a switch next to it - it supports expressions. You can always read more about expressions on the "Expressions" page.
¶ Debug Document with Playground
If you want to see what the actual data will be available on your Document Template, feel free to use the "Playground" tab. It can also serve as a means to debug your flow when things aren't going as they should be.
Try changing the Locale definition in your settings, for example, and see how data changes. Magic isn't it?