Google Apps Script（GAS）で業務効率化「スプレッドシートの判定結果を集計する」

今回は、Google Workspaceを運用されている管理者の方なら一度は聞いたことがある、または既に活用している、様々なGoogle サービスと連携させることが可能であり業務効率化にはかかせないGoogle Apps Script（以下、GAS）について取り上げたいと思います。

概要
スプレッドシートの判定結果を集計する
さいごに
参考リンク

概要

GASとは、以下Googleのリンクから引用しますと「Google Workspace の統合、自動化、拡張のためのビジネスソリューションをすばやく簡単に構築するための唯一のローコードプラットフォームです。」と書かれています。詳細は以下のリンクを参照ください。

Apps Script

先日、とある友人よりExcelの関数を使って手作業でデータの判定や結果の集計を実施しており、膨大な時間を要しているため何とかしたいとご相談を受けました。

グループウェアとしてGoogle Workspaceを使用しているとのことでしたので、GASを使えば自動化が実現できるのではと思い、業務効率化の支援に取り組ませて頂きました。

本ブログでは、自動化を実現させたプログラムの中から1つを取り上げてご紹介させて頂きます。

スプレッドシートの判定結果を集計する

今回、GASで自動化した内容について記載します。ざっくり言いますとスプレッドシートのデータを取得⇒集計を行い、新しいシートへ結果を出力するといった処理を実現しました。

元データは以下画像のイメージです。事業所ごとに複数の結果について○×判定が記載されたデータがあり、事業所の列には同じ支社の行が複数存在しています。

こちらのデータについて、支社ごとにまとめて、結果1、結果2、結果3に×の値がいくつあるのかを集計します。そして、集計した結果は新しいシートを作成して出力します。出力結果は以下画像のイメージです。

上記処理を行うサンプルコードは以下の通りです。

//データ集計実行メニュー作成
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  const menu = ui.createMenu("データ集計メニュー");
  menu.addItem("データ集計実行", "aggregateResults");
  menu.addToUi();
}

// データ集計処理の実行
function aggregateResults() { 
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();

  // 新規シートの作成
  const newSheet = ss.insertSheet('集計結果');

  // ヘッダー情報の入力
  const headers = ['事業所', '結果1', '結果2', '結果3'];
  newSheet.getRange('A1:D1').setValues([headers]);

  // 判定結果が「✕」のデータを集計
  let countArray = [];
  const data = sheet.getDataRange().getValues();
  const map = new Map();

  data.forEach((row, index) => {
    if (index === 0) return;
    const key = row[0]; // column A
    const values = row.slice(1, 4); // columns B,C,D
    const countX = values.map(value => value === '✕' ? 1 : 0);
      
    if (map.has(key)) {
      const currentValue = map.get(key);
      const updatedCount = currentValue.count.map((value, i) => value + countX[i]);
      map.set(key, {count: updatedCount});
    } else {
      map.set(key, {count: countX});
    }
  });

  map.forEach((value, key) => {
    countArray.push([key, ...value.count]);
  });

  // 集計結果シートへ結果を入力
  const startRow = 2;
  const startColumn = 1;
  const numRows = countArray.length;
  const numColumns = countArray[0].length;
  newSheet.getRange(startRow, startColumn, numRows, numColumns).setValues(countArray);

  Browser.msgBox("出力結果の集計が完了しました");
}

以下コードの部分は、スプレッドシートの上部メニュー欄に任意のメニューを追加するために使用しています。追加したメニューをクリックすることで、判定結果を集計するGASが実行されます。

//データ集計実行メニュー作成
function onOpen() {
const ui = SpreadsheetApp.getUi();
const menu = ui.createMenu(“データ集計メニュー”);
menu.addItem(“データ集計実行”, “aggregateResults”);
menu.addToUi();
}

イメージは以下画像の通りです。GASでは、サイドバーを作成したりWebフォームを実装することも可能なのですが、個人的にはこの方法が簡単なので気に入っています。

さいごに

いかがだったでしょうか？日々新しいデータを今回のように集計しなければいけないとしたら、Excelの関数で手作業するとしたら結構手間がかかるのではと思います。

GASはこの他にも様々な業務シーンで活躍する素晴らしいツールだと思っています。

また、GASを使用した業務効率化の事例について第2弾、第3弾とご紹介できればなと思います。

参考リンク

Apps Script