GASでエラー`Exception: You do not have permission to call SpreadsheetApp.openByUrl`が発生する

Question

jsは一通り書けるつもりですが、GASは初心者です。
Googleスプレッドシートで他のスプレッドシートを参照して、結果を返すユーザ関数を書こうとしています。

セルO2 には=test3()と入力しており、結果の期待値としてbb2が入るはずなのですが、実際は下記エラーとなります。

Exception: You do not have permission to call SpreadsheetApp.openByUrl.
Required permissions: https://www.googleapis.com/auth/spreadsheets

このエラーを検索すると、App Scriptエディタにappsscript.jsonを追加して、そこにoauthScopesというキーでエラーで示されているURLを追加すればよいとありましたので、そのとおりにしました。

しかし、結果は変わらず、エラーメッセージにも変化は見られません。

参考にしたページによると、appsscript.jsonを追加して一度スクリプトを実行すると、「承認が必要です」というダイアログが開き、そこで権限を付与する手順となっていましたが、そのようなダイアログは表示されませんでした。

エラーの原因は、おそらく参照先のスプレッドシートに権限が付与されていないためではないかと思いますが、このダイアログが表示されないため行き詰まっている状態です。

スプレッドシートを新規作成して3回ほど同じ手順を試してみましたが、やはり同じ現象です。

あと試したこととして、問題が起きているtest3()関数をApp Scriptエディタ上で実行やデバッグすると期待通りにbb2が返ることを確認しています。

このような現象に遭遇された方がおられましたらアドバイスいただけますと助かります。
よろしくお願いいたします。

• appsscript.json

{
  "timeZone": "Asia/Tokyo",
  "dependencies": {
  },
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets"
  ],
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8"
}

• 問題のスクリプト

const DB_Master = 'https://docs.google.com/spreadsheets/d/16AbcQ9HXfe_A_Bgz4nfPWMEMvu_ACCIN-pRrWjxVhQY/edit';

// ------------------------------------
// マスタDBのOPをnameで検索してその行を取得
function find_values(lines, v, col) {
  for (const i in lines) {
    if (lines[i][col] == v) return lines[i];
  }

  return false;    // not found
}

// マスタDBのOPをnameで検索してその行を返す
function search_db_op(code_name) {
  const ss = SpreadsheetApp.openByUrl(DB_Master);
  const sheet = ss.getSheetByName('OP');
  const vals = sheet.getRange('A2:AG').getValues();

  const search_col = 4;    // for name
  return find_values(vals, code_name, search_col);
}

function test3() {
  const dat = search_db_op("bar");
  const ret = dat[6];
  console.log(ret);
  return ret;
}

• App Scriptエディタ上でのtest3()の実行結果

• 問題が起きているスプレッドシート(work)：https://docs.google.com/spreadsheets/d/1m_t5eTXlxA3h1xVk_7ODP1Aghq7MGx8AOFml11CJSuw/edit

• GASで参照している外部のスプレッドシート(master)：https://docs.google.com/spreadsheets/d/16AbcQ9HXfe_A_Bgz4nfPWMEMvu_ACCIN-pRrWjxVhQY/edit

• 参考にさせていただいたページ：https://zenn.dev/atomic/articles/831195249869ff

Comments

[YAmaGNZ]

こちらの[ドキュメント](https://developers.google.com/apps-script/guides/sheets/functions?hl=ja#advanced)に

> スプレッドシート 読み取り専用（ほとんどの get*() メソッドを使用できますが、set*() は使用できません）。
> 他のスプレッドシート（SpreadsheetApp.openById()）を開くことはできません または SpreadsheetApp.openByUrl()）。

という記述があります。

Answer 1

YAmaGNZさんがコメントしていただいているように、
スプレッドシート上で利用する関数でSpreadsheetApp.openById()などは利用できません。

代替案としては、セキュリティ的に可能であればですが、
GAS（Google Apps Script）がバインドされているスプレッドシートに
IMPORT用のシートを作成しデータをIMPORTします。

下記のように関数でデータを取得する、またはGASで定期的にデータを同期させるなどします。

=IMPORTRANGE("DB用スプレッドシートのURL", "OP!A:AG" )

その後、下記のように自身のスプレッドシートのシートからデータを取得するようにすれば
やりたいことは実現できるかなと思います。

// マスタDBのOPをnameで検索してその行を返す
function search_db_op(code_name) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('IMPORT_OP'); // データをインポートしたシート名
  const vals = sheet.getRange('A2:AG').getValues();

  const search_col = 4;    // for name
  return find_values(vals, code_name, search_col);
}

シートを保護したり、非表示にするなどすれば、運用に支障はないかなと思います。
どうしても見せられないデータなどであれば上記の方法ではリスクがあるので、
別途方法を検討する必要があります。
要件に合わせてご検討ください。

Comments

[yaxavr]

詳細な説明ありがとうございました。

なるほど、ユーザがGASで書いた関数をスプレッドシートのセルに書いて呼び出すと「カスタム関数」というの扱いになるのですね。

で、`openByUrl()`を使った関数もスプレッドシートのセルから呼び出すと、もうその関数はカスタム関数なので、カスタム関数で使用不可な'openByUrl()'メソッドを呼んだ時点でエラーが発生する、ということですね。

だからGASのエディタから`実行`すると期待通り動作するけど、セルに書いて呼び出そうとするとこのエラーが出るということですね。ようやく理解できました。

どうやら私には、このエラーメッセージからその内部動作を察したり、YAmaGNZさんに示していただいた公式ドキュメントの意味を理解する力が足りなかったようです。

おかげさまで、解決できそうです。
ありがとうございました。

[dadadaiiiiiii]

解決できそうとのことでよかったです！

カスタム関数は便利な所もあるのですが、下記の記事で説明されているような制限や注意点もあります。
https://qiita.com/uramotot/items/19b8baa2fb9c4f4109c7

カスタム関数以外にも時間トリガーやメニューからの実行など代替案もいろいろと考えられますので
要件に合わせて最適な方法を検討してみると面白いかなと思います！

[yaxavr]

ご教示いただいたqiitaの記事をブックマークしました（これは永久保存版です）。

js書けるならと気軽にGASを始めましたが、やはりGoogleスプレッドシート特有の制限があることがよくわかりました。

今回の場合は「カスタム関数」という用語をはっきり認識してなかったことで、公式のドキュメントを読んでもピンと来ませんでした。

人様のブログ記事を読むのもいいですが、トラブったときはやはり公式ドキュメントをしっかり読むべきだと痛感しました。

ご親切に感謝します。これからもGASやっていきたいと思います！

[yaxavr]

回答ありがとうございます。
試したところ問題が解決しました！
ベストアンサーに選ばせていただきました。