🎃

Google スプレッドシートのなかで生成AI（Gemini）に働いてもらう

2025/02/28に公開

GAS

Gemini

tech

課題

スプレッドシートのなかで優秀な生成AIが動いてくれたら人件費が浮いたり、ミスが減ったり、いろいろ入力業務が捗る、というプチDXの一環。

注意点

利用規約を、ぜったいにチェ〜〜〜〜〜〜ック！！！！

手続き前の手続き

Geminiに登録してAPI KEYを入手する
Google Apps Scriptを使えるようにする
プロパティにAPI KEYを登録する

1. Geminiに登録してAPI KEYを入手する

Geminiのサイトにアクセスして、Google AI Studio で Gemini API キーを取得する というボタンから登録すると、Google AI Studioというところに連れて行かれるので、「APIキーを作成」や「GET API KEY」をクリックすると、API KEYが画面に表示されて、正式に与えられます。

↑こちらをタップしてください

＜参考スクリーンショット＞

いったん、メモ帳とかにメモっておくとよいです。あとあとコピペするので。

AIzaSyPptmMTdj9sqHiTjdkdlUVyfzEekdOs9AI

上記はAPI KEYの嘘サンプルです。

2. Google Apps Scriptを使えるようにする

スプレッドシートのメニューバーから「拡張機能」→「Apps Script」を選択

3. プロパティにAPI KEYを登録する

Apps Scriptにアクセスすると、エディターのような見た目の画面がスタートします、が、左サイドのバーティクルバーから⚙️のマークをタップすると、設定画面になります。

いちばん下までいくと「スクリプトプロパティ」というボタンがあるので、タップすると「プロパティー」と「値」の入力欄が出現します。

key-valueの関係になっており、「プロパティー」にGEMINI_API_KEYという文字、「値」に先ほどGoogle AI STUDIOで入手したAPI KEY（AIzaSyPptmMTdj9sqHiTjdkdlUVyfzEekdOs9AIみたいなやつ）を入力して、保存してください。

漏洩してはいけないものなので、こういう保管方法になります。

手続き

ようやく本番のほうです。またエディターの画面に戻ってください。左サイドのバーティクルバーの＜＞みたいなアイコンです。

プロンプトをがんばって考える
GeminiのAPIキーをプロパティーサービスで取得する
Gemini APIのエンドポイント準備
ペイロードをつくって送信＆生成物を加工
スプレッドシートとの調整
GASの関数を発動させる

1. プロンプトをがんばって考える

プロンプトは「呪文」と言われるだけあって奥が深く、その深淵は誰もまだ見たことがないとのことです。とはいえ、公式サイトで言われていることは最重要でしょう。

「指示」と「目的」はマストで、それから「背景」「制約」「トーン」「ペルソナ」「ショット（サンプル）」あたりでしょうか。

こちらが公式サイトのプロンプト指南書。たすかります。

script.gs

/**
 * バカ売れプロンプトをつくる
 * @param {string} data
 * @return {string} 
*/
function makePrompt(data){
  return `以下のデータに基づいて、最強のキャッチコピーを10個考えてください。日本のやる気のある若者にバカ売れしたいです。バカ売れしたあかつきには、Geminiにも印税の1割を与えるので本気だしてください。水平思考的に、多角的なアイデアをください。
    【サンプル】
    聞いて驚くな。実家は意外とやることないぞ。（これはNetflixです）
    【出力注意点】
    キャッチコピーだけ出力してください。解説や余計な言葉は不要です。
    【データ】
    ${data}
    `
}

2. GeminiのAPIキーをプロパティーサービスで取得する

まずは先ほどプロパティに仕舞っておいた大切なAPI KEYを呼び出します。

getPropertyByName()というメソッドをつくりました。中では、PropertiesServiceというプロパティとやりとりするサービスがあり、そこからget()させてもらっています。

script.gs

/**
 * プロパティサービスからデータを取得する関数
 * @param {string} propertyName
 * @return {?string}
*/
function getPropertyByName(propertyName){
   return PropertiesService.getScriptProperties().getProperty(propertyName);
}

