2024年9月9日 / 最終更新日時 : 2024年9月11日 officeの杜 AI

Geminiを使ってVBAからGoogle Apps Scriptに変換する仕組みを作る【GAS】

新規にGoogle Workspaceを導入しようとなると１つ大きな障害があります。それが「マクロ資産はどうする？」問題。Enterprise PlusプランだとMacro Converterというのが利用できますが、この為だけに最上位の高価なプランに入るのはちょっと。

そこで、今回VBA + GAS + Gemini 1.5 Flashを使ってVBAコードをGASに変換し、スプシにPushするという仕組みを構築してみました。

VBA側ではModuleを出力したりファイルをアップし、サーバーたるGAS側は変換を主に担当します。

Google Workspaceを新規に導入する時に押さえたいポイント

1 今回利用するスプシやライブラリ等
2 事前準備
3 実際に使ってみる
4 ソースコード
- 4.1 GAS側コード
- 4.2 VBA側コード
  - 4.2.1 Scopeに注意
  - 4.2.2 変換実行するコード
5 関連リンク

今回利用するスプシやライブラリ等

VBA⇒GAS変換 - Googleスプレッドシート
VBA変換用のVBAプログラム - Microsoft Excel
ProjectApp2 - GASライブラリ
VBA-JSON - VBAライブラリ
ZeroInstall BrowserDriver for VBA - VBAライブラリ
Gemini 1.5 Flash API

今回の仕組みはなかなか複雑ですが、使うだけであれば事前準備のみで使えるのでそこまで大変ではありません。Excelのファイルをアドイン化して配布すれば、使ってるExcelをそのままドライブにアップ⇒GeminiがマクロをGASに変換⇒スプシのコード.gsに書き込みまでを一気に行います。

この仕組みで一番重要なのはProjectApp2ライブラリで、スプシにプロジェクトを作成し、GASのコードを直接プッシュすることが可能です。アドイン化する場合はVBAのメインの関数をリボンで構築してあげると良いでしょう。

※今回VBA側はマクロの記録で保存したModule1といった名称のついてる標準モジュールをエクスポートします。

※ChatGPTよりStrawberryという数学やプログラミングに焦点を当てたモデルがでるそうな。これを使った場合、より高度な変換が期待できるかもしれない。

ExcelとAccessに独自のリボンを追加する

【GAS】Google Apps Scriptでコードをバックアップ・遠隔更新する

事前準備

Gemini 1.5 FlashのAPIキーを取得する

2024年5月15日、Googleが発表した最新のAIモデルであるGemini 1.5 Flash。これは、Gemini Advancedのバックグラウンドで動いてるものになりますが、今回はそのAPIを直接利用します。

Bardの時とは違いGCPで色々準備してといった手間が省けるようになっています。以下の手順でGemini 1.5 FlashのAPIキーを今回は取得してみます。

Googleアカウントにログインした状態にしておく
こちらのウェブサイトにアクセスする
APIキーを作成をクリックする
1個だけはそのままAPIキーを作成で続行できます。2つ目は既存のGCPプロジェクトを選ぶよう指示が出ます。GCPプロジェクトを指定しなかった場合には新規にGCPプロジェクトが生成されます。
APIキーが発行されるのでコピーする

このAPIキーは無料ですが、流出することの無いように大切に保管する必要があります。このキーは後でGAS側で利用します。

図：APIキーを発行してる画面

AppSheetでGemini連携植物図鑑を作ってみた【GAS】

GCP側の準備

今回のGASのプログラムは、Drive APIを使ってVBA側からファイルをGoogle Driveにアップロードが必要なため、クライアントIDとシークレットが必要になります。

Google Apps Script APIをオンにする

Google Apps Script自体を管理や更新する為に、以下の作業をしておく必要があります。

こちらのリンクをクリックして開く
Google Apps Script APIをクリックする
チェックがあるのでオンにする

この作業はそのプロジェクトをデプロイするアカウント毎に行う必要があります。

図：APIを有効化する

GCP側でAPIを有効化する

また、GCP側でもGoogle Apps Script APIを有効化する必要があり、またこの場合Drive APIも有効化する必要があるので、以下の手順でGCP側のAPIを有効化しておきましょう。

GCPのプロジェクトを開く
左サイドバーからAPIとサービスを開く
上部にある「APIとサービスを有効化する」をクリックする
検索窓から「apps script」で検索すると一発でヒットするのでクリックする
有効化をクリックする
同じくDrive APIについても検索して有効化しておきます

図：GCP側でも有効化が必要です

GCP側とリンクする

この作業は、前述のGoogle Apps Script APIを利用する場合に必要な作業です。GCP側のプロジェクトとGASのプロジェクトをリンクさせる必要性があり、またGCP側でもAPIを有効化する必要があるので、連結しておきましょう。

連結する手順は以下の通り

Google Cloud Consoleを開く
左上にある▼をクリックする
ダイアログが出てくるので、新規プロジェクトを作るか？既存のプロジェクトを選択する。この時、Google Workspaceであれば選択元は「自分のドメイン」を選択する必要があります。
プロジェクト情報パネルから「プロジェクト番号」をコピーする
対象のGoogle Apps Scriptのスクリプトエディタを開く
サイドバーからプロジェクト設定を開く
プロジェクトを変更ボタンをクリック
GCPのプロジェクト番号に、4.の番号を入れてプロジェクトを設定をクリック

図：プロジェクト番号をコピーしておきます

図：プロジェクト変更画面

認証情報を作成する

VBA側からファイルをDriveにアップロードする等の為に必要なクライアントIDとシークレットを作成します。

Google Cloud Consoleの認証情報作成を開く
認証情報を作成をクリックする
OAuthクライアントIDを選択する
クライアントIDの作成では、「ウェブアプリケーション」を選択する
承認済みのリダイレクト URIはVBA側と同じリダイレクトURLを指定します。
作成ボタンを押すと、クライアントIDとクライアントシークレットが手に入るので控えておく。
Cloud Consoleを閉じる

この２つは大切なものなので、漏れたりしないように保存しておく必要があります。また、今回のケースは内部利用目的なので、OAuth同意画面に於いては、ユーザの種類は「内部」とし、スコープでは、VBA側で指定してるものを指定しています。

※リダイレクトURLは自社のホームページなどのURLを指定します。すると認証後にそこにAuthenticated Codeが表示されるので、このコードを取得してさらにAccess Tokenを取得する仕組みです。適当なURLを指定すると、相手側のサーバでログが残り、そこにAuthenticated Codeが記載されてるのでセキュリティ的にはよろしくありません。今回はこのサイトのホームページを指定しています。

図：クライアントID、シークレット取得しておきましょう。

図：OAuth同意画面の設定

VBAからGoogle Driveにファイルをアップロードする

GAS側の準備

ライブラリを導入する

GASのコードのバックアップや復元、またマニフェストの内容を更新する場合、Google Apps Script APIを利用してREST APIを叩いて操作する必要があります。リファレンスはこちらにあります。

こちらのサイトに掲示されてるライブラリのスクリプトIDを取得する

//スクリプトID 11qqgrTfCEydqwIF8RRrSZOrdq-KNsIDnUpnYefX5KobaMMArVSlXUqwS

1
2

//スクリプトID
11qqgrTfCEydqwIF8RRrSZOrdq-KNsIDnUpnYefX5KobaMMArVSlXUqwS
スクリプトエディタのライブラリの横にある＋をクリックする
1.のスクリプトIDを追加して検索をクリック。
追加をクリックする
一回、適当な関数を作って実行し承認を実行する
アカウントを選択して認証画面が出るので許可をクリックする

図：ライブラリを追加する

図：認証を許可する

Drive APIを有効にする

今回のスクリプトはDrive API v2を利用します。よって、以下の手順に従って、Drive APIをONにする必要性があります。

スクリプトエディター画面に於いて、サービス欄の＋記号をクリック
Drive APIを探しだして、選択する
追加ボタンをクリックする（デフォルトだとv3なのでv2にしておきます）

これでDrive APIがGoogle Apps ScriptでDrive APIを使えるようになります。

図：Drive APIをオンにした

APIキーを書き込む

前述のGemini 1.5 FlashのAPIキーをコードの中に記述します。本来はスクリプトプロパティに格納して呼び出すほうがセキュアですが、今回は便宜的にコードに直書きです。

コード.gsの一番上にあるapikeyの変数内に直接、取得したAPIキーを記述しましょう。

図：ここにAPIキーを書き込む

ウェブアプリケーションとしてデプロイ

今回のアプリはGASの関数を叩くのではなく、ウェブアプリとして出力したURLに対してGETでリクエストをしてアクションを実現しています。そのため、以下の手順でウェブアプリケーションとしてデプロイし、URLを取得しておく必要があります。

スクリプトエディタを開く
右上のデプロイをクリック
新しいデプロイをクリック
種類の選択ではウェブアプリを選択し、次のユーザとしてアプリケーションを実行で誰の権限で動かすかを指定する。今回は自分自身を指定します。
アプリケーションにアクセスできるユーザを指定する。今回は組織内ユーザに限定します。
最後に導入すると、ウェブアプリケーションのURLが取得できます。このURLでアクセスをします。URLの最後がexecが本番用、これを控えておきます。
次回以降コードを編集して再デプロイ時はデプロイを管理から同じURLにて、新しいバージョンを指定して発行することが出来ます。

図：今回は次のユーザとして実行は管理者権限のあるアカウントにて

適当なフォルダのIDを取得しておく

ExcelファイルやVBAのモジュールをアップロードするフォルダが必要です。そのフォルダのIDを控えておきましょう。この後のVBA側の準備で利用します。

このフォルダに対してExcelファイルやモジュールのテキストがアップロードされ、またGeminiで変換したコード.gsファイルが保存されるフォルダになります。

VBA側の準備

各種ライブラリを追加する

今回のVBA側サンプルファイルは、以下のエントリーで作成したVBAでOAuth2.0認証を行う新方式のファイルを使っています。この中にはZeroInstall BrowserDriver for VBAがすでに入っており、ここにさらにVBAでJSONを扱うVBA-JSONを追加しています。

これらライブラリでGoogle認証やGAS側とのやり取り、Drive APIとのやり取りを実現しています。

VBAでOAuth2.0認証 - 新方式を試してみた

コードに値を記述する

今回のサンプルのVBA部分にはいくつか前述までに取得したものを記述として追加する必要があります。

authenticate - GCPで取得したクライアントID、シークレット、リダイレクトURLを記述します。
module_exporter - アップロード先のDriveのフォルダのID、execで終わるウェブアプリのURLを記述します。

これらの値を持って、Google側と通信してファイルアップロードやGAS変換リクエストを投げます。

図：VBAのコード内に直接記述します

VBAのセキュリティ設定の変更

今回のVBAコードは、VBAの実行だけじゃなく、対象のExcelファイル内に記録された「マクロの記録」のModuleファイルをテキストとしてエクスポートします。よって、以下のように設定を変更する必要があります。

Excelのファイル⇒オプションを開く
トラストセンター⇒トラストセンターの設定をクリック
マクロの設定を開く
VBAマクロを有効にするをクリック
VBAプロジェクトオブジェクトモデルへのアクセスを信頼するにチェックを入れる
OKをクリックして保存する

とくに5.のチェックがオンでないと、VBAからVBEのモジュールエクスポートが出来ないでエラーとなるので要注意。

図：セキュリティ設定変更が必要

実際に使ってみる

VBA側で認証を実行する

VBA側のauthenticateモジュール内にある「googleAuthorization」を実行します。

Webブラウザが起動してログイン画面が出ます。
通常通り、Googleアカウントでログインします。
アクセス権限の承認画面が出るので、指示に従って承認します。
するとこのExcelファイルと同じディレクトリ内にsetting.iniにAccess Tokenが保存されます。このファイルは流出しないように大切に保管する必要があります。

これでGoogle側へのOAuth2.0認証が完了しました。

図：承認画面の様子

VBA側で変換を実行する

