BigQueryへ定期的にクエリ実行しているスプレッドシートを、一覧で特定する方法

概要

スプレッドシートのデータコネクタ機能によって以下のようになった方はいないだろうか？

• シートにデータコネクタ設定をして、さらにクエリの定期実行を設定して、そのまま放置 💪
• シートから定期実行されるクエリによって、いつの間にかBQコストが増える 💸
• クエリを定期実行しているシートを探して停止したいが、一覧で特定する方法がわからない 😇

このような場合に、監査ログを見ることで手軽にシートを一覧化できるので、その方法を記しておく。

方法

Cloud Loggingで以下のようにして絞ることで、スプレッドシートからBQにアクセスした際の監査ログを確認できる。

logName:"cloudaudit.googleapis.com"
protoPayload.metadata.firstPartyAppMetadata.sheetsMetadata.docId!=""

上記によって確認できたログからスプレッドシートのURLをどう特定するかというと、絞られたログを見るとわかるかもしれないが、上記の2行目で指定している docId の部分にスプレッドシートのIDが記載されており、このIDを用いてシートURLを得ることができる。

Cloud Logging上でひとつひとつログを見てIDを取っていくのは面倒なので、以下のように「操作」「ダウンロード」ボタンからCSVにDLして作業すると良い。

補足

監査ログってデフォルトで取得されているのか？

GCPではBigQueryについてはデフォルトでデータアクセス監査ログが有効になっている。BQ以外は明示的に設定する必要があるが、BQだけはデフォルトで有効になっている（むしろ無効にできない）ので、上記の作業を行うのに特別な事前作業などは不要。

複数プロジェクトにクエリ発行している場合

シートから複数のプロジェクトのBQへアクセスがある場合は、各プロジェクトにおいて上記方法によって監査ログを見ていく必要がある点に注意。

シートタイトルや最終更新日なども確認したい場合

シートURLを特定できても、シートが20個や30個もあるとその確認や方針決めが面倒である。シートのタイトルや最終更新日もわかると、例えば「Xヶ月以上更新されてないものは一旦止める」などの対応方針を決めて実行しやすい。

シートIDからタイトルや最終更新日時を出すGASを(AI等経由して非常に雑に)書いたので、それも載せておく。動けば良しの簡易なものなので参考程度に。

• GAS実行のための準備
  • スプレッドシートに「データコネクタ設定されているシート」という名前のシートを準備
  • そのシートの C列2行目以降にシートIDを記載しておく
• そうすると、C列2行目以降に対して、以下をやってくれる
  • シートIDからスプレッドシートを開き、以下の情報を取得
    • タイトル（スプレッドシート名）
    • 作成者（オーナーのメールアドレス）　※
    • 最終更新日（シートが最後に更新された日時）
    • 最終更新者（最後に変更を加えたユーザーの名前）　※
  • 上記の各情報を、以下の対応する列に記載
    • D列: タイトル
    • E列: 作成者（オーナー）
    • F列: 最終更新日
    • G列: 最終更新者

※ついている情報は、不安定でうまく取れてない気がするので、参考程度に。

function getSheetDetailsWithUpdateInfo() {
  const sheetName = "データコネクタ設定されているシート"; // 処理するシート名
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);

  if (!sheet) {
    console.log(`シート "${sheetName}" が見つかりません。`);
    return;
  }

  const startRow = 2; // 処理を開始する行
  const idColumn = 3; // C列
  const titleColumn = 4; // D列
  const ownerColumn = 5; // E列
  const lastUpdatedColumn = 6; // F列
  const lastUpdatedByColumn = 7; // G列

  const lastRow = sheet.getLastRow(); // 最終行を取得
  const ids = sheet.getRange(startRow, idColumn, lastRow - startRow + 1).getValues(); // C列の値を取得

  for (let i = 0; i < ids.length; i++) {
    const rowNumber = startRow + i;
    const sheetId = ids[i][0]; // シートIDを取得

    // 空のシートIDをスキップ
    if (!sheetId || sheetId.trim() === "") {
      console.log(`空のシートIDをスキップ: 行 ${rowNumber}`);
      sheet.getRange(rowNumber, titleColumn).setValue("空のシートID");
      sheet.getRange(rowNumber, ownerColumn).setValue("");
      sheet.getRange(rowNumber, lastUpdatedColumn).setValue("");
      sheet.getRange(rowNumber, lastUpdatedByColumn).setValue("");
      continue;
    }

    console.log(`処理中の行: ${rowNumber}, シートID: ${sheetId}`);

    try {
      const spreadsheet = SpreadsheetApp.openById(sheetId);
      const title = spreadsheet.getName();
      let owner = "不明";
      let lastUpdated = "不明";
      let lastUpdatedBy = "不明";

      // オーナー取得を試みる
      try {
        owner = spreadsheet.getOwner()?.getEmail() || "不明";
      } catch (e) {
        console.log(`オーナー情報が取得できません: ${e.message}`);
      }

      // 最終更新情報を取得
      try {
        const revision = DriveApp.getFileById(sheetId).getLastUpdated();
        lastUpdated = revision ? revision.toISOString() : "不明";
      } catch (e) {
        console.log(`最終更新日が取得できません: ${e.message}`);
      }

      // 最終更新者を取得
      try {
        const revisionInfo = Drive.Revisions.list(sheetId).revisions;
        if (revisionInfo && revisionInfo.length > 0) {
          lastUpdatedBy = revisionInfo[revisionInfo.length - 1].lastModifyingUser?.displayName || "不明";
        }
      } catch (e) {
        console.log(`最終更新者が取得できません: ${e.message}`);
      }

      // タイトル、オーナー、最終更新情報を対応するセルに書き込む
      sheet.getRange(rowNumber, titleColumn).setValue(title);
      sheet.getRange(rowNumber, ownerColumn).setValue(owner);
      sheet.getRange(rowNumber, lastUpdatedColumn).setValue(lastUpdated);
      sheet.getRange(rowNumber, lastUpdatedByColumn).setValue(lastUpdatedBy);

      // ログに成功内容を記録
      console.log(`成功: 行 ${rowNumber}, シートID: ${sheetId}, タイトル: ${title}, 作成者: ${owner}, 最終更新日: ${lastUpdated}, 最終更新者: ${lastUpdatedBy}`);
    } catch (e) {
      // エラー処理
      sheet.getRange(rowNumber, titleColumn).setValue("アクセス不可");
      sheet.getRange(rowNumber, ownerColumn).setValue("アクセス不可");
      sheet.getRange(rowNumber, lastUpdatedColumn).setValue("アクセス不可");
      sheet.getRange(rowNumber, lastUpdatedByColumn).setValue("アクセス不可");
      console.log(`エラー: 行 ${rowNumber}, シートID: ${sheetId}, メッセージ: ${e.message}`);
    }
  }
}