Google スプレッドシートで、自然言語で質問できる関数を作ってみよう（Google Cloud Vertex AI 後編）

/**
 * スプレッドシートを開いたときにカスタムメニューを表示します。
 *
 * copyright (c) 2024 SoftBank Corp.
 */
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu("プロンプト実験室")
    .addItem('開く', 'show_prompt_labo_')
    .addToUi();
  ui.createMenu('おそうじボットアンサー')
  .addItem('回答生成', 'generate_answer_')
  .addToUi();
}



/**
 * サイドバーを表示します。
 *
 * copyright (c) 2024 SoftBank Corp.
 */
function show_prompt_labo_() {
  const html = HtmlService.createHtmlOutputFromFile('PROMPT_LABO')
    .setTitle('プロンプト実験室');
  SpreadsheetApp.getUi()
    .showSidebar(html);
}



/**
 * 入力情報からテキスト生成関数を実行して、結果をシートに表示します。
 *
 * copyright (c) 2024 SoftBank Corp.
 */
function generate_answer_() {

  const spread_sheet = SpreadsheetApp.getActive()
  const sheet = spread_sheet.getSheetByName("アンサー")

  const question = sheet.getRange(1, 2).getValue()
  const instructions = sheet.getRange(3, 2).getValue()
  const context = sheet.getRange(4, 2).getValue()
  const prompt = `${instructions}\n\n${context}\n\n[質問]:${question}\n[回答]:`

  const answer = GENERATE_TEXT(prompt)
  sheet.getRange(5, 2, 2, 1).setValues([[answer], [prompt]])
}



/**
 * 生成AI APIを使用して、テキストを生成します。
 *
 * @param {"トイレはどこですか"} PROMPT プロンプト {文字列}
 *     プロンプトのテキストです。
 * @return {string}
 *
 * @customfunction
 * copyright (c) 2024 SoftBank Corp.
 */
function GENERATE_TEXT(PROMPT, MODEL = "text-bison") {

  let access_info;
  try {
    access_info = get_access_token_(GCP_CREDS.client_email, GCP_CREDS.private_key)
  }
  catch (e) {
    console.error(e)
    throw "内部エラーが発生しました"
  }
  const access_token = access_info.access_token

  const headers = {
    "Authorization": "Bearer " + access_token
  }

  const API_ENDPOINT = "us-central1-aiplatform.googleapis.com"
  const url = `https://${API_ENDPOINT}/v1/projects/${GCP_CREDS.project_id}/locations/us-central1/publishers/google/models/${MODEL}:predict`

  const payload = {
    instances: [
      {
        prompt: PROMPT
      },
    ],
    parameters: {
      "candidateCount": 1,
      "maxOutputTokens": 1024,
      "temperature": 0.0,
      "topP": 0.1,
      "topK": 1,
      "opt_out": true,
    }
  }

  const params = {
    method: "post",
    contentType: "application/json",
    headers: headers,
    payload: JSON.stringify(payload),
    muteHttpExceptions: true,
  }

  const res = UrlFetchApp.fetch(url, params)
  if (res.getResponseCode() != 200) {
    console.error(res.getContentText())
    throw "内部エラー"
  }

  const res_json = JSON.parse(res.getContentText())
  return res_json.predictions[0].content
}



/**
 * 対象ベクトルと最も関連している情報を複数のテキストから検索します。
 *
 * @param {"[-0.03439481556415558, ...]"} VECTOR 対象ベクトル {文字列}
 *     埋め込みベクトルです。
 * @param {A2:B7} RANGE 検索する範囲 {レンジ}
 *     検索する範囲を２列N行のレンジ形式で指定します。（１列目：テキスト、２列目：ベクトル）
 * @return [[ string, float ]]
 *
 * @customfunction
 * copyright (c) 2024 SoftBank Corp.
 */
function CONTENT_SEARCH(VECTOR, RANGE) {

  let top = {
    similarity: -10,
    index: 0,
  }
  for (let i = 0; i < RANGE.length; i += 1) {

    let s = VECTOR_SIMILARITY(VECTOR, RANGE[i][1])
    if (s > top.similarity) {
      top.similarity = s
      top.index = i
    }
  }

  return [[RANGE[top.index][0], top.similarity]]
}



/**
 * テキスト埋め込みAPIを使用して、テキストに対する埋め込みベクトルを取得します。
 * 結果は文字列化されたベクトルです。
 *
 * @param {"トイレはどこですか"} TEXT テキスト {文字列} 
 *     埋め込み対象のテキストです。
 * @return {string}
 * 
 * @customfunction
 * copyright (c) 2024 SoftBank Corp.
 */
function TEXT_EMBEDDINGS_MULTILINGUAL(TEXT) {

  if ( TEXT === "" ) {
    throw "テキストが空白です"
  }

  const url = `https://us-central1-aiplatform.googleapis.com/v1/projects/${GCP_CREDS.project_id}/locations/us-central1/publishers/google/models/textembedding-gecko-multilingual:predict`

  let access_info;
  try {
    access_info = get_access_token_(GCP_CREDS.client_email, GCP_CREDS.private_key)
  }
  catch (e) {
    console.error(e)
    throw "内部エラーが発生しました"
  }
  const access_token = access_info.access_token

  const headers = {
    "Authorization": "Bearer " + access_token
  }

  const payload = {
    "instances": [
      {
        "content": TEXT,
      }
    ],
    "parameters": {
      "opt_out": true,
    }
  }

  const params = {
    method: "POST",
    headers: headers,
    contentType: "application/json",
    payload: JSON.stringify(payload),
    muteHttpExceptions: true,
  }

  const res = UrlFetchApp.fetch(url, params)
  const res_json = JSON.parse(res.getContentText())

  if ("error" in res_json) {
    console.error(res_json.error)
    throw "内部エラーが発生しました"
  }

  const vector = res_json.predictions[0].embeddings.values
  return JSON.stringify(vector)
}