はじめてGoogle Apps Script(以下GAS)を使ってみたので、せっかくなのでメモ。
##やりたいこと
スプレッドシートで範囲選択したセルの1行ごとに、例えば3行づついっきに新規に行追加したい。
###今までのやりかた
１行づつコンテキストメニューから地道に挿入…
###これからはこうしたい
これを…
挿入したい行の範囲を指定して…
いっきにこうしたい！
ちなみにMacの場合、control+option+I、Wでも現在行の下に行を挿入できます。Mac以外はこちらで。
ということで、シンプルなGASスクリプトをつくりました。この記事最後には全コードを貼り付けてます。
##やれること
- 選択した範囲内の１行ごとに挿入できる。
- 挿入する行数を入力できる。(ついでにデフォルト値も)
##やったこと
やったことは主に下記の5つです。
- 独自メニューを追加する(スクリプトの起動ボタンとして利用)
- 挿入する行範囲と位置を取得する
- ダイアログを使って挿入する行数を取得する
- 取得した情報を元に新たに行を挿入する
- スクリプトをファイルオープン時に自動起動（トリガー）する
コーディングはスクリプトエディターで行います。メニューの【ツール > スクリプトエディタ】から開けます。
こちらの記事が参考になりました。
【Google Apps Script入門】スクリプトエディタの基本操作 | UX MILK
###1. カスタムメニューを追加する
GASに用意されているSpreadsheetAppオブジェクトのgetUiメソッドで、Uiオブジェクトを取得します。これはスプレッドシートのユーザーインターフェース機能を司っています。
/**
* メニュー、ダイアログ、サイドバーなどのユーザーインターフェース機能
* @type {Ui}
* {@link https://developers.google.com/apps-script/reference/base/ui }
*/
var ui = SpreadsheetApp.getUi();
UiオブジェクトのcreateMenuメソッドでは、カスタムメニュー用のMenuオブジェクトのビルダーが返ります。
MenuオブジェクトのaddItemメソッドで、カスタムメニューに新しいメニュー項目を追加。
第二引数はこのメニュー項目がクリックされたときに実行したい関数名です。
最後にaddToUiメソッドで、スプレッドシートのユーザーインターフェースに作成したカスタムメニューを追加。
/**
* この機能の設定値
* @type {object}
* @prop {string} LABEL_MEMU - メニュー表示名
* @prop {string} LABEL_SUB_MEMU - サブメニューの表示名
* @prop {number} DEFAULT_NUM_ROW - デフォルトの追加行数
*/
var config = {
LABEL_MEMU : '追加機能',
LABEL_SUB_MEMU : '複数行追加',
DEFAULT_NUM_ROW: 3
};
/**
* 実行
*/
function main() {
ui
.createMenu(config.LABEL_MEMU) // カスタムメニュー作成
.addItem(config.LABEL_SUB_MEMU, 'insertRows') // メニュー項目を追加
.addToUi(); // カスタムメニューをUIに追加
}
追加したカスタムメニュー【複数行追加 > 追加機能】はこんな感じ。挿入したい行を選択した後にこの【追加機能】をクリックすることで挿入。
###2. 挿入する行範囲と位置を取得する
SpreadsheetAppオブジェクトのgetActiveSheetメソッドで、現在選択されているSheetオブジェクトを取得。
このSheetオブジェクトから、セルの情報を取得したりセルを操作したりするRangeオブジェクトを取得。
/**
* 現在アクティブなスプレッドシートのシート
* @type {Sheet}
* {@link https://developers.google.com/apps-script/reference/spreadsheet/sheet }
*/
var st = SpreadsheetApp.getActiveSheet();
getActiveCellメソッドでRangeオブジェクトを取得して、getRowメソッドで現在指定されているセルの行番号を取得。
getActiveRangeメソッドでRangeオブジェクトで取得して、getNumRowsメソッドで現在指定されているセルの範囲の行数を取得。
insertRows関数はカスタムメニューの【複数行追加】がクリックされたとき実行される処理。
/**
* 行を挿入する
*/
function insertRows() {
/**
* 現在のハイライト行番号
* @type {numner}
*/
var targetRow = st.getActiveCell().getRow();
/**
* 挿入対象の範囲行数(連続した複数行選択のみ対応)
* @type {numner}
*/
var targetNumRows = st.getActiveRange().getNumRows();
// ダイアログを使って挿入する行数を取得する処理
// 取得した情報を元に新たに行を挿入する
// 処理が続く
...
}
###3. ダイアログを使って挿入する行数を取得する
Uiオブジェクトのpromptメソッドを使って、挿入する行数を取得するためのテキスト入力エリアを持つダイアログを表示。
ダイアログからは入力値などを含んだPromptResponseオブジェクトが返ります。
/**
* 挿入する行数を取得する
* @return {number} 挿入する行数またはエラーの場合は-1
*/
function getInsertNumRows() {
/**
* ダイアログ入力のレスポンス
* @type {PromptResponse}
* {@link https://developers.google.com/apps-script/reference/base/prompt-response}
*/
var response = ui.prompt('挿入する行数(未入力でデフォルトの' + config.DEFAULT_NUM_ROW + '行が挿入されます)');
...
}
UiオブジェクトのButtonプロパティは、ダイアログに関連するボタンの種類を示すプロパティを持っています。
そしてPromptResponseオブジェクトのgetSelectedButtonメソッドは、ダイアログでどのボタンが押されたかの情報を持っています。これを照らし合わせて【OK】ボタン以外が押された場合は処理のキャンセルとみなしています。
【OK】ボタンが押されたけど、入力値が空白の場合はデフォルト値の3を利用。
PromptResponseオブジェクトのgetResponseTextメソッドでダイアログの入力値を取得。
数値として有効な値の場合はこれを挿入する行数として利用。
/**
* 挿入する行数を取得する
* @return {number} 挿入する行数またはエラーの場合は-1
*/
function getInsertNumRows() {
...
/**
* ダイアログ入力値
* @type {mix}
*/
var insertRows;
// ダイアログでOKボタンが押されたかどうか
if (response.getSelectedButton() !== ui.Button.OK) { return -1; }
// 入力値がない
if ('' === response.getResponseText().trim()) { return config.DEFAULT_NUM_ROW; }
// 小数点以下切り捨て
var insertRows = parseInt(response.getResponseText(), 10);
// 入力値が数値かどうか
if (!isFinite(insertRows)) { return -1; }
// 値を絶対値にする
return Math.abs(response.getResponseText());
}
この関数から返される値が-1の場合は処理キャンセル。
/**
* 行を挿入する
*/
function insertRows() {
...
/**
* 挿入する行数
* @type {numner}
*/
var insertNumRows = getInsertNumRows();
// エラー時
if (0 > insertNumRows) {
ui.alert(config.LABEL_SUB_MEMU + '処理中止!');
return;
}
// 行を追加する処理
...
}
ダイアログはこんな感じ
###4. 取得した情報を元に新たに行を挿入する
カスタムメニューの【複数行追加】がクリックされたとき、このinsertRows関数が実行されます。
SheetオブジェクトのinsertRowsAfterメソッドで新規行を挿入。複数行が選択されている場合はその行数分だけ繰り返します。
現在ハイライトされているセルの行番号を起点としてその直後の行に追加。
複数行挿入する場合は行の挿入処理中に逐一行数が増えていくので、ハイライトされているセルの行番号を起点として挿入開始位置を調整。
/**
* 行を挿入する
*/
function insertRows() {
...
// targetNumRows 挿入対象の範囲行数
// targetRow 現在のハイライト行番号
// insertNumRows 挿入する行数
// 複数行に追加
for (var i = 0; i < targetNumRows; i++) {
// 行を挿入する
st.insertRowsAfter(targetRow + (i * insertNumRows) + i, insertNumRows);
}
}
###5. スクリプトをファイルオープン時に自動起動（トリガー）する
これでスクリプトエディタで、つくったmain関数を実行ボタンで実行することで、カスタムメニューがメニューに追加され機能が利用できます。でもこのままではスプレッドシートファイルを開くごとに毎回機能を実行しなおさなければなりません。
そこでスプレッドシートファイルを開いたら自動的に起動するよう設定します。
onOpenはGASが提供するファイルオープン時に自動的に実行される関数です。
/**
* スプレッドシートファイルオープン時に実行する処理
*/
function onOpen() {
// スプレッドシートファイルオープン時にカスタムメニュー追加
main();
}
以上、になります。
おわり
##スクリプトの全コード
/* ========================================
現在ハイライトされているセルの下に新しい行を挿入する
======================================== */
/* --------------------
設定
-------------------- */
/**
* この機能の設定値
* @type {object}
* @prop {string} LABEL_MEMU - メニュー表示名
* @prop {string} LABEL_SUB_MEMU - サブメニューの表示名
* @prop {number} DEFAULT_NUM_ROW - デフォルトの追加行数
*/
var config = {
LABEL_MEMU : '追加機能',
LABEL_SUB_MEMU : '複数行追加',
DEFAULT_NUM_ROW: 3
};
/* --------------------
変数
-------------------- */
/**
* 現在アクティブなスプレッドシートのシート
* @type {Sheet}
* {@link https://developers.google.com/apps-script/reference/spreadsheet/sheet }
*/
var st = SpreadsheetApp.getActiveSheet();
/**
* メニュー、ダイアログ、サイドバーなどのユーザーインターフェース機能
* @type {Ui}
* {@link https://developers.google.com/apps-script/reference/base/ui }
*/
var ui = SpreadsheetApp.getUi();
/* --------------------
関数
-------------------- */
/**
* 実行
*/
function main() {
ui
.createMenu(config.LABEL_MEMU) // カスタムメニュー作成
.addItem(config.LABEL_SUB_MEMU, 'insertRows') // メニュー項目を追加
.addToUi(); // カスタムメニューをUIに追加
}
/**
* 行を挿入する
*/
function insertRows() {
/**
* 現在のハイライト行番号
* @type {numner}
*/
var targetRow = st.getActiveCell().getRow();
/**
* 挿入対象の範囲行数(連続した複数行選択のみ対応)
* @type {numner}
*/
var targetNumRows = st.getActiveRange().getNumRows();
/**
* 挿入する行数
* @type {numner}
*/
var insertNumRows = getInsertNumRows();
// エラー時
if (0 > insertNumRows) {
ui.alert(config.LABEL_SUB_MEMU + '処理中止!');
return;
}
// targetNumRows 挿入対象の範囲行数
// targetRow 現在のハイライト行番号
// insertNumRows 挿入する行数
// 複数行に追加
for (var i = 0; i < targetNumRows; i++) {
// 行を挿入する
st.insertRowsAfter(targetRow + (i * insertNumRows) + i, insertNumRows);
}
}
/**
* 挿入する行数を取得する
* @return {number} 挿入する行数またはエラーの場合は-1
*/
function getInsertNumRows() {
/**
* ダイアログ入力のレスポンス
* @type {PromptResponse}
* {@link https://developers.google.com/apps-script/reference/base/prompt-response}
*/
var response = ui.prompt('挿入する行数(未入力でデフォルトの' + config.DEFAULT_NUM_ROW + '行が挿入されます)');
/**
* ダイアログ入力値
* @type {mix}
*/
var insertRows;
// ダイアログでOKボタンが押されたかどうか
if (response.getSelectedButton() !== ui.Button.OK) { return -1; }
// 入力値がない
if ('' === response.getResponseText().trim()) { return config.DEFAULT_NUM_ROW; }
// 小数点以下切り捨て
var insertRows = parseInt(response.getResponseText(), 10);
// 入力値が数値かどうか
if (!isFinite(insertRows)) { return -1; }
// 値を絶対値にする
return Math.abs(response.getResponseText());
}
/**
* スプレッドシートファイルオープン時に実行する処理
*/
function onOpen() {
// スプレッドシートファイルオープン時にカスタムメニュー追加
main();
}