Update List Items from a Spreadsheet using REST API calls in a Workflow

Scenario: There is an existing SharePoint lists that stores all jobs and their information. The user has a spreadsheet with additional information to populate for the each job. This can be done manually but this will be quiet time consuming as there are 3000 items to update. A faster approach is to imported the spreadsheet to SharePoint and then set up a site workflow to find the matching jobs in the imported list vs the existing SharePoint list and update the relevant data for each job.

Instructions:

1. Import the spreadsheet to SharePoint.
   Select Add an App, then select Import spreadsheet. Enter the name for the list and select the spreadsheet to import. The list will be created with all data
2. Open SharePoint Designer and create a 2013 Site Workflow.
3. Stage 1: Set Variables
   1. Add an Action to Set a string variable. This variable will be used to set the API query. We will query the existing SharePoint list.
      Query: [Site URL]_api/web/lists/GetbyTitle(‘Jobs‘)/items?$top=5000
      Replace [Site URL] with your site and ‘Jobs’ with your list title. By default, it will query only the first 100 items. If you have more items add ?$top=5000 to the end of the URL.
   2. Set an integer variable called Index to 0. The index will be use to loop through all items in the list.
   3. Add an action to Build a dictionary. This is needed for when you use the Call HTTP Web Service action. Create 2 items in the dictionary Accept and Content-Type both set to the same value: application/json;odata=verbose
      Output should be the dictionary variable called requestHeaders.
      
4. Stage 2: Call Web Service
   1. Insert Action: Call HTTP Web Service. Call the query URL variable set previously. Set the dictionary variables for responseContent and responseHeaders.
      
      Go to advance settings of the action and set the requestHeaders variable.
      
5. Stage 3: Process Response
   1. Add the action Get results from dictionary to get the results from responseContent and output to a dictionary variable for all items.
   2. Count the items in the dictionary to determine how many items the workflow will now have to loop through and update.
   3. Inset a Loop to run n times. Select n to the count variable.
   4. Get the results from the responseContent but this time include the index so that it will get the first item from the responseContent.
      d/results([Index])
      As it loops through each item we will add one to the index so it gets the next record.
   5. Get the field that has the unique identifier per item this will help the workflow find the matching item in the imported list, in this case its the field called Job No. We need a field that will have a matching unique value in both existing list and the imported spreadsheet list item, so we can get the new data for the correct job and update the corresponding item.
   6. Get this items ID from the dictionary so we know which item to update.
   7. Now run another API query to find the matching item in the imported list with the matching Job No.
   8. Set the query for the job URL – make sure to use the internal name for the column that is being filtered on.
      Query: [Site URL]_api/web/lists/GetbyTitle(‘ImportedList‘)/items?$filter=Title eq [Job No]
      Replace [Site URL] with your site and ‘Jobs’ with your list title. Look up the Job No from the variable created when we got exsiting list items Job No. (Number 5)
   9. Call the HTTP Web Service for the imported list filtered by Job No. Remember to set the request headers in the advance settings
   10. Get the results from the response Content and count the items returned.
   11. Add a condition, if count is equal to 1 then we will update the related item. If there are no results, it will move to the next item.
       If it didn’t return a value of 1 then there may be a duplicate or no matching records and you may want to log that so you can review those items later.
   12. Get the field values needed from the imported list to update the existing list using the get item from a dictionary action.
   13. Add the action to Update list item, select the list you want to update (the existing list). Then select the fields you want to update and set the value to the variable that was extracted from the related list item. Identify which item to update  which is where ID is equal to the variable ID.
       
   14. Once the item is updated, update the index by adding 1 to the index so it updates the next item in the dictionary.
       It will loop through all jobs until all items are updated.
6. Publish the workflow and run it from Site workflows

Note:
If you need to populate a multi select choice field, separate the choices with #;#; in the spreadsheet. Import the spreadsheet to SharePoint, the field will be a single line of text. When you run the workflow and select the single line of text field to populate the multi select field and it will populate properly as multiple values.