2024年12月10日 / 最終更新日時 : 2024年12月10日 officeの杜 Google

Pythonを使ってExcelからVBAコードを引き抜く方法【GAS】

以前、Geminiを使ってExcelのVBAコードをGASに変換するツールを作成しました。Excel VBA側でコードを引き抜いて、GAS側に渡す仕組みになってるため、VBAとGASの2つの仕組みが必要になります。ただ一番スマートな仕組みでもあります。

しかしVBAコードを引き抜く部分をクラウド側で実現出来たら、VBA側での仕組みが不要になるのでなんとか出来ないかなぁと思い調べてみた所、Pythonで実現出来そうなので、GCPを利用しつつ実現してみました。

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

今回利用するツール等

GCFとPythonでVBA引き抜く - Google Spreadsheet
Cloud Run Functions
Cloud Storage
Python 3.9
oletools

oletoolsはローカルで動作するPythonを使ってVBAのコードを引き抜くツールです。今回、Cloud Run Functionsで利用するのでGCP上で動作させます。合わせて、xlsmファイルを渡す必要がある為、Cloud Storageへのアップロードも必要になります。

以下の2つの内容も併用することになるため、先に閲覧していただければと思います。

Google Apps ScriptでCloud Run Functionsを操作する【GAS】

Google Apps ScriptでCloud Storageにファイルをアップする【GAS】

事前準備

GCP上の準備

APIの有効化

今回はGCP側のGoogle Cloud Storage JSON APIを利用してファイルをアップロードします。

GCPのAPI とサービスの画面を開く
APIとサービスを有効にするをクリックする
cloud storageで検索すると出てくる「Google Cloud Storage JSON API」を見つけてクリック
有効化をクリックする

図：Cloud Storage JSON APIを有効化する

サービスアカウントの作成

Cloud Run FunctionsやCloud StorageをAPI経由で操作する場合に利用するサービスアカウントを用意します。このサービスアカウントはGCSおよびGCF両方で利用します。

GCPの左サイドバーからIAMと管理を開く
サービスアカウントを開く
サービスアカウントを作成をクリックし、サービスアカウントの名前を入力。わかりやすい名前をつけましょう。キーのタイプはJSONを選択。
サービスアカウントの役割では、今回はとりあえず編集者にしましたが、本来はより小さいロールに絞る必要があります。
作成が完了したら、作ったサービスアカウントをクリックして中に入る
上部にある「鍵」をクリックする
キーを追加をクリックし、新しい鍵を作成をクリックする
JSONのまま作成をクリックする
JSONファイルが自動でダウンロードされる
JSONファイルがダウンロードされるので、これを誰とも共有しない形で、Google Driveにアップロードします。流出すると後で課金で痛い目を見るので絶対に共有はしないでください。
アップロードしたJSONファイルのIDを取得する
次の項目のJSONキーファイルを取得して認証するにて、冒頭の変数keyfileの場所にこのIDを入れてあげる。
このアカウント名も控えて於いて、後でCloud Storageのアクセス権限に追加します。

最後にこのサービスアカウントのメアドを控えておきます。あとでGCSでの設定で利用します。

図：このキーファイルが重要です

GAS側の準備

ライブラリの追加

OAuth2.0認証をする為のライブラリを追加します。以下の手順でスクリプトに追加する必要があります。

スクリプトエディタを開く
左サイドバーのライブラリの横にある＋アイコンをクリックする
ライブラリを追加欄に「1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF」を追加します。
バージョンは最新版を選んでおきます。
追加をクリックする

これでサービスアカウントの認証に利用します。

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

プロジェクトの移動

サービスアカウントを利用して認証を行いますが、GCP側のAPIを利用する為、GASのプロジェクトをGCPのプロジェクトに紐付けする必要性があります。連結する手順は以下の通り。

