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

概要

いわゆるスニペット集？

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

コードに差し込むスニペット７個

短かったり、他のコードと繋げたかったりで、function(){}内に差し込みたいコード。

【特定のシートをマルっと取得】

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シート名'); 
const data = sheet.getDataRange().getValues();

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

【実行時にダイアログで最終確認】

if(Browser.msgBox('最終確認','実行しますか？',Browser.Buttons.YES_NO)!='yes'){return;}

「はい」ならスクリプト続行。「いいえ」や「×」なら中断。

【配列の要素数を気にせずシートに書き出す】

var row = array.length;
var col = array[0].length;
sheet.getRange(1, 1, row, col).setValues(array);

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

【日付の取り扱い】

//本日
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

【シートのデータも画像もクリア】

//ヘッダ行を除いてセルをクリア
sheet.getRange(2, 1, lastrow, lastcol).clearContent(); 

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

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

【スプレッドシートをバックアップ】

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としてドライブに保存。

【スプレッドシートをエクセルに変換】

//エクセルを返す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(){}で囲いたいコード。

【カスタムメニュー】

function onOpen() { 

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

}

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

【シートの最終更新日時をセルに書き出す】

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文を使用。

【緯度経度から２点間の距離を計算】

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]を返す。

距離計算コードの詳細についてはコチラ
緯度経度からの距離計算ヒュベニより航海算法の方が高精度説
 【ワンライナー】緯度経度から１行で距離計算

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

概要