f:id:shikaku_sh:20210524153300p:plain

Spread Sheet と GAS
コーディング
参考

古来からのプログラマーは、Excel VBA を覚える気がなくても日本の社会通念より必ず身につけてしまうものでした。現在でも Excel ＋ VBA を使うケースがちょこっとあるんですが「自動化」をしたいときは Google Apps Script のほうがいいよな、と改めて実感する機会に恵まれたのでメモ。

特定のフォルダーないに、次のアクションがあったとき：

フォルダーが作成される
すでに存在するフォルダーの更新日時が更新される

自動的にメールを送信して更新をチームに周知させる、という目的です。なんかのデータが公開された、というような通知を自動化したかったのです。

「自動的に」というのがポイントになっていて、Excel + VBA は Excel ファイルを開かないと（基本的に）プログラムが走りません。なんで、勝手にやっててください、という状態を構築するのは得意ではありません。Google SpreadSheet はクラウドの特性を持つので（わりかし）得意かも。

Excel + Power Automate で組み合わせる、または、Power Automate で完結しているというのも今だったらありえますが、ちょっとした 100 行程度のコードで走るプログラムは、やっぱり VBA や GAS が便利に思えるのです。シンプルな構成で完結する、という強みのためです。

Spread Sheet と GAS

Google Spread Sheet は Web アプリケーションです。ファイルとして、存在していても保存されている情報は、（メモ帳で開くとわかりますが）次の３つだけ：

url
doc_id
email

このため、基本的には Google Spread Sheet は Google Drive 内にしか生息できません。Google Drive に実体がある、という認識でよいと思います。これは Google Document もそうだし、基本的なルールになっています。

はてなにて gas で記述したコードブロックを markdown でシンタックスハイライトにするときは、「Creating and highlighting code blocks 」を参照するも適切なものはわからなかったです。javascript で仮対応。

コーディング

正直、GAS の言語仕様を私は、全然まったく調べていないです。サンプルコードも冗長なシート取得を繰り返しているので、実効速度の観点でみると効率的ではないです。

ざっくりとしたことだけメモ。

スクリプトエディターを開くとき

どこを開くんだっけ、ってなるけど：

ツール > スクリプトエディタ

f:id:shikaku_sh:20210524154028p:plain:w500

デバッグを有効化する

デバッグが起動できるか、ブレークポイントが有効か、単純なサンプルコードでテストする。

function Debug()
{
  Logger.log("test");
}

実行ログは Ctrl + Enter で表示する。ただし、エディターにフォーカスがあるときは機能しないショートカットなので、フォーカスをメニューなどに移してからショートカットキーを利用する。

初回実行時は、「承認」が必要になることがあります。

f:id:shikaku_sh:20210524152704p:plain:w600 f:id:shikaku_sh:20210524152707p:plain:w600

サンプルコード

単純なコードなんで、一度も GAS に触れたことがなくても理解できると思います。

var TargetFolderID = "フォルダーのID";

var SpreadSheetID = "スプレッドシートのID";
var LoggingSheetName = "スプレッドシートのシート名";

var ToMailAddresses = ["メールアドレス 1", "メールアドレス 2"];

function UpdateLog()
{
    var notifications = WriteLog_(TargetFolderID, SpreadSheetID, LoggingSheetName);

    if (notifications.length > 0)
    {
       SendMail_(ToMailAddresses, notifications);
    }
}

function SendMail_(to, notifications)
{
    var titles = "";

    for (var i in notifications)
    {
        var notification = notifications[i];

        if (titles != "")
        {
            titles += "\r\n";
        }

        titles += notification.name;
    }

    var body = "指定したフォルダーが更新されました。\r\n" +
               "更新されたフォルダーは次のフォルダーです。\r\n\r\n" +
               "--\r\n" +
               titles + 
               "\r\n" +
               "--\r\n\r\n" +
               "このメールは送信専用です。返信しても確認できません。ご了承ください。";

    for (var i in to)
    {
        var mail = to[i];

        MailApp.sendEmail(mail, "指定フォルダー更新 連絡", body);
    }
}

function WriteLog_(folderID, sheetID, sheetName) 
{
    var folders = GetFoldersById_(folderID);
    var logs = GetSheetLogsByIdAndName_(sheetID, sheetName);

    var spreadSheet = SpreadsheetApp.openById(sheetID);
    var sheet = spreadSheet.getSheetByName(sheetName);

    var notifications = [];

    for (folderTitle in folders)
    {
        var selectedFolder =  folders[folderTitle];

        if (selectedFolder.name in logs)
        {
            // フォルダー名がシートに存在するときは、更新日時を確認して更新日時が更新されたとき対象
            if (selectedFolder.lastUpdated > logs[selectedFolder.name].lastUpdated)
            {
                sheet.getRange(logs[selectedFolder.name].rowNo, 2).setValue(logs[selectedFolder.name].lastUpdated);
                notifications.push(selectedFolder);
            }
        }
        else 
        {
            var lastRow = sheet.getLastRow();

            // フォルダー名がシートに存在しないときは、新規の通知対象
            sheet.getRange(lastRow + 1, 1).setValue(selectedFolder.name);
            sheet.getRange(lastRow + 1, 2).setValue(selectedFolder.lastUpdated);
            notifications.push(selectedFolder);
        }
    }

    return notifications;
}

function GetFoldersById_(id)
{
    var targetFolder = DriveApp.getFolderById(id);
    var folders = targetFolder.getFolders();
    var files = targetFolder.getFiles();

    var lastUpdateMap = {};
    var i = 1;

    // 指定したフォルダー内のフォルダーの名前と最終更新日を取得
    while (folders.hasNext())
    {
        var folder = folders.next();

        lastUpdateMap["folder_" + i++] = { name:folder.getName(), lastUpdated:folder.getLastUpdated() };
    }

    return lastUpdateMap;
}

function GetSheetLogsByIdAndName_(id, name)
{
    var spreadSheet = SpreadsheetApp.openById(id);
    var sheet = spreadSheet.getSheetByName(name);
    var values = sheet.getDataRange().getValues();

    var logs = {};

    for (var i=1; i < values.length; i++)
    {
        logs[values[i][0]] = { name:values[i][0], lastUpdated:values[i][1], rowNo:i+1 };
    }

    return logs;
}