今回はプロパティーオブジェクトを関数ブロックのなかでポイ捨てしていますが、案件によっては、

another.gs - 使いまわし改善案

function getProperties(){
    return PropertiesService.getScriptProperties();
}

function getPropertyByName(properties,propertyName){
    return properties.getProperty(propertyName);
}

いったんこのようにプロパティーオブジェクトを使い回せるようにしたほうがいいこともあると思います。適宜よしなにしてくださいまし。

3. Gemini APIのエンドポイント準備

エンドポイントはこちらから。エンドポイントには「モデル名」と「キー名」を添付する必要があるので、モデル名を調べます。

今回はたまたまタイミングよく「Gemini 2.0 Flash 試験運用版モデル」が出ていたので、ふだんのモデルとすこしちがって、試験運用版モデルのほうから取得しています。

gemini-1.5-flash:latestがいまのところ通常の選択で、gemini-2.0-flash-expが試験運用版のモデル名です。けっこう実力の差があるので、よっぽど簡単なタスクじゃなければ2.0のほうを指定するのをおすすめします。

script.gs

/**
 * geminiのAPIを叩くURL
 * 
 * @param {string} key
 * @param {string} model ["gemini-2.0-flash-exp","gemini-1.5-flash:latest"]
 * @return {string} 
*/
function makeGeminiEndpoint(key,model="gemini-2.0-flash-exp"){
  return `https://generativelanguage.googleapis.com/v1beta/models/${model}:generateContent?key=${key}`;
}

4. ペイロードをつくって送信＆生成物を加工

送信データや受信データのことを「ペイロード（payload）」と呼びますが、対価の発生する積載物や貨物のことのようですね。

script.gs


/**
 * @param {prompt}
 * @param {apiUrl}
 * @return {json}
*/
function generate(prompt,apiUrl) {
    // 送信データ（プロンプトテキスト）
    const payload = {
      contents: [
        {
          parts: [
            { text: promptText }
          ]
        }
      ]
    };
    
    const options = {
        method: "POST",
        contentType: "application/json",
        payload: JSON.stringify(payload),
        muteHttpExceptions: true 
    };
    
    try {
        // APIリクエストの実行
        const response = UrlFetchApp.fetch(apiUrl, options);
        const responseData = JSON.parse(response.getContentText());
        
        // エラーレスポンスのチェック
        if (response.getResponseCode() !== 200) {
          throw new Error(`API Error: ${responseData.error || "Unknown error"}`);
        }
        
        return responseData;
    } catch (error) {
        console.error("Failed to send request to Gemini:", error);
        throw error;
    }
};

5. スプレッドシートとの調整

どこのセルの情報がプロンプトに必要で、Geminiの回答をどのセルに仕舞いたいのか、というところを明確にし、それをGoogle Apps Scriptのコードに落とし込む作業です。

スプレッドシートは、列とか行とかを挿入・削除・移動することがけっこうあります。セル内の数式で、たとえば=A3のようにしておけば、移動させたらいい感じに=B3のように更新してくれますね。とてもありがたいです。

その感じでGAS側でもうまく更新されるかと思ったらされません。何行目とか、何列目とか、マジックナンバーを書くことになります。それはよくないので、どちらかというと更新頻度の低い「カラム名」を取得することで、左から何個目にあるのか動的に取得できるようにします。

今回はgetAllIndex()というメソッドをつくりました。

関数はこちら。事前にマッピングをつくっておいて、JavaScriptのデフォルトAPIのindexOf(str)という文字が含まれているかどうかチェックするやつをつかっています。カラム名が部分的にも重複しない想定です。もし部分的に重複している場合は、完全一致 const index = headers.findIndex(header => header === headerName);を利用しないといけないでしょう。

script.gs or functions.gs

