Google スプレッドシート整形術

Google スプレッドシートのデータを整形するための Google Apps Script を活用する方法について、ここでは完全かつ包括的に解説します。このガイドは、データの整理や自動化を行いたいと考えているユーザー向けに、スクリプトの基本から応用までを詳述しています。

Google Apps Script の概要

Google Apps Script は、Google スプレッドシートや他の Google サービス（Gmail、Google ドライブ、Google カレンダーなど）をカスタマイズしたり、自動化したりするためのスクリプト言語です。JavaScript を基盤としており、Web 上で動作します。これを使うことで、スプレッドシートのデータを操作する機能を自動化したり、特定の操作を簡略化したりすることができます。

Google スプレッドシートのデータを整形する際には、セルの書式設定や列の並べ替え、フィルターの適用、またはデータの一括変更などが必要になります。これらを Google Apps Script で効率よく行うための基本的な手順を説明します。

スプレッドシートで Google Apps Script を使用する準備

1. Google スプレッドシートの作成
   
   まず、Google スプレッドシートを作成します。データが入力されているシートを準備しておくと良いでしょう。

2. Google Apps Script のエディタを開く
   
   スプレッドシート上部の「拡張機能」から「Apps Script」を選択すると、スクリプトエディタが開きます。このエディタでスクリプトを作成し、スプレッドシートを操作します。

基本的なデータ整形の操作

1. セルにデータを入力する

javascript
CopyEdit
function writeData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange('A1').setValue('Hello, World!');
}

このスクリプトは、アクティブなシートのセル A1 に「Hello, World!」という文字列を入力します。getRange('A1') でセルを指定し、setValue() メソッドで値を設定します。

2. 複数のセルにデータを一度に入力する

javascript
CopyEdit
function writeMultipleData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = [
    ['名前', '年齢'],
    ['佐藤', 30],
    ['鈴木', 25]
  ];
  sheet.getRange('A1:B3').setValues(data);
}

setValues() メソッドを使用すると、2D 配列（行と列の組み合わせ）を指定して複数のセルに一度にデータを入力できます。

3. セルの書式設定

javascript
CopyEdit
function formatCells() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange('A1:B3');
  
  // セルの背景色を設定
  range.setBackground('#FFFF00');
  
  // フォントの色を設定
  range.setFontColor('#0000FF');
  
  // 太字に設定
  range.setFontWeight('bold');
}

このコードでは、setBackground() メソッドでセルの背景色を、setFontColor() でフォントの色を変更し、setFontWeight() で太字に設定しています。

4. データを並べ替える

javascript
CopyEdit
function sortData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange('A2:B4'); // ソートする範囲を指定
  range.sort({column: 2, ascending: false}); // 2列目（年齢）を降順で並べ替え
}

sort() メソッドを使って、指定した範囲のデータを並べ替えることができます。column パラメータで並べ替えの基準となる列を指定し、ascending パラメータで昇順か降順を決めます。

5. 特定の条件でデータをフィルタリングする

javascript
CopyEdit
function filterData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange('A1:B4');
  
  // 25歳以上のデータをフィルタリング
  var criteria = SpreadsheetApp.newFilterCriteria().whenNumberGreaterThan(25);
  range.createFilter().setColumnFilterCriteria(2, criteria);
}

この例では、2列目（年齢）の値が 25 より大きいデータのみをフィルタリングします。createFilter() メソッドでフィルターを作成し、setColumnFilterCriteria() でフィルター条件を設定します。

高度なデータ整形

1. 日付のフォーマットを変更する

javascript
CopyEdit
function formatDate() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange('A2:A10');
  range.setNumberFormat('yyyy/MM/dd'); // 日付フォーマットを変更
}

setNumberFormat() メソッドを使って、セル内の数値や日付のフォーマットを変更できます。上記の例では、日付を「年/月/日」の形式に変更しています。

2. 列幅を自動調整する

javascript
CopyEdit
function autoResizeColumns() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.autoResizeColumns(1, 2); // 1列目と2列目の幅を自動調整
}

autoResizeColumns() メソッドを使うと、指定した範囲の列幅を内容に合わせて自動で調整できます。

3. 行を削除する

javascript
CopyEdit
function deleteRow() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.deleteRow(3); // 3行目を削除
}

deleteRow() メソッドを使うことで、特定の行を削除できます。

4. 特定の値を持つ行を削除する

javascript
CopyEdit
function deleteRowWithValue() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var rows = sheet.getDataRange().getValues();
  
  for (var i = rows.length - 1; i >= 0; i--) {
    if (rows[i][1] == 30) { // 2列目が30の場合
      sheet.deleteRow(i + 1); // 行を削除
    }
  }
}

このコードでは、2列目の値が 30 の行を削除します。getDataRange().getValues() でシート内のデータをすべて取得し、条件に合う行を見つけて削除しています。

スクリプトをトリガーで自動化する

Google Apps Script では、特定のイベントが発生したときに自動でスクリプトを実行するように設定することができます。例えば、スプレッドシートが編集されたときに自動でデータ整形を行うトリガーを設定することができます。

javascript
CopyEdit
function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var editedRange = e.range;
  
  // 編集されたセルが特定の範囲であればデータを整形
  if (editedRange.getA1Notation() == 'A1') {
    sheet.getRange('B1').setValue('セルA1が変更されました');
  }
}

このスクリプトは、セル A1 が編集されると自動的にセル B1 にメッセージを表示するものです。onEdit() 関数はスプレッドシートで編集が行われるたびに自動で実行されます。

まとめ

Google Apps Script を使用すると、Google スプレッドシートのデータを柔軟に整形・操作できます。基本的な操作から、高度な自動化や書式設定、データのフィルタリングや並べ替えなど、多くの作業を自動化することが可能です。スクリプトを活用することで、手作業で行っていた煩雑な作業を簡略化し、作業の効率を大幅に向上させることができます。