Add new customers to a SharePoint List

About

Summary: Add new customers to a SharePoint List and create Salesforce accounts using their data.

Applications: SharePoint Lists, Excel, Salesforce

Description:

This example explains how to add all the new customers from a new Excel file to a SharePoint List, then create a new account and contact in Salesforce for each of them. Next, update the SharePoint List items with the Salesforce ID and status Processed. Finally, create a new folder for processed files and move the Excel file.

This example presents Microsoft 365 activities such as File Created, Add List Item, Get List Items, as well as Salesforce activities such as Create Account and Create Contact.

Note:

SharePoint List activities use column names, but the Studio user interface exposes the columns' display names. For example, if you have a column named Salesforce ID, when you retrieve this field in a variable, the variable will be [...]field_5, because field_5 is the column's name in the backend.

Workflow

Open Studio Web and create a new project.
For How to start the automation select Event based, then select the Microsoft 365 File Created trigger activity.
In the trigger activity:
1. Set up your Microsoft OneDrive & SharePoint connection.
2. Set the In Location field. For example, select one of your OneDrive folders.
Add a For Each Row in Workbook activity:
1. In the Workbook field, select the output variable of the File created trigger activity.
2. Select the Range. For example, the first sheet in the workbook, Sheet1.
3. Configure the other fields to suit your needs. For example:
  1. Has headers - Set to True.
  2. If row is empty - Set to Skip.
Inside the For Each Row activity, add a SharePoint Add List Item activity, to create new customers.
1. Choose the SharePoint list you need.
Next, still inside the For Each, add a activity. Rename it to Write Cell - Status, to easily identify it later, and configure it:
1. In the Workbook field, select the output variable of the File Created trigger activity.
2. Select the Sheet/Named range.
3. In the Cell field, select the CurrentRow variable of the For Each Row in Workbook activity and drill down to the Status_Address property.
4. For What to write - Type Added into the field.
Add a second Write Cell activity. Rename it to Write Cell - SharePoint List ID and configure it:
1. In the Workbook field, select the output variable of the File Created trigger activity.
2. Select the Sheet/Named range.
3. In the Cell field, select the CurrentRow variable of the For Each Row in Workbook activity and drill down to the SP_List_ID_Address property.
4. For What to write - Select the output variable of the Add List Item activity and drill down to the ID property.
Next, outside the For Each Row activity, add a Get List Items activity. This retrieves the customer information.
1. Select the SharePoint list.
2. In the Columns to retrieve field, select all the customer fields you need. For example: Firstname, Lastname, Email, Phone, Salesforce ID, ID, and Title.
  
  Note: These are the columns' display names. The column titles in the backend will differ.
Add a For Each activity:
1. For the List of Items field, select the output variable of the previous Get List Items activity and drill down to the Rows property.
2. The Item field is automatically set to currentDataRow.
Inside the For Each, add an If activity. This establishes what to do in case the Salesforce ID field is empty.
1. Set the Condition: if currentDataRow.field_5 (the Salesforce ID from the Columns to retrieve) is empty text. In the Expression editor, the condition is: string.IsNullOrEmpty(currentDataRow.field_5) = true
  Note: Salesforce ID is the display name, field_5 is the backend column name.
Set the Then branch, i.e., the activity to execute in case the condition is true.
1. Add a Log Message activity, to log the list item title instead. In the Message field, select the For Each currentDataRow variable and drill down to the Title property. This corresponds to the customer name (the Title column in the SharePoint list).
Add a Salesforce Create Account activity.
1. For Account name, select the currentDataRow.Title variable.
Add a Salesforce Create Contact and configure it. Match the activity fields with the information retrieved using the Columns to retrieve field in the Get List Items activity:
1. Last name - Set to currentDataRow.Lastname variable. Displayed as currentDataRow.field_2.
2. Account - Select the output variable of the Create Account activity.
3. Email - Set to currentDataRow.Email variable. Displayed as currentDataRow.field_3.
4. First name - Set to currentDataRow.Firstname variable. Displayed as currentDataRow.field_1.
5. Phone - Set to currentDataRow.Phone variable. Displayed as currentDataRow.field_4 variable.
Next, add an Update Single List Item activity and configure it:
1. Select the SharePoint list.
2. For List item id, select the For Each currentDataRow.ID variable.
Don't configure the Else branch.
Next, add a Get File/Folder activity:
1. File or folder Url - Select the output variable of the File Created trigger activity and drill down to its ParentUri property.
Add a Create Folder activity and configure it:
1. Folder name - For example, set the name to Processed Files.
2. Parent folder - Select the output variable of the Get File/Folder activity.
3. If folder already exists - Set to Auto rename.
Add a Move File/Folder activity, to move the new file to the processed items folder.
1. File or folder to move - Select the output variable of the File Created trigger activity.
2. Destination folder - Select the output variable of the Create Folder activity.
3. If file/folder already exists - Set to Replace.
You're done and ready to run your project!