GASでSpreadsheetを操作する自分的ベストプラクティス

ここ１月ほど、google spreadsheetを用いた案件管理シートを効率化するために、google app scriptを使って処理の自動化を行いました。その中で、google spreadsheetに対してgoogle app scriptを書く際の基本的な処理コードを、気をつけるポイント・高速化のコツと共にまとめます。

基本の操作

シートの取得

シートのデータを取得する場合、最初はシートを取得するのが基本かと思います。

//シートの名前で呼ぶ場合
var sheet = SpreadsheetApp.getActive().getSheetByName('シート1');

//今開いているシートを呼ぶ場合
var sheet = SpreadsheetApp.getActiveSheet();

SpreadSheetクラスとSheetクラスがありますが、SpreadSheetがシートファイル全体(エクセルでいうところの.xlsxファイル)を指し、Sheetクラスがシートファイル内の各シートを指します。

２回以上呼ぶならメモ化で高速化

複雑な処理を書き始めると、上記コードを何度も呼ぶことになりますが、２回以上呼ぶようであれば、メモ化を行うことでAPIへのアクセス回数を減らしましょう。

google spreadsheetのスクリプトで高速化の基本は、APIアクセスを減らすことです。

// 特定のシートのメモ化を行う関数
function getMainSheet() {
  if (getMainSheet.memoSheet) { return getMainSheet.memoSheet; }

  getMainSheet.memoSheet = SpreadsheetApp.getActive().getSheetByName('メインシート');
  return getMainSheet.memoSheet;
}

// 最初に呼ばれる関数
function firstCalledFunc() {
  var sheet = getMainSheet(); //APIを叩いてシートを取得

  // some code ....
}

// 次に呼ばれる関数
function secondCalledFunc() {
  var sheet = getMainSheet(); //メモ化されたシートを返す

  // some code ....
}

データの取得

データを取得する場合は、取得したいデータの範囲をRangeオブジェクトを用いて指定したり、全てのデータを取得したりできます。

//シート全体のデータを取得する場合
var data = sheet.getDataRange().getValues();
// dataにはシートのデータが２次元配列で入る

//特定の行(1行目)のデータを取得する場合
var rowIndex = 1;
var colStartIndex = 1;
var rowNum = 1;
var data = sheet.getRange(rowIndex, colStartIndex, rowNum, sheet.getLastColumn()).getValues();

//または、以下も使える 
// sheet.getSheetValues(rowIndex, colStartIndex, rowNum, sheet.getLastColumn());

シート全体のデータ探索処理は、JS側でチェックするほうが速い

案件管理などの場合、完了しているタスクに対して色を塗る等の処理が必要となります。このような、シートの各行の値に応じて何か処理をしたい場合は、各行のデータをgetRangeで取得し各行の状態に応じて色を塗ったりしてもよいですが、APIを多く叩くことになるため処理時間が長くなります。

先にシート全体のデータを取得し、js側で各行のデータをループさせ、処理をする行の位置をマークした後、マークした行群に対して色を塗るAPIを叩く処理のほうがよいでしょう。

var statusColumnIndex = 2; //ステータスを保持する列のインデックス
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
var finishedRowIndexes = []; //ステータスが完了済みの行数を保持する

for (var i = 0; i < data.length; i++) {
  if (data[i][statusColumnIndex] === '完了') {
    finishedRowIndexes.push(i + 1);
  }
}

for (var i = 0; i < finishedRowIndexes.length; i++) {
  // 完了済みの行に色を塗る
  sheet.getRange(finishedRowIndexes[i], 1, 1, sheet.getLastColumn()).setBackgroundColor('#eeeeee');
}

色塗り

シートを編集していると、色を塗る処理なども使われたりするかと思います。RangeクラスのsetBackgroundColorでrangeで指定された範囲のセルを塗れます。

var sheet = SpreadsheetApp.getActiveSheet();
//1行目だけに色を塗る
sheet.getRange(1, 1, 1, sheet.getLastColumn()).setBackgroundColor('#eeeeee');
//2行目から5行目まで色を塗る
sheet.getRange(2, 1, 3, sheet.getLastColumn()).setBackgroundColor('#eeeeee');

同じ色が近接セルにあるならまとめて塗ると、APIを叩く回数を減らせます。
ただし、1行飛ばし、みたいなRange指定はできません。

また、色塗りに関連して、罫線の太さを設定できるかと思ったのですが、太い罫線は現時点では引けないようです。

シート間の行移動(コピー)・同期

今回、案件管理のやり方として、以下のようなシート配置で行いました。