Google Cloud Consoleを開く
左上にある▼をクリックする
ダイアログが出てくるので、新規プロジェクトを作るか？既存のプロジェクトを選択する。この時、G Suiteであれば選択元は「自分のドメイン」を選択する必要があります。
プロジェクト情報パネルから「プロジェクト番号」をコピーする
対象のGoogle Apps Scriptのスクリプトエディタを開く
左サイドバーのプロジェクト設定を開く
Google Cloud Platform（GCP）プロジェクトのところまで移動する
4.で入手した番号をプロジェクトを変更のテキストボックスに入れて、プロジェクトを設定ボタンをクリックする
無事に移動が完了すればメッセージが表示されます。

図：プロジェクト番号が必要

図：GAS側でプロジェクト紐付け

Cloud Run Functions

今回、Cloud Run FunctionsではいつものNode.jsではなくPythonを利用します。その為の準備をしておきます。

環境の準備

事前にサービスアカウントの作成が必要になりますが、今回はデフォルトで用意されてるサービスアカウントをそのまま利用します。GCPの画面を開いて作業を開始します。

右上のハンバーガーメニュー（≡）をクリックし、サーバーレス項目にあるCloud Runをクリック
上部にある「関数を作成」をクリックする
インラインエディタで関数を作成するの選択状態のままにする
サービス名には適当な名前を入れます（今回はvbaconertとして入力しました）
エンドポイントURLが出ているのでコピーする（例：https://vbaconvert-xxxxx.us-central1.run.app）
ランタイムはPython3.9をとりあえず今回は選んでいます。
未認証の呼び出しを許可に今回はチェックを入れる（認証を要する場合には別途認証を用意する必要があります）。デフォルトではHTTPトリガーは認証を要するので、第一世代パターンでの未認証の場合にはこちらの手順も見ておきましょう。
トリガーは省略します
今回はテストなのでコンテナの設定に於いて、リソースでは、メモリ256MB, CPUは1で設定します。
続けて実行環境に於いては第２世代を選択します。
セキュリティタブでは作成しておいたサービスアカウントを選択します（今回はデフォルトのまま）
作成をクリックする
Cloud Build APIを有効にしろと出てくるので、有効にするをクリックする
するとrequirements.txtやmain.pyの記述画面が表示されるようになる。

さて、この状態ではサービスアカウントを指定していないので、以下の作業を追加で行います。

作成したGCFの中に入り、上部にある「新しいリビジョンの編集とデプロイ」をクリックします。
セキュリティタブを開く
サービスアカウントの欄があるので、ここで前述で作成しておいたサービスアカウントに変更しておきます。
デプロイをクリックして保存する

これでこのGCFについては、指定のサービスアカウントで動かすという規定ができました。

図：Cloud Run FunctionsでPythonを使う

図：作成済みサービスアカウントに変更しておく

Google Cloud Consoleを弄ってみる

requirements.txtの記述

用意したGCFのPython実行環境のソースを開くと、左サイドバーに「requirements.txt」という項目があります。Node.jsで言うところのpackage.jsonみたいな存在でここに利用するライブラリを登録します。今回はCloud Storageとoletoolsの２つを利用しますが、最初から登録されてる項目を消さずに追記で登録します。

最後に保存して再デプロイをクリックして完了です。

functions-framework==3.*
oletools
google-cloud-storage

functions-framework==3.*

oletools

google-cloud-storage

図：利用するライブラリを登録しましょう

ローカル開発環境の用意

以下はmacOSの場合の環境構築ですが、GCF上でソースコードを書いてデバッグはちょっとやりにくいので、ローカル環境である程度動かせるコードにしてからGCF上にコードを貼り付けたほうが生産的です。Homebrewが事前にインストール済みである必要があります。

brew install pipx
pipx install oletools
brew install --cask google-cloud-sdk

brew install pipx

pipx install oletools

brew install --cask google-cloud-sdk

Homebrewでpipxを入れてから、pipxを使って入れると目的のコマンドをインストールすることが可能です。oletoolsとgoogle-cloud-sdkが必要になります。

