This is the second post in a series of 3 about linking Revit to Excel with Dynamo. Click here to download the entire series in PDF format, along with 2 dynamo scripts.

In this post, you will learn how to create a Dynamo script that pushes information from Excel to Revit.

In this scenario, we imagine that a project manager updates a spreadsheet with room information. The BIM manager needs to push this data to Revit. In addition, we have more requirements:

1. The script should work with any project.

2. The data might not always be in the same order.

That means the Name column might sometimes be in column 1, sometimes in column 2, and so on. We’ll need to search for the field.

Also, we’ll need to find a way to have the Excel spreadsheet location as a parameter in the Revit model so users don’t have to manually pick it each time. That makes the script universal.

1- Add “Excel Location” Parameter

There is a different associated Excel sheet for each Revit model. Create a new parameter called Excel Location. It is a text parameter and should be set to Project Information. Set the windows location of the spreadsheet to this parameter:

2- In Dynamo, Extract Excel Location

Use these nodes to get the Excel Location text parameter:

The next steps are quite similar to the previous script. Retrieve Excel data. In this case, you must flatten the list by an amount of 1. This is because we are using a text parameter for the file path.

3- Transpose the Rest of the Data

Separate the data into the FirstItem, then into the RestOfItems. For the RestOfItems, use the List.Transpose node. This will regroup the values for each parameter.

4- Get all Rooms, Extract the Name

Now, let’s get all the rooms in the project.

Extract the name from the rooms.

5- Get Column Number for Name Parameter

The List.IndexOf node lets us know in what column the Name parameter is located. This way, the script works regardless of which column the name parameter is located. Since the name is on the first Excel column, we get the value 0.

6- Extract Excel Room Names

Use the List.GetItemAtIndex node to get all the values in column 0.

Now, we have a list of Excel room names and a list of Revit room names. You only want to keep elements that appear in both list. Read on.

7- Get Index of Revit Room Names in Excel

Once again, use the List.IndexOf node. This time, the elements are the revit room names, and the list is the Excel room names.

You can see we get a list of values. Revit room #1 is located at index 0, which is the first row. Revit room #3 is located at index 8, which is the 9th row. Remember that in Dynamo, the first item is set at 0 and the second at 1.

A value of -1 means that the Revit room wasn’t found in Excel. You’ll want to get rid of it.

8- Filter out Revit Rooms Not Found in Excel

Use the == and the List.FilterByBoolMask nodes to remove all -1 values. Since all -1 values will return a true value, keep the elements in the out output from the Filter node.

You’ll also want to filter out the actual Revit rooms with the same output from the == node.

9- Get Parameter Values to Modify in Revit

In this example, let’s say you want to modify the Comments value. All Revit rooms that have a name match in Excel will have their comments value modified.

The first part is similar to extracting the name value from Excel. Except that this time, we search for the Comments value. This is set at index 4, so the 5th column in Excel. Then, we acquire all the comment values from Excel.

10- Only Keep Filtered, Ordered Values

Use the output from the FilterByBoolMask node you’ve created and stick it in the GetItemAtIndex node.

This way, you only keep the comments values from room names found in both Revit and Excel.

Also, the comments values are reordered to be in the same order as the Revit rooms in the list.

11- Replace Null Values by Empty String

If a value is empty in Excel, this will cause a “Null” element in Dynamo. That can mess things up and create warnings. Use the ReplaceByCondition and Object.IsNull nodes to replace the Null elements with an empty string.

As you can see, the yellow “null” are replaced by emptiness.

12- Set Parameter Value by Name to Rooms

The final step. Use the Element.SetParameterByName node.

The elements are the Revit rooms, excluding those not found in Excel.

The parameter is chosen by the user. In this case, Comments.

The values are the strings extracted from Excel.

13- Verify Revit Schedule Data

Verify the Revit schedule. Ensure the values match Excel.

What is unique about this script is that:

• You don’t need to pick the Excel file each time. This is extracted from a field in each Revit model.

• The parameters in Excel don’t have to be in a specific order. The names only need to match.

• The script only keeps elements that are both in Excel and Revit. It's not a big deal if the list is not identical.

Want to download this post in a PDF format, including extra tips and how to schedule + automate scripts? Want a copy of the Dynamo scripts? Download issue #30 of our Pamphlets publication. You will receive all pamphlets.