【VBA+GAS】ローカルで保存したエクセルを(ほぼ全自動で)Google Driveに保存する

うちの会社はM365とGsuiteのどちらも使っています。
(ちなみに利用頻度はOffice＞＞＞＞＞＞｜超えられない壁｜＞Gsuite)

最近ひそかに(？)Gsuiteが便利なストレージ代わりとして使われることが多くなってきて、

「ローカルで管理しているマスタのエクセルファイルを他部署とも共有できるようにDrive上にアップロードできないか？」

というご要望が…

この手のものはなるべく二重管理したくねぇ…
そのままファイル共有すると壊されそうだし、
スプレッドシートでマスタを管理するとエクセル開きながら作業ができない上に、
弊社ではフォルダやSharepointへのアクセス権の管理が面倒なのでDriveとなりました。

まぁまぁ頻繁に更新されるファイルなので、
「チっ、いちいちChrome開いてアップロードすんのクソめんどくせぇな・・・」
と思っていたら自動化できたので書きます。

ツールの構成

※VBA⇒GASで処理します。

■VBAの部分(保存と同時に実行)

①マスタファイルのエクセルが所定のフォルダ内で保存されたら
②そのエクセルを添付としてOutlookからGmailあてにメール送る

VBAの部分で参考にしたサイト
この記事のタイトルが「ほぼ全自動で」となっているのは
VBAからメールを送ったときにOutlookにセキュリティ上の問題で送信するかどうかの確認を取られるからです。
下のリンクに対処法がのってますが私の環境では情シスにがちがちに管理されていたのでそもそもいじれませんでした。
まぁ、設定しても万が一ウイルスに感染したときに怖いのでこのままにしておくのが無難です。

support.microsoft.com

■GASの部分(トリガー実行)

①条件に合うメール(題名、ラベル、添付検索)をみつけたら
②すでにフォルダに入っている過去版のマスタファイルを過去フォルダにうつして
③更新されたマスタファイルの添付をフォルダにドライブに保存
④処理が終わったらメールに処理済みとわかるようにラベルをつける

GASの部分で参考にしたサイト
ファイルの移動っていう概念がないのは意外でした。
後ラベルつけるところてこずったけど、etauさんの教えてくれたレファレンスのヒントで書けました。
tonari-it.com
tyru.hatenablog.com
tonari-it.com

というわけでローカルで保存したエクセルをDriveにアップロードってきくと
一見魔法かRPAを使ってるっぽいけど、構造はめちゃ簡単！
(というかメールを使っていろいろできるVBAもGASもどっちもすごいのでできた)

以下コードです。

VBAの部分(更新したいマスタファイルのWorkbookモジュールの中にかきます。)

'保存後に作動してほしいのでWorkbookモジュールにかきます
Private Sub Workbook_AfterSave(ByVal Success As Boolean)

Application.ScreenUpdating = False
Application.DisplayAlerts = False

'保存先フォルダの指定
Dim savePath As String
Dim savePath2 As String
Dim gmailAddress As String
Dim uploadToDrive As Integer

'指定したパスにあるアカウントリストが保存されるとマクロが実行されます
savePath = "Z:\hogehoge\fugafuga"
savePath2 = "\\xxxx\hogehoge\fugafuga"
gmailAddress = "実行するGmailアドレス"

'アカウントリストのフォルダで保存されたらマクロを実行(相対パスと絶対パスがあるので一応二つ書いておく…)
If ThisWorkbook.Path = savePath Or ThisWorkbook.Path = savePath2 Then

'アカウントリストへアップロードするかどうか聞く(OKの場合メールを送信)
    uploadToDrive = MsgBox("GoogleDriveへアカウントリストをアップロードしますか？", vbOKCancel + vbQuestion, "保存前の確認")

    If uploadToDrive = 1 Then
'添付用にコピー版(パスワードつきxlsxで)を同じディレクトリに作成
        Dim newName As String
        Dim newPath As String
        newName = Format(Now(), "yyyymmdd_hhmmss") & "【XX営業部】マスタリスト"
        newPath = ThisWorkbook.Path & "\" & newName & ".xlsx"

        ActiveWorkbook.Sheets.Copy
        ActiveWorkbook.SaveAs Filename:=newPath, FileFormat:=51, Password:="1234"
        ActiveWorkbook.Close


        '▼▼▼▼▼ここからメールを送る部分▼▼▼▼▼
        'Outlook準備(参照設定でOutlookLibrary有効にしないとあかんです)
        Dim objOutlook As Object
        Set objOutlook = New Outlook.Application
        Dim objMail As Object
        Set objMail = objOutlook.CreateItem(olMailItem)


        'メール作成&送信(うちはセキュリティ上ポップアップで送信を押さないと送信できない)
        objMail.To = gmailAddress
        objMail.Subject = "マスタリスト自動更新マクロ"
        objMail.BodyFormat = olFormatPlain
        objMail.Body = "マクロから送信"
        Call objMail.Attachments.Add(newPath)
        objMail.Send
        '▲▲▲▲▲ここまで▲▲▲▲▲
        '添付用につくった一時ファイルを削除
        Kill newPath
    End If
End If

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

GASの部分(あらかじめメールのラベルは指定しておきます)

//大事な情報はスクリプトプロパティにいれとく
//保存するフォルダと過去ファイルを保存する用のoldフォルダ
const SAVE_FOLDER_ID = PropertiesService.getScriptProperties().getProperty('SAVEFOLDER_ID');
const OLD_FOLDER_ID = PropertiesService.getScriptProperties().getProperty('OLDFOLDER_ID');
//あらかじめGmail上でラベルつくっときます
const DONE_LABEL = 'リスト更新処理済';
//メール検索条件：VBAで作ったほうのメールのタイトルを含む+添付あり+まだ処理されていないメール
const SEARCH_TERM = 'subject:(マスタリスト自動更新マクロ) has:attachment -label:' + DONE_LABEL;


//メールを探してファイルを更新する関数(お好みの間隔で実行されるようにトリガーを指定しておく)
function fetchFile() {
    const folder = DriveApp.getFolderById(SAVE_FOLDER_ID);
    const threads = GmailApp.search(SEARCH_TERM, 0, 10);
    const messages = GmailApp.getMessagesForThreads(threads);
    const doneLabel = GmailApp.getUserLabelByName(DONE_LABEL);

    if (threads.length == 0) {
        console.log('対象なし');
    } else {
        //過去ファイルをoldフォルダに移動させる関数を呼び出し
        movefile();
        //条件に合ったメールの添付をフォルダに保存して、処理がおわったらメールにラベルを付ける 
        for (const thread of messages) {
            for (const message of thread) {
                const attachments = message.getAttachments();
                const mesId = message.getId();
                for (const attachment of attachments) {
                    folder.createFile(attachment);
                    GmailApp.getMessageById(mesId).getThread().addLabel(doneLabel);
                }
            }
        }
    }
}

//過去ファイルをoldフォルダに移動させる関数
function movefile() {
    var sourceFolder = DriveApp.getFolderById(SAVE_FOLDER_ID);
    var destinationFolder = DriveApp.getFolderById(OLD_FOLDER_ID);

    //フォルダー内の全ファイルを格納する
    var folderFiles = sourceFolder.getFiles();

    // ファイルがある限りoldフォルダへ格納して元を削除していく
    while (folderFiles.hasNext()) {
        var fileIterator = folderFiles.next();
        var fileId = fileIterator.getId();
        var child = DriveApp.getFileById(fileId);
        destinationFolder.addFile(child); 
        sourceFolder.removeFile(child); 
    }

}