/**
 * ヘッダー名でカラムを取得する
 * @param {Array} headers - ヘッダー
 * @param {Object} mapping - 固定のキー名とそのインデックスを持つオブジェクト
 * @return {Object} - 固定のキー名とそのインデックスを持つオブジェクト
 * @example
 * const mapping = {
 *     number: "生成No.",
 *     dataName: "データカラム",
 *     resultCell: "Geminiの生成結果カラム",
 *     chargingPerson: "担当者",
 *     status: "ステータス",
 * }
 */
function getAllIndex(headers, mapping) {
	const headerIndices = {}
	for (const [key, headerName] of Object.entries(mapping)) {
		const index = headers.indexOf(headerName)
		headerIndices[key] = index !== -1 ? index + 1 : null // インデックスは1から始まるため+1
	}
	return headerIndices
}

以下はスプシをちょっと便利にあつかうための関数群です。

script.gs or functions.gs

/**
 * アクティブなシートを取得する
 * @return {Object} - アクティブなシート
 */
function getSheet() {
	const book = SpreadsheetApp.getActiveSpreadsheet()
	return book.getActiveSheet()
}

/**
 * シートのデータを取得する
 * @param {Object} sheet - シートオブジェクト
 * @return {Array} - シートのデータ
 */
function getSheetData(sheet) {
	return sheet.getDataRange().getValues()
}

/**
 * 最終行を取得する
 * @param {Object} sheet - シートオブジェクト
 * @return {number} - 最終行の行番号
 */
function getLastRowNumber(sheet) {
	return sheet.getLastRow()
}

/**
 * ヘッダーを取得する
 * @param {Array} data - シートのデータ
 * @param {number} startNumber - ヘッダーの開始行番号
 * @return {Array} - ヘッダー
 */
function getHeaders(data, startNumber) {
	return data[startNumber - 1] // 実際の値は0からなので行数と調整する
}

/**
 * シートの特定のセルに値を設定する
 * @param {Object} sheet - シートオブジェクト
 * @param {number} row - 行番号
 * @param {number} column - 列番号
 * @param {string|number} value - 設定する値
 */
function setCellValue(sheet, row, column, value) {
	sheet.getRange(row, column).setValue(value)
}

/**
 * シートの特定のセルの値を取得する
 * @param {Object} sheet - シートオブジェクト
 * @param {number} row - 行番号
 * @param {number} column - 列番号
 * @return {string|number} - セルの値
 */
function getCellValue(sheet, row, column) {
	return sheet.getRange(row, column).getValue()
}

いったん、基本の関数だけ区切りました。こういうコアのロジックと関係ない関数はノイジーなので、新規ファイルをつくって、てきとうにfunctions.gsと命名して、まとめておいてもいいと思います。Google Apps Scriptはファイル分割してもぜんぶ読み込んでしまうので、こういう管理方法も有効です。

いまはV8ランタイム上で動かせてクラシック関数のホイスティングもあるので、変数も関数も別のファイルに分割してもよいと思います。（今回はデモなのでいっしょくたにしています）

次に実行するための関数です。

script.gs

// 実行のメイン関数
function main(){
    // API関連
    const apiKey = getPropertyByName("GEMINI_API_KEY");
    const apiUrl = makeGeminiEndpoint(apiKey);

    // スプシ基本関連
    const startRowNumber = 3; // 今回の案件は1,2行目にリンクを入れていて3行目から始まるため、スタート番号を3行目にしています。
    const sheet = getSheet();
    const data = getSheetData(sheet);
    const lastRowNumber = getLastRowNumber(sheet);
    const headers = getHeaders(data,startRowNumber);

    // スプシ固有インデックス（キー名は自由、value側はスプシで使っているカラム名です。カラム名を変更した場合は手作業修正が必要です）
    const mapping = {
        number: "生成No.",
        dataName: "データカラム",
        resultCell: "Geminiの生成結果カラム",
        chargingPerson: "担当者",
        status: "ステータス",
    };
    const indexes = getAllIndex(headers,mapping);
    
    // ヘッダー（＋１行）から最後尾までを繰り返すループ処理
    for (let i = startRowNumber + 1; i <= lastRowNumber; i++) {
        // セルから情報を取得するときは、縦と横のセル位置情報を入力します
        // 今回はすべての行をループしていて、そのイテレーションを「i」という変数に入れているので、あとはどのカラムなのかを入力します。そこで、indexesでマッピングしている値（indexes["dataName"]）を入れればカラム名を動的に入力できる、という建て付けです
        const data = getCellValue(sheet,i,indexes["dataName"]);
    
        // プロンプト
        const prompt = makePrompt(data);
    
        // 生成
        try {
            // 負荷とかを考えてループをゆっくり回すために3秒待機の姿勢、ミリ秒です
            Utilities.sleep(3000)

            // 生成関数にプロンプトとエンドポイントをいれる
            const responseJson = generate(prompt,apiUrl)

            // 返却されたデータ構造を掘って目当てのテキストを手に入れる
            const generatedText = responseJson.candidates[0].content.parts[0].text;

            // 生成結果を狙いのセルに保存 - setValue()メソッドをラッピングしている関数
            // get側とおなじ構造で、1行ずつ見ているので行数は「i」に入っていて、カラムは動的にとってきてい「resultCell」に対応しているカラム名に値が入ります。第四引数に、生成AIが出力した実際の値を書きます
            setCellValue(sheet,i,indexes["resultCell"],generatedText)
            // ログでも見る
            console.log("API Response:", result);
        } catch (error) {
            console.error("Error:", error);
            continue;
        }
    }
};

6. GASの関数を発動させる

main()というメソッドが全体を囲っているメソッドです。コードエディターの「実行」をタップすると、その右側に書いてあるメソッド名mainが実行されます。

権限付与のこと言われるかも

「権限付与」みたいなモーダルが表示されたら、左側の「詳細」をクリック、「安全ではないページを表示」をおそるおそる押して、スプレッドシートに権限を付与します。

どうしてもApp Scriptを開けないとき

クッキーの関係なのか、たまに拡張機能→Apps Scriptがぜんぜん開けなくて話にならないこともあります。そういうときは、Apps Scriptのダッシュボードに行き、新規プロジェクトをつくり、そこで無理やりスプシにアクセスします。

ダッシュボードにアクセス

こちらがダッシュボード

スプレッドシートのIDを取得する

https://docs.google.com/spreadsheets/d/{xxxxxxxxxxxxxxxxx}/edit

スプレッドシートのURLはこういう感じになっていると思いますが、dのあとに書いてある長いランダムの文字列が、そのシートのIDとなっています。これが必要です。

シートのIDでアクセスして権限画面を払ってもらう

さきほど新規でつくったプロジェクトの初期スクリプトに、適当にこういう感じの関数をつくって、実行しようとすると、権限の画面が出てきます。

コード.gs

function myFunction() {
  const bookId="xxxxxxxxxxxxxxxxxxxxxxxxxxx"
  const sheet = SpreadsheetApp.openById(bookId).getActiveSheet();
  console.log(sheet);
}

トリガーもつくれる

おまけ情報として、左サイドのバーティクルバーにある⏱️のアイコンでは、「編集されたとき」とか「朝何時に」とか、発動するトリガーを設定することもできます。

まとめ

スプレッドシートは便利なのにめんどうくさい。でも、めんどうくさいときのほうが興奮してしまうところがある説あります。

案件募集中

お金ないので案件募集中です。課題を解決します（！）。

課題

注意点

手続き前の手続き

1. Geminiに登録してAPI KEYを入手する

2. Google Apps Scriptを使えるようにする

3. プロパティにAPI KEYを登録する

手続き

1. プロンプトをがんばって考える

2. GeminiのAPIキーをプロパティーサービスで取得する

3. Gemini APIのエンドポイント準備

4. ペイロードをつくって送信＆生成物を加工

5. スプレッドシートとの調整

6. GASの関数を発動させる

権限付与のこと言われるかも

どうしてもApp Scriptを開けないとき

ダッシュボードにアクセス

スプレッドシートのIDを取得する

シートのIDでアクセスして権限画面を払ってもらう

トリガーもつくれる

まとめ

案件募集中

Discussion