はじめに

Google App Scriptで使って簡単なチェックリストを作成したときに、スプレッドシートをデータベースのようにして実装する方式が便利だったので、スプレッドシートを操作する方法についてまとめました。

Google Apps Script

Google Apps Scriptは、Googleが提供する無料のJavaScriptベースのスクリプト言語です。このスクリプトを使うと、Googleのさまざまなアプリケーションを自動化・カスタマイズできます。

例えば、Google Sheetsのスプレッドシート内のデータ処理や、特定の条件に基づくメールの自動送信などが可能です。また、Google Drive内のファイル整理や定期的なバックアップを行うスクリプトも簡単に作成できます。

特別なソフトウェアは不要で、ウェブブラウザ上で直接スクリプトを作成・実行できます。そのため、プログラミング初心者でも手軽に始められる点が魅力です。

スクリプトの概要

今回作成したチェックリストアプリでは次の操作を行うためのスクリプトを作成しました。

アイテムの追加
全てのアイテムの取得
全てのアイテムの削除
シートの追加
全てのシート名の取得

以下は、スプレッドシートを操作するためのクラスSpreadsheetHandlerを定義したスクリプトです。

class SpreadsheetHandler {
  constructor(spreadsheetId) {
    this.spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  }

  getSheetNames() {
    let sheets = this.spreadsheet.getSheets();
    let sheetNames = sheets.map(sheet => sheet.getName());
    return sheetNames;
  }

  addItem(item, sheetName) {
    try {
      let sheet = this.spreadsheet.getSheetByName(sheetName);
      sheet.appendRow([item, new Date()]);
    } catch (e) {
      console.log(e);
      return false;
    }
    return true;
  }

  getAllItem(sheetName) {
    let sheet = this.spreadsheet.getSheetByName(sheetName);
    return sheet.getDataRange().getValues().map(function(row) {
      return row[0];
    }).filter(function(item) {
      return item !== "";
    });
  }

  addSheet(sheetName) {
    try {
      this.spreadsheet.insertSheet(sheetName);
    } catch (e) {
      console.log(e);
      return false;
    }
    return true;
  }

  deleteSheetAllItems(sheetName) {
    try {
      let sheet = this.spreadsheet.getSheetByName(sheetName);
      sheet.deleteRows(1, sheet.getLastRow());
    } catch (e) {
      console.log(e);
      return false;
    }
    return true;
  }
}

const SPREAD_SHEET_ID = "スプレッドシートのID";
const mSpreadSheetHandler = new SpreadsheetHandler(SPREAD_SHEET_ID);

スクリプトの詳細解説

クラスの初期化

constructor(spreadsheetId) {
  this.spreadsheet = SpreadsheetApp.openById(spreadsheetId);
}

このコンストラクタでは、スプレッドシートIDを受け取り、SpreadsheetApp.openByIdを使用してスプレッドシートを開きます。

シート名の取得

getSheetNames() {
  let sheets = this.spreadsheet.getSheets();
  let sheetNames = sheets.map(sheet => sheet.getName());
  return sheetNames;
}

このメソッドは、スプレッドシート内のすべてのシートの名前を取得し、リストとして返します。

アイテムの追加

addItem(item, sheetName) {
  try {
    let sheet = this.spreadsheet.getSheetByName(sheetName);
    sheet.appendRow([item, new Date()]);
  } catch (e) {
    console.log(e);
    return false;
  }
  return true;
}

指定されたシートにアイテムと現在の日付を新しい行として追加します。エラーが発生した場合は、falseを返します。

すべてのアイテムの取得

getAllItem(sheetName) {
  let sheet = this.spreadsheet.getSheetByName(sheetName);
  return sheet.getDataRange().getValues().map(function(row) {
    return row[0];
  }).filter(function(item) {
    return item !== "";
  });
}

指定されたシートのすべてのアイテムを取得し、リストとして返します。空のアイテムは除外されます。

シートの追加

addSheet(sheetName) {
  try {
    this.spreadsheet.insertSheet(sheetName);
  } catch (e) {
    console.log(e);
    return false;
  }
  return true;
}

新しいシートを追加します。エラーが発生した場合は、falseを返します。

すべてのアイテムの削除

deleteSheetAllItems(sheetName) {
  try {
    let sheet = this.spreadsheet.getSheetByName(sheetName);
    sheet.deleteRows(1, sheet.getLastRow());
  } catch (e) {
    console.log(e);
    return false;
  }
  return true;
}

指定されたシート内のすべての行を削除します。エラーが発生した場合は、falseを返します。

スプレッドシートハンドラー

スプレッドシートIDを適切に置き換えてください。

const SPREAD_SHEET_ID = "スプレッドシートID";
const mSpreadSheetHandler = new SpreadsheetHandler(SPREAD_SHEET_ID);

おわりに

Google App Scriptは簡単なwebアプリを作成することが可能で、スプレッドシートを組み合わせることで実現できることの幅が広がると思いました。

kitaroの自由帳

Google App ScriptでGoogleスプレッドシートをDBとして操作する