見出し画像

Google Apps Scriptを使ってIMPORTRANGEを明らかにする(コピペ可)

N2K/OneManIT

このスクリプトは、特定のGoogle Driveフォルダ内のスプレッドシートで使用されているIMPORTRANGE関数を検索するツールです。
スクリプトは、指定されたフォルダを再帰的に検索し、IMPORTRANGE関数が含まれているセルを見つけると、その情報を別のスプレッドシートに記録します。


注意点

  • スクリプトの特性上、データが消えるということはありませんが、実行時の責任は負えませんのでご自身の判断の元実行してください

  • Google Apps Scriptの使用制限に耐えうる設計ではありません。6分、もしくは30分でScriptが終了しない場合はタイムアウトでエラーになってしまいます。

  • 再帰的にフォルダとファイルを取得して実行するため、タイムアウトになってしまう場合はルートフォルダの階層を下げてみてください。

  • 実行者にアクセス権限が無いファイルは抽出できないです（泣き

IMPORTRANGEについて

IMPORTRANGEは便利だ

スプレッドシートを使っていると他のシートと連携させたくなります。
そんなときの神関数がIMPORTRANGE。
他のスプレッドシートを参照し、値を引っ張ってこれる。

IMPORTRANGEを使っていて不便なこと

が、しかし、社内でIMPORTRANGEが使われ始めると、どのシートがどのシートを参照してるのかわからない！
そのため、行や列を追加していいのか、シートを消していいのか把握できなくなります。

しかも、Google Workspaceの標準機能ではその参照関係を明らかにすることはできません。（たぶん）

今回はGoogle Apps scriptを使って明らかにすることができたので、そのスクリプトを紹介します。

作成したスクリプト

事前準備

  1. 結果記録用のスプレッドシートを新規作成し、スプレッドシートIDをコピーしておいてください。スプレッドシートIDはURLから取得できます。（docs.google.com/spreadsheets/d/{スプレッドシートID}/edit）

  2. 調査をしたいGoogleドライブ上のフォルダのフォルダIDをコピーしておいてください。こちらもIDはURLから取得できます。（drive.google.com/drive/folders/{フォルダID}）

  3. Google Apps Scriptにて新しいプロジェクトを作成してください。

  4. [スクリプトの設定]メニューから以下のようにスクリプトプロパティを設定して下さい。

  • プロパティ : FOLDER_ID　値 : 2で取得したフォルダID

  • プロパティ : REPORT_SHEET_ID　値 : 1で取得したスプレッドシートID

スクリプト

searchFormulaInFolder

  • このスクリプトのメイン関数です

  • folderId と spreadsheetId というプロパティからフォルダとスプレッドシートのIDを取得します。

  • 指定されたフォルダを再帰的に検索して、searchFormula 関数を呼び出します。

  • 結果をスプレッドシートに記録します。

function searchFormulaInFolder() {
  // 調査したいフォルダの一階層目のIDを指定
  var folderId = PropertiesService.getScriptProperties().getProperty('FOLDER_ID');
  var spreadsheetId = PropertiesService.getScriptProperties().getProperty('REPORT_SHEET_ID');
  
  // 指定したフォルダを再帰的に調査
  var rootFolder = DriveApp.getFolderById(folderId);
  var resultArray = searchFormula(rootFolder);

  // 結果をスプレッドシートに記録
  var spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  var sheet = spreadsheet.getSheetByName("結果シート");

  if (!sheet) {
    sheet = spreadsheet.insertSheet("結果シート");
  }

  // ヘッダーを追加
  sheet.getRange(1, 1, 1, 5).setValues([["ヒットした関数", "入力されているセル", "ファイル名", "シート名", "シートURL"]]);

  // 結果を記録
  if (resultArray.length > 0) {
    sheet.getRange(sheet.getLastRow() + 1, 1, resultArray.length, 5).setValues(resultArray);
  }
}

