How I created a version of my freight management program with Google Sheets

 

Contents

•  Introduction
• Overview of the system
• The Train Sheet
• The Locations Sheet
• The Weightings Sheet
• The Wagons Sheet
• 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:

1. It's free
2. It will run on my laptop, my tablet and my phone
3. 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.

 

 

 A train of the given length is then generated showing each wagon's name, a brief description, its present location and the intended destination. If I don't fancy this particular train, I can generate another and so on until one turns up which is more appealing.

The train is then marshalled and sent around the railway. Once a wagon has been shunted into its new location, I can then choose 'Move_wagon' from the drop-down box beside it to tell the system it has been transferred to its new location.

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 Wagons Worksheet is accessed by clicking the 'wagons' tab at the foot of the page.

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.

 

 

Data in the first three columns are entered free-hand, but the wagon type category is selected from a drop-down list, drawn from the data on the weightings worksheet.

 

Similarly, the wagon's present location is entered using a drop-down list drawn from the locations worksheet. It's important, therefore that the data is entered firstly on the locations sheet, then on the weightings sheet and finally on the wagons sheet.

There is no limit to the number of wagons which can be entered into the system, but I have assumed I will not have any more than 100 wagons on my railway.

 

 

 Setting up the spreadsheet

I am assuming that you are reasonably familiar with using spreadsheets, but you may not have designed one using all the features which are needed (eg drop-down lists). I created the spreadsheet on my laptop as I find it easier than working on my tablet and more particularly on my phone - my fingers are too fat!

 

The first task is to create a new spreadsheet. This is accomplished by clicking on the multi-coloured plus sign in the bottom right of the screen.

 The spreadsheet then needs to be given a name by clicking on the name field in the top left corner of the screen.

I started with the Locations worksheet. Not only is this the easiest to create but its data needs to be used in all the other worksheets.

The Locations Worksheet

Firstly, I renamed the worksheet from its default of 'Sheet1', by clicking on the arrow beside the name of the worksheet and then clicking on 'Rename' from the pop-up menu.

 

Two headings were then typed into cells A1 and B1 - Locations and Short Name. It has turned out that I haven't used the short name in this version of the program, but it might be useful for future extensions of the program.

 

The headings were enlarged to 12 point and emboldened, using the relevant buttons in the text tools palette, ......

...... and then some data was entered for test purposes. I started the name of each location with a number as it's useful for sorting the locations into the order in which they appear on my railway. 

 

Note: I started with zero for Out of Service which ensures that any out of service stock will be ignored when calculating intended destinations.

The Locations Sheet has now been set up. Next, we need to set up the Weightings Sheet as this contains the data for the wagon category types which are needed on the Wagons Sheet.

 

The Weightings Worksheet

To add a new worksheet, we click on the + sign in the bottom left corner of the screen. As you can see, this has created a worksheet called Sheet2.

 After renaming the worksheet to 'weights',.......

....... we now enter the heading 'Type' into cell A1.

 

NOTE It is important that you type in the names for the sheets exactly as shown (eg lack of capital letters) as these names are used by the script

 

The heading was then centred, emboldened and increased in size to 12 point using the buttons in the text palette above the worksheet.

 

This formula is then typed into cell B1

 

=ARRAYFORMULA(TRANSPOSE(locations!A3:A15))

 

It takes the contents of cells A3 to A15 on the locations worksheet, transposes them (ie turns them from a vertical column into a horizontal row) and pastes the contents as an array from cell B1 onwards.

 

NOTE: If you have more than 13 locations, then increase the final cell address accordingly.

 

After entering the first category of wagon type into cell A2, we now need to create a drop-down list for the weightings in cell B2.

 

Click in cell B2 to select it, then select Data validation from the Data menu.

 

We now change the Criteria to List of items and enter the weightings 

 

0,1,2,3,4,5,6,7,8,9

 

into the field beside it.

 

We put a tick beside  Show validation help text and enter the prompt

 

Choose a weighting from 0 (low) to 9 (high)

 

into the field below it.

 

 

After clicking Save, we now need to copy the settings of this cell into the other cells we will be using.

 

Click on the small blue square in the bottom right corner of the cell and with the mouse button held down, .......

 

........ drag it down as far as is necessary for the number of category types you will will be creating(plus a few more for future expansion) - in my case I dragged it down to cell B15. 

 

We now select those cells again and click the blue square in the bottom right corner ........

 

...... and drag it across to the right as far as we expect the number of locations on our system to be (plus a few more for future expansion). In my case, I dragged it to column K.

 

We can now enter more data on to the sheet, using the drop-down lists to select the required weightings.

Note: The drop-down list is accessed by clicking on the downward facing arrow in the corner of the cell

 

Now we have completed the Weightings Worksheet, we need to create the all-important Wagons Worksheet

 

 The Wagons Worksheet

 We add a new worksheet, rename it to wagons and then enter the headings as shown below.

 

If you need to widen any of the columns as I did to fit in the header for Present Location, then drag the column divider above the column to make it wider.
 

We now need to create a drop-down list of wagon types, using the data from the Weightings Worksheet.

 

After selecting cell, D2, we choose Data validation from the Data menu and then make sure that List from a range is showing the the Criteria field

 

