Author: Jack Dunn (Team OpenSolver)

Today we are releasing an update to OpenSolver for Google Sheets that includes a new solver: the SolveEngine from Satalia

The update will roll out over the next day or so, and you will then be able to access the SolveEngine in the list of available solvers. The first time you use the solver, you will be prompted to enter your SolveEngine API key, which you can obtain by creating an account using the link in the prompt. This is the beta version of the SolveEngine, which will be free during the beta period. After the beta has ended, users will receive 1 hour of free solving time per month. There will be no queuing during the beta, and after the beta there will be a short queuing time for free users.

The SolveEngine is a cloud-based system, similar to NEOS except with more powerful solvers and much shorter queue times. We hope that it will prove a useful and powerful alternative to the other solvers we offer, and have plans to also offer it in OpenSolver for Excel in the coming future.

SolveEngine Overview

The SolveEngine boasts many features that make it a powerful tool for solving a variety of optimisation problems, notably:

• A single gateway to a portfolio of solvers;
• Modern optimisation translators and encoding that give access to a wider range of algorithms;
• No licence to manage and nothing to install;
• Distributed cloud computation that enables dynamic resource allocation to accommodate complex and large problems;
• Machine learning which matches the right algorithms to the problems it receives, improving itself over time.

Users of the SolveEngine benefit from :

• Access to a constantly evolving portfolio of algorithms, designed to solve problems such as:
  • Boolean Satisfiability problem (SAT)
  • Linear programming (LP)
  • Mixed integer linear programming (MILP)
• Free solving time (1 hour) after account creation;
• Free Academic usage;
• Access to cutting-edge algorithms from academia, and in doing so, supporting academics to commercialise and further develop their algorithmic innovations;
• Direct access to time honored and also newly developed commercial algorithms;
• Built-in translators, which means that there is no strict limit to the number of problem formats that can accepted by the SolveEngine and allows for human readable inputs such as GLPK to be submitted;
• The capability of the SolveEngine to run many solvers in parallel using sophisticated pre-processing and pipelining techniques which identify the most promising routes through to solving the problem faster;
• Submission of various problems and concurrency of problem solving in a scalable manner;
• Access to a community of optimisation/modelling experts for support.

Optimisation problems exist across the entire spectrum of business, science and engineering. The SolveEngine has already been used to solve many hard computational problems in graph theory, logistics, system verification, BigData and cryptography.

Satalia Overview

Satalia is a spin-out from the UCL Department of Computer Science. Satalia’s team of academics and artificial intelligence experts harness a library of algorithms, hosted in the SolveEngine, to help companies and organisations solve the world’s most difficult problems. In 2016 Satalia was recognised in the elite Gartner Cool Vendors in Data Science list, the only UK company chosen.

Satalia’s commitment to continued innovation in algorithm development underpins the SolveEngine model through which academics and commercial solver developers can deploy their solvers to the SolveEngine to address the current and emerging requirements of industry and research communities. Providing access to state-of-the-art optimisation algorithms, Satalia enables the industry to analyse problems inputs more efficiently and thus to solve their problems more rapidly.

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.

Today we are releasing OpenSolver for Google Sheets 2.2.0, which is a minor update with the following changes:

• We have added support for early stopping in GLPK. If your problem is taking a while to solve and you are satisfied with the quality of the best solution that has been found so far, you can stop the solve and OpenSolver will load this solution into the sheet. To stop a solve early, just click the “Cancel solve and use current best solution” button.
• When solving a “Target Objective” problem, we now try to minimize the deviation from the target rather than making the target a hard constraint. Previously, if the target was hard or impossible to reach, the solver may have taken a long time to finish. Coupled with the early stopping in GLPK, you can now stop the solve early if you see that the solution is close enough to your desired target. We will be adopting this approach in OpenSolver for Excel in the very near future.

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.

Today we are releasing version 2.1.0 of OpenSolver for Google Sheets! This is a minor update that brings the following changes:

• We have added support for the GLPK solver. You can now solve problems in your browser using the open-source GLPK solver, via glpk.js by Henri Gourvest. This solver runs in the browser on your computer rather than on a cloud server, so is typically much faster than the alternatives and we heavily recommend choosing it in place of Cbc via NEOS. Please let us know if you find this useful!
• The linearity check now occurs before solving, in order to catch non-linearities before spending time solving the model. We recently changed the linearity check in OpenSolver for Excel to be much more robust and better at detecting non-linearity, and we are pleased to roll these changes out to Google Sheets as well.
• Bugfixes:
  • Fixed bug where dialogs could time out before they finished loading.
  • Fixed sheet-scoping issue when storing constraints in multi-sheet models.

There is more information about the Google Sheets addon here on the dedicated page.

If you already use the addon, the update will automatically roll out. If you haven’t used it before, you can try it out here:

Update on Google’s Linear Solver

