[解決済み] Google Sheets、"リクエストに有効なAPIキーが不足しています"

質問

を使おうとしています。 Google Sheets API . 問題は、googleスプレッドシートでスクリプト関数を呼び出すと、以下のエラーが発生することです。

sheets.spreadsheets.values.get への API コールに失敗し、エラーが発生しました。The request is missing a valid API key. (というエラーで失敗しました（5行目）。

ここで line 5 をスクリプトにすると、次のようになります。

var values = Sheets.Spreadsheets.Values.get(spreadsheetId, rangeName).values;

と spreadsheetId と rangeName は最初の行で定義されています。

APIキーをどこにもコピーしていないことが問題なのかもしれませんが、どこでどうすればいいのか本当にわかりません。

を使って関数を呼び出すだけです。 = function() .

解決方法は？

のようなカスタム関数でSheets APIを使用する場合、Sheets APIを使用することができます。 =myFunction() をセルに入れると、このようなエラーが発生します。カスタム関数を実行すると ScriptApp.getOAuthToken() は null . これが主な原因だと思います。そして、残念ながら、これが仕様であると思います。この問題を回避するために、2つの回避策を提案したいと思います。

回避策1:

カスタム関数でSpreadsheet Servicesの一部を使用することができます。そのため、これを利用すると var values = Sheets.Spreadsheets.Values.get(spreadsheetId, rangeName).values; . あなたのスクリプトの場合 openById() は使用できません。そこで、スクリプトは次のようになります。

サンプルスクリプトです。

function customFunc() {
  var rangeName = "#####"; // Please set this.

  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var values = sheet.getRange(rangeName).getValues();
  return values;
}

回避策2

Sheets APIを使用する場合、アクセストークンが必要です。ただし、Advanced Google Servicesの内部ではアクセストークンが自動的に使用されるため、Sheets APIのエンドポイントに直接リクエストする必要がある。この場合の問題点として ScriptApp.getOAuthToken() がカスタム関数で実行されます。 null が返されます。これを避けるために、メソッドとして、PropertiesServiceを使って、アクセストークンを直接カスタム関数に渡しています。サンプルフローは以下の通りです。

1. Spreadsheetを開くと、OnOpenトリガでPropertiesServiceにアクセストークンを投入する。
   • これを利用する場合は、OnOpenトリガーを onOpenFunc() をサンプルスクリプトに追加してください。
2. カスタム関数を実行すると、PropertiesServiceが取得したアクセストークンがSheets APIを利用するために使用されます。

これにより、カスタム関数内でSheets APIを使用することができます。

サンプルスクリプトです。

// Please install OnOpen trigger to this function.
function onOpenFunc() {
  PropertiesService.getScriptProperties().setProperty("accessToken", ScriptApp.getOAuthToken());
}

function customFunc() {
  var spreadsheetId = "#####"; // Please set this.
  var rangeName = "#####"; // Please set this.

  var accessToken = PropertiesService.getScriptProperties().getProperty("accessToken");
  var url = "https://sheets.googleapis.com/v4/spreadsheets/" + spreadsheetId + "/values/" + rangeName;
  var res = UrlFetchApp.fetch(url, {headers: {"Authorization": "Bearer " + accessToken}});
  var obj = JSON.parse(res.getContentText());
  var values = obj.values;
  return values;
}

• アクセストークンの有効期限は1時間です。このサンプルスクリプトでは、PropertiesServiceを使用しています。この場合、Spreadsheetを開いてから1時間が経過すると、アクセストークンを使用することができなくなる。アクセストークンを継続的に使用したい場合は、時間駆動型トリガーを用いて更新することも可能です。

注意事項

• Sheets APIを使用する場合は、APIコンソールでSheets APIを有効にしてください。

参考文献

• Google Sheetsのカスタムファンクション
• spreadsheets.values.get
• プロパティサービス

これらの回避策があなたの望むものでなかったなら、申し訳ありません。