AllTravel
The version of the browser you are using is no longer supported. Please upgrade to a supported browser.Dismiss

ABCDE
1

This tool has been produced with the help of Google Maps and Google Apps Scripts see:-

In the xxxxDist sheet the distances can be calculate between two teams by just altering the cell. Any cell from 'F7' rightwards and downwards. Just put anything, say 'p' in a cell and the distance will be calculated. Sometimes the calculation fails, if it does just put something else in the cell and try again.

In the xxxxDist sheet the leagues are selected in column 'A'.
Put 'O' for teams in the Original league and 'T' for teams in the Target league.
The Target league is the league a team may be moved to from the Original league.
The distances are totalled for teams in the Original league to get to teams in the Target league.

The totals can be seen for travel out and back in the 'xxxxTotals' sheet. Put '1' in cell 'M1' to sort the teams, lowest mileage first.

New teams can be added or deleted by just typing a name into column 'B' of the xxxxDist sheet. A search function is in operation so that spellings are consistent. Just typing enough letters to uniquely identify the team should bring up the team. To turn the search off, put '1' in cell 'A1'. The teams are searched for in sheet 'LatLong' and the Latitude and Longitude of each team is copied to sheet 'xxxxDist'.
Of course if new teams are added or deleted then all the distance calculations involving those teams must be recalculated.

Google restricts the access to the distance function by limiting the number of calls per day. All the distances for West7Dist and Lev6Dist were done in one day. The AllDist totals were done over a few days.
1function DrivingMeters(origin, destination) {Google's distance function in metres.
2
2var directions = Maps.newDirectionFinder()
3
3.setOrigin(origin)
4
4.setDestination(destination)
5
5.getDirections();
6
6return directions.routes[0].legs[0].distance.value;
7
7}
8
8function DMiles(e) {
9
9var s = e.source.getActiveSheet();Get source sheet.
10
10var sheetname = s.getName();Get the name of sheet.
11
11var dist = sheetname.substr(sheetname.length - 4);Get the last 4 characters.
12
12if(dist != "Dist") return;If last 4 characters are not equal to 'Dist' return.
13
13var arow = e.range.getRow();Get row being edited.
14
14if (arow < 7) return;If row is less than 7 return.
15
15var acol = e.range.getColumn();Get column edited.
16
16if(acol == 2) {If column = 2 then a team is being edited.
17
17var range = e.range;get range being edited.
18
18var block = s.getRange("A1").getValue();Check if search turned off.
19
19if ( block == 1 ) return;
20
20var val = range.getValue();Get letters of team typed in.
21
22
22var name_s = ss.getSheetByName("LatLong");Get the sheet called 'LatLong'.
23
23name_s.getRange("D3").setValue(val);Put the letters typed in in the cell 'D3'.
24
24var tname = name_s.getRange("D5").getValue();The search result will be in cell 'D5'.
25
25s.getRange(arow,acol).setValue(tname);Put the result back in the original cell.
26
26return;
27
27}
28
28if(acol < 6 ) return;if column less than 6 return.
29
29var origin = s.getRange(arow,3).getValue();Get lat long of original.
30
30var destination = s.getRange(4,acol).getValue();Get lat long of destination.
31
31var val = DrivingMeters(origin, destination)/1609.34;Get distance in miles.
32
32s.getRange(arow,acol).setValue(val);Put this dist in original cell.
33
33}
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100