GAS(Google Apps Script)でデータを縦方向（列方向）に扱う

はじめに

この記事はLinkbal Advent Calendar 2018の19日目の記事です。

GAS歴1週間ほどの水野（キンチキ）です。
今回は、GAS(Google Apps Script)でスプレッドシートに関するコードを書いたことがあるなら一度は感じるであろう、縦方向（列方向）の操作が上手くできない問題について、私なりの解決策を書きます。

APIの呼び出し回数を少なくするために getValues() を使ったが、取得した二次元配列を縦方向に扱えずに困っている、、、そんな皆さんは読んでみてください。

GASでスプレッドシートを縦方向（列方向）に扱いたい

私が官報ブログの決算の情報を個人的にメモっているスプレッドシートを例にします。

このシートはSlackにRSSで飛んでくる情報を見て手入力しています。
データには間違いがあるかもしれませんのでご了承ください。
※その作業を自動化しろっていうツッコミはなしでお願いします。いずれやります。

このスプレッドシートは以下のようにデータが記載してあります。

• A列 日付
• B列 社名
• C列 売上
• D列 営業利益
• E列 経常利益
• F列 営業利益率
• G列 経常利益率
• H列 決算時期（どの四半期か、通期か）

ここで、通期決算の行（H列に「通期」が含まれている行）だけ抽出するとします。

純粋に getDataRange().getValues() とすると、以下のような二次元配列が返ってきて、H列に「通期」が含まれているか判定するのが非常にめんどくさいです。

めんどくさい理由は二次元配列だからです。
ということで、二次元配列を一次元配列にすればよいです。

二次元配列を一次元配列に変換する

今回必要なのは、H列に「通期」が含まれている行だけです。
つまり、H列に「通期」が含まれている行番号の一覧さえ取得できればOKですね。

書いてみましょう。

以下コード一覧です。

function myFunction() {
  // シート取得
  const sheet = getSheet('1m6WMSOLw4oBnVFLGz252v-Al6K0YAiLruiA3zBskUwU', 'シート1');
  const sheetData = sheet.getDataRange().getValues();

  // 対象データが存在する行番号の配列を取得
  const fullYearSettlementIndexes = getFullYearSettlementIndexes(sheetData);

  // 対象データが存在する行をログに出力
  showFullYearSettlement(sheetData, fullYearSettlementIndexes);
}

function getSheet(ssId, targetSheetName) {
  ss = SpreadsheetApp.openById(ssId);
  if (typeof targetSheetName === 'string' && targetSheetName !== '') {
    return ss.getSheetByName(targetSheetName);
  }
  return ss.getSheets()[targetSheetName];
}

function getFullYearSettlementIndexes(data) {
  const settlementColumnIndex = 7; // H列
  const settlementRows = convertTwoDimensionToOneDimension(data, settlementColumnIndex); // H列のみの一次元配列を取得
  const fullYearSettlementIndexes = []; // 行番号の格納用配列

  // H列に '通期' を含む行番号を配列に格納
  settlementRows.forEach(function(value, index) {
    if (value.indexOf('通期') != -1) {
      fullYearSettlementIndexes.push(index);
    }
  });
  return fullYearSettlementIndexes;
}

function showFullYearSettlement(data, indexes) {
  // const showDataNumber = [1, 2, 3, 4, 5, 6, 7]; 配列をループさせるのもよし
  const startRow = 1; // 開始列
  const endRow = 7;   // 終了列

  // 通期の行ひとつずつに対して、各列の値を出力
  // 本来は1行目（ヘッダー行）を上手いこと処理してループするのがいいと思いますが、手抜きしました
  indexes.forEach(function(i) { // iは行番号
    for (var j = startRow; j < endRow; j++) { // jは列番号
      Logger.log(data[i][j]); // i行目のj列目の値表示
    }
  });
}

// 第一引数の二次元配列を第二引数のインデックスの値の一次元配列に変換する関数
function convertTwoDimensionToOneDimension(twoDimensionalArray, targetIndex) {
  oneDimensionalArray = []
  twoDimensionalArray.forEach(function(value) {
    oneDimensionalArray.push(value[targetIndex]);
  });
  return oneDimensionalArray;
}

こちらを実行すると、以下のように通期を含む行のデータだけ出力できています。
これは1セルずつ表示するという雑な出力なので、キレイに出力されたい方は showFullYearSettlement をいじってください。

説明

細かい処理はコメントで書いてあるのでそちらを見ていただくとして、注目していただきたいのは convertTwoDimensionToOneDimension という関数です。
この関数がこのプログラムの肝です。

この関数は、第一引数で渡した二次元配列から、第二引数で渡したインデックスの値を抽出した一次元配列＝縦方向（列方向）の一次元配列に変換します。

例えば以下のような結果になります。

const sampleArray = [
  ['hoge1', 'hoge2', 'hoge3'],
  ['foo1', 'foo2', 'foo3'],
  ['bar1', 'bar2', 'bar3']
]
convertTwoDimensionToOneDimension(sampleArray, 1).forEach((v) => console.log(v));
/*
hoge2
foo2
bar2
*/

これを使うことで getValues() で取得した値を縦方向に抽出することができるため、特定の列の値を処理することができます。

おわりに

getValues() で取得した二次元配列は、一次元配列に変換すると縦方向の扱いを簡単にできるというお話でした。

Underscore for GASなるものがありますが、私は少し試した限り convertTwoDimensionToOneDimension 関数を実装して使った方がよいと感じました。
それに、2018年12月20日時点で最終commitは2015年4月14日かつスターが35個しかないライブラリを使うのもどうかと思います。

使いたい方は以下の記事など参考になさるとよいかと。
Google Apps Scriptで二次元配列の行と列を入れ替える方法とその革命的な効果

GASはスプレッドシートだけでなく、GmailやSlackと合わせて使うことも可能な優れものです。
ビジネスサイドとの親和性でいったらおそらく最強の言語なので、有効に使って仕事を楽にしましょう！