searchFormula

  • 指定されたフォルダ内の各ファイルを検索し、スプレッドシートであればその中の各シートに対して処理を行います。

  • スプレッドシートじゃないファイルはスキップします

  • シート保護がかかっている場合やファイルに何らかの理由でアクセスできない場合はスキップします

  • 各シートの数式を取得し、指定された関数が含まれているか検索します。

  • ヒットした場合、結果を resultArray に追加し、最終的にその配列を返します。

function searchFormula(folder, sheet) {
  var resultArray = []; // ヒットした情報を格納するための配列

  // フォルダ内のファイル一覧を取得
  var files = folder.getFiles();

  // 調査したい関数を指定
  var targetFormula = "IMPORTRANGE";


  // 各ファイルに対して処理を行う
  try {
  while (files.hasNext()) {
    var file = files.next();
    var fileName = file.getName();
    console.log(fileName);
      // スプレッドシートであるか確認
      if (file.getMimeType() === "application/vnd.google-apps.spreadsheet") {
        // ファイルを開く
        var spreadsheet = SpreadsheetApp.open(file);

        // 各シートに対して処理を行う
        spreadsheet.getSheets().forEach(function(sheet) {
          try {
            // シートの全範囲を取得
            var range = sheet.getDataRange();

            // シート内の数式を取得
            var formulas = range.getFormulas();

            // 数式を検索し、importrange関数が含まれているか確認
            for (var i = 0; i < formulas.length; i++) {
              for (var j = 0; j < formulas[i].length; j++) {
                var formula = formulas[i][j];
                if (formula.indexOf(targetFormula) !== -1) {
                  // importrange関数が見つかった場合、配列に情報を追加
                  resultArray.push([
                    "'" + formula, // 先頭にシングルクォートを追加
                    range.getCell(i + 1, j + 1).getA1Notation(),
                    fileName,
                    sheet.getName(),
                    spreadsheet.getUrl() + "#gid=" + sheet.getSheetId()
                  ]);
                }
              }
            }
          } catch (error) {
            // シートがロックされている場合のエラーをキャッチして無視
          }
        });
      }
    } 
  } catch (error) {
      // ファイルがロックされている場合のエラーをキャッチして無視
    }

  // サブフォルダに対して再帰的に処理を行う
  var subFolders = folder.getFolders();
  while (subFolders.hasNext()) {
    var subFolder = subFolders.next();
    resultArray = resultArray.concat(searchFormula(subFolder, sheet));
  }
  return resultArray;
}

この2つを保存して searchFormulaInFolder を実行してください。
結果記録用のスプレッドシートに実行結果が記録されると思います。

実行結果のイメージ

こんな感じで出力されます。
A列に実際に入力されている関数が表示されるのでここから調べていくことが可能です。
改善点としてこのURLやスプレッドシートIDからファイル名を持ってくることはできそうなので追々改修予定です！

実行結果スプシ

まとめ

良かった点

フォルダ内のサブフォルダも再帰的に検索するので、ある程度、大規模なフォルダ階層でも使用できます。
IMPORTRANGE関数がどこで使用されているかを簡単に把握し、スプレッドシートの構造を理解するのに役立てると思います。

改善点

  • 参照先シート

関数の抜き出しまではできているものの、「どのスプレッドシートを参照しているか」についてはリスト化しきれていないんですよね～。
ここについては今後の改修ポイントかなと思っています。
URLを指定しているパターンとファイルIDのみで指定しているパターンがありそうなのでここをうまく利用してファイル名を表示できれば、やりたかったことは達成できそう。

  • 30分制限の突破

    • 内部トリガーで解決

    • 並列処理をする

いずれか、もしくは2つを組み合わせることで実現できそうですが、FileItelatorの処理について技術的な面で追いついていなく今回は実装できませんでした。これができると夜間バッチのように走らせてやるといった未来が見えてくるのですが、追々技術習得して実装できればなと思います。
（もし知っている方がいらっしゃれば教えて欲しい…）


この記事が気に入ったらサポートをしてみませんか？