-
Notifications
You must be signed in to change notification settings - Fork 3
/
GoogleSheets.gs
154 lines (125 loc) · 4.85 KB
/
GoogleSheets.gs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
/* Start of the GAS code. */
/*
-------------------------------------------------------------------------------
The next set of functions are used to perform geocoding and reverse geocoding.
Written By: Christos Samaras
Date: 18/08/2018
E-mail: xristos.samaras@gmail.com
Site: https://www.myengineeringworld.net
-------------------------------------------------------------------------------
*/
/**
* This function returns the latitude and longitude of a given address using the Geocoder class.
*
* @param {A2} address A cell that contains an address.
* @return The latitude and the longitude of the given address.
* @customfunction
*/
function getCoordinates(address)
{
// Initialize the geocoder object.
var geocoder = Maps.newGeocoder().geocode(address);
// Initialize the latitude / longitude variables.
var lat = 0;
var long = 0
// Check if the response returned without a problem.
if (geocoder.status == 'OK')
{
// Retrieve the latitude / longitude information.
lat = geocoder["results"][0]["geometry"]["location"]["lat"];
long = geocoder["results"][0]["geometry"]["location"]["lng"];
}
// Return the latitude / longitude information as string.
return lat + ", " + long;
}
// --------------------------------------------------------------------------------------------------------------
/**
* This function returns the latitude and longitude of a given address as an array using the Geocoder class.
*
* @param {A2} address A cell that contains an address.
* @return The latitude and the longitude of the given address.
* @customfunction
*/
function getCoordinatesArray(address)
{
// Initialize the geocoder object.
var geocoder = Maps.newGeocoder().geocode(address);
// Initialize the latitude / longitude variables.
var lat = 0;
var long = 0
// Check if the response returned without a problem.
if (geocoder.status == 'OK')
{
// Retrieve the latitude / longitude information.
lat = geocoder["results"][0]["geometry"]["location"]["lat"];
long = geocoder["results"][0]["geometry"]["location"]["lng"];
}
// Return the latitude / longitude information as an array.
return [lat, long];
}
// --------------------------------------------------------------------------------------------------------------
/**
* This function returns the latitude of the given address using the Geocoder class.
*
* @param {A2} address A cell that contains an address.
* @return The latitude of the given address.
* @customfunction
*/
function getLatitude(address)
{
// Initialize the geocoder object.
var geocoder = Maps.newGeocoder().geocode(address);
// Initialize the latitude variable.
var lat = 0;
// Get the latitude if the response returned without a problem.
if (geocoder.status == 'OK')
lat = geocoder["results"][0]["geometry"]["location"]["lat"];
// Return the latitude as double.
return lat;
}
// --------------------------------------------------------------------------------------------------------------
/**
* This function returns the longitude of the given address using the Geocoder class.
*
* @param {A2} address A cell that contains an address.
* @return The longitude of the given address.
* @customfunction
*/
function getLongitude(address)
{
// Initialize the geocoder object.
var geocoder = Maps.newGeocoder().geocode(address);
// Initialize the longitude variable.
var long = 0;
// Get the latitude if the response returned without a problem.
if (geocoder.status == 'OK')
long = geocoder["results"][0]["geometry"]["location"]["lng"];
// Return the longitude as double.
return long;
}
// --------------------------------------------------------------------------------------------------------------
/**
* This function returns the address from a given pair of latitude and longitude using the Geocoder class.
* In other words, it performs reverse geocoding.
*
* @param {A2} lat A cell that contains a latitude value between -90 and +90 degrees.
* @param {B2} long A cell that contains a longitude value between -180 and +180 degrees.
* @return The address from a given pair of latitude and longitude.
* @customfunction
*/
function getAddress(lat, long)
{
// Checking the input variables:
// The valid range of latitude in degrees is -90 and +90 for the Southern and Northern hemisphere respectively.
if(lat < -90 || lat > 90)
return "Invalid Latitude";
// Longitude is in the range -180 and +180 specifying coordinates West and East of the Prime Meridian, respectively.
if(long < -180 || long > 180)
return "Invalid Longitude";
var response = Maps.newGeocoder().reverseGeocode(lat, long);
var address = "";
if (response.status == 'OK')
address = response["results"][0]["formatted_address"];
return address;
}
/* End of the GAS code. */