Cloud Storage

GCF側からExcelファイルにアクセスする為には、Cloud Storageのバケット内にファイルが存在している必要があります。よって、GAS側からアップロードする先として入れ物を用意しておく必要があります。以下の手順でバケットを用意して、アップロード用コードの為のバケット名を取得しておきます。

Cloud Consoleにて、Cloud Storageを開き、バケットの作成をクリック
名前に半角英数字で命名する。これがバケット名になる。（今回はdriveman_gsという名前にしました）
作成をクリックして完了。
今回は更に、publicという名前のフォルダを用意しました。
作成したGCSの中に入る（バケットの詳細）
上部にある「権限」をクリックする
アクセス制御が「均一: 有効なオブジェクトレベルの ACL がない」となってるので、きめ細かい管理に切り替えるをクリックする
きめ細かい管理にチェックを入れて、保存をクリックする
GCSの詳細に戻ってきたら、アクセスを許可をクリックする
サービスアカウントで控えておいたサービスアカウントのメアドを新しいプリンシパルに入れる
ロールは「ストレージオブジェクト作成者」を割り当てます
保存をクリックする

よって、バケット名driveman_gsとpublic以下にファイルをアップするので、ファイル名としてはpublic/hoge.xlsmという形になりますので、控えておきましょう。

図：この名前があとで重要になります

図：きめ細かい管理とする

図：サービスアカウントを権限として追加

ソースコード

GCF側のコード

GCFに対して、「extract_vba」という名前の関数を用意しました。そして、その中に以下の仕様でGAS側からのリクエストに対してコードを引き抜いて返すコードを記述しています。

bucket名とファイルのパスを受け取る。
VBAの標準モジュールに於いて、「Module」と名前がついてるファイルについてだけ抜き出し対象とする。
対象となったコードをvba_codesに加えて最後にGAS側へと返す。
GAS側に返す前にCloud Storageにアップされたファイルを削除する

import oletools.olevba
from google.cloud import storage

def extract_vba(request):
  try:
    # リクエストからバケット名とファイル名を取得
    request_json = request.get_json()
    bucket_name = request_json['bucket']
    filename = request_json['filename']

    # Cloud Storage クライアントを初期化
    storage_client = storage.Client()

    # バケットとファイルを取得
    bucket = storage_client.bucket(bucket_name)
    blob = bucket.blob(filename)

    # ファイルをメモリにダウンロード
    file_content = blob.download_as_bytes()

    # oletools.olevba を使用して VBA コードを抽出
    vba_codes = []
    try:
      vba_parser = oletools.olevba.VBA_Parser(filename='from_bytes', data=file_content)

      for (filename, stream_path, vba_filename, vba_code) in vba_parser.extract_macros():
        # 標準モジュールの名前に "Module" が含まれる場合のみ VBA コードを追加
        if "module" in vba_filename.lower():  
          vba_codes.append(vba_code)  # VBAコードをリストに追加
          print(vba_code)
    except Exception as e:
      print(f"olevbaでエラーが発生しました: {e}")
      return f"olevbaでエラーが発生しました: {e}", 500

    # VBA コードを返す前に、Cloud Storage 上のファイルを削除
    try:
      blob.delete()
      print(f"ファイル {filename} を削除しました。")
    except Exception as e:
      print(f"ファイル削除中にエラーが発生しました: {e}")
      return f"ファイル削除中にエラーが発生しました: {e}", 500

    # VBA コードを返す
    return '\n\n'.join(vba_codes), 200

  except Exception as e:
    print(f"エラーが発生しました: {e}")
    return f"エラーが発生しました: {e}", 500

import oletools.olevba

from google.cloud import storage

def extract_vba(request):

try:

# リクエストからバケット名とファイル名を取得

request_json = request.get_json()

bucket_name = request_json['bucket']

filename = request_json['filename']

# Cloud Storage クライアントを初期化

storage_client = storage.Client()

