【GAS】スプレッドシートの行と列を入れ替える処理を自動化しよう！

以前の記事でPowerQueryを使って
エクセルの行と列の入れ替えを
自動化する方法を紹介しました！

【中級編】【PowerQuery】エクセルで行と列を簡単に入れ替える方法！

今回はGASを使って
スプレッドシートのデータの行と列を
自動的に入れ替える処理を作ってみます！

行と列を入れ替える考え方

かじむー

まずはどういう考え方をすれば良いのか？整理して行こう！

行と列を入れ替えるとは？

例えばこんな表があったとします！

福岡支店の10月1日～7日までの
いちご・バナナ・みかん・リンゴ・すいかの売上です！

では果物の名前を「商品名」列
果物の売上を「売上」列として
縦に並び替えてみます！

ここで10月1日の売上(青枠)に注目して見ると分かり易いです！

はい！こんな感じです！

商品名(名前)⇔売上(値)
という感じで名前と値がセットになっています！

エクセルだと貼付けの種類に
行列を入れ替える方法がありますよね！

でもこの処理をプログラムで書くとき
どんな手順を踏めばこの形になるのか！？

一緒に整理して行きましょう！

行と列を入れ替えるロジック

3つの領域に分ける

まずは3つの領域に分けます！

①商品名・・・名前(属性とも言う)
②売上・・・名前に対する値
③固定データ・・・「名前」と「値」以外の部分

ではステップを踏みながら
縦に並び替えていきます！

「名前」を縦に並べる

固定データに注目！
固定データを下方向に1行ずつ
iRow = 0, 1, 2, … と数えていくとします。

まずは10月1日( iRow = 0 )だけについて考えていきます！

①商品名の数だけ固定データを作成

この場合、商品名が5つあるので
福岡・10月1日(iRow=0)のデータを 5行作ります！

②商品名を縦に並べる

次に商品名を5つ縦に並べます！

「値」を縦に並べる

そして最後に
福岡・10月1日(iRow=0) に対する売上を
縦に並べればOKです！

その後は iRow = 1, 2, 3, … で
同じことを繰り返せば良いのです！

例えば iRow = 1 ( 福岡・10月2日 ) の場合は

こんな感じですね！

行と列を入れ替えるGASを書いてみよう！

かじむー

それではこの考え方でGASを書いてみよう！

メイン処理を書いてみよう！

まずは定数を整理します！

●赤枠：データ開始位置
●青枠：固定データ終了列

次にメイン関数を書いていきます！

まず「福岡支店」シートを取得した後
getTransData関数(※後ほど自作する関数です)にシート情報を渡して
行列を入れ替えたデータを取得します。
データ形式は2次元配列になります。

その後データ(aryPasteData)をシートに出力します！

それでは行列を入れ替える転置処理を行う
getTransData関数を作ってみましょう！

転置処理を書いてみよう！

6行目

function getTransData(sheet){

「福岡支店」シートの情報を(sheet)を取得します！

9行目

  // 転置データ格納用の配列
  let aryData = [];

最終的にシートに出力する2次元配列データを
格納する配列(aryData)を準備します！

11～20行目

   // データの最終行を取得
  let lastRow = sheet.getLastRow();
  // データの行数を算出(空白行+列名行を差引く)
  let numRow = lastRow - 2
  // データの最終列を取得
  let lastClm = sheet.getLastColumn();
  // 固定データの列数を算出(空白列を差引く)
  let numConstClm = CONST_END_CLM - 1;
  // 転置データの列数を算出(固定データ終了列を差引く)
  let numTransClm = lastClm - CONST_END_CLM;

ここではデータの位置(行・列の番号)を算出しています！
これらの変数を使って
序盤に説明した「3つの領域に分ける」を行い
3つのデータを取得します！

22～27行目

  // 固定データを取得(支店・日付)(2次元配列)
  let aryConstData = sheet.getRange(START_ROW, START_CLM, numRow, numConstClm).getValues();
  // 転置データを取得(売上)(2次元配列)
  let aryTransData = sheet.getRange(START_ROW, CONST_END_CLM + 1, numRow, numTransClm).getValues();
  // 転置列名を取得(いちご・バナナ・みかん・リンゴ・すいか)(1次元配列)
  let aryTransName = sheet.getRange(START_ROW - 1, CONST_END_CLM + 1, 1, numTransClm).getValues()[0];

ここでそれぞれ3つのデータを取得しています！

●固定データ：aryConstData
●売上データ：aryTransData
●商品名　　：aryTransName

29～39行目

  // 転置
  for(var iRow=0; iRow<numRow; iRow++){
    for(var iClm=0; iClm<numTransClm; iClm++){
      // 商品名
      let name = aryTransName[iClm];
      // 売上
      let value = aryTransData[iRow][iClm];
      // 配列に格納([支店, 日付, 商品名, 売上])
      aryData.push([aryConstData[iRow][0], aryConstData[iRow][1], name, value]);
    }
  }

ここで行列を入れ替えて
「aryData」にデータをセットしています！

中身を見てみると
これは2重ループになっています！
何かが2つカウントされていってます！

1つ目は行方向にカウントする iRow
2つ目は列方向にカウントする iClm

x軸・y軸みたいな感じですね！
こうすればデータの位置を特定できますよね！

例えば iRow = 0 ( 福岡・2022/10/01 ) に対して
商品名と売上を縦に並び替えたデータを作る場合は
記事前半で説明したロジック通り作れば良いですね！

まず1行目の[ 福岡, 2022/10/01, いちご, 2436 ]を作る場合
2重ループの1回目( iRow = 0, iClm = 0 )で以下の様になります！

①「福岡」= 固定データ[iRow][0] = aryConstData[iRow][0]
②「2022/10/01」= 固定データ[iRow][1] = aryConstData[iRow][1]
③「いちご」(name) = 商品名[iClm] = aryTransName[iClm]
④「2436」(value) = 売上データ[iRow][iClm] = aryTransData[iRow][iClm]