ConstraintsToSpreadsheet [streamlined]

Need help, or want to share a macro? Post here!
Posts: 1566
Joined: Thu Apr 05, 2018 1:53 am

ConstraintsToSpreadsheet [streamlined]

Postby TheMarkster » Tue Jul 03, 2018 10:16 pm

I've made some changes to this macro. The idea here is to streamline and simplify this macro, both for the user and your humble developer. I removed the SSHelper object. I also removed the feature to add expressions from objects, such as Cube's, etc. to the spreadsheet. The macro will now focus on one thing (and hopefully do that one thing well) -- take named constraints from the document's sketches and convert them to aliases in a spreadsheet. I've left the old version, renamed as on the github site. Here is a new animated .gif demonstrating its usage:



Latest bug fix: (2018.07.05b) --SSHelper object wasn't getting re-initialized upon re-creation, so it wasn't updating the Spreadsheet values. It had *one* job. Lol.

Changes (2018.07.05)

To get the macro to ignore a constraint name you should append an underscore (_) to the end of the constraint name, not at the beginning, as before. Underscores are okay for use in constraint names, but evidently not at the beginning. This was the source of some invalid constraint errors I was getting.

Old way to get macro to ignore constraint names: _prependToTheName;
new way: appendToTheName_

Spreadsheets are useful at times, but it can be very tedious to set them up. As an example, suppose you wish to create cells in the spreadsheet and link the values in those cells to your constraints in multiple sketches. You have to create the spreadsheet. You have to assign a label to the cell next to the cell you want to use for the value. You have to assign an alias to the value cell. You have to enter a valid value in the value cell. You have to set the constraint in the sketch to use the value cell in the spreadsheet. There are a lot of steps, a lot of left-clicking and right-clicking. That's where this macro comes in.

This macro automates the process of entering named constraints into a spreadsheet. All you have to do is give the constraint a name, the macro will do the rest.

To use it, select the sketches containing named constraints (and the spreadsheet if you already have made one) and run the macro. If a spreadsheet is part of the selection it will be used. If no spreadsheet is in the selection you will be prompted whether you want the macro to make one for you.

The macro then goes through all of the constraints in all of the selected sketches. Unnamed constraints are ignored. Elements without constraints are ignored. Named constraints get added to the spreadsheet and the constraint gets referenced to that cell in the spreadsheet, which said cell now has alias = constraint name.

Only columns A, B, C, and D are used. Anything in E, F, or beyond will not be modified. Each time the macro is run, columns A through D get reset and repopulated. The upshot of this is if you have multiple sketches linked to the same spreadsheet you will need to be sure to select all the sketches, along with the spreadsheet, each time you run the macro to update the named constraints. You only need to run the macro again when you've added more constraints to your sketch(es).

You should not have spaces or any non-ASCII characters in the constraint names.
screenshot1.png (78.85 KiB) Viewed 783 times
screenshot2.png (58.99 KiB) Viewed 783 times
There is an animated gif at the github site.
Last edited by TheMarkster on Mon Aug 20, 2018 10:38 pm, edited 4 times in total.
My FreeCAD video series on youtube:
User avatar
Posts: 4690
Joined: Tue Nov 12, 2013 4:06 pm

Re: ConstraintsToSpreadsheet

Postby microelly2 » Wed Jul 04, 2018 7:56 am

nice idea.
Posts: 59
Joined: Thu Oct 20, 2016 10:06 pm

Re: ConstraintsToSpreadsheet

Postby silopolis » Wed Jul 04, 2018 2:27 pm

Very nice indeed !
Posts: 25204
Joined: Tue Mar 17, 2015 9:14 am

Re: ConstraintsToSpreadsheet

Postby chrisb » Wed Jul 04, 2018 5:28 pm

This could be really helpful. I often start a model with named constraints using expressions instead of a spreadsheet, because I think I need only very few references. As the project goes on they become more and more and finally it would have been sensible to start with a spreadsheet right away.
Posts: 1566
Joined: Thu Apr 05, 2018 1:53 am

Re: ConstraintsToSpreadsheet

Postby TheMarkster » Wed Jul 04, 2018 5:59 pm

New Features (2018.07.04):

  • Added SSHelper FeaturePython object to document tree, can be used to edit spreadsheet aliases via the SSHelper data tab in the combo view.
  • Now checks for unselected sketches and offers to use them all.
  • If no spreadsheet is selected it now searches the document object, and offers to use the first one it finds.
  • Constraint names beginning with an underscore (e.g. _myConstraintName) ending with an underscore (e.g. myConstraintName_) are now ignored by the macro.
This is my first FeaturePython object, but it seems to be working (mostly). You can safely delete the SSHelper object if you don't want this functionality. I just find it annoying that you can't edit the aliases in the data tab of the spreadsheet combo view, so I added this capability. It uses hidden aliases (hidden properties) linked to the spreadsheet, so if you update the spreadsheet, the SSHelper object gets updated, too, and, of course, vice versa. The danger here is the possibility of self-references sending FreeCAD off into an infinite loop. I've had that happen a couple times during development (oops), but I think (maybe) I have it ironed out.

I'm getting an invalid constraint error message at times that I still need to figure out, but the constraint seems to work just fine despite the error message. (was related to using underscores at the beginning of constraint names)

Animated gif: (including ebay bid notification update,lol)
constraints-to-spreadsheet2-optimized.gif (614.41 KiB) Viewed 683 times
Last edited by TheMarkster on Thu Jul 05, 2018 9:19 pm, edited 1 time in total.
My FreeCAD video series on youtube:
User avatar
Posts: 7512
Joined: Thu Jan 05, 2017 9:03 pm

Re: ConstraintsToSpreadsheet

Postby Kunda1 » Wed Jul 04, 2018 6:06 pm

Joel_graff wrote:CCing Joel_graff
Would this be useful for the Civil Engineering WB ?
Want to contribute back to FC? Checkout:
#lowhangingfruit | Use the Source, Luke. | How to Help FreeCAD | How to report FC bugs and features
User avatar
Posts: 1754
Joined: Fri Apr 28, 2017 4:23 pm

Re: ConstraintsToSpreadsheet

Postby Joel_graff » Thu Jul 05, 2018 11:48 am

Kunda1 wrote:
Wed Jul 04, 2018 6:06 pm
Joel_graff wrote:CCing Joel_graff
Would this be useful for the Civil Engineering WB ?
Possibly. The inability to change aliased values in the property tab annoyed me, too
FreeCAD Trails workbench for transportation engineering:

pivy_trackers 2D coin3D library: