Googleスプレッドシートと連携！自作GPTsで開く新しい可能性

自作GPTs

こんにちは。

自作GPTｓ第１回はGPTを用いてスプレッドシートとの連携してみました。
今回はこちらの動画

【有料級】gpts actionのapiでスプレッドシートに連携させてプロンプト連鎖させる方法【カスタムChatGPT】
今回の動画では、ChatGPTのMy GPTs（カスタムGPT）機能のActionでAPIを使ってスプレドシートを連携させてスプシ内のデータを利用する方法を紹介。そして応用技術としてプロンプトでプロンプトを呼び出して連鎖させるエージェントGPTsの作成方法も紹介します。※動画内でコードの場所は伝えてます。▼Dis...
www.youtube.com

と、こちらの記事

https://qiita.com/iconss/items/3db0761bb4cfe1422a6e

を参考に作成してみました。

 

Contents
  1. 作り方
  2. 制作過程の失敗談

作り方

①Googleスプレッドシートの準備とAppScript

まずはスプレッドシートを用意しました。
スプレッドシートには４ケタの番号とプロンプトを書いています。

拡張機能からApps Scriptを選び
下記のコードをコピー＆ペーストします。

function doGet(e) {
  // リクエストからパラメータを取得
  console.log("start GPTs Custom Actions API test");
  console.log("e: " + JSON.stringify(e));
  var searchQuery = e.parameter.id;
  console.log("e.parameter.id: " + e.parameter.id);
  // 4桁の数字かどうかをチェック
  if (!searchQuery || !/^\d{4}$/.test(searchQuery)) {
    console.log("bad format: " + searchQuery);
    return ContentService.createTextOutput(
      JSON.stringify({ "error": "Invalid request. Please provide a 4-digit number." })
    ).setMimeType(ContentService.MimeType.JSON);
  }
  // スプレッドシートの準備
  var sheet = SpreadsheetApp.openById('ここにスプレッドシートのIDを貼り付ける').getSheetByName('シート1');
  var data = sheet.getDataRange().getValues();
  console.log("format OK");
  // スプレッドシートを検索
  for (var i = 1; i < data.length; i++) {  // 2行目から開始
    if (data[i][0].toString() === searchQuery) {
      console.log("hit: " + i);
      console.log("prompt: " + data[i][1]);
      // 見つかった場合、JSONとして返す
      return ContentService.createTextOutput(
        JSON.stringify({ "prompt": data[i][1]})
      ).setMimeType(ContentService.MimeType.JSON);
    }
  }
  console.log("not found");
  // 一致するデータが見つからない場合
  return ContentService.createTextOutput(
    JSON.stringify({ "error": "No data found for the provided number." })
  ).setMimeType(ContentService.MimeType.JSON);
}

 

一部変えましたが、ほとんどリンク先のブログから拝借いたしました・・・
その後のやり方もリンク先のブログの通り
コードを入力して、デプロイし、デプロイ後の
URLをコピーして完了をクリック

次にGPTを作っていきます。

 

②Create GPT

ExploreGPT→Createをクリックし、NewGPTを作成

Configure画面に移り下記の通り入力していきます。

Configure
Name spreadsheets×GPT
Description スプレッドシートに書かれたプロンプトを実行する
Instructions ユーザーが4桁の数字を入力したら、その数字をIDとして使用し、指定されたIDに基づいてプロンプトデータを返す
Google Apps Scriptウェブアプリにリクエストを送信してください。
返されたプロンプトデータは、次のユーザーへの応答やアクションの実行に利用されます。
https://script.google.com/macros/s/…（デプロイ時のURL）
Conversation starters 1001
1002
1003
1004
Knowledge ー
Capabilities ☐Web Browsing
☑DALL·E Image Generation
☐Code Interpreter
Actions script.google.com
Additional Settings no check
Edit actions
Authentication None
Schema {
“openapi”: “3.1.0”,
“info”: {
“title”: “Get prompt data”,
“description”: “Get prompt by id”,
“version”: “v1.0.0”
},
“servers”: [
{
“url”: “https://script.google.com”
}
],
“paths”: {
“/macros/s/…（デプロイ時のURL）“: {
“get”: {
“description”: “Get prompt by id”,
“operationId”: “GetPromptByid”,
“parameters”: [
{
“name”: “id”,
“in”: “query”,
“description”: “ID No.”,
“required”: true,
“schema”: {
“type”: “string”
}
}
],
“deprecated”: false
}
}
},
“components”: {
“schemas”: {
“NameResponse”: {
“type”: “object”,
“properties”: {
“prompt”: {
“type”: “string”
}
}
}
}
}
}
Available actions GetPromptByid GET /macros/s/…（デプロイ時のURL）
Privacy policy ー