# バケットとファイルを取得

bucket = storage_client.bucket(bucket_name)

blob = bucket.blob(filename)

# ファイルをメモリにダウンロード

file_content = blob.download_as_bytes()

# oletools.olevba を使用して VBA コードを抽出

vba_codes = []

try:

vba_parser = oletools.olevba.VBA_Parser(filename='from_bytes', data=file_content)

for (filename, stream_path, vba_filename, vba_code) in vba_parser.extract_macros():

# 標準モジュールの名前に "Module" が含まれる場合のみ VBA コードを追加

if "module" in vba_filename.lower():

vba_codes.append(vba_code) # VBAコードをリストに追加

print(vba_code)

except Exception as e:

print(f"olevbaでエラーが発生しました: {e}")

return f"olevbaでエラーが発生しました: {e}", 500

# VBA コードを返す前に、Cloud Storage 上のファイルを削除

try:

blob.delete()

print(f"ファイル {filename} を削除しました。")

except Exception as e:

print(f"ファイル削除中にエラーが発生しました: {e}")

return f"ファイル削除中にエラーが発生しました: {e}", 500

# VBA コードを返す

return '\n\n'.join(vba_codes), 200

except Exception as e:

print(f"エラーが発生しました: {e}")

return f"エラーが発生しました: {e}", 500

GAS側のコード

サービスアカウント認証

authorize.gsの中に以下のコードを記述しました。keyfileにはサービスアカウントのJSONキーファイルのファイルIDを入れて、startoauthを実行すると認証が完了しダイアログが出ます。ユーザの認証ではないのでこれで完了です。

//サービスアカウントのJSONキーファイルのID
var keyfile = "ここにJSONキーファイルのIDを入れる";

//認証用各種変数
var tokenurl = "https://accounts.google.com/o/oauth2/token"

//OAuth2認証を実行する
function startoauth(){
  //UIを取得する
  var ui = SpreadsheetApp.getUi();
  
  //認証を実行する
  var service = checkOAuth();
  ui.alert("認証が完了し、Access Tokenを取得しました。")
}
 
//Google DriveにあるサービスアカウントキーのJSONファイルを取得する
function getServiceAccKey(){
  //JSONファイルの中身を取得する
  var content = DriveApp.getFileById(keyfile).getAs("application/json").getDataAsString();
  return JSON.parse(content);
}
 
//アクセストークン取れてるかどうかチェック
function acctokencheck(){
  var ui = SpreadsheetApp.getUi();
  var service = checkOAuth();
  ui.alert(service.getAccessToken());
}
 
//OAuth2.0認証を実行する
function checkOAuth() {
  //JSONファイルの中身を取得する
  var privateKeys = getServiceAccKey();
  
  return OAuth2.createService('gcs_authorize:' + Session.getActiveUser().getEmail())
  //アクセストークンの取得用URLをセット
  .setTokenUrl(tokenurl)
  
  //プライベートキーとクライアントIDをセットする
  .setPrivateKey(privateKeys['private_key'])
  .setIssuer(privateKeys['client_email'])
  
  //Access Tokenをスクリプトプロパティにセットする
  .setPropertyStore(PropertiesService.getScriptProperties())
  
  //スコープを設定する
  .setScope('https://www.googleapis.com/auth/cloud-platform https://www.googleapis.com/auth/devstorage.read_write');
}

//サービスアカウントのJSONキーファイルのID

var keyfile = "ここにJSONキーファイルのIDを入れる";

//認証用各種変数

var tokenurl = "https://accounts.google.com/o/oauth2/token"

//OAuth2認証を実行する

function startoauth(){

//UIを取得する

var ui = SpreadsheetApp.getUi();

//認証を実行する

var service = checkOAuth();

ui.alert("認証が完了し、Access Tokenを取得しました。")

}

//Google DriveにあるサービスアカウントキーのJSONファイルを取得する

