few hours with …Grasshopper and Excel

Some short time ago I was introduced to a plug-in named Grasshopper. This plug-in is a built-in software named Rhino. Even when I did a few simple exercises in it I was very excited and knew that I wanna dive deeper into this. Rhino is a design software primarily used in architecture, industrial design, and jewelry design. Grasshopper is a plug-in that works with generative design — it gives us a visual interface for building algorithms that generate geometry for Rhino. One thing that was interesting for me was that Grasshopper is compatible with a lot of other software like Tekla Structures and Excel which I use 5 days a week. Besides these two software's you can connect grasshopper with Autocad, Autodesk Revit, Unity, Unreal Engine, and others. So I want to show some very simple basic examples where Grasshopper connects with excel. I work a lot with excel but I always avoided VBA and macros. I was already learning two programming languages and the third was not an option at the time.

It’s important to understand, that here I am just showing a very small piece of the puzzle. In real-life situations, you probably won’t use the Grasshopper plug-in to approach these easy situations, but this could help you spot situations where this plug-in can help you a lot.

In this tutorial, I want to show three quite easy situations that can be automated using the Grasshopper plug-in. These situations are very basic but I hope you will get an idea of how to use it and upgrade it to your more complex problem. I know that you can solve these problems using Excel built-in macros and using VBA, but this time I wanna show you how to do it using the Grasshopper plug-in. Additionally — data that you get from excel can be transferred to all these compatible software and also reversed — data from software can be exported to excel.

You can go to the Rhino homepage and get a free trial for 90 days after that you will need to buy a license. To not make it a very long tutorial I will not show how to download — install — open if you have a problem with that just google it.

The first situation — count together data with the same name

If I need to count together data from an Excel sheet, my manual approach in Excel would be :

1. All data which need to be counted together are in column A
2. Copy the whole list of data to a new cell F
3. Select all data in the F column and use the function “Remove duplicates” to get unique values
4. Use the function “COUNTIF” on unique values to get a count of each data

It’s quite easy to do in Excel but if you need to do it a lot of times, it just takes your time, because you are doing it manually. Here we can use the Grasshopper to create it once and use it how often it’s necessary.

To get a block into the canvas you can try to find it in the list above the canvas or just do a double mouse tap on the canvas and start writing the block name.

So how can this be done in the Grasshopper:

1. Add block “Read Excel Sheet” to the canvas. This block will read an Excel file. In this case, will read just column A with data that is necessary to count. You can use “Panel” to see what kinda a piece of information is coming out of blocks.
2. As you will see, this block requires a few parameters to work. In this case, we will fill just two of them, so in front of the block add “Boolean Toggle” which works like a Button with True or False values. You need to switch it to True so it works. Another block that is necessary to add is “File Path”. This block will hold a reference to your previously saved Excel file. To add your Excel reference to the Grasshopper click the right mouse button on the “File path” and then click “Set One File Path”. Find your excel file and you will have referenced your Excel file. If you have some changes in the Excel file, then save your Excel file, and in Grasshopper just toggle “Boolean toggle” to False and then back to True and everything will recalculate
3. Add block “Create Set” and connect “Columns” output from “Read Excel Sheet”. This will create a new list with unique values (same as “Remove duplicates” in excel)
4. The next step is to add the “Member Index” block to the canvas. In this block add “Columns” output to “Set” input and “Set” output from “Create Set” to “Member” input. This block can find all indexes of values you need to find in the “Index” output. But in this case, we will use another output “Count” which already counts together values with the same indexes
5. If you need to know the result and that’s all then you are finished, but we can give a nice output to excel. To do that first we need to merge data together so it can be displayed in Excel. Add the “Merge” block to the canvas. Connect outputs “Set” from “Create Set” and “Count” from “Member Index”
6. The last step is to add the “Write To Excel” block and fill it with four inputs. The first input is “Boolean Toggle”, you can add a new one or just use the old ones. Next, you will need two “Panel” blocks, in the first write “Result” and connect it to the “Excel Worksheet” (excel will have a new sheet with this name). In the other panel write “FALSE” and connect it to “Columns or Rows?” (this just gives information that you need to use vertical alignment). And the last thing to do is add “Results” output from “Merge” to “Data to write into Columns”. I strongly advise connecting “Boolean Toggle” as a false and when everything is ready change it to true. If the toggle is on true, then every time you do something with blocks you send new info to excel and if something is not finished you can get strange results

The second situation — count together details with the same dimensions

Let’s upgrade our previous solution with some additional functionality. For example this time we don’t care about the name, but we need to count together details that have the same dimensions. So we need to compare it to cells this time. So this time my manual approach in Excel would be:

1. Use function “Concatenate” with a comma between width and height to get both values in one cell, in this example it’s F
2. Copy data from the column F to column H without formula (paste special — values)
3. And then you can use the steps from the first situation

This is also quite an easy situation to solve in excel, but again — the more data you have, the more times you need to do it — boring and not very effective. So how can we do this in Grasshopper? We can use all blocks from the first example and additionally between “Read Excel Sheet” and “Create Set” add three new blocks:

Zoom:

1. First, we add the “Cull Index” with parameters “Row” from “Read Excel Sheet” and 0 to “Indicies”. This will remove A column from excel, where was written “DATA”. We don’t need this information for this situation so we get rid of it.
2. Next, we add “List Item” to get B and C columns from excel. When zooming closer to “List Item” you will see + sign, click on it to get “+1” output in “List Item”
3. And one more block that is necessary to add is “Concatenate”. Same as in excel — putting both dimensions in one cell with a comma between them

The third situation — compare information in two different excels

In this example, let’s look at some not realistic situations but you will get an idea of how Grasshopper could be used in a situation when you have a lot of data. So imagine the situation you have two excel sheets. One called “STOCK” has information about what kinda the details you have in stock. In this case for simplicity, those will be details with unique numbers from 1 to 100. Another excel called “ORDER” has information about a new order — a list of details. So in this example, we will look if details from the order are available in stock.

If a detail list is very small you can always check it manually, but if there’s gonna be thousands of details it could get complicated. There is more than one way how to do it in excel manually using formulas. You could use VLOOKUP or even COUNTIF. Excels could look like this:

In Grasshopper, we could do this even without opening an excel file. Just referencing files and adding some blocks:

1. Let’s start with reading and referencing excels files as in previous situations with “Read Excel Sheet”
2. Add “Member Index” to find if the ordered detail is found in stock. Next, add “List Length”. If detail from the order will be found, then the value will be 1 if not 0
3. Add the “Larger Than” block and let’s check if the value is larger than 0. Block will give back the value True or False. We can add this information next to the ordered detail number to see what is in stock
4. You can already see that the fourth position “Detail 105” is not in stock, but ok, let’s write this information to excel. Add the “Concatenate” block and give order details and True or False value and write to excel as we did in the previous examples

So this is all I wanted to show in these simple examples. If you had some problems or just wanna download all examples you can find the file here. All examples are very simplified but you can do a lot with large amounts of data. If you go deep in Grasshopper you can do quite amazing things not just in excel. You can find a lot of information about Grasshopper on google and youtube.