Tag: Excel

From Excel to SalesForce in Nintex Workflow Cloud

It’s easy we get into perception that a workflow always start from a form submission, and we’ve been telling the story of that. But in reality it’s not always the case, take Attendance List for instance, event coordinator taking attendance list with Excel at the reception of the event, is something very common and practical. the Attendance List will then be used to create Leads in CRM system for Account Management to follow up. This is just the right scenario we could easily automate in Nintex Workflow Cloud platform, I am going to share how the scenario could be automated in Workflow Cloud, the attendance of an event was taken using the Excel spreadsheet, and saved in OneDrive to trigger a workflow reading the attendance list to create leads in SalesForce.

Instead of explaining every single workflow action, I am just going to explain the key actions used to achieve our objectives here. Later part of this writing, I will share the pictorial flowchart of the workflow that I have created to give the overall view of the workflow design.

1. Microsoft Graph API to read the Excel range of records.

I am using the Excel Range Operations to get the range of cells from the excel file, for more details on the Graph API for Excel, you may refer to Microsoft Graph – Documentation – Excel , here is how the Get request forms: 

GET /{version}/me/drive/items/{item-id}/workbook/worksheets(<id|name>)/range(address='<address>’)
authorization: Bearer {access-token}
workbook-session-id: {session-id}

Example of my URL to get excel range from the excel file in a Group’s drive:

https://graph.microsoft.com/v1.0/groups/6a75c864-e199-4983-92a0-d746acf7ad0b/drive/items/01RFTJSKJSC…
Smiley Very Happy

‘)

Here is the example on how you configure it in Workflow Cloud:

The Response content of this action is saved to the resContent variable as shown. We will be interested to look at the “Values” of the returned JSON content.

2. Extract “values” from the returned JSON content

We use the “Query JSON” action to extract the “values” from the returned JSON content that was saved to resContent variable. The “Query JSON” action gives us row variable with value of [ [“Alice”, “Kho”, “Licek@mail.com“, “Abc o.” ],  [ “Jason “, “Smith”, “Jason.smith@ntxte07.com“, “Ntxte07” ], [  “”,  “”,  “”, “” ] ] as shown below.

3. Convert “values” string to Collection

“Apply a regular expression” to convert that single string of text (i.e. row variable) into array/collection of string and save it to rows collection variable, The Extract operation of the Regular Expression using the Pattern of (?<=\[)[^\[\]].*?(?=\])+ gives us the outcome of rows collection with three items as shown.

4. Get fields of each row of attendance records

“Start a loop” logic action to loop through the rows collection to “get item from collection” to get each of the item in the rows collection and save it to row variable. The following regular expression with pattern = (?<=\”)[^\,].*?(?=\”)+ extracts each of the field from the row and saved it to fields collection variable.

That shows us how we get the attendance list row by row from the Excel file that was saved in a Group drive.  

5. Create leads in SalesForce

We can simply use the SalesForce “Create a record” action to create leads in SalesForce. here is how it’s configured in Workflow Cloud using the “fields” collected from excel.

The diagram below illustrates the complete workflow design in Nintex Workflow Cloud

Using Document Generation to create Excel Report from List Items

With the new Nintex Document Generation action in Nintex for Office 365, it is now possible to create an Excel Report from Sharepoint List items. With the output options of either its original format (i.e. Excel file in this case) or PDF in the defined destination of Sharepoint folder, you can then automate the generated report document for approval as usual in Nintex Workflow.

In the following diagram, I have my Expense home page consists of Expense Items (i.e. Customer List) and Create Expense Report query form. Create Expense Report Query form is a simple form one can use to specify report filter criterial (i.e. Title, Start Date and End Date, etc. of the items to be included in the excel report to be generated).

The Create Expense Report query form is nothing but just another custom list to trigger a Document Generation workflow (i.e. “Process Expense Report – Demo” workflow in this case) to export the filtered list items to an Excel document with the newly available Document Generation workflow action. Here is a screen capture of the created Expense Report Query custom list. The “Process Expense Report – Demo” workflow will be triggered when new item (i.e. “KK Expense Claim for Dec” item in this example) being created.

Once the “Process Expense Report – Demo” workflow is being executed, it will generate an Excel document and output the file to the defined destination folder (i.e. Expense Reports in my scenario) as shown in the diagram below

The output of the Excel file (i.e. “KK Expense Claim for Dec – (Demo)” is shown below.

The “Process Expense Report – Demo” workflow is basically a 7 steps workflow made up of 13 workflow actions as shown in the workflow diagram below

StepsWorkflow ActionsWhat it does…
1Action ①Query List (i.e.Expense Items custom list) returned a list of items stored in list items collection
2Action ②Set the Variable idx to 0
3Action ③Loop through the list item collection to..
4Action ④-⑨Create dictionary pair of key (i.e. Field Name), and Value (i.e. Field Value). The dictionary has 7 pairs of 7 fields of expense list item to be built.
5Action ⑩Build a dictionary (i.e. dicItem dictionary) of the Expense list item (i.e. itemNo, itemExpType, itemDate, itemCurrency, itemAmount, itemAmountInSG).
6Action ⑪Add the list item dictionary (i.e. dicItem) to a new Expense collection (i.e. collExpenses)
7Action ⑫With the Expense Collection (i.e. collExpenses) as input, the Doc Gen action will used the “Expense Form – DEMO.xlsx” document template to generate “KK Expense Claim – Dec (Demo).xlsx” excel documents

Here is how I visualized the outcome of the built Collection “collExpenses” done by “Action 11” after completing the “Action 3” for-each loop.

Document Generation Action uses the “Expense Form – DEMO.xlsx” as template, and generate the Excel document which was then placed in the “Expense Reports” SharePoint Document Library. Diagram below highlights how the “Expense Form – Demo.xlsx” template is being “Tagged” in my example for Document Generation action to insert values into it.

The configuration of the Document Generation should be straight forward, here is the configuration of my example

The key challenges for my first time testing getting the Document Generation to work for creating the “Row Replication”, would be surrounding the building of “collExpenses” collection and Tagging the excel template for the repeating row purposes. Thanks to @Dan Stoll‘s hint on how to create the collection to be used as variable by the Document Generation action (i.e. Dan’s blog on that could be found here -> It’s here… Nintex Document Generation . With this blog, I am hoping it will help those who encountered the same challenges as me.