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:
‘)
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