今回はサンプルファイルである自分自身のExcelと、その中に記述済みのマクロの記録であるMacro1とMacro2の標準モジュールの内容をGoogle Driveにアップロードして、GAS側で引き続きスプレッドシート変換、GeminiによるGASへの変換、スプシ内のコードgsに変換結果のコードをプッシュします。

VBA側のmodule_exporter内にあるExportAndUploadを実行します。今回は自分自身をアップしてるため、一旦自分自身のコピーをしてからアップしています。Moduleの中身はmodule.txtとして1枚にまとめられます。この2つのファイルをアップロードして、GAS側で処理をしてもらいます。

※GAS側のマクロとするには、今回のコード以外にもappsscript.jsonへの追記などちょっと仕組みの追加が必要です。以下のエントリーにまとめています。また、Macroの中身のコードは以下のとおりです。

Sub Macro1()
'
' Macro1 Macro
'
'
Range("A1:E21").Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Columns("A:E").Select
Range("E1").Activate
Columns("A:E").EntireColumn.AutoFit
Columns("E:E").Select
Application.CutCopyMode = False
Selection.Delete shift:=xlToLeft
ActiveWindow.SmallScroll Down:=6
Rows("14:21").Select
Selection.Delete shift:=xlUp
End Sub

Sub Macro1()

' Macro1 Macro

Range("A1:E21").Select

Selection.Copy

Sheets("Sheet2").Select

ActiveSheet.Paste

Columns("A:E").Select

Range("E1").Activate

Columns("A:E").EntireColumn.AutoFit

Columns("E:E").Select

Application.CutCopyMode = False

Selection.Delete shift:=xlToLeft

ActiveWindow.SmallScroll Down:=6

Rows("14:21").Select

Selection.Delete shift:=xlUp

End Sub

Googleスプレッドシートのマクロの記録機能を使ってみた

変換結果を確認する

最終的に変換が完了すると、スプレッドシートが自動的に開かれて、対象のスプシ内にコードが入っていれば完了。このコードをテスト実行して認証すれば、移植が完了ということになります（アップしたフォルダ内には２個のファイルとは別に変換結果のコード.gsと変換したスプシの２つの計4つのファイルが出来上がります）。

アップロード先フォルダを確認しましょう。

図：無事に変換されてファイルが生成されました

図：変換結果が無事に書き込まれてました

ソースコード

GAS側コード

コード.gs

VBA側から、ExcelファイルのID、モジュールファイルのIDの２つをdoGetで受け取ったら、順番にファイル変換やGeminiによるGAS変換、プロジェクト作成、コードプッシュまでの一連の作業を実行し、最終的にスプシのIDを返します。

一番のメインの処理を行う部分になります。

makeProjectでは、GASのプロジェクトの作成と関数の追加だけじゃなく、追加したマクロの関数をマクロの一覧に登録するようにappsscript.jsonに追加しています。

VBA側で関数リスト構築で用意したfuncnamelistシートはGoogleスプレッドシートでは不要なので削除しています。

図：マクロリストの構築まで行います。

図：appsscript.jsonもマクロメニューを自動構築

