¶ XLSX Templates
The Maven Add-in for XLSX files is a powerful tool that helps you build an Excel sheet with data from Salesforce.
¶ Install Maven Add-in
You can use the add-in in both the online and offline versions of Microsoft Excel. However, the installation process differs between them.
¶ Online version
Follow the next steps to install the Maven Add-in on the online version of Microsoft Excel.
-
Go to Office.com or Microsoft OneDrive and log in to your Microsoft account.
-
Create a new Excel book or open an existing one.
-
On the "Home" tab in the Excel taskbar, click on Add-ins.
-
In the dropdown menu, click the "+More Add-ins" button.
-
It opens the Office store. Search for the Maven Add-in there.
-
Select the Maven Add-in and you'll be redirected to the add-in's page. You can find some useful links and information there. Then click the "Add" button.
-
Read terms and conditions. If you agree with them, check the respective box and click Continue.
-
You'll see the "Welcome" message that includes some useful links. Click Let's go.
Voilà! The Maven Add-in can now be used on the online XLSX file.
¶ Offline version
Follow the next steps to install the Maven Add-in on the offline version of Microsoft Excel.
-
Go to https://appsource.microsoft.com/en-us/product/Office365/WA200008920 and click Get it now.
-
After the installation is complete, click Open in Excel.
-
When Microsoft Excel opens, you'll see the standard Microsoft page with quick instructions on launching the new add-in. Click on the "Trust this add-in" button.
-
You'll see the message that the Maven Add-in is successfully loaded and its logo appears in the Excel taskbar.
That's it! Now you have the Maven Add-in installed!
¶ Run Maven Add-in
The first thing that you should do before building the template is to run the Maven add-in on your XLSX file and log in to the Salesforce account.
-
Run Microsoft Excel and create a new or open an already existing book.
-
In the "Home" tab, click on the "Add-ins" icon and select the Maven Add-in from My Add-ins.
-
You'll see the "Welcome" message that includes some useful links.
Click Let's go. -
After the Maven Add-in task pane appears, click the "Login to Salesforce" button, which looks like a cog.
-
A new dialogue window should open, offering two login options:
- Log in to Production
- Log in to Sandbox
Select where you want to log in.
We recommend you log in to Sandbox first to see if everything is working fine.
-
In the following dialogue window, enter your credentials and click Log in.
If this is your first time logging in to the Salesforce account with the Maven Add-in, user verification is required from the Salesforce side. You'll receive an email with the verification code, which you should enter in the required field.
-
Click Allow.
After that, the new dialogue window opens, where you finish configuring the add-in.
-
The first step is to select the Maven Documents instance that fits your Org location and type. Currently, there are two options available:
- EU staging. Choose this option when you log in to the Sandbox Org.
- EU production. Choose this option when you log in to the Production Org.
-
After that, select the Document Solution you would like to use.
-
Once you select the Solution, you can select the Document Template to which you will upload the document.
-
By marking the "Override sharing options" checkbox, you can change the access rules to the document you upload. With that feature, you determine who can generate the document using this template.
Here are the options you have:- Share View All. Anyone who has the link can access the uploaded file.
- Share View Organization. Users from the particular domain can access the uploaded file.
- None. The file is private, meaning only the creator can access and use it.
If users are authenticated with "Per User Principal", meaning with their own Microsoft credentials, you need to make sure they have access to the template to generate documents. If the user is authenticated with "Named Principal" and is also the owner of the template, then there's no need to Override sharing options.
More about authentication types you can find here. -
If you'd like to upload the file immediately, check the box below. Otherwise, leave the field empty.
You can upload the file later with the add-in. -
Click Done.
Under the "Solution/Template Select" title, you'll find the information on which org you've connected the Maven Add-in.
Once you finish the login process, you'll see that the Maven Add-in reflects the query from the connected Document Solution.
As the taskbars in online and offline versions of Microsoft Excel are almost the same, the process of running the add-in on the offline version will be similar.
¶ Meet Maven Add-in
Once you finish the login process, you can finally use the Maven Add-in.
Before you start, let's take a glance at what we call the "Maven Add-in work pane." Let's divide it into two parts: the "work" part and the "administration" part.
¶ The "work" part includes:
-
The "{} Fields" tab.
Here you will find the objects and the fields you added to the query on Document Solution. All objects and fields can be inserted into the document with one click.
-
The </> Elements tab.
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 "XLSX Elements Reference" page.
-
The fx or Expression Builder tab.
Expression Builder lets you create expressions (similar to formula fields in Salesforce) without needing a formula field directly on your document. You can read more about expressions and their syntax on the "Expressions" page.
The icon in the bottom left of the expression input field opens Expression Builder.
First of all, you can see all 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 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 > ...).
After the function and fields are inserted, they appear in the text field below. You can edit the expression manually or use the Expression Builder again.
Before inserting the function or a field, verify that you've highlighted the text you want to replace in the text field below
¶ The "administration" part includes:
-
The "Refresh Nodes" button.
This button serves two purposes:- To reflect changes made on the Salesforce side. For example, you've changed the name of an object or edited the query in Query Builder. After clicking "refresh", you will see the changes in the add-in.
- 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 on the Maven Add-in after refreshing.
-
The "Login to Salesforce" button.
You are already familiar with this one. It helps to connect the Salesforce Org with the Maven Add-in and select what Document Solution and Document Template to use. -
The "Upload File to Template" button.
Click on this button to upload the DOCX file you are working on to the Document Template that you selected in the Login to Salesforce step.After changes are made to the DOCX file, you need to upload it to Document Template. Because once the file is uploaded to Salesforce, it doesn't refresh automatically. To have the up-to-date version, you need to upload it after changes are made.
¶ Build Templates using Maven Add-in
Now that you know how to install and run the Maven Add-in on the XLSX file, it is time to create and modify the template document using all the add-in abilities.
¶ Add fields and expressions
In our example, we are using the cell in the second row and the "B" column, and further examples will consider that location. You can start with whatever cell you like.
Write down the header of the document and place the cursor in the cell below so you can insert the fields there.
One cell can hold only one merge field.
First of all, you need to log in to Salesforce to have the up-to-date query in the add-in.
-
In the first page's header, you can place some information about the organization, for example, the name and address. You can select all these fields from the Maven Add-in.
-
Then, we use the "Name" field from the "Opportunity" Object to show from which opportunity you will take the data. Select the "{}Fields" tab and click on the "Opportunity" Object. In the dropdown list of fields, click on the "{}Name" field. So, in the cell under the header of the document, you will have the following merge field: {!Opportunity. Name}.
-
Then use "Name" and "Billing Address" fields from the "Account" Object to indicate who will use the quote. Also, insert the "CreatedDate" field directly from Opportunity to show when the quote was prepared.
All the fields we've put using the "{!QueryName.FieldName}" notation are dynamic. It means they depend on the results that our queries returned.
-
After you have the prepared date in the document, you need to add the expiration date in your quote. For example, it expires 30 days after the quote (document) has been generated. So let’s use some magic to do that. By magic, we mean Expressions.
-
Select the "fx" tab on the add-in work pane. As you remember, this one stands for the Expression Builder.
-
Open the Expression Builder by clicking on the icon in the bottom left of the expressions input field.
-
In the "Select function" section, select the "Format" function type and the "format date" function. Click Insert. That function makes the date look in the "dd/mm/yyyy" format.
-
Highlight the "DateField" in the text field below, and select the "Date" function type and the "addDays" function in the "Select function" section. Click Insert.
-
Now you need to replace DateTime in the expression with the current date. From the Select function menu, choose the "Date" function type and the "now()" function. This function reflects the current date and time.
-
Highlight DateTime in the text field below and click Insert. This action will replace DateTime with the "now()" function.
-
Replace NumberOfDays with 30.
-
The last thing is to change Format to short.
The result should look like this.
-
Click Insert Expression and then Insert Expression one more time.
-
If needed, you can use Excel formulas with merge fields from the add-in as building blocks. The result of such formulas is shown only after the template is generated and the "Edit" mode of the generated file is activated.
¶ Insert elements
The next step is to display the information about our Opportunity's products.
If you remember, in the "Query Builder" article, you added OpportunityLineItems as a sub-query. For the sub-query to be available in the Maven Add-in, you need to be in a specific context. Sub-queries can only be accessed from within an element that belongs to their parent query. Like Repeater, for example. This is where the "Elements" part kicks in.
Elements are the basic building blocks of a document or sheet. They help turn a plain Excel file into a content-rich document. Elements use HTML-like code and are added directly to the template.
As you can notice, there is no Table element in the "Element Type" list. Because an Excel sheet is a table itself, instead of Table, you will use the "Repeater" element to show the information about the products.
In XLSX files, you can use two types of Repeater:
- Vertical repeater
- Horizontal repeater
The type of Repeater must be specified under the "Direction" attribute.
Repeater is an iteration component that allows you to output the contents of a collection according to a structure that you specify because the whole body of the "Repeater" element will be shown for every record in the collection.
- Write down the headers of every column of the table starting with column B.
- Place your cursor in the cell where you want your products to be shown. Then, in the Maven Add-in, open the "Elements" tab.
- In the "Element Type" field, select the "Repeater" element.
- Fill the fields with the following values:
Name: line-items
Value: Opportunity.OpportunityLineItems
Variable: oli
Direction: Vertical
Last Cell: F - Leave the "Hidden" parameter untouched and click Insert Element.
Take a look at the screenshot.
Now that you have inserted the table header and Repeater, it is time to fill them with data.
- Place the cursor in the cell inside Repeater and click the "Refresh" button on the add-in. It retrieves fields from the "oli" variable shown in the "Fields" tab in the Maven Add-in.
- Insert fields into their corresponding cells in the row inside Repeater.
- In the bottom row, put "Opportunity Amount", by adding the field from the Maven Add-in.
- Make some cosmetic changes, like changing the color of the rows and making the font bolder.
The table should look like this.
All the Repeaters you add to the document should have different names, so the data in the generated document is shown properly.
¶ Continue the modification with known tools
The next part of the template is content related to terms and conditions. With the power of Microsoft Excel, you can edit the content. Using the Maven Add-in features, you will add a few placeholders to dynamically populate the account name and delivery period, for example.
Following the terms and conditions, the "Payment Details" section appears in the template.
Again, the combination of Microsoft Excel editing features alongside Maven Add-in abilities helps you create the required template with all the needed data.
Another section of the document will contain the contact details of the sales agent and the customer.
Add the sales agent's contact details by using the respective fields from the add-in. To insert the customer contacts, you can use the already-known "Repeater" element:
- Place your cursor in the cell where you want the information to be shown. Then, in the Maven Add-in, open the "Elements" tab.
- In "Element Type" fields, select the "Repeater" element.
- Fill fields with the following values:
Name: contacts
Value: Opportunity.OpportunityContactRoles
Variable: con
Direction: Vertical
Last Cell: F - Leave the "Hidden" parameter as it is and click Insert Element.
When the Repeater is inserted, fill the corresponding cells with the needed data.
- As in the previous example, the first row will be the header row. So, populate its cells with the names of the fields.
- Put the cursor in the second row and refresh the query. With that move, you have the fields from the sub-query appear in the Maven Add-in.
- Insert needed fields inside the respective cells of the table.
In the screenshot below, you can see what you could have as a result.
We should also mention the option to remove or hide any rows or columns according to your needs. There are Row and Column elements that can do that for you.
-
Select the "Row" or "Column" element in the Maven Add-in.
-
If you want to remove the row or the column, leave the "Collapse Only" checkbox empty.
-
If you want to hide but not remove the row or the column, then mark the "Collapse Only" checkbox.
-
Insert the element in the corresponding row or column and generate the template.
So the provided element removes the selected column...
...and the element below only hides the defined row.
With all the steps from above, you can create any Document Template in Microsoft Excel.
¶ Upload the XLSX File to Document Template
Now, when you have the document ready, you can easily upload it to the Document Template that you chose in the Login process.
All you need to do is click the "Upload File to Template" button on the Maven Add-in work pane. Once you've done it, you'll see the message that the file has been successfully uploaded.
The way the uploaded online file is displayed in the corresponding Document Template differs from offline versions of the file.
- If you are uploading the online XLSX file, the "Document Template URL" field is populated with the file's URL. The "Document Template Id" field is populated with the ID of the uploaded file.
- If you are uploading the offline XLSX file, the "Document Template URL" field stays empty. The "Document Template Id" field is populated with the ID of the uploaded file. The uploaded file appears in the "Files" section of the Document Template.
Once the template document gets Template ID, you can generate the file.
Template Document ID appears a couple of seconds after the file was uploaded. If the ID doesn’t appear in a maximum of two minutes, try uploading the same file one more time.
The waiting time solely depends on the load that Salesforce is dealing with, including upload time.
If you want to add a new file to the Document Template, you can use the "Add Files" button or the "Upload New Version" option. Maven Documents will use the latest file version that is uploaded. To keep things clean, it is good practice to have one file per Document Template uploaded.
¶ 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 the Flow when things aren't going as they should be.
Try changing the Locale definition in your settings, for example, and see how the data changes. Magic isn't it?