Google’s LinearOptimizationService has been fixed as of earlier today, so the Google Linear Solver should be working again. We are happy to now offer two additional solvers in case this happens again. It took over two weeks for Google to fix this bug, which is very frustrating for all of us, and hopefully is a problem we will be able to avoid in future!

Feedback and Issues

OpenSolver for Google Sheets is younger and less mature than the Excel version, so does not yet have the full range of features as in the Excel version. There are also a lot of possibilities that are created by the online aspect of Google Sheets (such as sharing sheets and multiple users on a sheet at once). This means there are many new features to consider and add to the addon as it evolves over time.

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!

We are always interested to hear how you are using OpenSolver, and in particular if you find the Google Sheets version useful (because we can”t count downloads, we have much less user information on the number of Google Sheets users). This allows us to balance the time we spend developing the Excel and Google Sheets versions according to user demand.

If you like the Google Sheets addon, please consider rating us in the store and/or leaving us a review, which will help other users find out about OpenSolver!

We are very excited to announce the release of OpenSolver for Google Sheets 2.0! This is a major update that brings many improvements to the Google Sheets addon:

• Support for one more than one model per workbook! Now, each sheet can have its own model. Use the sheet selector at the top of the sidebar to switch to the model for a sheet.
• Models can reference cells on different sheets! You can define models using ranges on any sheet in the workbook.
• Added a new solver: Cbc on NEOS! You can choose to either use the solver provided by Google, or the Cbc solver on the NEOS optimization cloud. Note that all problems submitted to NEOS become publicly available, please see the terms of use.
  , and use the new Cbc solver to solve models on the NEOS cloud server.
• Support for resuming incomplete solves! If the solve takes more than 6 minutes, it will be stopped before a solution is found. Now you can resume the solve from where you left off, allowing you to solve larger models than before.

There is more information about the Google Sheets addon here on the dedicated page.

If you already use the addon, the update will automatically roll out. If you haven’t used it before, you can try it out here:

Update on Google’s Linear Solver

Last week, Google made breaking changes to their LinearOptimizationService inside Google Sheets, which means that it currently is not functional at all, and so we can’t use it in OpenSolver to solve optimisation problems. We reported the issue to Google immediately, but it has not been fixed at the time of writing this post. In the meantime, you should use the Cbc solver via the NEOS server.

If you would like to see Google fix their solver faster, you can click the star on the issue page linked before. The more stars the issue gets, the faster they will fix the problem.

Migrating models from previous versions of OpenSolver

Due to supporting one model per sheet, we have had to make changes to how we store the OpenSolver models, and so your current models are not automatically loaded into the new version. To load in your old model, you can either redefine it manually, or use the “Import previous model” item in the menu. This will load in your previous model as the model for the currently selected sheet.

Please get in touch if you have problems with this feature, we want to make the transition as smooth as possible for everyone. You should also use the “Show previous model data” menu item to open a box with the model information for your sheet. Please include this when you get in touch so that we can diagnose the problem.

Feedback and Issues

OpenSolver for Google Sheets is younger and less mature than the Excel version, so does not yet have the full range of features as in the Excel version. There are also a lot of possibilities that are created by the online aspect of Google Sheets (such as sharing sheets and multiple users on a sheet at once). This means there are many new features to consider and add to the addon as it evolves over time.

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!

We are always interested to hear how you are using OpenSolver, and in particular if you find the Google Sheets version useful (because we don’t control the downloads, we have much less user information on the number of Google Sheets users). This allows us to balance the time we spend developing the Excel and Google Sheets versions according to user demand.

If you like the Google Sheets addon, please consider rating us in the store and/or leaving us a review, which will help other users find out about OpenSolver!

Today we are happy to announce the release of OpenSolver 2.7.0, available for download here. The release fixes a large number of bugs and also introduces some great new features:

• Update Checker: OpenSolver now includes an automatic update checker that will periodically check for updates. This is an opt-in feature that we hope will make it easier to keep up with the latest releases. As a side effect, we plan to move to more frequent releases so that new features and fixes are available sooner. More info on the Update Checker is available here.
• VBA API: We have created a complete VBA interface to OpenSolver that allows full control of OpenSolver using VBA, much like Excel Solver’s API. We strongly recommend using these new functions to control OpenSolver instead of the Solver functions. The full list of API methods is here.
• Error Reporting: We have revamped the error handling within OpenSolver to give us much more information when an error occurs. There is also an option to report issues from within Excel when an error occurs. This will email us directly with full information about the error, which will hopefully make it easier to resolve any issues you might encounter.
• All solvers now support extra parameters being defined, and these parameters can now be set from the Options dialog. More information about using these parameters is here.
• Highly experimental support has been added for the NOMAD solver on Mac. This feature is very new, so if you try this out please let us know how it goes.

We have also updated the included solvers to the following versions:

• CBC 2.9.4
• Bonmin 1.8.1
• Couenne 0.5.3
• NOMAD 3.7.2

As always, please let us know how you find the new version, and we appreciate any feedback you can offer.