//APIキー
var apikey = "ここにGeminiのAPIキーを入れる";
//リクエストエンドポイント
var endpoint = "https://generativelanguage.googleapis.com/v1beta/models/gemini-1.5-flash-latest:generateContent?key="
//ファイル情報を受取る
function doGet(e) {
//受け取るパラメータを用意する（ドライブにアップしたファイルのID）
let excelfile = e.parameter.param1;
let modulefile = e.parameter.param2;
//対象のExcelのファイル名を取得
let name = DriveApp.getFileById(excelfile).getName()
//ファイル名から拡張子を除外する
let tempname = name.split(".")
name = tempname[0]
//Excelファイルを変換する
let ssid = xls2sheetApi(excelfile);
//Excelファイルのカレントディレクトリを取得
let current = driveman(ssid);
//funcnamelistシートから関数一覧を取得
let array = [];
let ss = SpreadsheetApp.openById(ssid);
let sheet = ss.getSheetByName("funcnamelist").getRange("A1:A").getValues();
for(let i = 0;i<sheet.length;i++){
if(sheet[i][0] == ""){
//空になった時点で抜ける
break;
}
array.push(sheet[i][0]);
}
//シートを削除
ss.deleteSheet(ss.getSheetByName("funcnamelist"))
//GASプロジェクトを作成
let scriptId = makeProject(ssid,name,array);
//moduleファイルの中身をGeminiに聞いてGAS変換
let vbacode = DriveApp.getFileById(modulefile).getBlob().getDataAsString("Shift_JIS"); 
let answer = geminiman(vbacode);
//変換したGASコードをTXT化してカレントディレクトリに保存
let tempblob = DriveApp.getFolderById(current).createFile("コード.gs", answer, MimeType.PLAIN_TEXT).getBlob();
//変換したGASコードをプッシュする
let blob = [tempblob];
let res = ProjectApp2.updateProjectByBlob(scriptId, blob);
//完了用URLを構築
let returnurl = "https://docs.google.com/spreadsheets/d/" + ssid + "/edit?"
//return
return ContentService.createTextOutput(returnurl); 
}
//カレントフォルダを返す関数
function driveman(id){
var parentsfolder = DriveApp.getFileById(id).getParents();
while (parentsfolder.hasNext()) {
var folder = parentsfolder.next();
var idval = folder.getId();
return idval;
}
}
//空っぽのスプシにプロジェクトを作成する
function makeProject(ssid,name,array){
//関数リストからマクロリストを構築する
let macro = [];
for(let i = 0;i<array.length;i++){
let temparr = {
"menuName": array[i],
"functionName":array[i]
}
macro.push(temparr)
}
//appsscript.jsonを構築
let json = {
timeZone: "Asia/Tokyo",
dependencies: {
},
exceptionLogging: "STACKDRIVER",
runtimeVersion: "V8",
sheets: {
macros:macro
}
};
//appscript.jsonをblobとして取得
let blob = Utilities.newBlob(JSON.stringify(json, null, "\t")).setName(
"appsscript.json"
);
let blob2 = [blob];
//対象スプシに対してGASプロジェクトを新規作成
let res = ProjectApp2.createProjectByBlob(
name,
blob2,
ssid
);
//スクリプトIDを返す
return res.scriptId
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

//APIキー

var apikey = "ここにGeminiのAPIキーを入れる";

//リクエストエンドポイント

var endpoint = "https://generativelanguage.googleapis.com/v1beta/models/gemini-1.5-flash-latest:generateContent?key="

//ファイル情報を受取る

function doGet(e) {

//受け取るパラメータを用意する（ドライブにアップしたファイルのID）

let excelfile = e.parameter.param1;

let modulefile = e.parameter.param2;

//対象のExcelのファイル名を取得

let name = DriveApp.getFileById(excelfile).getName()

//ファイル名から拡張子を除外する

let tempname = name.split(".")

name = tempname[0]

//Excelファイルを変換する

let ssid = xls2sheetApi(excelfile);

//Excelファイルのカレントディレクトリを取得

let current = driveman(ssid);

//funcnamelistシートから関数一覧を取得

let array = [];

let ss = SpreadsheetApp.openById(ssid);

let sheet = ss.getSheetByName("funcnamelist").getRange("A1:A").getValues();

for(let i = 0;i<sheet.length;i++){

if(sheet[i][0] == ""){

//空になった時点で抜ける

break;

}

array.push(sheet[i][0]);

}

//シートを削除

ss.deleteSheet(ss.getSheetByName("funcnamelist"))

//GASプロジェクトを作成

let scriptId = makeProject(ssid,name,array);

//moduleファイルの中身をGeminiに聞いてGAS変換

let vbacode = DriveApp.getFileById(modulefile).getBlob().getDataAsString("Shift_JIS");

let answer = geminiman(vbacode);

//変換したGASコードをTXT化してカレントディレクトリに保存

let tempblob = DriveApp.getFolderById(current).createFile("コード.gs", answer, MimeType.PLAIN_TEXT).getBlob();

//変換したGASコードをプッシュする

let blob = [tempblob];

let res = ProjectApp2.updateProjectByBlob(scriptId, blob);

//完了用URLを構築

let returnurl = "https://docs.google.com/spreadsheets/d/" + ssid + "/edit?"

//return

return ContentService.createTextOutput(returnurl);

}

//カレントフォルダを返す関数

function driveman(id){

var parentsfolder = DriveApp.getFileById(id).getParents();

while (parentsfolder.hasNext()) {

var folder = parentsfolder.next();

var idval = folder.getId();

return idval;

}

//空っぽのスプシにプロジェクトを作成する

function makeProject(ssid,name,array){

//関数リストからマクロリストを構築する

let macro = [];

for(let i = 0;i<array.length;i++){

let temparr = {

"menuName": array[i],

"functionName":array[i]

}

macro.push(temparr)

}

//appsscript.jsonを構築

let json = {

timeZone: "Asia/Tokyo",

dependencies: {

exceptionLogging: "STACKDRIVER",

runtimeVersion: "V8",

sheets: {

macros:macro

}

};

//appscript.jsonをblobとして取得

let blob = Utilities.newBlob(JSON.stringify(json, null, "\t")).setName(

"appsscript.json"

);

let blob2 = [blob];

//対象スプシに対してGASプロジェクトを新規作成

let res = ProjectApp2.createProjectByBlob(

name,

blob2,

ssid

);

//スクリプトIDを返す

return res.scriptId

}

xlsxconvert.gs

Drive APIを使ってExcelファイルをスプレッドシートに変換するコードです。詳細については以下のエントリーでまとめています。

Google Apps ScriptにてExcelファイルを変換して取り込んでみる【GAS】

//ExcelをGoogleスプレッドシートに変換するメソッドを呼び出すルーチン(Drive API)
function xls2sheetApi(fileid) { 
let xlsFile = DriveApp.getFileById(fileid); 
let xlsBlob = xlsFile.getBlob(); 
let xlsFilename = xlsFile.getName(); 
//対象のExcelのファイル名を取得
let name = DriveApp.getFileById(fileid).getName()
//ファイル名から拡張子を除外する
let tempname = name.split(".")
xlsFilename = tempname[0]
let destFolders = driveman(fileid); // xlsxファイルがあるカレントフォルダを指定する
let ssid = convertExcel2SheetApi(xlsBlob, xlsFilename, destFolders);
//変換したファイルからデータを取得する
return ssid;
}
//Drive.Files.insetを使った変換方法
function convertExcel2SheetApi(excelFile, filename, targetfolder){
//変換情報を作成する
let files = {
title: filename,
mimeType: MimeType.GOOGLE_SHEETS,
parents: [{id: targetfolder}],
};
//Drive APIで変換
let res = Drive.Files.insert(files, excelFile);
//変換シートのIDを返す
return res.id;
}

//ExcelをGoogleスプレッドシートに変換するメソッドを呼び出すルーチン(Drive API)

function xls2sheetApi(fileid) {

let xlsFile = DriveApp.getFileById(fileid);

let xlsBlob = xlsFile.getBlob();

let xlsFilename = xlsFile.getName();

//対象のExcelのファイル名を取得

let name = DriveApp.getFileById(fileid).getName()

//ファイル名から拡張子を除外する

let tempname = name.split(".")

xlsFilename = tempname[0]

let destFolders = driveman(fileid); // xlsxファイルがあるカレントフォルダを指定する

let ssid = convertExcel2SheetApi(xlsBlob, xlsFilename, destFolders);

//変換したファイルからデータを取得する

return ssid;

}

//Drive.Files.insetを使った変換方法

function convertExcel2SheetApi(excelFile, filename, targetfolder){

//変換情報を作成する

let files = {

title: filename,

mimeType: MimeType.GOOGLE_SHEETS,

parents: [{id: targetfolder}],

};

//Drive APIで変換

let res = Drive.Files.insert(files, excelFile);

//変換シートのIDを返す

return res.id;

}

Gemini.gs

ここはVBAのコードをGeminiにGASへ変換させています。但し、指定のmaxOutputTokensが2048でしか指定していないので、長いコードの場合はもっと大きな数値をしておいたほうが良いと思います。

また余計な文言を削除するようにプロンプトを調整しています。

Geminiは与えるトークン量がかなり上限が高い（100万トークン位）ので、ClaudeやChatGPTよりもVBAコード変換のような事例では効果的です。

※この内容を変更すれば、ClaudeやChatGPTのAPIに置き換えることが出来るので、用途に応じてこの中身な書き換えると良いでしょう。

//Geminiに問い合わせをして回答を得る
function geminiman(request) {
//リクエスト用URLを構築
let url = endpoint + apikey;
//request文にプロンプトを追加する
request = "以下のVBAのコードをGASのコードに変換してください。\n" + request + "\n\n但し、回答はコードのみを返し、余計な文章を前後につけないでください。"
//リクエストボディを構築する
var body = {
"contents": [
{
"parts": [
{
"text": request    //質問文がここに入る
}
]
}
],
"generationConfig": {
"temperature": 0.4,       //回答のランダム性を制御する
"topK": 50,               //AIが考慮する選択肢の幅。小さいほど絞られ、高いほど多様性が増します
"topP": 0.1,              //AIが考慮する回答のうち、確率の高いものをどれだけ優先するか？低いほど選択肢が狭まります。
"maxOutputTokens": 2048,  //Tokensの値が大きいほど多くの文字数で答えが返ってきます。
"stopSequences": []
}
};
//リクエストオプションを構築する
let options = {
method: 'post',
contentType: 'application/json',
payload: JSON.stringify(body)
}
// リクエストを送信する。(Gemini 1.5 Flashを使用)
let response = UrlFetchApp.fetch(url, options);
// レスポンスをパースする。
var responseJson = JSON.parse(response.getContentText());
// 生成されたテキストを返す。
let answer = responseJson.candidates[0].content.parts[0].text;
//生成されたテキストから余計な文字を削除する
answer = answer.replace("```js","")
answer = answer.replace("```","")
//答えを返す
return answer;
}

//Geminiに問い合わせをして回答を得る

function geminiman(request) {

//リクエスト用URLを構築

let url = endpoint + apikey;

//request文にプロンプトを追加する

request = "以下のVBAのコードをGASのコードに変換してください。\n" + request + "\n\n但し、回答はコードのみを返し、余計な文章を前後につけないでください。"

//リクエストボディを構築する

var body = {

"contents": [

{

"parts": [

{

"text": request //質問文がここに入る

}

]

}

"generationConfig": {

"temperature": 0.4, //回答のランダム性を制御する

"topK": 50, //AIが考慮する選択肢の幅。小さいほど絞られ、高いほど多様性が増します

"topP": 0.1, //AIが考慮する回答のうち、確率の高いものをどれだけ優先するか？低いほど選択肢が狭まります。

"maxOutputTokens": 2048, //Tokensの値が大きいほど多くの文字数で答えが返ってきます。

"stopSequences": []

}

};

//リクエストオプションを構築する

let options = {

method: 'post',

contentType: 'application/json',

payload: JSON.stringify(body)

}

// リクエストを送信する。(Gemini 1.5 Flashを使用)

let response = UrlFetchApp.fetch(url, options);

// レスポンスをパースする。

var responseJson = JSON.parse(response.getContentText());

// 生成されたテキストを返す。

let answer = responseJson.candidates[0].content.parts[0].text;

//生成されたテキストから余計な文字を削除する

answer = answer.replace("```js","")

answer = answer.replace("```","")

//答えを返す

return answer;

}

VBA側コード

Scopeに注意

OAuth2.0の認証に関する処理については、こちらのエントリーにある通りですが、1箇所注意点があります。今回のAccess Tokenは以下の複数のScopeに対して取得してるため、scope変数の中身が異なります。これはGAS側で使ってるスコープと同じだけ準備する必要があります。

Private Const scope As String = "openid profile email https://www.googleapis.com/auth/spreadsheets https://www.googleapis.com/auth/script.external_request https://www.googleapis.com/auth/drive https://www.googleapis.com/auth/script.projects"  '半角スペースで区切る

1	Private Const scope As String = "openid profile email https://www.googleapis.com/auth/spreadsheets https://www.googleapis.com/auth/script.external_request https://www.googleapis.com/auth/drive https://www.googleapis.com/auth/script.projects" '半角スペースで区切る

変換実行するコード

行ってる作業は

自分自身をコピー
自身のVBA内にある標準モジュールの内、Module1といった名称のものだけを出力しmodule.txtにまとめて出力
関数リストをまとめたfuncnamelistシートを別途コピーしたExcelに追記
以上の２つをDrive APIを使ってアップロード
アップロード後にファイルのIDが取得できるので、2つのファイルのIDを元に、GAS側のexecのURLに対して変換依頼。
変換完了後、スプシのURLを取得して自動的に開いて完了

３回、HTTPリクエストを叩いています。最後の処理についてsetTimeoutsを長めに設定しておかないと、タイムアウトになってしまってエラーになるので要注意。

'Sleepを使う
Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal ms As LongPtr)
'Drive Upload用エンドポイント
Public Const endpoint As String = "https://www.googleapis.com/upload/drive/v3/files?uploadType=media"
Public Const renemepoint As String = "https://www.googleapis.com/drive/v3/files/"
'アップロード先親フォルダのID
Public Const upfolder As String = "ここにアップロード先フォルダのIDを入れる"
'GAS変換依頼先のdoGetのURL
Public Const converturl As String = "ここにdoGetで公開したウェブアプリケーションのURLを入れる"
Sub ExportAndUpload()
Dim module                  As Variant
Dim module_array            As Variant
Dim dirpath                 As String
Dim CodeModule              As Object
Dim textfilepath            As String
Dim excelfilepath           As String
Dim objFileSys              As Object
Dim tempbuf As String, ProcNames As String
Set objFileSys = CreateObject("Scripting.FileSystemObject")
' カレントディレクトリーの取得
dirpath = ActiveWorkbook.Path
'自身のファイル名を取得
excelfilepath = dirpath & "¥" & ActiveWorkbook.name
'出力先ファイルを作成する
textfilepath = dirpath & "¥module.txt"
'テキストファイルが存在していたら削除しておく
If Dir(textfilepath) <> "" Then
Kill textfilepath
End If
'直接ロードするとエラーになるのでコピーする
Dim copyfilename As String
copyfilename = dirpath & "¥" & objFileSys.GetBaseName(ActiveWorkbook.name) & "-変換用.xlsm"
objFileSys.CopyFile excelfilepath, copyfilename, True
'関数リスト用シートを用意
Dim wb1 As Workbook
Set wb1 = Workbooks.Open(copyfilename)
With wb1.Worksheets.Add
.name = "funcnamelist"
End With
wb1.Save
' ブックのモジュール一覧を取得
Set module_array = ThisWorkbook.VBProject.VBComponents
Dim counter As Integer
counter = 1
For Each module In module_array
'標準モジュールのModuleという名前のものだけ対象にする
If module.Type = 1 Then
If module.name Like "Module*" Then
'Moduleを取得する
Set CodeModule = module.CodeModule
'textファイルを取得する（追記で行う）
Open textfilepath For Append As #1
'モジュールの中身をどんどん追記
For i = 1 To CodeModule.CountOfLines
Print #1, CodeModule.Lines(i, 1)
If tempbuf <> CodeModule.ProcOfLine(i, 0) Then
tempbuf = CodeModule.ProcOfLine(i, 0)
wb1.Worksheets("funcnamelist").Range("A" & counter).value = tempbuf
counter = counter + 1
End If
Next i
'空行を追記
Print #1, ""
Close #1
End If
End If
Next module
'wb1を閉じる
wb1.Save
wb1.Close
'Access Tokenを取得する
Dim tokenstatus As Boolean
ret = getNewToken()
'Access Tokenを取得する
Dim access_token As String
access_token = IniRead("USER", "access_token", "")
'JSONをパースする用の変数
Dim doc, jsn
'HTMLDocumentを取得
Set doc = CreateObject("HtmlFile")
'scriptタグを追加
doc.write "<script>document.JsonParse=function (s) {return eval('(' + s + ')');}</script>"
'ADODB.streamを用意する
Set Stream = CreateObject("ADODB.Stream")
Stream.Open
Stream.Type = 1 ' adTypeBinary
Stream.LoadFromFile = copyfilename
'Mimetype用
Dim mimetype As String
Dim temprec
Dim JsonObject As Object
Dim json
Dim xlsid, txtid
mimetype = "application/vnd.ms-excel.sheet.macroEnabled.12"
'Excelファイルアップロードリクエストを実行
With CreateObject("WinHttp.WinHttpRequest.5.1")
.Open "POST", endpoint, False
'.setProxy 2, proxyuri
.SetRequestHeader "Content-Type", mimetype
.SetRequestHeader "Authorization", "Bearer " & access_token
.SetRequestHeader "Content-Length", Stream.Size
.Send Stream.Read(Stream.Size)
Debug.Print .status
'返ってきた値をもとにデータを処理
Select Case .status
Case 200
'返ってきたJSON文字列を取得
json = .ResponseText
'パース関数でJSONオブジェクトを取得
Set jsn = doc.JsonParse(json)
'ファイルのIDを取得する
temprec = CallByName(jsn, "id", VbGet)
'リネーム用のエンドポイントを指定（ついでに移動先フォルダの指定）
requrl = renemepoint & temprec & "?addParents=" & upfolder
Debug.Print requrl
'リクエストボディ
Set JsonObject = CreateObject("Scripting.Dictionary")
JsonObject.Add "name", ActiveWorkbook.name   'ファイル名の指定
'3秒間ウェイトをかける
Sleep 3000
'HTTPリクエスト
With CreateObject("WinHttp.WinHttpRequest.5.1")
.Open "PATCH", requrl, False
'.setProxy 2, proxyuri
.SetRequestHeader "Content-Type", "application/json"
.SetRequestHeader "Authorization", "Bearer " & access_token
.Send JsonConverter.ConvertToJson(JsonObject)
'返ってきた値をもとにデータを処理
Debug.Print status
Select Case .status
Case 200
'レスポンスヘッダを取得
Debug.Print .getAllResponseHeaders()
'終了メッセージ
Debug.Print "ファイルアップロード完了"
'ファイルIDを格納する
xlsid = temprec
Debug.Print xlsid
Case Else
MsgBox "ファイル名変更に失敗しました"
End Select
End With
Case Else
MsgBox "アップロードに失敗しました。"
End Select
End With
'streamオブジェクトを閉じる
Stream.Close
Set Stream = Nothing
'ADODB.streamを用意する
Dim postdata() As Byte
Set Stream = CreateObject("ADODB.Stream")
Stream.Open
Stream.Type = 2 ' adTypeText
Stream.LoadFromFile = textfilepath
Stream.Charset = "Shift_JIS"
mimetype = "text/plain"
'バイナリ変換
Dim buf As String
Stream.Position = 0
Stream.Type = 1
With objFileSys
With .GetFile(textfilepath).OpenAsTextStream
buf = .ReadAll
.Close
End With
End With
postdata = buf
'TXTファイルアップロードリクエストを実行
With CreateObject("WinHttp.WinHttpRequest.5.1")
.Open "POST", endpoint, False
'.setProxy 2, proxyuri
.SetRequestHeader "Content-Type", mimetype
.SetRequestHeader "Authorization", "Bearer " & access_token
.SetRequestHeader "Content-Length", Stream.Size
.Send postdata
'返ってきた値をもとにデータを処理
Select Case .status
Case 200
'返ってきたJSON文字列を取得
json = .ResponseText
'パース関数でJSONオブジェクトを取得
Set jsn = doc.JsonParse(json)
'ファイルのIDを取得する
temprec = CallByName(jsn, "id", VbGet)
'リネーム用のエンドポイントを指定（ついでに移動先フォルダの指定）
requrl = renemepoint & temprec & "?addParents=" & upfolder
Debug.Print requrl
'リクエストボディ
Set JsonObject = CreateObject("Scripting.Dictionary")
JsonObject.Add "name", "module.txt"   'ファイル名の指定
'3秒間ウェイトをかける
Sleep 3000
'HTTPリクエスト
With CreateObject("WinHttp.WinHttpRequest.5.1")
.Open "PATCH", requrl, False
'.setProxy 2, proxyuri
.SetRequestHeader "Content-Type", "application/json"
.SetRequestHeader "Authorization", "Bearer " & access_token
.Send JsonConverter.ConvertToJson(JsonObject)
'返ってきた値をもとにデータを処理
Select Case .status
Case 200
'レスポンスヘッダを取得
Debug.Print .getAllResponseHeaders()
'終了メッセージ
Debug.Print "ファイルアップロード完了"
'ファイルIDを格納する
txtid = temprec
Case Else
MsgBox "ファイル名変更に失敗しました"
End Select
End With
Case Else
MsgBox "アップロードに失敗しました。"
End Select
End With
'streamオブジェクトを閉じる
Stream.Close
Set Stream = Nothing
'GAS変換URLに送るURLを構築
Dim sendurl As String
Dim result As String
sendurl = converturl & "?param1=" & xlsid & "&param2=" & txtid
With CreateObject("WinHttp.WinHttpRequest.5.1")
.Open "GET", sendurl, False
.SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"
.SetRequestHeader "Authorization", "Bearer " & access_token
.SetTimeouts 100000, 100000, 100000, 100000
.Send
result = .ResponseText
End With
'スプレッドシートを開く
Dim WSH
Set WSH = CreateObject("Wscript.Shell")
WSH.Run result, 3
Set WSH = Nothing
MsgBox "エクスポート完了"
End Sub

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

'Sleepを使う

Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal ms As LongPtr)

'Drive Upload用エンドポイント

Public Const endpoint As String = "https://www.googleapis.com/upload/drive/v3/files?uploadType=media"

Public Const renemepoint As String = "https://www.googleapis.com/drive/v3/files/"

'アップロード先親フォルダのID

Public Const upfolder As String = "ここにアップロード先フォルダのIDを入れる"

'GAS変換依頼先のdoGetのURL

Public Const converturl As String = "ここにdoGetで公開したウェブアプリケーションのURLを入れる"

Sub ExportAndUpload()

Dim module As Variant

Dim module_array As Variant

Dim dirpath As String

Dim CodeModule As Object

Dim textfilepath As String

Dim excelfilepath As String

Dim objFileSys As Object

Dim tempbuf As String, ProcNames As String

Set objFileSys = CreateObject("Scripting.FileSystemObject")

' カレントディレクトリーの取得

dirpath = ActiveWorkbook.Path

'自身のファイル名を取得

excelfilepath = dirpath & "¥" & ActiveWorkbook.name

'出力先ファイルを作成する

textfilepath = dirpath & "¥module.txt"

'テキストファイルが存在していたら削除しておく

If Dir(textfilepath) <> "" Then

Kill textfilepath

End If

'直接ロードするとエラーになるのでコピーする

Dim copyfilename As String

copyfilename = dirpath & "¥" & objFileSys.GetBaseName(ActiveWorkbook.name) & "-変換用.xlsm"

objFileSys.CopyFile excelfilepath, copyfilename, True

'関数リスト用シートを用意

Dim wb1 As Workbook

Set wb1 = Workbooks.Open(copyfilename)

With wb1.Worksheets.Add

.name = "funcnamelist"

End With

wb1.Save

' ブックのモジュール一覧を取得

Set module_array = ThisWorkbook.VBProject.VBComponents

Dim counter As Integer

counter = 1

For Each module In module_array

'標準モジュールのModuleという名前のものだけ対象にする

If module.Type = 1 Then

If module.name Like "Module*" Then

'Moduleを取得する

Set CodeModule = module.CodeModule

'textファイルを取得する（追記で行う）

Open textfilepath For Append As #1

'モジュールの中身をどんどん追記

For i = 1 To CodeModule.CountOfLines

Print #1, CodeModule.Lines(i, 1)

If tempbuf <> CodeModule.ProcOfLine(i, 0) Then

tempbuf = CodeModule.ProcOfLine(i, 0)

wb1.Worksheets("funcnamelist").Range("A" & counter).value = tempbuf

counter = counter + 1

End If

Next i

'空行を追記

Print #1, ""

Close #1

End If

Next module

'wb1を閉じる

wb1.Save

wb1.Close

'Access Tokenを取得する

Dim tokenstatus As Boolean

ret = getNewToken()

'Access Tokenを取得する

Dim access_token As String

access_token = IniRead("USER", "access_token", "")

'JSONをパースする用の変数

Dim doc, jsn

'HTMLDocumentを取得

Set doc = CreateObject("HtmlFile")

'scriptタグを追加

doc.write "<script>document.JsonParse=function (s) {return eval('(' + s + ')');}</script>"

'ADODB.streamを用意する

Set Stream = CreateObject("ADODB.Stream")

Stream.Open

Stream.Type = 1 ' adTypeBinary

Stream.LoadFromFile = copyfilename

'Mimetype用

Dim mimetype As String

Dim temprec

Dim JsonObject As Object

Dim json

Dim xlsid, txtid

mimetype = "application/vnd.ms-excel.sheet.macroEnabled.12"

'Excelファイルアップロードリクエストを実行

With CreateObject("WinHttp.WinHttpRequest.5.1")

.Open "POST", endpoint, False

'.setProxy 2, proxyuri

.SetRequestHeader "Content-Type", mimetype

.SetRequestHeader "Authorization", "Bearer " & access_token

.SetRequestHeader "Content-Length", Stream.Size

.Send Stream.Read(Stream.Size)

Debug.Print .status

'返ってきた値をもとにデータを処理

Select Case .status

Case 200

'返ってきたJSON文字列を取得

json = .ResponseText

'パース関数でJSONオブジェクトを取得

Set jsn = doc.JsonParse(json)

'ファイルのIDを取得する

temprec = CallByName(jsn, "id", VbGet)

'リネーム用のエンドポイントを指定（ついでに移動先フォルダの指定）

requrl = renemepoint & temprec & "?addParents=" & upfolder

Debug.Print requrl

'リクエストボディ

Set JsonObject = CreateObject("Scripting.Dictionary")

JsonObject.Add "name", ActiveWorkbook.name 'ファイル名の指定

'3秒間ウェイトをかける

Sleep 3000

'HTTPリクエスト

With CreateObject("WinHttp.WinHttpRequest.5.1")

.Open "PATCH", requrl, False

'.setProxy 2, proxyuri

.SetRequestHeader "Content-Type", "application/json"

.SetRequestHeader "Authorization", "Bearer " & access_token

.Send JsonConverter.ConvertToJson(JsonObject)

'返ってきた値をもとにデータを処理

Debug.Print status

Select Case .status

Case 200

'レスポンスヘッダを取得

Debug.Print .getAllResponseHeaders()

'終了メッセージ

Debug.Print "ファイルアップロード完了"

'ファイルIDを格納する

xlsid = temprec

Debug.Print xlsid

Case Else

MsgBox "ファイル名変更に失敗しました"

End Select

End With

Case Else

MsgBox "アップロードに失敗しました。"

End Select

End With

'streamオブジェクトを閉じる

Stream.Close

Set Stream = Nothing

'ADODB.streamを用意する

Dim postdata() As Byte

Set Stream = CreateObject("ADODB.Stream")

Stream.Open

Stream.Type = 2 ' adTypeText

Stream.LoadFromFile = textfilepath

Stream.Charset = "Shift_JIS"

mimetype = "text/plain"

'バイナリ変換

Dim buf As String

Stream.Position = 0

Stream.Type = 1

With objFileSys

With .GetFile(textfilepath).OpenAsTextStream

buf = .ReadAll

.Close

End With

postdata = buf

'TXTファイルアップロードリクエストを実行

With CreateObject("WinHttp.WinHttpRequest.5.1")

.Open "POST", endpoint, False

'.setProxy 2, proxyuri

.SetRequestHeader "Content-Type", mimetype

.SetRequestHeader "Authorization", "Bearer " & access_token

.SetRequestHeader "Content-Length", Stream.Size

.Send postdata

'返ってきた値をもとにデータを処理

Select Case .status

Case 200

'返ってきたJSON文字列を取得

json = .ResponseText

'パース関数でJSONオブジェクトを取得

Set jsn = doc.JsonParse(json)

'ファイルのIDを取得する

temprec = CallByName(jsn, "id", VbGet)

'リネーム用のエンドポイントを指定（ついでに移動先フォルダの指定）

requrl = renemepoint & temprec & "?addParents=" & upfolder

Debug.Print requrl

'リクエストボディ

Set JsonObject = CreateObject("Scripting.Dictionary")

JsonObject.Add "name", "module.txt" 'ファイル名の指定

'3秒間ウェイトをかける

Sleep 3000

'HTTPリクエスト

With CreateObject("WinHttp.WinHttpRequest.5.1")

.Open "PATCH", requrl, False

'.setProxy 2, proxyuri

.SetRequestHeader "Content-Type", "application/json"

.SetRequestHeader "Authorization", "Bearer " & access_token

.Send JsonConverter.ConvertToJson(JsonObject)

'返ってきた値をもとにデータを処理

Select Case .status

Case 200

'レスポンスヘッダを取得

Debug.Print .getAllResponseHeaders()

'終了メッセージ

Debug.Print "ファイルアップロード完了"

'ファイルIDを格納する

txtid = temprec

Case Else

MsgBox "ファイル名変更に失敗しました"

End Select

End With

Case Else

MsgBox "アップロードに失敗しました。"

End Select

End With

'streamオブジェクトを閉じる

Stream.Close

Set Stream = Nothing

'GAS変換URLに送るURLを構築

Dim sendurl As String

Dim result As String

sendurl = converturl & "?param1=" & xlsid & "&param2=" & txtid

With CreateObject("WinHttp.WinHttpRequest.5.1")

.Open "GET", sendurl, False

.SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"

.SetRequestHeader "Authorization", "Bearer " & access_token

.SetTimeouts 100000, 100000, 100000, 100000

.Send

result = .ResponseText

End With

'スプレッドシートを開く

Dim WSH

Set WSH = CreateObject("Wscript.Shell")

WSH.Run result, 3

Set WSH = Nothing

MsgBox "エクスポート完了"

End Sub

Geminiを使ってVBAからGoogle Apps Scriptに変換する仕組みを作る【GAS】

今回利用するスプシやライブラリ等

事前準備

Gemini 1.5 FlashのAPIキーを取得する

GCP側の準備

Google Apps Script APIをオンにする

GCP側でAPIを有効化する

GCP側とリンクする

認証情報を作成する

GAS側の準備

ライブラリを導入する

Drive APIを有効にする

APIキーを書き込む

ウェブアプリケーションとしてデプロイ

適当なフォルダのIDを取得しておく

VBA側の準備

各種ライブラリを追加する

コードに値を記述する

VBAのセキュリティ設定の変更

実際に使ってみる

VBA側で認証を実行する

VBA側で変換を実行する

変換結果を確認する

ソースコード

GAS側コード

コード.gs

xlsxconvert.gs

Gemini.gs

VBA側コード

Scopeに注意

変換実行するコード

関連リンク

コメントを残すコメントをキャンセル

AppSheetのマップで使えるKMLを作る

Power Automate DesktopでGoogleスプレッドシートは操作出来るのか？新着!!

今回利用するスプシやライブラリ等

事前準備

Gemini 1.5 FlashのAPIキーを取得する

GCP側の準備

Google Apps Script APIをオンにする

GCP側でAPIを有効化する

GCP側とリンクする

認証情報を作成する

GAS側の準備

ライブラリを導入する

Drive APIを有効にする

APIキーを書き込む

ウェブアプリケーションとしてデプロイ

適当なフォルダのIDを取得しておく

VBA側の準備

各種ライブラリを追加する

コードに値を記述する

VBAのセキュリティ設定の変更

実際に使ってみる

VBA側で認証を実行する

VBA側で変換を実行する

変換結果を確認する

ソースコード

GAS側コード

コード.gs

xlsxconvert.gs

Gemini.gs

VBA側コード

Scopeに注意

変換実行するコード

関連リンク

コメントを残す コメントをキャンセル

AppSheetのマップで使えるKMLを作る

Power Automate DesktopでGoogleスプレッドシートは操作出来るのか？新着!!

コメントを残すコメントをキャンセル