Available actionsは自動で入ると思います。

そしてこれらを基に出力した結果がこちらです。

上手く生成できました。

・・・しかし、ここに行くまでに数々の失敗がありましたので、次の章で紹介します。

 

制作過程の失敗談

上記のようにやれば私はうまく行ったのですが、無事画像が生成されるまでのいくつも失敗しました・・・（いかんせん初心者なもので・・・）

殆どコピペでしたが制作過程をツラツラと書いてまいります・・・

まどろっこしい文章なので、結論どうやったの？？て方向けに最初に言っておきますが、

何か問題が発生すれば『ChatGPT聞きまくり戦法』で乗り切りました（笑）

以下は具体的な方法です。初心者以外は、あまり参考にはならないかと思いますので

読み飛ばしていただければと思います・・・

初のGPTｓ制作で、まずは動画をみて作成しました。

動画内でこのブログ記事をほとんどコピペしたとの話だったので試してみます。

ブログ記事を基に入力して、試したところ早速エラーが出ました。

ブログ記事ではスプレッドシートの苗字と名前をGPTに渡すことになっていたので、コード部分がすべて『name』になっていました・・・（普通に考えれば当然のことですよね・・・）

今回は名前ではなくプロンプトを渡すので

『name』の部分をすべて『prompt』に置き換えます。

・・・まだエラーが出ます。

自分の足りない頭ではどうしようもないので、ChatGPTにエラーが出たコードをぶち込んでみます。

まずはAppScriptのコードをぶちこんでみました。

