We are excited to announce that OpenSolver has made it to Google Sheets, letting you solve your optimisation problems with OpenSolver from anywhere!
With the OpenSolver Add-on you can solve both linear and mixed-integer programs from within Google Sheets with no additional setup required. We use the excellent, open source solvers Glop (for LP) and SCIP (for MIP) to solve the problems.
You can install the OpenSolver Add-on by clicking on the Chrome Web Store link below and then clicking on the “Free” button. You can also find us by searching for “OpenSolver” in the Google Sheets Add-on store. Once the Add-on is installed, you can find OpenSolver in the “Add-ons” menu inside the spreadsheet.
This is our first release of this Add-on, so please let us know how you find it! You can either leave messages here, or leave feedback on the Chrome Web Store by following the link above. If you use the Add-on and like it, please consider leaving us a review in the store so that we can move up the rankings inside the store.
There are some currently some limitations, mainly that the model can only be contained on a single sheet. This is something we intend to fix very soon.
If you have any feature requests or experience any problems while using it, please let us know either here or by using the “Report an Issue” button inside Google Sheets (you can find this in “Add-ons > OpenSolver > Help”). If you use “Report an Issue”, please make sure that you include your contact details, otherwise there is no way for us to follow up!
Give it a try and let us know how you find it!
I already tried successfully to solve my Lin. Program in NEOS. It says: Processsing… Building… Solving model on NEOS…
I suppose that he is sending a AMPL program. Is it possible to get this AMPL code? Do you have any suggestion about converting spreadsheet models in AMPL?
Thank you
In OpenSolver for Excel, you can use the “View last model file” menu option to see the AMPL. This is not possible in Google Sheets
Great add on!
I would like to save the settings on Opensolver so that it is always optimising the same cell, the variables are the same cells and the contraints relate to the same cells. (We are essentially performing the same operations on different sets of numbers.)
However, after a I run the solver and close the sidebar the settings disappear. Is there anyway to keep these settings?
I would ideally like to keep copying the same google sheet with the same solver settings and then insert different numbers.
Apologies if I have missed something very obvious.
Many thanks!
Martin, is the problem that the model settings are never being saved (a bug), or that the model is not being copied when you make a new copy of the sheet (expected)? We aren’t able to copy the model when a sheet is copied in Google Sheets (unlike Excel), but the model is copied if you make a copy of the entire Google Doc rather than just the sheet.
Another option is to have one sheet in the file that has the model, and have it pull the data from another sheet in the same file, setting up the formulae on the sheet so that you can toggle which sheet it is currently pulling data from.
This solver has been working GREAT for the last couple months. It stopped working a couple days ago. now every model has the solution 0,0,0,…,0,1 I’ve gone back and resolved models that were working with the same results. Any help for me?
This a bug that Google has introduced. We have filed the issue with them and hope that it will be resolved soon:
https://code.google.com/p/google-apps-script-issues/issues/detail?id=5881
In the meantime, we will very soon be releasing a new version with the option to use the NEOS servers for solving to get around this and provide some redundancy in solvers. Watch this space
I’ve just come across Opensolver for Google Sheets. Great project!
One question. How do I then share that model with someone else? I would like to share a sheet/model with students which they can then edit. Copying the sheet seems to omit the model. Is there some other way to do this?
Sorry for the delay, I missed this comment.
In the next release which is coming very soon, the model will be stored on a hidden sheet in the workbook. This should allow sharing a copy of the model by copying the entire document (rather than just the sheet).
I have installed this add-on and built sample model with 200 binary variables. Them main problem I’m facing is that ‘Bulding variable ??/200’ phase lasts very long (about 2 minutes). The solving part is almost immediate. The sheet is not too complex. Is there any way to improve model building speed?
Thank you for your great work.
Keeping the sheet simple is all we can suggest. Jack might have some other ideas. Andrew
Unfortunately there is not a way to speed this up currently. The design of Google Sheets (not OpenSolver) means that it takes roughly 0.5-1 seconds to recalculate the sheet values. We do this once for each variable, so for 200 variables it will take around 2 mins as you have found.
We have long-term ideas to speed up the model building by doing it a different way, but these need a lot of work to be completed so we don’t have any sort of expected timeframe sorry.
Thank you very much for your detailed explanation.
I understand that at the moment increasing the speed of model building is not possible. Maybe you could tell me if there is a way to simplify the model I’m trying to solve (if it’s not the right place to ask such questions, please advice if you have some kind of forum to ask it).
My problem is: I have constant 20 rows x 10 columns array arrA of integers and I have to choose 10 elements with maximum sum. Only one element in each row and column can be chosen. My model is:
– variable:
– array arrB (same size as arrA)
– constraints:
– arrB == bin
– sum of arrB rows <= 1
– sum of arrB columns == 1
– objective:
– maximise SUMPRODUCT(arrA,arrB)
Is there any way to simplify the model (reduce number of variables and still keep the model linear)?
Thank you in advance for any suggestions.
What you have there looks like it can’t go any simpler unfortunately. For now you could try the Solver addon for Google Sheets (I’m not sure about what their free limits are), or use Excel if that is a possibility.
This is very cool. Is there (planned) integration with AMPL?
By “integration with AMPL” do you mean a SolverStudio environment that would support AMPL on Google Sheets? Now that would be a challenge. It is not on our current To Do list (but one of our students has been playing with something a bit like this).
Is there a way to reference the open solver for Google sheets through a script so that iIdon’t have to open the solver window each time I update information?
Thanks!
I have asked about this, and a limitation of Google addons is that there is no way for a script to interface directly with the addon. It may be possible to offer this in the future, but unfortunately it’s not feasible right now.