Googleスプレッドシートのスクリプト

少し前まで、個人アカウント（gmail）用のGoogleスプレッドシートのスクリプトサービスが利用可能になりました。

googledocs.blogspot.com/2010/03/apps-script-gallery-for-google.html

簡単な説明

www.google.com/google-ds/scripts/scripts.html

さらに、テーブルから直接、ギャラリーにスクリプトを公開するサービスが利用可能になりました。



これで、Googleスプレッドシート用のスクリプトを作成し、既製をダウンロードして公開し、他のユーザーと共有できます。

スクリプトには豊富な機能があります。 Googleサービスで利用可能なすべてのコントロールを使用できます。

www.google.com/google-ds/scripts/overview.html



たとえば、スクリプトの結果は次のようになります。

• 標準セットにはない新機能。
• SOAP、WSDLなどを含むWebサービスによって返される値。
• サイト、カレンダー、メーリングリストなどの他のサービスの管理。
• テーブルの自動入力（スプレッドシートの直接制御）;
• ユーザーインターフェイスの作成（カスタムスプレッドシートUI）。

新地理で使用するために作成されたスクリプトの例



大きな円弧に沿ったポイント間の距離の計算

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; }​
    
    
    
    

ダイレクトジオコーディング

場所名でKMLを返す

Copy Source | Copy HTML

1. 関数getKML（placeName）{
2. // KMLを地名で返す
3. if（placeName == ""）{
4. return "名前を場所に書く必要があります"
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. }

場所名で座標を返す

1. 関数getLngLat（placeName）{
2. //地名でLngLatitudeを返します
3. if（placeName == ""）{
4. return "名前を場所に書く必要があります"
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. }

場所名で経度を返す

1. 関数getLng（placeName）{
2. //地名で経度を返します
3. if（placeName == ""）{
4. return "名前を場所に書く必要があります"
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（）+ '）'）。目印[0] .Point.coordinates [0];
9. return str;
10. }

場所名で緯度を返す

1. 関数getLat（placeName）{
2. //地名で緯度を返します
3. if（placeName == ""）{
4. return "名前を場所に書く必要があります"
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. }

逆ジオコーディング

座標で住所を返す

1. 関数getAddress（placeCoord）{
2. // placeCoord（逆ジオコーディング）で住所を返すplaceCoord = lat、lng
3. if（placeCoord == ""）{
4. return "名前を場所に書く必要があります"
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. }

これらのスクリプトを使用した計算表の例

news.eastgeology.com/2010/04/google-spreadsheets.html