🎢

LookerStudioで可視化するためにデータを列から行に転置する処理をGASで自動化する（ディメンション作成）

2024/02/13に公開

GAS

経緯

このようにキーワード毎に行でまとまっているデータを

日毎にこのように配列したい

ECモールの検索順位の日毎のデータを折れ線グラフで表示がしたいが、取得できる日毎のCSVが行方向にデータがまとまっていて、このままではLookerStudio（他のBIツールでも）でディメンション毎に可視化ができない。

今回、ディメンションは固定されているものとして、ヘッダーとして２行目にキーワードを入力しています。

今回取得するのは順位のみ。ちなみにヘッダーが変動するものには適応していません。

では早速ですが解説していきます。

※データの取得は、前回同様Power AutomateでGoogle Driveに自動取得されています。

GASでGoogle Driveに取り込んだCSVファイルをスプレッドシートに自動転記する

要件

今回表示したいのは検索順位。商品毎にキーワードが設定してあり、それぞれに順位があります。1列に順位が取得されるので、こちらを列方向に展開していきます。

ファイルの取得

function Search_ranking() {
  // アクティブなスプレッドシートを取得
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  // データを追記する元のシートと、追記先のシートを取得
  const sourceSheet = spreadsheet.getSheetByName('データマート');
  const targetSheet = spreadsheet.getSheetByName('検索順位');

  // 元のデータを取得 (指定した列の2行目から取得)
  const sourceData = sourceSheet.getRange(2, 7, sourceSheet.getLastRow() - 1, 1).getValues();

  // 転記する列の指定
  const columnsToTranscribe = [7]; // ここでは「7」と入力されているが列のインデックスを入力してください

今回は生データを貯めておくデータマートも作成しています。そこから本日分だけ別のシートに転記して、さらに本日分から可視化用のシートに転記しています。

列を指定して検索順位を取得し、sourceDataに格納します。

const sourceData = sourceSheet.getRange(2, 7, sourceSheet.getLastRow() - 1, 1).getValues();

2, 7,と入力されていますが、任意の行、列を入力してください。

最後の変数 columnsToTranscribe は、将来的にコードが拡張されたり変更されたりする場合に、転記する対象の列を柔軟に変更できるようにするために使用されていますので、消さなくてもいいのかも。

ファイルの転記

// 日付を最終行に転記して追記
  const currentDate = new Date();
  const formattedDate = Utilities.formatDate(currentDate, "JST", "yyyy/MM/dd");

 // 日付を"JST"形式で取得
  const expandedData = transposeData(sourceData);
  expandedData.forEach(row => row.unshift(formattedDate));

  // 追記先のシートにデータを追記
  const lastRow = targetSheet.getLastRow() + 1; // 現在の最終行 + 1
  targetSheet.getRange(lastRow, 1, expandedData.length, expandedData[0].length).setValues(expandedData);
}

この部分のコードは、取得したデータに日付を付与してシートに追記することです。これにより、日毎にデータを蓄積していくような処理が行われ、BIツールで可視化できるようになります。

上から順に解説していきます。

const lastRowは最終行に追記する部分。

その後日付を列に追記する部分のコードに繋がっていきます。

データの転置

// データを列方向に転置する関数
function transposeData(data) {
  return data[0].map(function(_, colIndex) {
    return data.map(function(row) {
      return row[colIndex];
    });
  });
}

colとrowを入れ替えると逆の処理が可能です。col→row　colIndex→rowIndex

ここで重要なのはmap関数です。map関数は、配列の要素を個別に処理する処理を簡単に記述できる便利な関数です。
GASで配列を処理する際は、map関数の利用を利用するとコードの記述が簡素化するようです。
今回は新しい配列として生成する必要があるため、map関数を使用しています。

コード全文

function Search_ranking() {
  // アクティブなスプレッドシートを取得
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  // データを追記する元のシートと、追記先のシートを取得
  const sourceSheet = spreadsheet.getSheetByName('データマート');
  const targetSheet = spreadsheet.getSheetByName('検索順位');

  // 元のデータを取得 (指定した列の2行目から取得)
  const sourceData = sourceSheet.getRange(2, 7, sourceSheet.getLastRow() - 1, 1).getValues();

  // 転記する列の指定
  const columnsToTranscribe = [7]; // ここでは「7」と入力されているが列のインデックスを入力してください

  // 日付を"JST"形式で取得
  const currentDate = new Date();
  const formattedDate = Utilities.formatDate(currentDate, "JST", "yyyy/MM/dd");

  // データに日付を追加
  const expandedData = transposeData(sourceData);
  expandedData.forEach(row => row.unshift(formattedDate));

  // 追記先のシートにデータを追記
  const lastRow = targetSheet.getLastRow() + 1; // 現在の最終行 + 1
  targetSheet.getRange(lastRow, 1, expandedData.length, expandedData[0].length).setValues(expandedData);
}

// データを列方向に転置する関数
function transposeData(data) {
  return data[0].map(function(_, colIndex) {
    return data.map(function(row) {
      return row[colIndex];
    });
  });
}

まとめ

どのようなデータでも配列を操作することで、BIツールで可視化できるようになれるのでは、とワクワクしています。
CRMツールで出力されるデータも、行でまとまって出力されることがあり、このような処理ができれば手作業でのデータクレンジングから解放されます。

今回の処理はヘッダーが固定されていましたが、そうでない場合は新たな課題が発生するかもしれません。その際は、SQLを用いてデータ処理を行う必要があるかもしれません。
引き続き学習を継続し、柔軟に様々なデータ形式に対応できるスキルを身につけていく必要がありそうです。

最後までご覧いただき、ありがとうございました。