JDBC

TLS セキュリティプロトコルのバージョン 1.0 と 1.1 が無効になります。接続を確立するには、TLS 1.2 以降を使用します。

Google Apps Script は、標準の Java Database Connectivity テクノロジーのラッパーである JDBC サービスを介して外部データベースに接続できます。JDBC サービスは、Google Cloud SQL for MySQL、MySQL、Microsoft SQL Server、Oracle、PostgreSQL の各データベースをサポートしています。

スプレッドシートが大きくなりすぎたり、複雑な計算でタイムアウトの問題が発生したりする場合は、データを外部データベースに移動することで、パフォーマンスと信頼性を大幅に向上させることができます。

JDBC を使用して外部データベースを更新するには、スクリプトでデータベースへの接続を開き、SQL ステートメントを送信して変更を行う必要があります。

Google Cloud SQL データベース

Google Cloud SQL を使用すると、Google のクラウドに存在するリレーショナルデータベースを作成できます。Cloud SQL では、使用量に基づいて料金が発生する場合があります。

Cloud SQL クイックスタートに記載されている手順に沿って、Google Cloud SQL インスタンスを作成します。

Google Cloud SQL 接続を作成する

Apps Script の JDBC サービスを使用して Google Cloud SQL データベースとの接続を確立するには、次の 2 つの方法があります。

（推奨）Jdbc.getCloudSqlConnection を使用して接続する
Jdbc.getConnection を使用して接続する

どちらも有効ですが、2 番目の方法では、データベースへのアクセス用に IP 範囲のセットを承認する必要があります。

Jdbc.getCloudSqlConnection を使用する（推奨）

このメソッドは、Jdbc.getCloudSqlConnection メソッドを使用して Google Cloud SQL MySQL インスタンスへの接続を作成します。データベース URL の形式は jdbc:google:mysql://subname です。ここで、subname は Google Cloud コンソールの Cloud SQL インスタンスの [概要] ページに表示される MySQL のインスタンス接続名です。

Cloud SQL SQL Server に接続するには、Jdbc.getConnection をご覧ください。

Jdbc.getConnection を使用する

この方法を使用するには、Apps Script のサーバーがデータベースに接続できるように、特定の CIDR（クラスレスドメイン間ルーティング）IP アドレス範囲を承認する必要があります。スクリプトを実行する前に、次の手順を完了します。

Google Cloud SQL インスタンスで、このデータソースから一度に 1 つずつIP 範囲を承認します。
データベースに割り当てられた URL をコピーします。URL は jdbc:mysql:subname の形式になります。

これらの IP 範囲を承認したら、Jdbc.getConnection メソッドのいずれかと、先ほどコピーした URL を使用して、Google Cloud SQL インスタンスへの接続を作成します。

その他のデータベース

独自の MySQL、Microsoft SQL Server、Oracle、PostgreSQL データベースをすでに使用している場合は、Apps Script の JDBC サービスを使用して接続します。

他のデータベース接続を作成する

Apps Script の JDBC サービスを使用してデータベース接続を作成するには、データベース設定でこのデータソースの IP 範囲を承認する必要があります。

JDBC サービスは、ポート 1025 以降にのみ接続できます。データベースが下位ポートでサービスを提供していないことを確認します。

これらの許可リストが設定されたら、Jdbc.getConnection メソッドのいずれかとデータベースの URL を使用して、データベースへの接続を作成します。

サンプルコード

次のサンプルコードは、Google Cloud SQL データベースに接続することを前提としており、Jdbc.getCloudSqlConnection メソッドを使用してデータベース接続を作成します。他のデータベースの場合は、Jdbc.getConnection メソッドを使用してデータベース接続を作成する必要があります。

JDBC メソッドの詳細については、JDBC の Java ドキュメントをご覧ください。

データベース、ユーザー、テーブルを作成する