屯田平
屯田平
function doGet(e) { // リクエストからパラメータを取得 console.log(“start GPTs Custom Actions API test”); console.log(“e: ” + JSON.stringify(e)); var searchQuery = e.parameter.id; console.log(“e.parameter.id: ” + e.parameter.id); // 4桁の数字かどうかをチェック if (!searchQuery || !/^\d{4}$/.test(searchQuery)) { console.log(“bad format: ” + searchQuery); return ContentService.createTextOutput( JSON.stringify({ “error”: “Invalid request. Please provide a 4-digit number.” }) ).setMimeType(ContentService.MimeType.JSON); } // スプレッドシートの準備 var sheet = SpreadsheetApp.openById(‘d/スプレッドシートのID/edit#gid=0’).getSheetByName(‘シート1’); var data = sheet.getDataRange().getValues(); console.log(“format OK”); // スプレッドシートを検索 for (var i = 1; i < data.length; i++) { // 2行目から開始 if (data[i][0].toString() === searchQuery) { console.log(“hit: ” + i); console.log(“prompt: ” + data[i][1]); // 見つかった場合、JSONとして返す return ContentService.createTextOutput( JSON.stringify({ “prompt”: data[i][1]}) ).setMimeType(ContentService.MimeType.JSON); } } console.log(“not found”); // 一致するデータが見つからない場合 return ContentService.createTextOutput( JSON.stringify({ “error”: “No data found for the provided number.” }) ).setMimeType(ContentService.MimeType.JSON); }
ChatGPT
ChatGPT

このコードは、HTTP GETリクエストに応答するGoogle Apps Scriptウェブアプリの一部として設計されています。リクエストに含まれるパラメータを処理し、このパラメータが4桁の数字であることを期待し、Googleスプレッドシートでこの数字を検索し、検索結果に基づいて応答を返します。動作の概要は以下の通りです：

  1. ログ開始：テストの開始と受け取ったパラメータをデバッグ目的でログに記録します。
  2. パラメータ検証：受け取ったパラメータidが存在し、4桁の数字に一致するかどうかをチェックします。一致しない場合は、JSON形式でエラーメッセージを返します。
  3. スプレッドシートの準備：スクリプトはスプレッドシートのIDを使用してGoogleスプレッドシートを開こうとし、名前で特定のシートを選択します。これにはスプレッドシートの事前設定と正しい権限が必要です。
  4. データ検索：スクリプトはシートからすべてのデータを読み取り、2行目から（最初の行はヘッダーとして扱います）それを反復処理します。各行の最初の列を提供されたidパラメータと比較します。
  5. 一致時の応答：一致が見つかった場合、ヒットをログに記録し、対応するプロンプトを2列目から抽出し、このプロンプトをJSON形式で返します。
  6. 不一致時の応答：シート全体をスキャンした後で一致するデータが見つからない場合、この結果をログに記録し、提供された番号に対するデータが見つからないことを示すエラーメッセージを返します。

重要な考慮事項と修正点：

  • スプレッドシートIDの抽出：提供されたスクリプトスニペットは、SpreadsheetApp.openById()でURLのような文字列を使用してスプレッドシートを開こうとします。これは正しくありません。スプレッドシートの実際のIDのみを渡す必要があります。たとえば、スプレッドシートのURLがhttps://docs.google.com/spreadsheets/d/XX-XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX-XXXXXXXXX/edit#gid=0 の場合、IDはXX-XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX-XXXXXXXXXです。
  • 権限：スクリプトはGoogleスプレッドシートへのアクセス権限を必要とします。ウェブアプリとしてデプロイする際には、スプレッドシートにアクセスするための適切な権限が設定されていることを確認する必要があります。
  • レスポンスオブジェクトの作成：ContentService.createTextOutput().setMimeType()はレスポンスを作成するために正しく使用されています。この方法は、レスポンスがJSONとして適切にフォーマットされることを保証し、クライアントが解析しやすくなります

修正し、念のためChatGPTにも見てもらいます。

屯田平
屯田平
下記の通り修正しました。他に修正点はありますか？ function doGet(e) { // リクエストからパラメータを取得 console.log(“start GPTs Custom Actions API test”); console.log(“e: ” + JSON.stringify(e)); var searchQuery = e.parameter.id; console.log(“e.parameter.id: ” + e.parameter.id); // 4桁の数字かどうかをチェック if (!searchQuery || !/^\d{4}$/.test(searchQuery)) { console.log(“bad format: ” + searchQuery); return ContentService.createTextOutput( JSON.stringify({ “error”: “Invalid request. Please provide a 4-digit number.” }) ).setMimeType(ContentService.MimeType.JSON); } // スプレッドシートの準備 var sheet = SpreadsheetApp.openById(‘d/XX-XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX-XXXXXXXXX’).getSheetByName(‘シート1’); var data = sheet.getDataRange().getValues(); console.log(“format OK”); // スプレッドシートを検索 for (var i = 1; i < data.length; i++) { // 2行目から開始 if (data[i][0].toString() === searchQuery) { console.log(“hit: ” + i); console.log(“prompt: ” + data[i][1]); // 見つかった場合、JSONとして返す return ContentService.createTextOutput( JSON.stringify({ “prompt”: data[i][1]}) ).setMimeType(ContentService.MimeType.JSON); } } console.log(“not found”); // 一致するデータが見つからない場合 return ContentService.createTextOutput( JSON.stringify({ “error”: “No data found for the provided number.” }) ).setMimeType(ContentService.MimeType.JSON); }
ChatGPT
ChatGPT

修正箇所は適切に直されており、スプレッドシートのIDを正確に使用しています。これにより、スプレッドシートにアクセスする際の問題が解決されました。ただし、スクリプト全体を通して、現在の実装に基づいて他に考慮すべき点がいくつかあります：

  1. ～～～～

これらの点を考慮することで、より堅牢で使いやすいウェブアプリケーションを作成できます。

ＧPTパイセンからOKが出ました。（他に何か言ってますが、無視です。満点ではなく合格点を目指します。）
デプロイのバージョンを更新し、再び試してみます・・・

・・・まだ、エラーが出ます。

懲りずにGPTパイセンに聞きまくり戦法です。

屯田平
屯田平

上記コードをデプロイし、GPTｓのAction SchemaとInstructionに下記のコードを入れましたがエラーが発生しました。 #Action Schema { “openapi”: “3.1.0”, “info”: { “title”: “Get prompt data”, “description”: “Get prompt by id”, “version”: “v1.0.0” }, “servers”: [ { “url”: “https://script.google.com” } ], “paths”: { “/macros/s/（デプロイしたURL）/exec”: { “get”: { “description”: “Get prompt by id”, “operationId”: “GetPromptByid”, “parameters”: [ { “name”: “id”, “in”: “query”, “description”: “ID No.”, “required”: true, “schema”: { “type”: “string” } } ], “deprecated”: false } } }, “components”: { “schemas”: { “NameResponse”: { “type”: “object”, “properties”: { “prompt”: { “type”: “string” } } } } } #Instruction。

ChatGPT
ChatGPT

Action SchemaとInstructionを設定し、Google Apps ScriptをデプロイしてGPTのCustom Actions APIとして使用しようとする際にエラーが発生する原因は、様々です。提示された情報に基づいて、いくつかの一般的なトラブルシューティングのポイントを挙げます。

正しいエンドポイントURL:

Google Apps Scriptをデプロイした後に生成されるURLを正確にserversセクションのurlに指定しているか確認してください。提示されたurlフィールドは一般的な形式であり、具体的なスクリプトのURLではありません。
正しい形式はhttps://script.google.com/macros/s/…で始まり、スクリプトのデプロイ時に提供される具体的なURLです。

パラメータの型:

Action Schemaでパラメータidの型をstringとして定義していますが、スクリプトでは4桁の数字のみを受け入れるように検証しています。これは矛盾しているわけではありませんが、APIを使用する際の説明やドキュメントには、このパラメータが数字のみであるべきだと明確に記載すると良いでしょう。

HTTPS要件:

GPTのCustom ActionsではHTTPSを通じた通信が必須です。Google Apps ScriptのウェブアプリもHTTPSを使用しますが、安全な通信が確保されていることを再確認してください。

CORS（Cross-Origin Resource Sharing）の問題:

ブラウザベースのクライアントからGoogle Apps Scriptウェブアプリにリクエストを送る場合、CORSポリシーによってエラーが発生する可能性があります。しかし、GPTのCustom Actionsからのリクエストでは通常、この問題は発生しません。

Instructionが不足:

Instruction部分が空です。Custom Actionsを実装する際には、具体的な命令が必要です。例えば、”指定されたIDに基づいてプロンプトデータを取得します。”のように、何をするためのアクションなのかを明確に記述する必要があります。

レスポンスのスキーマ:

レスポンスのスキーマが定義されていますが、エラーケースについての記述がありません。成功したレスポンスだけでなく、エラーが発生した場合のレスポンス構造も定義すると良いでしょう。

これらの点を確認・修正後も問題が解決しない場合、具体的なエラーメッセージや挙動を詳細に説明していただけると、さらに具体的な解決策を提供することが可能です。

Instructionsを入力する前に、送っちゃいました・・・笑
気を取り直してInstructionsを送ってみます。

屯田平
屯田平
Instructionsの記述を忘れていました。 下記の通りです。
#Instructions
ユーザーから4ケタの数字を入力されたら、 こちらのGoogleスプレッドシートから番号に対応したプロンプトを実行してください。 https://script.google.com/macros/s/デプロイしたURL/exec
ChatGPT
ChatGPT

指示の追加ありがとうございます。これで、Action SchemaとInstructionが完全な形で提示されました。しかし、提供されたInstructionsのテキストがGoogle Apps Scriptの実行URLを示しているように見えますが、Instructionsの内容としては不適切です。Instructionsには、ユーザーから受け取った入力に基づいて、どのような処理を行うべきかの説明が含まれるべきです。このURLは、実際にはAPIのエンドポイントとしてAction Schemaに記述されるべきものです。

Instructionsの正しい記述は、実際にユーザーのアクションやAPIの使用方法に関する指示を提供するものでなければなりません。以下は、Instructionsに適した記述の一例です：

# Instructions
ユーザーが4桁の数字を入力したら、その数字をIDとして使用し、
指定されたIDに基づいてプロンプトデータを返すGoogle Apps Scriptウェブアプリに
リクエストを送信してください。返されたプロンプトデータは、
次のユーザーへの応答やアクションの実行に利用されます。

この記述は、ユーザーの入力（この場合は4桁の数字）をどのように処理し、どのようなアウトプットを期待するかを明確にしています。また、このプロセスがGoogle Apps Scriptウェブアプリを介して行われることを指摘しています。

エラーが発生する原因を特定するためには、具体的なエラーメッセージや発生状況を確認する必要があります。エラーメッセージが提供されていない場合は、以下の点を再確認してください：

  • Google Apps Scriptのウェブアプリが正しくデプロイされ、外部からのリクエストを受け付けるようになっているか。
  • Action Schemaに記述されたURLが、デプロイされたウェブアプリの正確なURLであるか。
  • 入力パラメータやレスポンスの形式が、定義したSchemaに適合しているか。

これらの基本的なチェックを行い、設定や実装に問題がないかを確認してください。

なぜか御礼を言われちゃいました（笑）
それにしても『Google Apps Scriptの実行URL』を書くのが正解という口ぶりですね。
ブログの方はスプレッドシートのURLだった気がしたのですが、ここはGPTパイセンを信じてみます。

・・・まだ、エラーが出ました。

GPTパイセンからはエラーメッセージも送ってくれとのことでしたので今度はエラーメッセージを送ってみます。

屯田平
屯田平
エラーメッセージと入力した内容は以下の通りです。
You:1001
GPTs: [debug] Calling HTTP endpoint [debug] Calling HTTP endpoint [debug] Response received Error talking to It seems there was an error with the request to retrieve the prompt data using ID 1001. The error mentions an unexpected issue with accessing the method or property openById on the SpreadsheetApp object. This could be due to issues with the spreadsheet or the script itself. If possible, please check the script and the spreadsheet for any issues or restrictions, and then try again.
ChatGPT
ChatGPT

エラーメッセージから分かることは、SpreadsheetAppオブジェクト上のopenByIdメソッドまたはプロパティへのアクセス時に予期しない問題が発生したということです。このようなエラーが生じる原因として考えられるのは、主に以下のポイントです。

  1. スプレッドシートのIDが不正:

    • openByIdメソッドに渡されるスプレッドシートのIDが正しいものであるか再確認してください。IDはスプレッドシートのURLから取得できる部分で、https://docs.google.com/spreadsheets/d/{スプレッドシートID}/edit#gid=0の形式で提供されるURL内の{スプレッドシートID}に該当します。

  2. ～～～～

・・・ああ、そういうことか

AppScriptを見直してみると、スプレッドシートのURLに余計な/d/{スプレッドシートID}が混じってました。

というか、最初のAppScriptのコード修正の時にコード転記を間違えていました。
（じゃあなんでそのあとパイセンはOK出したんだろう・・・）

とりあえず修正して、バージョンアップ、デプロイし直して、ＧＰＴsを動かしてみます。

・・・ようやく、コアラの画像が生成されました！

まとめ

今回、Googleスプレッドシートと自作GPTsの連携に成功しました。今後もGPTs制作で何か問題があれば、『ChatGPT聞きまくり戦法』で乗り切るのが有効そうです。また今回の経験で、Google Apps Scriptの活用法やGPTsの設定技術に関する貴重な知見を得ることができました。特に、スプレッドシートからテキストデータを取得してGPTsに渡す方法の実装は、今後色々と活用できればと思います。さらに、Googleのさまざまなサービスとの連携ポテンシャルを探ることで、GPTsの応用範囲を広げていきたいです。

コメント

タイトルとURLをコピーしました