September 17, 2018

How to create a business card draw using Dynamo, Revit and an Excel sheet.

Charl van der Merwe

Introduction and setup

For our upcoming Revit Usergroup we have planned a random draw whereby we’ll give away a prize to one lucky attendee. We’ve decided that we’ll do the draw with Dynamo ! Here is a summary of the idea:

Step 1. To create a Revit file containing Model text.

Step 2. To enter all the User group attendees name in a Excel file. This could be done by the reception lady entering name as attendees register or arrive. The file is saved on the server.

Step 3 will be to run the Dynamo script reading the excel file, picking a winner and writing the winner’s name to the Revit Model text.

This process above illustrates a simple task but the reading of Excel information and using it in Revit can be applied in many ways.

To recreate the Random Draw process in a step by step manner please follow the workflow below:

Firstly start up Dynamo from within Revit by going to the Manage Tab and Dynamo. You can use the latest version of Dynamo which is 2.0.1 downloaded from www.dynamobim.org.

You also need to load a Dynamo package called ‘Clockwork for Dynamo 1.x’ as one of the nodes we use are from this package. To download the Clockwork Dynamo package please follow part the following blog posted by my colleague: https://www.mgfx.co.za/blog/building-architectural-design/dynamo-clockwork-package/

The Dynamo process could be divided into a couple of parts:

Select and Read an Excel file.
Refine the list.
Selecting a random number.
Applying this number to the list.
Selecting and editing the Model Text.

1. Select and Read an Excel file.

To Start, open a new Dynamo project. You’ll be faced with an empty screen. In the dynamo search area, search node called: ‘file path ‘by typing file path in the search area. Select the searched item and the node will appear in the dynamo work area. See below.

Now search and place ‘File from Path’ node. This node will ‘get’ the file from the earlier selected path. Once placed, select the output connector from the ‘File Path’ node and thereafter select the input connector on the ‘File from Path’ node. See below.

This connection will create a workflow of tasks in which you tell Dynamo what to do and what to do next. Save the project.

Now search and place node called: ‘Data Import Excel’ and link the ‘File from Path’ node.

This node requires additional input nodes. Double click in the work area and a Code Block node should appear. In the Code Block type “Sheet1” with no spaces and including the quotation marks. To store click elsewhere. The quotation marks indicate that the Code Block contains text, also called a string, and not a number. The ‘Sheet1’ text indicates the name of the sheet inside Excel we’ll be looking at.

Create another code block and type only a 0 in the block and click elsewhere. This creates a 0 as number item. A number 1 turns a switch on and a 0 turn off a switch inside a node.

Link both Code Blocks to the ‘Data.ImportExcel’ node as shown.

These two nodes finishes the process to read from excel. The main node ‘Data.ImportExcel’ displays an error but that is because we have not browsed to any Excel file yet –as per the first node. We’ll do this when we test the part of the Dynamo script at the end of creating this section.

To group these nodes select them all by click-and-dragging a selecting window over them. Once selected right click and select ‘Create Group’. Name the group as shown.

To test this first part of the Dynamo script we need to create an Excel file with a couple of lines of text showing the names of the attendees in this case. So minimise Revit and Dynamo and open Excel. Create a list of names as below on Sheet1. Save this file and close Excel.

In Revit and Dynamo click on the first node and browse to the Excel file created earlier. If the Dynamo interface is set to run the script in Automatic mode run it will have finished otherwise click the Run button. You can also pin the result on the bottom of the node as shown. If this result window show a list then this first part is successful.

2. Refine the list.

The second part is to process and refine the list drawn from the Excel file.

Search for and place node called ‘List Transpose’. Now link the ‘data’ output from the ‘Data.ImportExcel’ node to the import ‘lists’ node, as shown. Also node swaps a list form being read horizontally to vertical. If you look that the lists below. The one on the left has 15 columns with 1 item in each and the list on the right has 15 rows in a column.

The next step is to add a node to look the list and ‘get’ the items listed at a certain column. Search for ‘get item at index’ node and place. Link the ‘list’ output and input.

For the ‘index’ input add a Code Block and add 0 as number. Link its output to the ‘index’ input.

The reason for the 0 value is to ‘get’ the items from the column called ‘0’ from the ‘List.Transpose’ node and add it to a new list. Note the items in the list on the left is under the ‘0’ column and the items on the right has been elevated to not be in a column but in the ‘root’ of the list, so to say.

Now group these three nodes and name it as below.

3. Selecting a random number.

The first step is to count how many items are in the excel sheet list. To do this you’ll add a ‘List.Count’ node. Link it to the workflow and Run the script. In our example there are 15 items.

The next node to use is one to select a number at random between a starting- and ending number. Search for ‘math random’ node and place.

This node requires two values: a starting value and an ending value. Now there are various mathematical routes you can take to give each number an equal chance of being selected as winner in this example. The route I took is to select from a list containing the number of items used in the excel sheet. In our case 15. Then include 0. So technically 16 items. I then round down the random generated value to get to the whole number. (This process gives the starting (0) and ending value (15) an equal chance of being selected as ‘winner.’

So to start create a Code block and add a 0. This will become the starting value to match the list.

Now link the ‘count’ output from the ‘List.Count’ node to ‘value2’ as input to the ‘Math.Random’ node. This will became the upper value.

The last node in this part is to round down the random number generated. Search for ‘math floor’ node, place it and link it to the output of ‘Math.Random’ node.

Group these nodes four nodes and call the group: ‘3. Selecting a random number.’ You may run the Dynamo script and pin the results to test the results.

4. Applying this number to the list.

This part is to take the random number that was selected at step 3 and match it to the entries on the excel spreadsheet. To do this is to add a node called ‘List.GetItemAtIndex’. Now link the main list at part 2 to this node at the ‘list’’ input and the random number at the ‘index’ input. You can also pin the result to test this node.

Group this node and rename.

5. Selecting and editing the Model Text.

This part writes the selected name to the Model Text in the drawing.

The first node to add is ‘TextElement.SetText’ node. This node is from the ClockWork Package so it has a yellow gear as icon as shown.

Link the ‘item’ output to the ‘text’ input.

Now add a node called ‘Select Model Element’ and connect its output to the ‘element’ input of the existing node.

To test the Dynamo script click on the Select button on this ‘Select Model Element’ node and select the Model text in your Revit Drawing. If should change to reflect the random name selected.

Select the two nodes and group it. Rename it as shown.

To test the Dynamo script, you can set the Dynamo UI to run Automatically. Then zoom to the 1^st part and unlink the path as shown and relink it. Each time you relink the nodes the Revit Model Text will change, each time picking a new ‘Winner’.