function getServiceAccKey(){

//JSONファイルの中身を取得する

var content = DriveApp.getFileById(keyfile).getAs("application/json").getDataAsString();

return JSON.parse(content);

}

//アクセストークン取れてるかどうかチェック

function acctokencheck(){

var ui = SpreadsheetApp.getUi();

var service = checkOAuth();

ui.alert(service.getAccessToken());

}

//OAuth2.0認証を実行する

function checkOAuth() {

//JSONファイルの中身を取得する

var privateKeys = getServiceAccKey();

return OAuth2.createService('gcs_authorize:' + Session.getActiveUser().getEmail())

//アクセストークンの取得用URLをセット

.setTokenUrl(tokenurl)

//プライベートキーとクライアントIDをセットする

.setPrivateKey(privateKeys['private_key'])

.setIssuer(privateKeys['client_email'])

//Access Tokenをスクリプトプロパティにセットする

.setPropertyStore(PropertiesService.getScriptProperties())

//スコープを設定する

.setScope('https://www.googleapis.com/auth/cloud-platform https://www.googleapis.com/auth/devstorage.read_write');

}

GCSへアップロード

Google Drive上にアップされたVBA入りのExcelファイル（xlsmファイル）について、Cloud Storageの該当の場所にアップロードするコードです。昔に書いたものより若干変わってる場所があります。

methodは「PUT」を指定します。
リクエストの内容にhostという項目が増えているので、これを追加する
gcsurlのリクエストエンドポイントが以前のコードとはちょっとURLが変わっていますので注意。

無事に動くと指定のバケットのpublicフォルダ以下に対してファイルがアップロードされます。

//アップロードするファイル
var fileid = "ここにアップロードするxlsmファイルのIDを入れる";

//GCS用の変数
var bucket = "ここにGCSのバケット名を入れる";

//アップロード先フォルダを指定
var gcsfolder = "ここにGCSのフォルダ名を入れる";

//アップするファイル名を指定
var filename = "ここにアップするファイルのファイル名を入れる";

//GCSにファイルをアップロードする
function gcsuploader(){
  //アップロード先フォルダを指定
  let target = "/" + gcsfolder + "/" + filename;
  
  //Access Tokenを取得
  let service = checkOAuth();
  if (!service.hasAccess()) {
    Logger.log("まずは認証を実行してください。 %s", service.getAuthorizationUrl());
    return;
  }
  
  //指定ファイルを取得
  let blob = DriveApp.getFileById(fileid).getBlob();
  let bytes = blob.getBytes();
  
  //アップロードURLを組み立て
  let gcsurl='https://storage.googleapis.com/'+ bucket + target;
  
  //UrlfetchAppでアクセス（上書きアップロードになる）
  let res = UrlFetchApp.fetch(gcsurl,{
    headers: {
      Authorization: "Bearer " + service.getAccessToken(),
    },
    method: "PUT",
    contentType: blob.getContentType(),
    host: bucket + ".storage.googleapis.com",
    payload: bytes
  });

  //レスポンスを取得する
  let rescode = res.getResponseCode();

  //アップロード結果を取得する
  if(rescode == 200){
    return true;
  }else{
    return false;
  }
}

//アップロードするファイル

var fileid = "ここにアップロードするxlsmファイルのIDを入れる";

//GCS用の変数

var bucket = "ここにGCSのバケット名を入れる";

//アップロード先フォルダを指定

var gcsfolder = "ここにGCSのフォルダ名を入れる";

//アップするファイル名を指定

var filename = "ここにアップするファイルのファイル名を入れる";

//GCSにファイルをアップロードする

