OpenSolver for Google Sheets – OpenSolver for Excel (original) (raw)

OpenSolver for Google Sheets Not Working for Some Users: Google have changed something in their code which has broken OpenSolver for Google Sheets for some users (who cannot interact with the OpeSolver panel). Other users report it is working ok. Unfortunately, we don’t currently have a timeline for this issue being fixed. We would welcome volunteers who could assist with this. Andrew 21 June 2021

Avoid Spaces in Sheet Names: We have had a report that OpenSOlver for Google Sheets has problems if the sheet names have spaces in them. We hope to have someone look at the code in the next few months. In the meantime, please avoid spaces. Andrew Nov 2022. PS: We are a volunteer project, so this would be a good time to step up if you have JaveScript/Google skills…

OpenSolver has also 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. 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.

Get OpenSolver for Google Sheets

Using OpenSolver for Google Sheets

Note: You may wish to watch this YouTube video on OpenSolver for Google Sheets or this 2022 YouTube video. Or, 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):

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.

See, edit, create and delete 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.

Connect to an external service

This is needed for using the script.external_request scope to use URL Fetch Service to access other Solver Engines such as Satalia SolveEngine. This also includes when solving models using NEOS-enabled solvers, since we have to send the model information from Google Sheets to the NEOS server.

OpenSolver for Google Sheets also triggers the user.email scope when sending jobs to the NEOS server. This is because NEOS requires an email address to be submitted with the job. This address is only sent when solving a model and only if the user selects NEOS-CBC as the solver engine.

Submitting a model to NEOS results in it becoming publicly available. Use of NEOS is subject to the NEOS terms and conditions.

Please note that no user data is sent to other sites unless the user chooses to use another solver.

Display and run third-party web content in prompts and sidebars inside Google applications.

This is needed for the script.container.ui scope. OpenSolver requires authorization with this scope for opening a sidebar in the document containing the add-on’s main user interface, as well as for other modal dialogs.

Google Limited Use Requirements

There are no additional permissions required by OpenSolver other than the ones listed above. OpenSolver’s use of information recieved from Google’s APIs will adhere to Google’s Limited Use Requirements. For other information and FAQs please visit our privacy policy page: https://opensolver.org/privacy/