Geminiを使ってVBAからGoogle Apps Scriptに変換する仕組みを作る【GAS】
新規にGoogle Workspaceを導入しようとなると１つ大きな障害があります。それが「マクロ資産はどうする？」問題。Enterprise PlusプランだとMacro Converterというのが利用できますが、この為だけに最上位の高価なプランに入るのはちょっと。
そこで、今回VBA + GAS + Gemini 1.5 Flashを使ってVBAコードをGASに変換し、スプシにPushするという仕組みを構築してみました。
VBA側ではModuleを出力したりファイルをアップし、サーバーたるGAS側は変換を主に担当します。
今回利用するスプシやライブラリ等
- 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という数学やプログラミングに焦点を当てたモデルがでるそうな。これを使った場合、より高度な変換が期待できるかもしれない。
制限事項
VBA→GAS変換するに当たって、Quota一覧を見てみるとGASのプロジェクト作成は1アカウント1日最大50個までのようです。よって、50個以上の変換をしたい場合には他のアカウント（例えばCloud Identity Freeでも可）を利用して変換する必要性があります。
事前準備
Gemini のAPIキーを取得する
GeminiのAPIキーが必要です。以下のエントリーに独立してまとめています。課金されますので利用のしすぎには要注意。また課金されていない場合、学習に利用されてしまう恐れがあるので、きちんとGoogle Cloud上で課金アカウントとの紐付けなどをしておきましょう。
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を有効化する必要があるので、連結しておきましょう。
図：プロジェクト変更画面
認証情報を作成する
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同意画面の設定
GAS側の準備
ライブラリを導入する
GASのコードのバックアップや復元、またマニフェストの内容を更新する場合、Google Apps Script APIを利用してREST APIを叩いて操作する必要があります。リファレンスはこちらにあります。
- こちらのサイトに掲示されてるライブラリのスクリプトIDを取得する
//スクリプト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部分にはいくつか前述までに取得したものを記述として追加する必要があります。
- 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
変換結果を確認する
最終的に変換が完了すると、スプレッドシートが自動的に開かれて、対象のスプシ内にコードが入っていれば完了。このコードをテスト実行して認証すれば、移植が完了ということになります（アップしたフォルダ内には２個のファイルとは別に変換結果のコード.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
}
xlsxconvert.gs
Drive APIを使ってExcelファイルをスプレッドシートに変換するコードです。詳細については以下のエントリーでまとめています。
//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;
}
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" '半角スペースで区切る
変換実行するコード
行ってる作業は
- 自分自身をコピー
- 自身の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 & "¶m2=" & 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
VBAファイルを解析
xlsm形式のファイルから直接的にVBAのコードを引き抜けないか？ということで調べてみましたが、かなり面倒。
VBAコードが入ってる本体
xlsm形式のファイルはZIPで固められたファイルなので、拡張子をZIPにして解凍してみる。中に入ってるxlフォルダの中の「vbaProject.bin」、これが本体です。しかしバイナリファイルなので、どうにもこうにも。
SheetJSというJavaScriptライブラリではこのvbaproject.binの中身から取り出せるとのことなのですが、有償のPro版でなければ出来ないようです。このvbaproject.binというのが、Compound File Binary File Formatという特殊なファイルのようで、よってGASから直接的に引き抜くというのは難しそう（ただし、7zではこのファイルを解凍できるようです）。
図：これが問題のファイル
Pythonのライブラリで取り出す
Node.jsを使って取り出すのは難しそう。ということで利用するのがpython-oletools。このライブラリを使うとxlsmから直接的にVBAコードを引き抜くことが可能のようです。macOSでとりあえずPythonに対してこのライブラリを追加してコマンドラインから操作してみました。
インストール
公式サイトを見てコマンドを打っても入ってくれなかったので、以下のコマンドをターミナルから打ってみてインストールすることができました。ただパスが通っていないので、このままでは使えない。
brew install pipx pipx install oletools
Homebrewでpipxを入れてから、pipxを使って入れると目的のコマンドをインストールすることが可能です。
PATHを通す
次にこのpipxでインストールしたコマンド群のある場所のパスを通します。
- open ~/.zshrcでzshrcのファイルを開く
- 以下の内容を追記して上書き保存する
export PATH=$PATH:/Users/hiroyuki/.local/bin
- source ~/.zshrcを実行し、ターミナルを再起動する
- olevbaを実行してコマンドリストが出てきたら成功。
echo $PATHをターミナルで実行すればパスが通ってることが確認できます。
実行してみる
実際にxlsmファイルに対してコマンドを実行してみます。
- xlsm形式のファイルがある場所までターミナルで移動する
- 以下のコマンドを実行する
olevba -c test.xlsm
- VBAコードが見事に表示される
Pythonのプログラムからも呼び出すことが可能のようで、Cloud FunctionsでPython3を動かしCloud Storageにファイルを配置してGASから叩いてあげれば、クラウドベースでVBAのコードを引き抜いて、今回のプログラムで変換して埋め込むといったようなことができるのではないか？と思います。過去にNode.jsでは似たようなものを構築しています。
図：Pythonならば引き抜ける
Cloud Run Functionを使って引き抜く
前述ではターミナルのコマンドを使ってコードを引き抜いていますが、以下のエントリーにてCloud Storageにxlsmファイルをアップ、Cloud Run FunctionsでPythonコードを動かし、GAS側へと引き抜いたコードを返すという仕組みを構築することが出来ました。
抜いたコードをさらに前述のGeminiでGASに変換し、スプシに対してコードをインポートするようなものに繋げることができれば、クラウドだけでVBA→GAS変換を完結させることが出来るようになります。
関連リンク
- プログラムから Office VBA プロジェクトへのアクセスが拒否される
- ExcelVBAのソースコードを一括で抽出
- 【すぐ出来る】VBAを使ってテキストファイルを出力してみよう
- ExcelVBAでソースコード一括出力 & クラス一括開放
- GASでwebアプリの作成とパラメータの確認方法(doGet、doPost)
- VBAからWinHTTPRequestで文字列データを投げ込んだら化けて困った話
- tanaikech/ProjectApp2
- 開いたままで自分自身をバックアップ(コピー)するプロシージャ
- 【Google Workspace】Macro Converterを使ってExcelマクロを変換してみた
- VBA→GASに移植する際に調べたり試したりしたこと
- Excel文書をGoogleスプレッドシートに移行支援、永和がVBAからGoogle Apps Scriptへの変換サービスを提供開始
- ChatGPTでGASスクリプトを出力させ、難しい作業を自動化する手順【AIワークハック】
- 実務であまり役に立たないVBAの内部の話
- Add VBA script to file I am creating from scratch #2805
- how to read vba code from hex stream extracted from vbaProject.bin?
- extracted VBA hex files from vbaproject.bin - how to extract VBA code? #38
- How to Download and Install oletools
- python-oletools
- macOS の zsh ではこれだけはやっておこう
- How to use the oletools.olevba.VBA_Parser function in oletools
- Extract VBA as TXT from XLSM with Python