ほとんどのデベロッパーは、MySQL コマンドラインツールを使用して、データベース、ユーザー、テーブルを作成します。ただし、次の例に示すように、Apps Script で同じことを行うことは可能です。スクリプトが常に root としてデータベースに接続する必要がないように、少なくとも 1 人の他のユーザーを作成します。

service/jdbc.gs

GitHub で表示

/**
 * Create a new database within a Cloud SQL instance.
 */
function createDatabase() {
  try {
    const conn = Jdbc.getCloudSqlConnection(instanceUrl, root, rootPwd);
    conn.createStatement().execute(`CREATE DATABASE ${db}`);
  } catch (err) {
    // TODO(developer) - Handle exception from the API
    console.log("Failed with an error %s", err.message);
  }
}

/**
 * Create a new user for your database with full privileges.
 */
function createUser() {
  try {
    const conn = Jdbc.getCloudSqlConnection(dbUrl, root, rootPwd);

    const stmt = conn.prepareStatement("CREATE USER ? IDENTIFIED BY ?");
    stmt.setString(1, user);
    stmt.setString(2, userPwd);
    stmt.execute();

    conn.createStatement().execute(`GRANT ALL ON \`%\`.* TO ${user}`);
  } catch (err) {
    // TODO(developer) - Handle exception from the API
    console.log("Failed with an error %s", err.message);
  }
}

/**
 * Create a new table in the database.
 */
function createTable() {
  try {
    const conn = Jdbc.getCloudSqlConnection(dbUrl, user, userPwd);
    conn
      .createStatement()
      .execute(
        "CREATE TABLE entries " +
          "(guestName VARCHAR(255), content VARCHAR(255), " +
          "entryID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(entryID));",
      );
  } catch (err) {
    // TODO(developer) - Handle exception from the API
    console.log("Failed with an error %s", err.message);
  }
}

データベースに書き込む

次の例は、単一のレコードと 500 件のレコードのバッチをデータベースに書き込む方法を示しています。バッチ処理は一括オペレーションに不可欠です。

パラメータ化されたステートメントが使用されます。変数には ? が使用されます。SQL インジェクション攻撃を防ぐには、パラメータ化されたステートメントを使用して、ユーザーが指定したすべてのデータをエスケープします。

service/jdbc.gs

GitHub で表示

/**
 * Write one row of data to a table.
 */
function writeOneRecord() {
  try {
    const conn = Jdbc.getCloudSqlConnection(dbUrl, user, userPwd);

    const stmt = conn.prepareStatement(
      "INSERT INTO entries " + "(guestName, content) values (?, ?)",
    );
    stmt.setString(1, "First Guest");
    stmt.setString(2, "Hello, world");
    stmt.execute();
  } catch (err) {
    // TODO(developer) - Handle exception from the API
    console.log("Failed with an error %s", err.message);
  }
}

/**
 * Write 500 rows of data to a table in a single batch.
 */
function writeManyRecords() {
  try {
    const conn = Jdbc.getCloudSqlConnection(dbUrl, user, userPwd);
    conn.setAutoCommit(false);

    const start = new Date();
    const stmt = conn.prepareStatement(
      "INSERT INTO entries " + "(guestName, content) values (?, ?)",
    );
    for (let i = 0; i < 500; i++) {
      stmt.setString(1, `Name ${i}`);
      stmt.setString(2, `Hello, world ${i}`);
      stmt.addBatch();
    }

    const batch = stmt.executeBatch();
    conn.commit();
    conn.close();

    const end = new Date();
    console.log("Time elapsed: %sms for %s rows.", end - start, batch.length);
  } catch (err) {
    // TODO(developer) - Handle exception from the API
    console.log("Failed with an error %s", err.message);
  }
}

/**
 * Write 500 rows of data to a table in a single batch.
 * Recommended for faster writes
 */
