chatGPTのGPTsでgoogleスプレッドシート連携、read、append、updateを１つでやってみた

久しぶりにAI技術的な話。

openAIのGPTsは、ユーザーが独自にカスタマイズしたAIを作って公開できる。
ChatGPT (openai.com)

で、googleスプレッドシートと連携して、取得と更新ができる汎用的なスクリプトを作ってみた。
一つのActionに2つのdoGetができず、複数アクションにするとgoogleスプレッドシートが1つまでで謎制限がかかっていたので今回は1つで取得と交信を行う。
※別々でスマートにできるやり方あったら教えて……

①googleスプレッドシートのスクリプト

let sheetId = "※スプレッドシートのID※"
let sheetName = "シート1"
function doGet(e){
  let updateCSVData = "";
  let updateOperations = ""; //追加(append)or更新(update)or読込(空白)
  try {
    if( e != null && e.parameter != null ){
      if( e.parameter["sheetId"] != null && e.parameter["sheetId"].length > 0 ){
        sheetId = e.parameter["sheetId"]
      }
      if( e.parameter["sheetName"] != null && e.parameter["sheetName"].length > 0 ){
        sheetName = e.parameter["sheetName"]
      }
      if( e.parameter["updateCSVData"] != null && e.parameter["updateCSVData"].length > 0 ){
        updateCSVData = e.parameter["updateCSVData"]
      }
      if( e.parameter["updateOperations"] != null && e.parameter["updateOperations"].length > 0 ){
        updateOperations = e.parameter["updateOperations"]
      }
    }
    let mySpreadSheet = SpreadsheetApp.openById(sheetId);
    let sheet = mySpreadSheet.getSheetByName(sheetName);

    //updateOperations"update"の場合はcsvで全て更新し、"append"の場合は末端にcsvを追加する
    if( updateCSVData != "" && ( updateOperations == "update" || updateOperations == "append" ) ){ 
      let startRow = 1;
      if( updateOperations == "append" ){
        //startRow = mySpreadSheet.getLastRow();
        startRow = sheet.getRange(sheet.getMaxRows(),1).getNextDataCell(SpreadsheetApp.Direction.UP).getRowIndex() + 1;
      } else { //update(全更新)
        if( sheet == null ){ //シートがない場合は新規作成'
          sheet = mySpreadSheet.insertSheet();
          sheet.setName(sheetName);
        }
      }
      var rows = updateCSVData.split("\n");
      for (var iRow = 0; iRow < rows.length; iRow++) {
        var rowArray = rows[iRow].split(",");
        for( var iCol = 0 ; iCol < rowArray.length ; iCol++ ){
          sheet.getRange( iRow+startRow, iCol+1 ).setValue( rowArray[iCol] )
        }
      }
    }

    if( sheet == null ){
      return ContentService.createTextOutput("Error: シートなし sheetId='" + sheetId +"', sheetName='" +sheetName + "'");
    }
    var values = sheet.getDataRange().getValues();
    var returnCSVText = values.join('\n');

    //console.log(csvText);

    return ContentService.createTextOutput(
      JSON.stringify({
        'csvData' : returnCSVText
      })
    ).setMimeType(ContentService.MimeType.JSON)
  } catch (error) {
    // エラー発生時の処理
    console.error("エラー: " + error.message);
    return ContentService.createTextOutput("Error: " + error.message);
  }
}

②GPTsのActions

{
  "openapi": "3.1.0",
  "info": {
    "title": "CSVデータ連携",
    "description": "googleスプレッドシートからcsvの取得・更新を行う",
    "version": "v1.0.0"
  },
  "servers": [
    {
      "url": "https://script.google.com"
    }
  ],
  "paths": {
    "/macros/s/※デプロイしたID※/exec": {
      "get": {
        "description": "SheetからCSVを取得",
        "operationId": "GetCSVData",
        "parameters": [
          {
            "name": "sheetId",
            "in": "query",
            "description": "取得するスプレッドシートのID",
            "required": false,
            "schema": {
              "type": "string"
            }
          },
          {
            "name": "sheetName",
            "in": "query",
            "description": "取得するシートの名前",
            "required": false,
            "schema": {
              "type": "string"
            }
          },
          {
            "name": "updateCSVData",
            "in": "query",
            "description": "更新するCSVデータ",
            "required": false,
            "schema": {
              "type": "string"
            }
          },
          {
            "name": "updateOperations",
            "in": "query",
            "description": "更新設定(update,append,read)",
            "required": false,
            "schema": {
              "type": "string"
            }
          }
        ],
        "deprecated": false
      }
    }
  },
  "components": {
    "schemas": {
      "NameResponse": {
        "type": "object",
        "properties": {
          "csvData": {
            "type": "string"
          }
        }
      }
    }
  }
}

③GPTsのInstructions

最初にGetCSVDataを実行して、結果のcsvDataのテキスト配列の通りに必ずプロンプトを実行してください。
sheetId='※デプロイＩＤ※'
sheetName="シート1"
updateOperations="read"
必ずcsvDataのプロンプト一つ一つを、ユーザーに提示しながら対話してください。

で、①シート情報取得(read)、②シート情報追記(append)、③シート情報上書き(update)のやり方は以下

↓①読込
sheetId='※デプロイしたID※'
sheetName="シート1"
updateOperations="read"
上記設定をして、GetCSVDataを呼び出してcsvDataを表示してください


↓②追記
sheetId='※デプロイしたID※'
sheetName="追記シート"
updateOperations="append"
updateCSVData="A,B,C\nD,E\nF\nG,H,I"
上記設定をして、GetCSVDataを呼び出してください


