スプシのGASでよく使うスクリプトコードまとめ【コピペ用】 – Google Apps Script

2022年4月11日

プログラミングのイメージ画像

よく使うスプレッドシートGoogle Apps Scriptコードの備忘録として。

備忘録っていうかいつもとは別のPCで作業するときなんかにコピペして使う用。二行以上だったり長すぎると辞書登録できないですし。

随時修正したり追加すると思う。

定数・変数は私の好みでconstとかletとか適当に使い散らしています。

使い方によっては適さないものもあるかもなので、コピペする際は適宜修正してお使いください。

アクティブなスプシ・シートの取得

const ss = SpreadsheetApp.getActiveSpreadsheet(); // アクティブなスプレッドシートを取得
const sh = ss.getActiveSheet(); // アクティブなシートを取得

スプシのGASはこれがないとはじまらない、ってレベルですね。スクリプトの内容にもよるけど、ほぼ初期設定レベル。

定数表記、私はss、sh派。以後のコードはss、shで宣言済み前提です。

そのほかスプシ・シート取得

let ssByID = SpreadsheetApp.openById('id'); // 別のスプレッドシートをIDで取得

let shAll = ss.getSheets(); // すべてのシートを取得
let shName = ss.getSheetByName('シート名'); // シートの名前で取得
const shIndex = ss.getSheets()[index];  // シートのインデックス・並び順から（0が左から一番目）

// スプシ上の一番最後（右）にあるシートを取得
const shNum = ss.getNumSheets(); // シートNoを取得
const shLast = ss.getSheets()[shNum - 1]; // 最後のシートを取得

別スプシ取得はIDのほかにもURL取得できるopenByUrlメソッドもあるけど、記述が短くて済むIDの方が好み。

ログの出力

Logger.log(data);
console.log(data);

GAS画面でログを出力するコード。スクリプトのテスト・デバッグ時に欠かせませんよね。

どっちも基本的な働きは同じだけど、Logger.log(data);なら%sを使って書式を整えてのログ出力が可能。

const age = "17";
const gender = "女性";
const occupation = "美少女";

Logger.log("私は%s歳、性別は%s、職業は%sです", age, gender, occupation);

// 私は17歳、性別は女性、職業は美少女です

一方、Logger.log(data)形式ではトリガー実行スクリプトのログを確認できません。

その場合はconsole.log(data)を使用する必要があります。

私はログの書式を整えて確認することはほぼないので、制約の少ないconsole.log(data)を使うことがほとんどです。

特定行の最終列数、特定列の最終行数を取得

// 999行目の最終列が何列目か取得
let lastCol = sh.getRange(999, sh.getMaxColumns()).getNextDataCell(SpreadsheetApp.Direction.PREVIOUS).getColumn();
console.log(lastCol);

// 999列目の最終行が何行目か取得
let lastRow = sh.getRange(sh.getMaxRows(), 999).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
console.log(lastRow);

特定行・列中で存在する最後のの行・列から、Ctrlを押しながら前の行・列に向けて矢印キーを押した時と同じ動きをさせます（Macなら⌘Command + 矢印キー）。

近い働きをするメソッドでgetLastColumnやgetLastRowもあります。

ただ、getLastColumnとgetLastRowはスプシ全体で最も後ろにデータがある行・列を探すメソッド。特定の行や列だけを見るときは上記コードを使うが吉。

コードまでは書いてないけど、単純に取得したシートに対してメソッドをかければ取得できます。

行と列の縦横を入れ替える転置コード

const transpose = a=> a[0].map((_, c) => a.map(r => r[c])); // 行・列を入れ替える転置関数

const transArr = transpose(arr);

具体的な使い方は別記事にしていますので、詳しくはそちらで。

関連記事
二次元配列の縦横を入れ替え・転置する方法 スプレッドシート GAS

英数字＆記号の半角⇔全角を置き換える関数

// 半角 → 全角（半角英数字＋半角記号）
function z2h(str){
  return str.replace(/[！-～]/g, function(s){
    return String.fromCharCode(s.charCodeAt(0) - 0xFEE0);
  });
}

// 半角→全角（全角英数字＋全角記号）
function h2z(str){
  return str.replace(/[!-~]/g, function(s){
    return String.fromCharCode(s.charCodeAt(0) + 0xFEE0);
  });
}

上記は記号も含めて全角⇔半角を置き換えるコード。記号は置き換えないとか数字だけでいい場合などは正規表現をうまく調整すればOK。

カタカナの全角⇔半角置き換えもできるけど、あまり使わないので割愛。変換テーブルの記述が長くなるので使うときだけ用意した方がいいかと。

私にもスプレッドシートとかExcelで英数字を全角で入力する人に怒りを覚えていた時期がありました。特にECサイト関連だとCSV受け付けてもらえなくなるし。

ただ、DTP系だと逆にレギュレーションで決まってたりしてなかなか統一が難しいこともあるんですよね。

入力ミスだってあるし、人間が運用で努力するより、フェールセーフ的な考え方した方が建設的。めんどくさいけど。

繰り返し（ただのforループ構文）

// 回数指定
for (let i = 0; i < 10; i++) {
  // 処理内容←iの初期値0から9までの10回繰り返す
}

// 配列の長さ分だけ
let arr = なんか適当な配列;
let arrLength = arr.length;

for (let i = 0; i < arrLength; i++) {
  // 処理内容 ← 配列の長さだけ繰り返す
}

Google Apps ScriptとかJavascriptに限らずホントよくあるヤツだけど。

なお、配列の長さをfor構文の引数の中でlengthプロパティを使って直接配列の長さを確認するのはオススメしません。そうすると処理を繰り返す度に配列の長さを確認しちゃうため。

特に配列の長さが変わる場合、例えば配列中身を追加したり削除するスクリプトだと、長さが増減するためループ処理が予定した回数で終わらなくなる場合があります。

なので、上記コードのように変数に入れていったん数を確定させるのが無難。

メッセージ表示＆「キャンセル」で処理中止

function questionMsgBox(){
  let questionMsg = Browser.msgBox("ここにメッセージテキスト", Browser.Buttons.OK_CANCEL);

  if(questionMsg == 'cancel'){
    return;
  }

  Browser.msgBox("okが押されました");

メッセージボックスに設定したテキストで選択肢を表示、OKなら引き続き処理し、キャンセルなら処理を止めるコード。

スクリプトの安全装置としてもオススメ。人に使ってもらうときとか、誤って発火させるとマズいようなスクリプトとか。

上記はとりあえずcancelなら処理を中断するコードにしたけど、if内にokなら処理する内容を書くのでも可。お好きな方で。

GoogleスプレッドシートJavaScript,Google Apps Script