function writeManyRecordsUsingExecuteBatch() {
  try {
    const conn = Jdbc.getCloudSqlConnection(dbUrl, user, userPwd);
    conn.setAutoCommit(false);

    const start = new Date();
    const stmt = conn.prepareStatement(
      "INSERT INTO entries " + "(guestName, content) values (?, ?)",
    );
    const params = [];
    for (let i = 0; i < 500; i++) {
      params.push([`Name ${i}`, `Hello, world ${i}`]);
    }

    const batch = stmt.executeBatch(params);
    conn.commit();
    conn.close();

    const end = new Date();
    console.log("Time elapsed: %sms for %s rows.", end - start, batch.length);
  } catch (err) {
    // TODO(developer) - Handle exception from the API
    console.log("Failed with an error %s", err.message);
  }
}

データベースから読み取る

この例では、データベースから多数のレコードを読み取り、必要に応じて結果セットをループ処理する方法を示します。

service/jdbc.gs

GitHub で表示

/**
 * Read up to 1000 rows of data from the table and log them.
 */
function readFromTable() {
  try {
    const conn = Jdbc.getCloudSqlConnection(dbUrl, user, userPwd);
    const start = new Date();
    const stmt = conn.createStatement();
    stmt.setMaxRows(1000);
    const results = stmt.executeQuery("SELECT * FROM entries");
    const numCols = results.getMetaData().getColumnCount();

    while (results.next()) {
      let rowString = "";
      for (let col = 0; col < numCols; col++) {
        rowString += `${results.getString(col + 1)}\t`;
      }
      console.log(rowString);
    }

    results.close();
    stmt.close();

    const end = new Date();
    console.log("Time elapsed: %sms", end - start);
  } catch (err) {
    // TODO(developer) - Handle exception from the API
    console.log("Failed with an error %s", err.message);
  }
}

/**
 * Read up to 1000 rows of data from the table and log them.
 * Recommended for faster reads
 */
function readFromTableUsingGetRows() {
  try {
    const conn = Jdbc.getCloudSqlConnection(dbUrl, user, userPwd);
    const start = new Date();
    const stmt = conn.createStatement();
    stmt.setMaxRows(1000);
    const results = stmt.executeQuery("SELECT * FROM entries");
    const numCols = results.getMetaData().getColumnCount();
    const getRowArgs = [];
    for (let col = 0; col < numCols; col++) {
      getRowArgs.push(`getString(${col + 1})`);
    }
    const rows = results.getRows(getRowArgs.join(","));
    for (let i = 0; i < rows.length; i++) {
      console.log(rows[i].join("\t"));
    }

    results.close();
    stmt.close();

    const end = new Date();
    console.log("Time elapsed: %sms", end - start);
  } catch (err) {
    // TODO(developer) - Handle exception from the API
    console.log("Failed with an error %s", err.message);
  }
}

接続を閉じる

スクリプトの実行が終了すると、JDBC 接続は自動的に閉じます。（単一の google.script.run 呼び出しは、呼び出しを行った HTML サービスページが開いたままでも、完全な実行としてカウントされます）。

ただし、スクリプトの終了前に接続、ステートメント、結果セットが完了することがわかっている場合は、JdbcConnection.close、JdbcStatement.close、または JdbcResultSet.close を呼び出して手動で閉じます。

アラートまたはプロンプトダイアログを表示すると、開いている JDBC 接続も終了します。ただし、カスタムメニューやダイアログ、カスタムコンテンツを含むサイドバーなど、他の UI 要素の表示は許可されません。

Google、Google Workspace、および関連するマークとロゴは、Google LLC の商標です。その他すべての企業名および商品名は関連各社の商標です。

JDBC コレクションでコンテンツを整理 必要に応じて、コンテンツの保存と分類を行います。

Google Cloud SQL データベース

Google Cloud SQL 接続を作成する

Jdbc.getCloudSqlConnection を使用する（推奨）

Jdbc.getConnection を使用する

その他のデータベース

他のデータベース接続を作成する

サンプルコード

データベース、ユーザー、テーブルを作成する

データベースに書き込む

データベースから読み取る

接続を閉じる

JDBC