↓③更新
sheetId='※デプロイしたID※'
sheetName="新規作成シート"
updateOperations="update"
updateCSVData="a,b,c\nd,e\nf\ng,h,i"
上記設定をして、GetCSVDataを呼び出してください

今まではgoogleスプレッドシートのスクリプト側でIdを入れていたけど、シートの名前やIdをGPTs側で管理することで、扱いやすくなった。

①の１行目の「sheetId」に自分のスプレッドシートのIdを入れた場合は、GPTs側はsheetIdを空白（初期値）の状態でも呼び出せる。

初期値は「シート1」の値を取得するので、取得したA1～Axまでのスクリプトを順番に実行するように指示すれば、GPTsはその中のプロンプトを実行してくれる。

実行して
GPT
[debug] Calling HTTP endpoint
{
  "domain": "script.google.com",
  "method": "get",
  "path": "/macros/s/ｘｘｘｘ/exec",
  "operation": "GetCSVData",
  "operation_hash": "690413733272552a8c9dd99ce68d2e9a21df4234",
  "is_consequential": false,
  "params": {
    "sheetId": "ｘｘｘｘｘｘ",
    "sheetName": "シート1",
    "updateOperations": "read"
  }
}
[debug] Response received
{
  "response_data": {
    "csvData": "リンゴを英語で答えて\n織田信長の歴史を教えて\n234232x21412は？"
  },
  "status_code": 200,
  "action_id": "g-7f0ca072df15a7065b9f402dd4f6cc8d20850b6f"
}
script.google.comと通信しました
プロンプトを受け取りました。以下の質問に沿って対話を進めていきます。

リンゴを英語で答えて
織田信長の歴史を教えて
234232x21412は？
それでは、最初の質問から始めましょう。リンゴを英語で答えてください。

User
教えて
GPT
リンゴは英語で "Apple" と言います。

次に、織田信長の歴史について教えてほしいとのことですが、どのような点に焦点を当てて解説を希望されますか？たとえば、生涯、功績、または特定のエピソードなどがあります。

User
時系列で教えて
GPT
織田信長は、戦国時代を代表する武将の一人で、日本の歴史において重要な役割を果たしました。ここでは、彼の生涯を簡単に時系列でご紹介します。

1534年：織田信長は尾張国（現在の愛知県）に生まれました。
1551年：父・織田信秀が亡くなり、家督を継ぎます。
1560年：桶狭間の戦いで今川義元を破り、名将としての名声を高めます。
1568年：足利義昭を奉じて上洛し、室町幕府を再興させるものの、後に義昭を追放します。
1570年〜1573年：浅井長政・朝倉義景との戦い（姉川の戦いなど）、そして石山本願寺との戦いを経て、近畿地方の支配を確立します。
1575年：長篠の戦いで武田信玄の後を継いだ武田勝頼を破り、鉄砲隊の有効性を示します。
1582年：本能寺の変で明智光秀によって謀反を受け、京都の本能寺で命を落とします。
信長の死後、彼の野望は豊臣秀吉とその後徳川家康によって受け継がれ、日本は統一への道を歩み始めます。信長は独自の政策や戦術で多くの地域を制圧し、日本の戦国時代の終焉と安土桃山時代の始まりを告げる重要な役割を果たしました。

次に、"234232x21412は？"について答えます。これは計算問題ですね。少々お待ちください。

計算を行うと、
234232
×
21412
234232×21412 は、
5
,
015
,
946
,
304
5,015,946,304 になります。

これで、ご提供いただいた質問に全てお答えしました。他にも知りたいことがあれば、お気軽にお尋ねください。

直接InstructionやKnowledgeでプロンプトを指示しなくても、外的ファイルで読み書きできるので便利である。
特にスプレッドシートはセルによる表管理が得意なので、テキスト出力では分かりづらい回答をスプレッドシート側に更新することで柔軟に対応できる。

さらに、回答した内容をシートに追記(append)することで、疑似的に学習機能を持たせたり、過去の実績を統計で分析することもできるようになった。

内容を確認したい場合は、「＞[debug]」を押す。

[debug] Calling HTTP endpoint
{
  "domain": "script.google.com",
  "method": "get",
  "path": "/macros/s/ｘｘｘｘ/exec",
  "operation": "GetCSVData",
  "operation_hash": "690413733272552a8c9dd99ce68d2e9a21df4234",
  "is_consequential": false,
  "params": {
    "sheetId": "ｙｙｙｙｙｙ",
    "sheetName": "シート1",
    "updateOperations": "read"
  }
}
[debug] Response received
{
  "response_data": {
    "csvData": "リンゴを英語で答えて\n織田信長の歴史を教えて\n234232x21412は？"
  },
  "status_code": 200,
  "action_id": "g-7f0ca072df15a7065b9f402dd4f6cc8d20850b6f"
}

Calling HTTPの"params"の項目で、3項目をGPTsが渡しているのが分かる。
次にResponseのresponse_dataで、スプレッドシートの内容と一致していることを確認する。

受け取ったGPTs側は「"リンゴを英語で答えて\n織田信長の歴史を教えて\n234232x21412は？"」を読み取って、順番に処理をするのである。

当然、スプレッドシート側を修正すれば、次回以降の結果も変わる。

今回はsheetId,sheetName,updateCSVData,updateOperationsの4項目を渡したが、シート情報を取得するだけならsheetNameだけで良い。

書き込み情報が多すぎる場合は、タイムアウトになる場合があるので、書き込むプロンプトは短めにしよう。

個人的な話になるが、色々なクライアントさんからGPTsの依頼が来ているので最近楽しいのである！

あなたに合ったGPTsを提案して作ります AIを全く知らなくても昔の夢が叶えられる宝石箱 (coconala.com)

GPTsに興味がある人は、ココナラ出品から相談してみてね（ダイマ）。