Today we are releasing OpenSolver for Google Sheets 2.2.1, which is a very minor update with changes to improve our support for larger models.
Any script running in Google Apps Script has a strict 6-minute time limit while running, and is immediately cut off with no feedback if this limit is reached. It can often take longer than this to build the OpenSolver models if they are large, so we save the model-building progress as we go. If the build is stopped early, we can simply resume the solve from where we last saved the progress. Previously, this would occur the next time the “Solve” button was clicked. With this update, the solve is now automatically resumed if it is cut off, meaning that there is no need to click the “Solve” button repeatedly to build larger models piece-by-piece.
The other important change in this release is a new mechanism for saving the progress as we build the model. Previously we used the Google Apps Script CacheService to store the updates, but this limits the saved data to 100 KB which is too small for larger models. This update changes our behaviour to instead save the progress to a new hidden sheet in the workbook, allowing us to save much larger models.
Other minor changes include:
- A bug where the costs were not loading correctly when resuming the solve has been fixed.
- Error messages now contain the stack trace for better debugging.
If you already use OpenSolver for Google Sheets, the update will roll out automatically. If you are not yet a user, you can add it to Google Sheets using the link below:
As always, we welcome any feedback or ideas. We are coming up on a big milestone of 10,000 weekly active users for the Google Sheets addon, and are very happy to see that it is proving useful! If you find it useful, we would appreciate if you could rate us in the Chrome Webstore and/or leave a review for others.
2 thoughts on “OpenSolver for Google Sheets 2.2.1 (25 July 2016)”
first of all I want to thank you for your great work on this tool! It’s an amazing tool!
I have been using OpenSolver to solve big LP problems for my quantitative analysis graduate class.
One thing I notice is, that when I run a problem with about 1000 decision variables and a few thousand constraint variables, it takes a few seconds to solve on a Windows computer. However, on my Mac, where I have to use Google Sheets to solve the same problem, it takes 5+ minutes. Is there any way to reduce the time in Google Sheets? Will there also be support for Excel 2016 for Mac in the future?
Thanks and keep up the great work!
OpenSolver for Excel is much faster than for Google Sheets due to the design of the system by Google. As we are building the model in both versions, we repeatedly recalculate the values on the sheet and read them back into our code, but in Google Sheets there are two different Google servers that handle (1) running our code and (2) calculating the values on the sheet. This means each time we ask for the values to be recalculated, we actually have to wait for the message to go to the calculation server and then come back with the new values. Basically this means that each iteration in the model building process is vastly slower than for Excel.
We are exploring other options for Google Sheets that would allow us to build the models faster, but these are likely to come at the cost of only being able to use a specific subset of formulae when creating the model (like SUM, SUMPRODUCT, etc.)
As for Excel 2016 on Mac, we are limited by how fast Microsoft can release updates that fix some of the many problems in what is a very buggy piece of software. We have made significant progress to getting things working and can now solve a selection of our test problems with CBC, but there are still a large number of problems that remain and bugs in Excel that can crash the entire program for no reason at all.
We hope soon to be able to release a version of OpenSolver with alpha-level support for 2016 on Mac, but based on our current impressions that may or may not be very stable. Our current recommendation is to avoid using OpenSolver in 2016 in Mac and instead continue to use 2011 (which fortunately can exist peacefully alongside 2016). Better yet, if possible use Windows where Excel is a lot faster all round (believe it or not, running Excel on a Windows virtual machine is usually faster than running the Mac version of Excel!)