1. マスターシートに案件の全情報を置く。
2. 案件は、マスターシートに１案件が１行として保存されており、案件ID, 担当者、ステータスがある。
3. 各ユーザーのシートにユーザーが担当する案件を、マスターシートからコピーする。

このような方法にしたため、マスターシートから、各ユーザーのシートに案件行をコピーする必要がありました。この場合は空白行を追加した後、その領域にデータをsetValuesすることで実現できます。

var userSheet = SpreadsheetApp.getActive().getSheetByName('担当:ryan5500');
var copyData = [[] ...]; //ユーザーシートにコピーするデータ

// 2行目の前に空白行を挿入
userSheet.insertRowsBefore(2, copyData.length);

// 挿入した空白行領域にデータをコピー
var range = userSheet.getRange(2, 1, copyData.length, userSheet.getLastColumn());
range.setValues(copyData);

シート間の情報の同期

各ユーザーのシートで、例えば案件のステータスを変更したことを、マスターシートにも反映したい、という場合があります。その場合は、セル編集時のトリガで呼び出される関数onEditの引数で、シートと変更したセルの位置を受けとれるので、それに応じて処理をするような形で実現できます。

function onEdit(event) {
  var idColumnIndex = 0; //シート内で案件IDがセットされている列のインデックス
  var editedRowIndex = event.range.getRow();        //編集された行のインデックス
  var sheetName      = event.source.getSheetName(); //編集されたシートの名前

  //編集されたシートから、編集された行の案件データを取得する
  var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  var editedRowData = sheet.getSheetValues(editedRowIndex, 1, 1, sheet.getLastColumn());

  //マスターシートのデータを取得する
  var masterSheet = SpreadsheetApp.getActive().getSheetByName('マスターシート');
  var masterSheetData = masterSheet.getSheetValues();

  for (var i = 0; i < masterSheetData.length; i++) {
    //マスターシートにおける、編集された案件の行を案件IDで探す
    if (masterSheetData[i][idColumnIndex] === editedRowData[0][idColumnIndex]) {
      //マスターシートのその行に対して編集された行の案件データを貼付ける
      var range = masterSheet.getRange(i + 1, 1, 1, masterSheet.getLastColumn());
      range.setValues(editedRowData);
      break;
    }
  }
}

※ マスターシートでの変更もユーザーシートに反映されるべきであるため、このサンプルは必ずしも正しくありません。

カスタムメニュー

Spreadsheetのgoogle app scriptでは、シートのメニューUI内にカスタムメニューを配置できます。

私の場合は、案件の追加などをマスターシートに対して処理した後に、各ユーザーのシートに案件を分配する処理を実行できるリンクにしています。

このような、ユーザーの特定の行動の後に実施したいタスクについては、カスタムメニューに入れると良いと思います。

// シートが表示された際に呼び出される関数
function onOpen() {
    var ss    = SpreadsheetApp.getActive();
    var items = [{name: '案件を各担当者シートにコピー', functionName: 'customMenuMigrateCaseToUserSheet'}];
    ss.addMenu('案件管理メニュー', items);
}

// カスタムメニューの「案件を各担当者シートにコピー」をクリックされると呼ばれる関数
function customMenuMigrateCaseToUserSheet() {
  Logger.log('here');
}

基本の操作は以上です。次に、避けて通れないエディタやデバッグなどの開発スタイルについてです。

開発スタイルについて

スクリプトエディタ

spreadsheetに対応づくような、container-basedと言われるgasの場合、ソースコードのexport/import機能が(現時点では)使えません。そのため、お気に入りのエディタで編集したコードは、コピーした上でspreadsheetのスクリプトエディタを開き、貼付けることになります。

ただし、gasのスクリプトエディタは、基本的には2スペースインデントです。もとのコードが４スペースインデントだと、貼付けたあとちょっと手直しする場合に残念な気持ちになります。なので、お気に入りのエディタで開発される場合も、2スペースインデントをお勧めします。

デバッグ

onOpen等のトリガのコールバック関数のデバッグは、引数を取らないため、再生ボタンを選べば簡単に実行できます。
しかし、onEdit等の、コールバック関数に渡す引数に、特定の値を渡すようなトリガの場合、デバッグ機能を有効活用できません。onEdit等のトリガのコールバック関数内は、できるだけサブの関数に分けて処理を進めるのがよいかと思います。

終わりに

いかがでしたでしょうか？

１月ほどしか触っていないので、間違いのご指摘、もっとこうした方が速い！というご意見歓迎です！
よろしくお願いします。