【GAS】スプレッドシートで（個人的に）よく使うコード１０選

##概要
いわゆるスニペット集？

既存のスクリプトを開いてコピペしている事の多いコードを、１０個ばかり選定してみた（あくまで私的な^^;）チートシート。

##コードに差し込むスニペット７個
短かったり、他のコードと繋げたかったりで、`function(){}`内に差し込みたいコード。

###【特定のシートをマルっと取得】
```javascript
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シート名'); 
const data = sheet.getDataRange().getValues();
```

シートを二次元配列に格納するので、A1セルなら`data[0][0]`のように参照できる。いちいちシートを呼び出す必要がなくなって便利。

###【実行時にダイアログで最終確認】
```javascript
if(Browser.msgBox('最終確認','実行しますか？',Browser.Buttons.YES_NO)!='yes'){return;}
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/708687/e4a8138f-3bad-691c-7b88-481a2b7f7018.png)
「はい」ならスクリプト続行。「いいえ」や「×」なら中断。

###【配列の要素数を気にせずシートに書き出す】
```javascript
var row = array.length;
var col = array[0].length;
sheet.getRange(1, 1, row, col).setValues(array);
```

配列をシートに書き出す際、要素数を間違えるとエラーになってしまう。こういう書き方なら`array`の要素数が違ってもコードは同じでOK。

###【日付の取り扱い】
```javascript
//本日
var date = new Date();  
var today = Utilities.formatDate(date, 'Asia/Tokyo', '表示形式');

//明日ならi=1 昨日ならi=-1
var date = new Date();  
date.setDate(date.getDate() + i);  
var day = Utilities.formatDate(date, 'Asia/Tokyo', '表示形式');
```

表示形式は、`年：y` `月：M` `日：d` `時（12時間表記）：h` `時（24時間表記）：H` `分：m` `秒：s`で、それぞれ表現できる。
例）`'yyyy/MM/dd/HH:mm'` → `2020/12/31/23:59`

###【シートのデータも画像もクリア】
```javascript
//ヘッダ行を除いてセルをクリア
sheet.getRange(2, 1, lastrow, lastcol).clearContent(); 

//全ての画像をクリア
var images = spreadsheet.getImages(); 
for (var image of images){ 
  image.remove(); 
}
```

画像をクリアするメソッドが分からなかったので、シートの画像を全て取得してforで繰り返しクリアしている。トリガーと併用することで、毎日始業前にシートをリセットしたりできる。

###【スプレッドシートをバックアップ】
```javascript
var file = DriveApp.getFileById('ファイルID'); 
var folder = DriveApp.getFolderById('フォルダID'); 
var filename = file.getName() + '_' + Utilities.formatDate(new Date(),'JST','MM/dd/HH:mm'); 
   
file.makeCopy(filename, folder);
```

特定のファイルのコピーを`ファイル名_MM/dd/HH:ss`としてドライブに保存。

###【スプレッドシートをエクセルに変換】
```javascript
//エクセルを返すURL 
var fetchUrl = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + "シートID" + "&amp;exportFormat=xlsx"; 
   
//OAuth
var fetchOpt = { 
  "headers" : { Authorization: "Bearer " + ScriptApp.getOAuthToken() }, 
  "muteHttpExceptions" : true 
}; 
   
//エクセルファイルゲット！
var xlsxFile = UrlFetchApp.fetch(fetchUrl, fetchOpt).getBlob().setName("任意の名前.xlsx");
```

あとは`xlsxFile`をドライブに保存するなりメールに添付するなり。

##独立したコード３個
ちょっと長かったり、単独で動かしたかったりで、`function(){}`で囲いたいコード。

###【カスタムメニュー】
```javascript
function onOpen() { 

  SpreadsheetApp 
    .getActiveSpreadsheet() 
    .addMenu('メニュー名', [ 
      {name: 'サブメニュー名', functionName: 'スクリプト名'}, 
      {name: 'サブメニュー名', functionName: 'スクリプト名'}, 
      {name: 'サブメニュー名', functionName: 'スクリプト名'}, 
    ]);

}
```

スプレッドシート起動時に作動させたいため、`onOpne()`で作成。

###【シートの最終更新日時をセルに書き出す】
```javascript
function onEdit(e) { 

  //目当てのシートでなければ中断
  if(e.range.getSheet().getSheetName() !== "シート名"){return;} 
   
  //ヘッダ行だった場合も中断
  const row = e.range.getRow(); 
  if(row < 2){return;} 

  var date = new Date(); 
  var today = Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy/MM/dd'); 
  var time = Utilities.formatDate(date, 'Asia/Tokyo', 'HH:mm'); 
   
  //A1セルに８桁の年月日 B1セルに２４時間表記の時刻
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シート名'); 
  spreadsheet.getRange('A1').setValue(today);  
  spreadsheet.getRange('B1').setValue(time);  
   
}
```

スプレッドシートに変更がある度に作動させたいため、`onEdit()`で作成。

同じスプレッドシート内に別シートがあり、そちらの変更を反映させたくない場合に最初のif文。ヘッダ行など特定の行の変更を反映させたくない場合に２つ目のif文を使用。

###【緯度経度から２点間の距離を計算】
```javascript
function googleGeosail(LatA,LngA,LatB,LngB){

  with(Math){

    var i = PI/180;
    var X = acos(sin(latA*i)*sin(latB*i)+cos(latA*i)*cos(latB*i)*cos(lngA*i-lngB*i))*6371.008;

  }
  return X;
}
```

引数として地点Ａの緯度,経度をlatA,lngA、地点Ｂの緯度,経度をlatB,lngBを与えると、２点間の距離[km]を返す。

距離計算コードの詳細についてはコチラ
[緯度経度からの距離計算 ヒュベニより航海算法の方が高精度説](https://qiita.com/YasumiYasumi/items/3ed8ea69b85dac055381)
[【ワンライナー】緯度経度から１行で距離計算](https://qiita.com/YasumiYasumi/items/9e8a6f185b00cba8c8bd)

###おしまい