function gcsuploader(){

//アップロード先フォルダを指定

let target = "/" + gcsfolder + "/" + filename;

//Access Tokenを取得

let service = checkOAuth();

if (!service.hasAccess()) {

Logger.log("まずは認証を実行してください。 %s", service.getAuthorizationUrl());

return;

}

//指定ファイルを取得

let blob = DriveApp.getFileById(fileid).getBlob();

let bytes = blob.getBytes();

//アップロードURLを組み立て

let gcsurl='https://storage.googleapis.com/'+ bucket + target;

//UrlfetchAppでアクセス（上書きアップロードになる）

let res = UrlFetchApp.fetch(gcsurl,{

headers: {

Authorization: "Bearer " + service.getAccessToken(),

method: "PUT",

contentType: blob.getContentType(),

host: bucket + ".storage.googleapis.com",

payload: bytes

});

//レスポンスを取得する

let rescode = res.getResponseCode();

//アップロード結果を取得する

if(rescode == 200){

return true;

}else{

return false;

}

GCFへリクエスト

ファイルのアップロードが完了したら、次にGCFに用意した関数を実行して、コードを引き抜く為のリクエストをします。

bucketとfilenameの指定が必要です。これをpayloadに加えてリクエストを投げます。filenameは「public/hoge.xlsm」といった形で指定します。
methodは「POST」でリクエストを送ります（上記のpayloadがあるため）
レスポンスには引き抜いたVBAコードが返ってきますので、これをさらにGeminiに投げるなどしてGASのコードに変換するであったり、ファイル化してドライブに保存することが可能です。

//GCS用の変数
var bucket = "ここにGCSのバケット名を入れる";

//アップロード先フォルダを指定
var gcsfolder = "ここにGCSのフォルダ名を入れる";

//アップするファイル名を指定
var filename = "ここにアクセスするファイル名を入れる";

//GCF側のHTTPエンドポイント
var url = "ここにGCFのエンドポイントのURLを入れる";

//アップされたファイルを元にGCFを叩いてVBAコードを引き抜く
function rungcf_func() {
  //リクエストボディ
  let payload = {
    "bucket": bucket,
    "filename": gcsfolder + "/" + filename
  }

  //POSTで関数を実行する
  let response = UrlFetchApp.fetch(url, {
    method: 'POST',
    contentType: "application/json",
    payload : JSON.stringify(payload),
    muteHttpExceptions: true
  });
  
  //サーバーレスポンスコードを取得する
  let rescode = response.getResponseCode();
 
  //リターンされて来たJSONデータを取得する
  if (rescode === 200) {
    console.log(response.getContentText())
  }else{
    //エラーメッセージ
  }
}

//GCS用の変数

var bucket = "ここにGCSのバケット名を入れる";

//アップロード先フォルダを指定

var gcsfolder = "ここにGCSのフォルダ名を入れる";

//アップするファイル名を指定

var filename = "ここにアクセスするファイル名を入れる";

//GCF側のHTTPエンドポイント

var url = "ここにGCFのエンドポイントのURLを入れる";

//アップされたファイルを元にGCFを叩いてVBAコードを引き抜く

function rungcf_func() {

//リクエストボディ

let payload = {

"bucket": bucket,

"filename": gcsfolder + "/" + filename

}

//POSTで関数を実行する

let response = UrlFetchApp.fetch(url, {

method: 'POST',

contentType: "application/json",

payload : JSON.stringify(payload),

muteHttpExceptions: true

});

//サーバーレスポンスコードを取得する

let rescode = response.getResponseCode();

//リターンされて来たJSONデータを取得する

if (rescode === 200) {

console.log(response.getContentText())

}else{

//エラーメッセージ

}

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

Google Apps ScriptでGeminiを叩いてスライドを生成する【GAS】

今回利用するツール等

事前準備

GCP上の準備

APIの有効化

サービスアカウントの作成

GAS側の準備

ライブラリの追加

プロジェクトの移動

Cloud Run Functions

環境の準備

requirements.txtの記述

ローカル開発環境の用意

Cloud Storage

ソースコード

GCF側のコード

GAS側のコード

サービスアカウント認証

GCSへアップロード

GCFへリクエスト

関連リンク

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

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

Google Apps ScriptでGeminiを叩いてスライドを生成する【GAS】

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