OpenSolver for Google Sheets

OpenSolver has also made it to Google Sheets, letting you solve your optimisation problems with OpenSolver from anywhere!

logo-440With 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. As of April 2016 we now provide some redundancy in solver choice by supporting using GLPK via glpk.js to solve problems inside your browser, and using Cbc on the NEOS Server to solve problems in the cloud.

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.

Using OpenSolver for Google Sheets

(If you speak Spanish, you may prefer this post on “Solver en Google Drive”.)

When you start OpenSolver, the sidebar will open which is where you can enter your optimisation model. This is almost exactly the same as for OpenSolver in Excel, with the following key differences:

  • You must select which sheet contains your model using the dropdown box at the top of the sidebar. You can press the “Current Sheet” button to load the model for the currently active sheet.
  • Google Sheets does not allow ranges with multiple areas (e.g. “A1:A2,A4:A5”), so you cannot define the variable cells in this way. Instead, you should select on the sheet each area of the variable cells one-by-one and click the “Add” button under the variables section of the model. You can also use the “Update” or “Delete” buttons to change or remove, respectively, the currently selected variable area.
  • Google Sheets does let us use RefEdit controls for selecting ranges on the sheet. Instead, you should select on the sheet the relevant range, and then click the appropriate “Update” button to load this range into the appropriate area of the model.

Below are some screenshots of various models defined inside the Google Sheets interface (you can click to make these larger):

Simple LP model

Transshipment model

Facility Location model

Solving the model

Once you have selected a solver, you can build and solve the model using the “Solve Model” button, which will load the solution into the sheet when it is finished.

One limitation of the Google Sheets platform is that there is a timelimit of 6 minutes for any script to run. This means that if your model takes longer than 6 minutes to solve, the procedure will just cut out at the 6-minute mark with no feedback.

To address this, we have added the ability to “resume” a solve. As we proceed with building the model, we regularly save our progress, and then delete the saved model once the solve is complete. If the solve is ended early because of the timelimit, we can use the saved model and pick up from where we left off. If an incomplete solve is detected, you will be asked whether you want to resume the solve or start over from scratch. This means that you can build larger models by repeatedly resuming the solve and letting it run for another 6 minutes each time.

Resources

Jeffrey Kantor has kindly provided several small examples of models he developed for teaching purposes in Google Sheets using OpenSolver. To run these yourself, you will need to copy the files to your own Google Drive, and install OpenSolver for Google from the Sheets add-on menu. (You might also want to try Jeffrey’s online tool for building & solving models using the MathProg modelling language.)

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!

Guide to Permissions

The first time you use OpenSolver for Google Sheets, you will be asked to grant it some permissions. These permissions are detailed below.

View and manage your spreadsheets in Google Drive

This allows us to access your spreadsheet data to build the optimization model and write the solution of the optimization problem back into the sheet once the solve is finished.

View and manage data associated with the application

This allows us to save information about the current document and/or user. Earlier versions of OpenSolver used this to save the model information (later versions now store the model information on the sheet itself). We also use this to track the latest version of OpenSolver that you have used, so that we can show you a message when we release a new version (otherwise, the updates happen automatically without any notification to you).

Connect to an external service

We use this when solving models using NEOS-enabled solvers, since we have to send the model information from Google Sheets to the NEOS server.

Allow this application to run when you are not present

An older version of OpenSolver required this permission to automatically and regularly run tests to ensure that it was working correctly. We have since changed the way we test the code, so this permission is no longer required. However, at the time of writing (April 2016), there is a bug in Google Apps Script where it still requests permissions based on what previous versions of the add-on required, so this permission is still asked for even though we don’t use it! We hope Google will get around to fixing this in the future so that we don’t have to ask for it, but in the meantime you have to grant this permission, but please rest assured we aren’t making use of it!