A Public Procurement Research Chatbot Using Coze

Basic operation

• Public Procurement Survey is a COZE BOT that provides bidding information posted on the websites of the Japanese government, independent administrative agencies, local governments, etc.
• Enter a keyword and it will reply with the latest bidding information.

Improvements

• We have developed our own plugin "kkj_plugin" to connect to the Public Service Information Portal Site Search API.
• The Public Service Information Portal Site Search API outputs results in XML format, but the COZE plugin does not support XML.
• Therefore, API requests are made with GAS (Public Procurement Survey_gas), and responses from the Public Procurement API are converted from XML format to JSON format using GAS.
• Using Claude 3.5 Sonnet, LLM can now compile large-scale API data in the format desired by users.
• Public Procurement Survey_gas is as follows.

Others

• This COZE BOT was selected as the most creative bot in the Coze July AI Bot Challenge by the COZE official Discord site.
• I am participating in the #AIChatbotWritingContest hosted by Coze and HackerNoon.

  
  

function doGet(e) {
  // クエリパラメータの取得とデフォルト値の設定
  var query = e.parameter.Query;
  if (!query) {
    return ContentService.createTextOutput("Error: 'Query' parameter is required.").setMimeType(ContentService.MimeType.TEXT);
  }
  
  var count = e.parameter.Count || '1';
  var issueDate = e.parameter.CFT_Issue_Date || '2024-04-01/';

  var url = '//www.kkj.go.jp/api/?Query=' + encodeURIComponent(query) + '&Count=' + encodeURIComponent(count) + '&CFT_Issue_Date=' + encodeURIComponent(issueDate);
  var response = UrlFetchApp.fetch(url);
  var xml = response.getContentText();
  
  // XMLをパース
  var document = XmlService.parse(xml);
  var root = document.getRootElement();
  
  // SearchResults要素を取得
  var searchResults = root.getChild('SearchResults');
  if (!searchResults) {
    Logger.log("SearchResults要素が見つかりません。");
    return ContentService.createTextOutput("SearchResults要素が見つかりません。").setMimeType(ContentService.MimeType.TEXT);
  }
  
  // SearchResult要素を取得
  var searchResult = searchResults.getChildren('SearchResult');
  if (searchResult.length === 0) {
    Logger.log("SearchResult要素が見つかりません。");
    return ContentService.createTextOutput("SearchResult要素が見つかりません。").setMimeType(ContentService.MimeType.TEXT);
  }
  
  // データを抽出
  var data = searchResult.map(function(result) {
    var obj = {};
    result.getChildren().forEach(function(child) {
      obj[child.getName()] = child.getText();
    });
    return obj;
  });
  
  // データをログに出力して確認
  Logger.log(JSON.stringify(data));
  
  // スプレッドシートにデータを書き込む
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // データが存在するか確認
  if (data.length > 0 && Object.keys(data[0]).length > 0) {
    // ヘッダーを取得
    var headers = Object.keys(data[0]);
    
    // 最終行の次の行に書き込む
    var lastRow = sheet.getLastRow();
    
    // ヘッダーが無い場合は追加
    if (lastRow === 0) {
      sheet.appendRow(headers);
      lastRow = 1;
    }
    
    // データを書き込む
    data.forEach(function(row) {
      var values = headers.map(function(header) {
        return row[header];
      });
      sheet.appendRow(values);
    });
    
    // 結果をJSON形式で返す
    return ContentService.createTextOutput(JSON.stringify(data)).setMimeType(ContentService.MimeType.JSON);
  } else {
    Logger.log("データが存在しないか、変換に失敗しました。");
    return ContentService.createTextOutput("データが存在しないか、変換に失敗しました。").setMimeType(ContentService.MimeType.TEXT);
  }
}