Scripts in Google spreadsheets

Not so long ago, the scripting service in Google tables for personal accounts (gmail) became available.
googledocs.blogspot.com/2010/03/apps-script-gallery-for-google.html
Short description
www.google.com/google-ds/scripts/scripts.html
In addition, the service of publishing scripts to the gallery became available directly from the tables.

Now we can create scripts for Google Spreadsheets, download ready-made, publish, share with other users.
Scripts have a rich set of features. We can use all the controls available in Google services.
www.google.com/google-ds/scripts/overview.html
')
The result of the script, for example, can be:

• A new feature that is not in the standard set;
• the value returned by the web service, including SOAP, WSDL and others;
• management of other services, for example, website, calendar, mailing;
• automatic table filling (direct management of our spreadsheets);
• creating user interfaces (custom spreadsheets UI).

Examples of scripts created for use in neogeography

Calculating the distance between points along a large arc

Copy Source | Copy HTML

1. function GCD1(lat1, lon1, lat2, lon2) { // Return Great Circle Distance between points calculation function radians(a) { var outNum =Math.PI*a/180; return outNum; } var R = 6372.795; var d1=Math.sin(radians(lat1))*Math.sin(radians(lat2))+Math.cos(radians(lat1))*Math.cos(radians(lat2))*Math.cos(radians(lon2)-radians(lon1)); var d2=Math.cos(radians(lat2))*Math.sin(radians(lon2)-radians(lon1)); var d3=Math.cos(radians(lat1))*Math.sin(radians(lat2))-Math.sin(radians(lat1))*Math.cos(radians(lat2))*Math.cos(radians(lon2)-radians(lon1)); var len=R*Math.atan2(Math.sqrt(d2*d2+d3*d3),d1); return len; }​
2. function GCD1(lat1, lon1, lat2, lon2) { // Return Great Circle Distance between points calculation function radians(a) { var outNum =Math.PI*a/180; return outNum; } var R = 6372.795; var d1=Math.sin(radians(lat1))*Math.sin(radians(lat2))+Math.cos(radians(lat1))*Math.cos(radians(lat2))*Math.cos(radians(lon2)-radians(lon1)); var d2=Math.cos(radians(lat2))*Math.sin(radians(lon2)-radians(lon1)); var d3=Math.cos(radians(lat1))*Math.sin(radians(lat2))-Math.sin(radians(lat1))*Math.cos(radians(lat2))*Math.cos(radians(lon2)-radians(lon1)); var len=R*Math.atan2(Math.sqrt(d2*d2+d3*d3),d1); return len; }​
3. function GCD1(lat1, lon1, lat2, lon2) { // Return Great Circle Distance between points calculation function radians(a) { var outNum =Math.PI*a/180; return outNum; } var R = 6372.795; var d1=Math.sin(radians(lat1))*Math.sin(radians(lat2))+Math.cos(radians(lat1))*Math.cos(radians(lat2))*Math.cos(radians(lon2)-radians(lon1)); var d2=Math.cos(radians(lat2))*Math.sin(radians(lon2)-radians(lon1)); var d3=Math.cos(radians(lat1))*Math.sin(radians(lat2))-Math.sin(radians(lat1))*Math.cos(radians(lat2))*Math.cos(radians(lon2)-radians(lon1)); var len=R*Math.atan2(Math.sqrt(d2*d2+d3*d3),d1); return len; }​
4. function GCD1(lat1, lon1, lat2, lon2) { // Return Great Circle Distance between points calculation function radians(a) { var outNum =Math.PI*a/180; return outNum; } var R = 6372.795; var d1=Math.sin(radians(lat1))*Math.sin(radians(lat2))+Math.cos(radians(lat1))*Math.cos(radians(lat2))*Math.cos(radians(lon2)-radians(lon1)); var d2=Math.cos(radians(lat2))*Math.sin(radians(lon2)-radians(lon1)); var d3=Math.cos(radians(lat1))*Math.sin(radians(lat2))-Math.sin(radians(lat1))*Math.cos(radians(lat2))*Math.cos(radians(lon2)-radians(lon1)); var len=R*Math.atan2(Math.sqrt(d2*d2+d3*d3),d1); return len; }​
5. function GCD1(lat1, lon1, lat2, lon2) { // Return Great Circle Distance between points calculation function radians(a) { var outNum =Math.PI*a/180; return outNum; } var R = 6372.795; var d1=Math.sin(radians(lat1))*Math.sin(radians(lat2))+Math.cos(radians(lat1))*Math.cos(radians(lat2))*Math.cos(radians(lon2)-radians(lon1)); var d2=Math.cos(radians(lat2))*Math.sin(radians(lon2)-radians(lon1)); var d3=Math.cos(radians(lat1))*Math.sin(radians(lat2))-Math.sin(radians(lat1))*Math.cos(radians(lat2))*Math.cos(radians(lon2)-radians(lon1)); var len=R*Math.atan2(Math.sqrt(d2*d2+d3*d3),d1); return len; }​
6. function GCD1(lat1, lon1, lat2, lon2) { // Return Great Circle Distance between points calculation function radians(a) { var outNum =Math.PI*a/180; return outNum; } var R = 6372.795; var d1=Math.sin(radians(lat1))*Math.sin(radians(lat2))+Math.cos(radians(lat1))*Math.cos(radians(lat2))*Math.cos(radians(lon2)-radians(lon1)); var d2=Math.cos(radians(lat2))*Math.sin(radians(lon2)-radians(lon1)); var d3=Math.cos(radians(lat1))*Math.sin(radians(lat2))-Math.sin(radians(lat1))*Math.cos(radians(lat2))*Math.cos(radians(lon2)-radians(lon1)); var len=R*Math.atan2(Math.sqrt(d2*d2+d3*d3),d1); return len; }​
8. function GCD1(lat1, lon1, lat2, lon2) { // Return Great Circle Distance between points calculation function radians(a) { var outNum =Math.PI*a/180; return outNum; } var R = 6372.795; var d1=Math.sin(radians(lat1))*Math.sin(radians(lat2))+Math.cos(radians(lat1))*Math.cos(radians(lat2))*Math.cos(radians(lon2)-radians(lon1)); var d2=Math.cos(radians(lat2))*Math.sin(radians(lon2)-radians(lon1)); var d3=Math.cos(radians(lat1))*Math.sin(radians(lat2))-Math.sin(radians(lat1))*Math.cos(radians(lat2))*Math.cos(radians(lon2)-radians(lon1)); var len=R*Math.atan2(Math.sqrt(d2*d2+d3*d3),d1); return len; }​
9. function GCD1(lat1, lon1, lat2, lon2) { // Return Great Circle Distance between points calculation function radians(a) { var outNum =Math.PI*a/180; return outNum; } var R = 6372.795; var d1=Math.sin(radians(lat1))*Math.sin(radians(lat2))+Math.cos(radians(lat1))*Math.cos(radians(lat2))*Math.cos(radians(lon2)-radians(lon1)); var d2=Math.cos(radians(lat2))*Math.sin(radians(lon2)-radians(lon1)); var d3=Math.cos(radians(lat1))*Math.sin(radians(lat2))-Math.sin(radians(lat1))*Math.cos(radians(lat2))*Math.cos(radians(lon2)-radians(lon1)); var len=R*Math.atan2(Math.sqrt(d2*d2+d3*d3),d1); return len; }​
10. function GCD1(lat1, lon1, lat2, lon2) { // Return Great Circle Distance between points calculation function radians(a) { var outNum =Math.PI*a/180; return outNum; } var R = 6372.795; var d1=Math.sin(radians(lat1))*Math.sin(radians(lat2))+Math.cos(radians(lat1))*Math.cos(radians(lat2))*Math.cos(radians(lon2)-radians(lon1)); var d2=Math.cos(radians(lat2))*Math.sin(radians(lon2)-radians(lon1)); var d3=Math.cos(radians(lat1))*Math.sin(radians(lat2))-Math.sin(radians(lat1))*Math.cos(radians(lat2))*Math.cos(radians(lon2)-radians(lon1)); var len=R*Math.atan2(Math.sqrt(d2*d2+d3*d3),d1); return len; }​
11. function GCD1(lat1, lon1, lat2, lon2) { // Return Great Circle Distance between points calculation function radians(a) { var outNum =Math.PI*a/180; return outNum; } var R = 6372.795; var d1=Math.sin(radians(lat1))*Math.sin(radians(lat2))+Math.cos(radians(lat1))*Math.cos(radians(lat2))*Math.cos(radians(lon2)-radians(lon1)); var d2=Math.cos(radians(lat2))*Math.sin(radians(lon2)-radians(lon1)); var d3=Math.cos(radians(lat1))*Math.sin(radians(lat2))-Math.sin(radians(lat1))*Math.cos(radians(lat2))*Math.cos(radians(lon2)-radians(lon1)); var len=R*Math.atan2(Math.sqrt(d2*d2+d3*d3),d1); return len; }​
12. function GCD1(lat1, lon1, lat2, lon2) { // Return Great Circle Distance between points calculation function radians(a) { var outNum =Math.PI*a/180; return outNum; } var R = 6372.795; var d1=Math.sin(radians(lat1))*Math.sin(radians(lat2))+Math.cos(radians(lat1))*Math.cos(radians(lat2))*Math.cos(radians(lon2)-radians(lon1)); var d2=Math.cos(radians(lat2))*Math.sin(radians(lon2)-radians(lon1)); var d3=Math.cos(radians(lat1))*Math.sin(radians(lat2))-Math.sin(radians(lat1))*Math.cos(radians(lat2))*Math.cos(radians(lon2)-radians(lon1)); var len=R*Math.atan2(Math.sqrt(d2*d2+d3*d3),d1); return len; }​
13. function GCD1(lat1, lon1, lat2, lon2) { // Return Great Circle Distance between points calculation function radians(a) { var outNum =Math.PI*a/180; return outNum; } var R = 6372.795; var d1=Math.sin(radians(lat1))*Math.sin(radians(lat2))+Math.cos(radians(lat1))*Math.cos(radians(lat2))*Math.cos(radians(lon2)-radians(lon1)); var d2=Math.cos(radians(lat2))*Math.sin(radians(lon2)-radians(lon1)); var d3=Math.cos(radians(lat1))*Math.sin(radians(lat2))-Math.sin(radians(lat1))*Math.cos(radians(lat2))*Math.cos(radians(lon2)-radians(lon1)); var len=R*Math.atan2(Math.sqrt(d2*d2+d3*d3),d1); return len; }​
14. function GCD1(lat1, lon1, lat2, lon2) { // Return Great Circle Distance between points calculation function radians(a) { var outNum =Math.PI*a/180; return outNum; } var R = 6372.795; var d1=Math.sin(radians(lat1))*Math.sin(radians(lat2))+Math.cos(radians(lat1))*Math.cos(radians(lat2))*Math.cos(radians(lon2)-radians(lon1)); var d2=Math.cos(radians(lat2))*Math.sin(radians(lon2)-radians(lon1)); var d3=Math.cos(radians(lat1))*Math.sin(radians(lat2))-Math.sin(radians(lat1))*Math.cos(radians(lat2))*Math.cos(radians(lon2)-radians(lon1)); var len=R*Math.atan2(Math.sqrt(d2*d2+d3*d3),d1); return len; }​

Direct geocoding
Return KML by place name

Copy Source | Copy HTML

1. function getKML (placeName) {
2. // Return KML by placename
3. if (placeName == "") {
4. return "you have to write the name the place"
5. }
6. var url = "http://maps.google.com/maps/geo?q="+ placeName +" & output = kml ";
7. var response = UrlFetchApp.fetch (url);
8. var str = response.getContentText ();
9. return str;
10. }

Return coordinates by place name

1. function getLngLat (placeName) {
2. // Return LngLatitude by placename
3. if (placeName == "") {
4. return "you have to write the name the place"
5. }
6. var url = "http://maps.google.com/maps/geo?q="+ placeName +" & output = json ";
7. var response = UrlFetchApp.fetch (url);
8. var str = eval ('(' + response.getContentText () + ')'). Placemark [0] .Point.coordinates;
9. return str;
10. }

Return longitude by place name

1. function getLng (placeName) {
2. // Return Longitude by placename
3. if (placeName == "") {
4. return "you have to write the name the place"
5. }
6. var url = "http://maps.google.com/maps/geo?q="+ placeName +" & output = json ";
7. var response = UrlFetchApp.fetch (url);
8. var str = eval ('(' + response.getContentText () + ')'). Placemark [0] .Point.coordinates [0];
9. return str;
10. }

Return latitude by place name

1. function getLat (placeName) {
2. // Return Latitude by placename
3. if (placeName == "") {
4. return "you have to write the name the place"
5. }
6. var url = "http://maps.google.com/maps/geo?q="+ placeName +" & output = json ";
7. var response = UrlFetchApp.fetch (url);
8. var str = eval ('(' + response.getContentText () + ')'). Placemark [0] .Point.coordinates [1];
9. return str;
10. }

Reverse geocoding
Return the address by coordinates

1. function getAddress (placeCoord) {
2. // Return Address by placeCoord (reverse geocoding) placeCoord = lat, lng
3. if (placeCoord == "") {
4. return "you have to write the name the place"
5. }
6. var url = "http://maps.google.com/maps/geo?q="+ placeCoord +" & output = json ";
7. var response = UrlFetchApp.fetch (url);
8. var str = eval ('(' + response.getContentText () + ')'). Placemark [0] .address;
9. return str;
10. }

Sample calculation table using these scripts
news.eastgeology.com/2010/04/google-spreadsheets.html