Contents
- Introduction
- Overview of the system
-
Setting-up the spreadsheet
- The Locations Sheet
- The Weightings Sheet
- The Wagons Sheet
- The Train Sheet
-
Optional - creating buttons
- The Script
- Conclusion
Introduction
You may have seen that, over the years, I have written three versions of my computerised freight management system. Initially, I used a relational database called 4D or 4th Dimension - because it came as a freebie with a magazine. I then used another free programming environment called LiveCode, because the version of 4D I was using became outdated and it was going to cost me around £600 to buy a version which would run on Windows 7. I then created another version which ran on my old resurrected Psion Pocket computer as it meant I could interact with the program out in the garden, rather than having to consult print-outs. However, my Psion was of an age where it had very limited storage capacity and so tended to run out of system memory at critical moments.
I tried exporting an Android version of my LiveCode program to enable it to run on my phone or tablet computer but, despite a fair amount of effort and several abortive attempts, I couldn't make it run and so, I sought an alternative. It was then, I realised that underlying Google Sheets was a computer scripting language which I could use. All I had to do was teach myself how to write programs using JavaScript.
Mine is probably not the most elegant of scripts, but it does seem to work and has three advantages:
- It's free
- It will run on my laptop, my tablet and my phone
- Changes made on any of those devices are synchronised with the others
At the end of the day, Google Sheets is a spreadsheet and so has some limitations. It's not easy to design an attractive user interface and also, for reasons best known to themselves, on-screen buttons won't work on the mobile app - which is a bit frustrating as large buttons are far more suited to small phone screens than trying to select from a small drop-down box in a cell. However, it is free and remarkably powerful, so who's complaining?
An overview of the system
The Train Worksheet
The main screen (Worksheet) is the one which generates the trains.
The first task is to choose the length of train you want the system to
generate. I have restricted the length of my trains from 4 wagons to 18. If
you made your own version, you could set this range to whatever suits your
railway.
Next, we select the 'createTrain' option from the drop-down box beside it.
Alternatively, I could wait until I have shunted all the wagons to a
particular location and select 'Move_these' from the list to transfer them all to that new location.
The 'Present location' field changes to reflect their new locations on
the railway and, as a further reminder, a prompt shows up indicating which
wagons have so far been 'Done'.
Or, I could wait until I have transferred every wagon in the train to their new locations and select 'Move_all' to inform the system I have done so.
The locations worksheet
The Locations Worksheet is accessed by clicking on the locations tab at the bottom of the window
As you can see, there isn't much to this sheet. The locations on the railway are listed in the first column. Notice that they are not just stations, also included are locations such as the Mill Siding and the Copper Mine as they are locations which will receive and despatch freight on to the system.
I included a column showing short names for each location as I expected them to be used in the programming. In actuality they weren't needed but I have kept them in case they might be needed in future extensions of the program.
The weightings worksheet
At the heart of the system lies the weighting figures for each type of wagon. These are used to determine the upper limit of the random number calculation when figuring out which location each wagon could move to. The higher the weighting, the more likely it is a wagon will be sent there. However, as the calculation involves a random element, there is still a possibility that a wagon could be sent to any location unless the weighting figure is set to zero.
To get to the Weightings Worksheet, the 'weights' tab at the foot of the screen is clicked.
The categories of wagon types can now be entered in the first column.
These are used to determine the likelihood of a particular type of wagon being
sent to a particular location. There is no limit to the number of
'Type' categories which can be created but for convenience I have
assumed there will be no more than 15 in my version of the spreadsheet.
The weightings are then selected for each wagon type and each location using a
drop-down list. 0 means there is no chance a wagon of that type will be sent
to that location (eg no brewery traffic will go to the Copper Mine). A
likelihood of 9 means there is a high probability that that type of wagon will
be sent to that location. The weightings can be altered at any time, so if I
feel that a particular location seems to be receiving very little traffic, I
can adjust the weightings for that location.
The Wagons Worksheet
The sheet holds details of every wagon on the system. A unique identifier is used in the first column, followed by the wagon's name and then a brief description to help with its identification. The next column indicates which type of wagon it is and the last column shows where it is (or should be) currently located.
Setting up the spreadsheet
The spreadsheet then needs to be given a name by clicking on the name field in the top left corner of the screen.
The Locations Worksheet
The Weightings Worksheet
After renaming the worksheet to 'weights',.......
The Wagons Worksheet
The Train Worksheet
Optional buttons
We must now type in the name of the script function which the button will call. As previously, it must be typed in exactly as shown (or the name of the function changed in the script to match what is assigned to the button). Click OK when you are finished
The script
// These two functions generate a sequence of 'n' random numbers between 'frm' and 'to'
function getRandomNumber(min, max) {
return Math.random() * (max - min) + min;
}
function getNRandomNumbers(frm, to, n){
var listNumbers = [];
var nRandomNumbers = [];
for(var i = frm; i <= to; i++) {
listNumbers.push(i);
}
// This part of the function ensures none of the numbers is repeated
for(var i = 0; i < n; i++) {
var index = getRandomNumber(0, listNumbers.length);
nRandomNumbers.push([listNumbers[parseInt(index)]]);
listNumbers.splice(index, 1);
}
return nRandomNumbers;
}
function wagonRand(noOSWags) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var wagSheet = ss.getSheetByName('wagons');
var trainSheet = ss.getSheetByName('train');
var noWagons = wagSheet.getLastRow();
var trainLen = trainSheet.getRange("F1").getValue();
trainSheet.getRange(2,1,20,1).clearContent();
var nRandomNumbers = getNRandomNumbers(2+noOSWags,noWagons,trainLen);
trainSheet.getRange(2,1,trainLen,1).setValues(nRandomNumbers);
}
// This function responds whenever a cell is edited. It is used to check when actions are selected
// from the drop-down lists on the Train Worksheet.
// This is necessary as the Google Sheets app doesn't allow scripts to be triggered using buttons
function onEdit(evt) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var wagSheet = ss.getSheetByName('wagons');
var trainSheet = ss.getSheetByName('train');
// Checks what has been selected from cell G1 on the Train Worksheet and then triggers the
// relevant function - ie either createTrain or resetAll
if(evt.range.getA1Notation() == 'G1') {
if(/^\w+$/.test(evt.value)) {
this[evt.value]();
evt.range.clear();
}
}
var col = evt.range.getColumn();
var row = evt.range.getRow();
var trainLen = trainSheet.getRange(1,6).getValue();
var adjacentCell = trainSheet.getRange(row, col + 1);
// This moves one wagon from its old location to its new one when this option is
// selected from the drop-down list beside the wagon in the Train Worksheet
if (evt.value == "Move_wagon"){
var newDest = trainSheet.getRange(row,col-1).getValue();
trainSheet.getRange(row,4).setValue(newDest);
var wagRow = trainSheet.getRange(row,1).getValue();
wagSheet.getRange(wagRow,5).setValue(newDest);
adjacentCell.setValue ("Done");
evt.range.clear();
}
// This moves all the wagons from their old location to the intended location
// on the Train Worksheet
if (evt.value == "Move_all"){
evt.range.clear();
for (var i=2;i<=trainLen + 1;i++){
var newDest = trainSheet.getRange(i,col-1).getValue();
trainSheet.getRange(i,4).setValue(newDest);
var wagRow = trainSheet.getRange(i,1).getValue();
wagSheet.getRange(wagRow,5).setValue(newDest);
trainSheet.getRange(i, col + 1).setValue("Done");
}
}
// This moves the wagons with the same intended location from old to new
if (evt.value == "Move_these"){
evt.range.clear();
var newDest = trainSheet.getRange(row,col-1).getValue();
for (var i=2;i<=trainLen+1;i++){
if (trainSheet.getRange(i,col-1).getValue() == newDest){
trainSheet.getRange(i,4).setValue(newDest);
var wagRow = trainSheet.getRange(i,1).getValue();
wagSheet.getRange(wagRow,5).setValue(newDest);
trainSheet.getRange(i, col + 1).setValue("Done");
}
}
}
}
// This is the master function which calls the other functions when CreateTrain is chosen
function createTrain() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var wagSheet = ss.getSheetByName('wagons');
var Avals = wagSheet.getRange("A1:A").getValues();
var noWagons = Avals.filter(String).length;
var range = wagSheet.getRange("A2:E"+noWagons);
var noOSWagons = 0;
range.sort([5]);
for (var i = 2; i<=noWagons;i++) {
var wagTyp = wagSheet.getRange('E'+i).getValue();
if (wagTyp.charAt(0) == "0"){
noOSWagons++;
}
}
wagonRand(noOSWagons);
fetchWagons();
semiRandDestination();
}
//This function sends all the wagons back to the first station in the Locations Worksheet
function resetAll(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var wagSheet = ss.getSheetByName('wagons');
var locSheet = ss.getSheetByName('locations');
var Avals = wagSheet.getRange("A1:A").getValues();
var noWagons = Avals.filter(String).length;
// Change the co-ordinates in the getRange(3,1) statement to select a different location
// to which the wagons are reset - ie getRange(row,column)
var homeDest = locSheet.getRange(3,1).getValue();
for (var i=2;i<=noWagons;i++){
wagSheet.getRange(i,5).setValue(homeDest);
}
}
// This function clears the previous train from the Train Worksheet
// and then copies the data for the wagons needed in the new train from the
// Wagons Worksheet to the Train Worksheet
function fetchWagons() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var wagSheet = ss.getSheetByName('wagons');
var trainSheet = ss.getSheetByName('train');
var trainLen = trainSheet.getRange("F1").getValue() + 1;
trainSheet.getRange("B2:E20").clear();
trainSheet.getRange("G2:G20").clear({contentsOnly: true});
for(var J=2;J<=trainLen;J++) {
var wagNo = trainSheet.getRange('A'+J).getValue();
var range1 = wagSheet.getRange('B'+wagNo+':C'+wagNo);
var range2 = wagSheet.getRange('E'+wagNo);
range1.copyTo(trainSheet.getRange('B'+J+':C'+J));
range2.copyValuesToRange(trainSheet,4,4,J,J);
}
}
//This function as the name suggests, calculates the new destination for each wagon
// in the train using the weightings from the Weightings Worksheet
function semiRandDestination() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var wagSheet = ss.getSheetByName('wagons');
var trainSheet = ss.getSheetByName('train');
var wtSheet = ss.getSheetByName('weights');
var locSheet = ss. getSheetByName ('locations');
var Avals = locSheet.getRange("A1:A").getValues();
var noLocs = Avals.filter(String).length;
var trainLen = trainSheet.getRange("F1").getValue() + 1;
var Avals = wtSheet.getRange("A1:A").getValues();
var noTypes = Avals.filter(String).length;
for (var i=2;i<=trainLen;i++){
var wagRow = trainSheet.getRange('A'+i).getValue();
var wagType = wagSheet.getRange('D'+ wagRow).getValue();
var hiWt = 0;
var newDest = "";
for(var j=2;j<=noTypes;j++){
if(wagType == wtSheet.getRange('A'+ j).getValue()){
for(var k=2;k<=noLocs;k++){
var wtLimit = wtSheet.getRange(j,k).getValue();
// Adjust the value for the generated random number by changing the default value of 5
// if you want to limit the range of random numbers generated
var randWt = Math.floor(Math.random()*5)+wtLimit-5
if(randWt < 0){
randWt = 0
}
// This picks the highest randomised destination for the wagon from each of the randomised calculations
if (randWt > hiWt && wtSheet.getRange(1,k).getValue() != wagSheet.getRange('E' + wagRow).getValue()){
hiWt = randWt;
newDest = wtSheet.getRange(1,k).getValue();
}
}
}
}
trainSheet.getRange('E'+i).setValue(newDest);
}
var range = trainSheet.getRange("A2:E"+trainLen);
range.sort([4,5]);
}
function moveWagon(){
var cellMove = e.range.getA1Notation()
}
Conclusion
This implementation of my freight handling program isn't perfect. It is a bit of a compromise between what I aspire to and what is achievable given the structure and limitations of the Google Sheets environment. However, as Google Sheets is a free resource, I am happy to live with its shortcomings.
I will probably tweak the programming and make a few adjustments to its structure when I make more extensive use of it out in the garden. In the meantime, I hope some of you will find it useful. I will be very interested in hearing about any modifications, extensions and improvements which others might make to it.
14 comments:
Thank you so much for this Freight manager, love it.
I noticed that there was not a sheet for your Locos. Or do you make that determination?
Much more fun for me to decide which loco to use in that session. So, no, no sheet for locos. But you could easily add one. Then you would have to decide what criteria you'd use to select it.
Thanks for your reply about Locos, I now agree with your thought.
Seeing that I have no scripting know how, how can I construct more than one consist? Right now you cannot have more than one which you should complete before you make up another train which overwrites the existing one, yes I know I can print it out but then once you create a new train the system cannot update the first.
Hmmmmmmm.... Interesting. How many consists would you want running at the same time? Each one would require its own worksheet. And then, when making up each new consist, the system would have to check which wagons had already been assigned to another consist. It would be possible, but the coding would need to be changed. It would make it a lot more complicated even having just two consists running at the same time.
Rik
Thanks for your prompt response, yes, I can see that it would be very complicated. On my TABLETOP GARDEN RAILWAY, I have designated my storage shed as North and South Depot as I have a circular circuit with one line going out and in then at the Turnout choosing north or south circuit. Either way you can deliver to all sidings, just from the opposite direction (hope that makes sense). My thinking was to have two consists running opposite directions and crossing somewhere in the middle. I now see that that would be a nightmare to script. I'll go with as is and still have fun. Thank you for your wonderful videos.
If I have time, I might give it a try. Sometimes these things work out simpler than they at first appear. Two consists might doable
Rik
Hi Rike,
On the train sheet, once it has created a new train I noticed that in column B it's printing the Name and not the Wagon ID. Have you realized or did I do something wrong?
Hi
Column A should show the wagon ID and so Column B should be showing the wagon name. So it sounds as if you've got the column headings wrong on the Train worksheet.
Rik
Sorry, you're right, it's me who got the heading wrong. Column B does show the wagon name.
Rik
If you would prefer to show the wagon ID on the train sheet rather than the wagon name then you will need to change these lines in the fetchWagon function as follows
From
var range1 = wagSheet.getRange('B'+wagNo+':C'+wagNo);
var range2 = wagSheet.getRange('E'+wagNo);
range1.copyTo(trainSheet.getRange('B'+J+':C'+J));
range2.copyValuesToRange(trainSheet,4,4,J,J);
To
var range1 = wagSheet.getRange('A'+wagNo);
var range2 = wagSheet.getRange('C'+wagNo);
var range3 = wagSheet.getRange('E'+wagNo);
range1.copyTo(trainSheet.getRange('B'+J));
range2.copyTo(trainSheet.getRange('C'+J));
range3.copyValuesToRange(trainSheet,4,4,J,J);
Either that or keep the code as is and change the heading on the Train Sheet from Wagon ID to Wagon Name
Sorry about the confusion
Rik
Hi Rik,
I went with the new script and seems to work fine. But I have another problem and that is when creating a train, it shows present location but then after about three lines the intended location is blank and if I then reset all, my Wagon sheet has no present location for those that had no intended location. By the way it also rearranges the wagons to different lines, i.e. the wagon that was originally on line one is now at line 20.
Luckily I can move them back on the spreadsheet. Your thoughts would be appreciated
Hmmmm ..... That shouldn't happen.
Check you've entered the code exactly as shown. Make sure there isn't a missing semi-colon or quote mark. Did you copy and paste the new code or type it in?
Rik
Sorry Rik,
It was my mistake, on checking the wagons sheet, I had not extended the Present Location when I added all of my rolling stock.
Thank you for your kind assistance.
I've just doubled checked and tried out the revised coding and it works perfectly. From the sound of what you're describing, I wonder if you've accidentally erased one of the curly brackets which follows the bit of code you changed? There should be two curly brackets.
To fix it, you might need erase the modified script and then copy and paste the original script back in to the script editor. Then very carefully replace the four lines of code with the six lines shown above.
Post a Comment