I’ve got an Excel file of sheet set data that I’ve exported using a Dynamo graph (yay!). Now I’m working on a graph to import said Excel file back into the sheet set, but when I get to the custom properties I’m running into an issue.
I’ve got a dictionary (and a list) of the sheet set properties and their values. Using the SheetSet.CustomProperties node I get a list of all of the custom sheet set and sheet properties (not just sheet set). I can filter those to get just the custom sheet set properties (the in filter below). Now I need to set the values of the custom properties based on what the dictionary (or list) has. My issue is that the number of custom properties and their names won’t always be the same for different projects. So how do I go about updating the properties if the names change?
The node
CustomProperty.EditValue requires a customProperty object, the sheet set, then the value. Since that node doesn’t use a string for the property, I’m not sure how to tie the string names from the Excel import to the customProperty object and set the values as well.
I could use some direction on how to tackle this one. It’s probably something simple I’m missing.
I think I got it worked out, although there’s an issue with the Total Sheets value being a number and the node
CustomProperty.EditValue requires a string. I did a String from Object, but it converts the digits to have a large decimal value. I can edit that specific index to truncate it, but I don’t know if in future projects that this index will always be the total sheet count. Is there a way to convert any numbers to a string without all the decimals? Am I going to have to do a another filter based on
Object.Type, convert any object with a type of
System.Double to string, then do a truncate and add back to the list?
Seems like a like of overhead, but I don’t know of another way.
This is a known ‘feature’ of older versions. A simple way is using String.Split node. Or using Data.OpenXMLImportExcel with reasAsString set to true
That worked for the number problem, but the date is becoming an issue as well. In Excel the Revision Date is using the MM/DD/YYYY format. When imported with readAsStrings it comes in as MM/DD/YYY 12:00:00 AM. How can the date come in as just MM/DD/YYYY?
I’m trying to automate this as much as possible, so I’d rather have Dynamo handle this part. If there’s not a simple way then I guess I’ll use the
String.Remove node to drop the last part.
I’m still trying to figure out how to handle the date issue. Excel is automatically setting the field to be formatted as a date (as M/D/YYYY) when Dynamo exports the data. Then when Dynamo imports it back it becomes M/D/YYYY 12:00:00 AM. Below on the left is how it’s set in the sheet set manager and on the right is how it looks when Dynamo exports the data.
I know Dynamo can’t control what Excel does, but I’m not sure how to handle the returned date. I need the date format as MM/DD/YYYY, but it’s being imported as M/D/YYYY 12:00:00 AM. It’s easy enough to drop everything past the year using String.Remove, but I’m not sure how to force two digits for the month and day each.
String.Split > String.PadLeft > String.Join
Bonus points if you do that it as a single in line design receipt statement.
I got the date issue figured out (yay!), but now I realize the list order is getting screwed up. I used a List.Filter to get all non-null records of the dates, but that split the list up. Now I need to add these back in the proper order to the overall list and I’m not sure how to go about doing that while preserving the indices.
First thought is to use a If test on the full list to find nulls, but not sure if that’ll preserve the list order.
I ran the string split directly from the overall list, including the nulls, and it ends up producing a list that looks correct (despite the null warnings) as shown above in the Watch node on the right. However, theSheet.SetRevisionDate won’t pass that list on. It says “Object reference not set to an instance of an object.” No idea what’s going on as the input is the sheet object list that all of the other nodes use as well without issue.