We now click on the Select data range button on the left of the field which will contain the list of options.

 

A new pop-up message will appear asking us to Select a data range.

 

We now go to the Weightings Worksheet to select the cells from which the data will be drawn. Click on the weightings tab and then drag down from cell A2 to the bottom of the range you want to use - in my case from cell A2 to cell A15.

This will enter the selected range into the field on the pop-up box. Alternatively, you can type in the range - but make sure you get the name of the tab for the weightings worksheet absolutely correct, followed by an exclamation mark and then the range of cells - ie

weights!A2:A15

I then clicked on the 'Show validation help text' checkbox followed by Save.

 Returning to the Wagons Worksheet, the drop-down list should now appear when clicking on the down arrow in cell D2.

We now need to copy this format from cell D2 to the cells below it. As previously, we drag the small blue square in the bottom right corner of the cell down the column as far as is necessary - in my case it was to cell D15

We now need to populate the Present location column with drop-down lists. As before, we click on cell E2 and then select Data validation from the Data menu

 We then select the required data range (by clicking the small button at the end of the data range field), navigating to the Locations Worksheet (by clicking its tab) and then highlighting the cells containing the list of location names - in my case, cells A2 to A4.

 

 I then clicked on the Show validation help text checkbox and wrote an appropriate message in the help text field below it. I then clicked Save.

 

Returning to the Wagons Worksheet, I then checked the drop down list was working properly and copied its format from cell E2 down the column to cell E15 (by dragging the small blue square downwards in the bottom right corner of cell E2).

 

I then entered some sample wagon data on to the sheet for later testing purposes.

 

 

Incidentally, I then spent a little while creating a validation rule for column A to ensure that anything entered followed the format of a single upper case alphabetical character followed by a number. But this isn't really necessary as it turned out I didn't need to use these data in the script.

The formula needed to check the data turned out to be:

=REGEXMATCH(A3,"^[A-Z][0-9]?[0-9]$")

 

You might want to tinker with this if you decide to restrict the type of data which is entered into this column.

 

The Train Worksheet

 Another new worksheet was created, renamed 'train' and given the headers shown below.

 

 In cell F1, I decided to put a drop-down list to determine the length of train which the system will eventually generate. So, cell F1 was selected and then Data validation selected from the Data menu.

A 'List of items' ....

 

4,5,6,7,8,9,10,11,12,13,14,15

.... was typed into the validation field, and the 'Show validation help text' checkbox ticked. The Save button was then clicked.

.

In cell F2, another list of items was entered into the Data validation field .....

 

Move_wagon,Move_these,Move_all

 

 ..... and the validation help text changed and its checkbox ticked.

NOTE: If you are going to use my script, then it is essential the list is typed in exactly as show - including the capitalisation and underscore character as these are the names of the functions which will be called

 

This format was then copied down the column (by dragging the small blue square), in my case to cell F16 (as my maximum train length is 15 wagons).

In cell G1, I created a drop-down validation list comprising two items.....

 

createTrain,resetAll

This will enable us to trigger the main train creation function or to relocate all the wagons to location number 1 (or wherever your main station is located).

I'm assuming that, by now, you know how to create this drop-down validation list.

Optional buttons

The main functions are triggered by using drop-down lists as on-screen buttons don't work in the mobile app versions of Google Sheets. A great pity as large on-screen buttons are much easier to tap on a small phone screen than tiny drop-down arrows in the corner of a cell.

The buttons do work on a PC, and so I include how to create them, just in case you would prefer the option of using them on your laptop.

To create a button, we first of all need to draw it, using Google Sheet's drawing tools. From the Insert menu, select Drawing

To keep things simple, our button will simply be a text box. Click the Text box button in the drawing tools palette and then, on the drawing screen, drag out a suitably sized box. Click inside the box and type in some appropriate text.

The text can be formatted, using various tools accessed by clicking the three dots at the end of the Tool palette. I increased its size to 14 point, emboldened it and centred it in the text box.

 

 

 

I then filled the text box with a warm gold colour, using the Fill tool.

I then clicked the Save and Close button and was able to drag the text box into the position I wanted it to appear on the screen.

 

To turn it into a button, we need to create an action for it. Click on the three vertical dots in the top right corner of the text box. From the pop-up menu, select, Assign script.

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

If you click on the button or try to move or resize it now, you will be presented with the message message shown below - because, of course, we haven't yet written the script. 

To move or resize the button, firstly right-click on it. It can now be moved or resized as normal.

 The script

Underlying the operation of the Freight management system is a script, written in JavaScript. If you're anything like me, you will probably want to try out out - and then figure out how it works later. 

 

To gain access to the Script Editor, select <>Script Editor from the Tools menu

 

Then, rename the script by clicking on the title field at the top of the Script Editor window. 

You will notice that there is the beginnings of a script in the editing pane. You can delete this if you want to keep things neat, but as this function won't be called, it can sit there doing nothing and the script shown below can be copied and pasted below it.

 I have included comments within the script to explain what each section does. Many of the JavaScript statements are fairly self explanatory, but if you do decide to make changes then it is very important to exactly follow the format of the statements. Even a stray capital letter or a mistaking a comma for a semi-colon can wreak havoc! You have been warned!

 

// 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.