opensolver / opensolvergoogle Goto Github PK

Working through an issue with a user, and it seemed like the solve process would stop on the first solve while building the model, but would be solving fine on the second time.

Also the solve process seemed to loop and restart while building the model, but this seemed to be due to a browser extension interfering somehow.

Can't reproduce either of these issues, but just leaving here for reference's sake

We can start with CBC and reuse the existing numeric methods for constructing the model.

Here's an xmlrpc client for GAS:

https://github.com/ikikko/Mimic-for-Google-Apps-Script

Hasn't been updated recently but can check it out

We don't have access to an On Sheet Change type event, so perhaps we can add support for manually creating different models and the user can pick one from a dropdown, add new ones, and give them names. Open to ideas on this

The CacheService can only store objects up to 100KB, which can be a limiting factor for larger models (which are the models that need the cache most). If possible, it would be good to have a caching procedure that can work for any model size.

In the last couple of days it seems OpenSolver no longer inserts solutions into the variable cells on a sheet. Has something changed to cause this behavior?

Jeff

If a variable is specified as part of two overlapping ranges then it is processed twice in the numeric differentiation part, which throws off the result and the linearity checker.

To fix we keep track of the variable names when processing the ranges to make sure we don't add a variable more than once

Received anonymous bug report:

Cannot find method addConstraint((class),number).

This is probably due to undefined being passed as one of the bounds, e.g.:

engine = LinearOptimizationService.createEngine();
engine.addConstraint(undefined, 5);

This would imply we are setting undefined as an entry of this.rhs

If the sheet name is enclosed within single quotation marks, the objective cell and constraints sections are unable to be updated.

A new comment on the post "Help" is waiting for your approval
https://opensolver.org/help/

Author: Pietro Garmatter (IP address: 2001:1284:f01c:18d0:8da:187d:8b95:3119, 2001:1284:f01c:18d0:8da:187d:8b95:3119)
Email: [email protected]
URL:
Comment:
I am using OpenSolver with google sheets.
I works very well. However, if the cell name contains spaces, the solver tab doesn't update cell references correctly.

example: my first sheet was called 'ex1 - p1', second 'ex1 - p2' and so on. When trying to update variables, objetive etc for the second sheet, nothing happened. I went back to the first and ran its solver again. It worked OK, because references weren't lost.
Tried updating values from the first sheet (now renamed with the spaces) and it didn't work.

I did, however, manage to use it by renaming sheets from 'ex1 - p1' to 'e1p1'.

Approve it: https://opensolver.org/opensolverwordpress2/wp-admin/comment.php?action=approve&c=546348#wpbody-content
Trash it: https://opensolver.org/opensolverwordpress2/wp-admin/comment.php?action=trash&c=546348#wpbody-content
Spam it: https://opensolver.org/opensolverwordpress2/wp-admin/comment.php?action=spam&c=546348#wpbody-content
Currently 162 comments are waiting for approval. Please visit the moderation panel:
https://opensolver.org/opensolverwordpress2/wp-admin/edit-comments.php?comment_status=moderated#wpbody-content

Key goals:

• support one model per sheet
• provide a Google Apps Script library to allow automation by users

Problems with the current architecture:

• Addons cannot expose functions as a library, so we can't allow people to do automation. The solution to this is to provide a library to handle the most of the work, and use our addon as a GUI for the library. Note that we need to copy and paste the library code into the addon rather than referencing a library: reference
• This causes another problem: the way properties are handled. Currently, we run as an addon, which gives us access to storing document-specific properties, which means we can save details per-spreadsheet. Running as a library means we can only save properties per-library (only one set of properties for all users 👎) or per-user (meaning only the user that made the model can see it 👎). Thus we need to move away from properties.
• Another issue with properties is that the property store is not robust to changes in the spreadsheet. If a sheet is renamed, the corresponding property values will not be updated.
• We currently are only able to support one model

Architecture requirements

This means we need to solve the following problems:

• Store the model in a way that exposes the model identically to both addon and library users
• Store the model in a way that is robust to sheet name changes
• Store the model in a way that allows one model per sheet
• Come up with a way of switching the model in the sidebar based on the current sheet

Model Storage

A possibility is to use a storage system similar to Frontline's Addon. They create a hidden sheet __Solver__ that contains all model details for the whole document:

The models are stored one per column. All model details are saved with the full sheet prefix and as references. This means they are updated on sheet rename.

• Row 2 contains the objective is stored. One of =Max(<obj>), =Min(<obj>) or =<obj>=<target>.
• Row 3 contains the decision vars =<range>
• Row 4 seems to contain a hash corresponding to the selected solver?
• Row 5 seems to contain a hash of the model options?
• Row 6 seems to contain information about the constraints in the model (not sure what)
• Rows 7+ contain the constraints in the form =<lhs> <rel> <rhs>

We could store in a similar format on an __OpenSolver__ sheet:

• Row 1: objective
• Row 2: variables
• Row 3: num constraints
• Rows 4 to (num constraints + 4): constraints
• rows (num constraints + 5) onwards: options (solver, linearity check, show progress)

We can use the variables to determine the sheet name (with the implicit constraint that each model is contained on a single sheet).

Changing sheets in the UI

The way that solver does this is from what I can understand, any time there is a click in the UI, they check whether the current sheet matches the sheet of the model that is showing, and if not, they reload the model.

We can do something similar, and to load a model we search through the columns of the hidden model sheet to find the one that references the current sheet.

Where to go from here

Adopting this approach similar to solver seems to resolve most of the problems. I'd like to discuss any thoughts on the best way to store the models, load them, and switch contexts in the GUI.

¨Sign in with Google temporarily disabled for this app
This app has not been verified yet by Google in order to use Google Sign In.¨

Variables and constraints should be scoped to sheet

If the name of the sheet being worked on contains either symbol, then updating cell references for anything will clear all prior cell references in the sidebar.

Add formula parser for another way of making the model. Possibility of sharing this as a library with Office 365 version

If there's a problem loading the model from the cache, then this needs to be handled gracefully. We might also provide a 'Clear cache' menu item to manually empty the cache