サイトアイコン わんすけに聞いてみる

[GAS]スプレッドシートの更新を通知する勉強

yizm

以前、ちょろっと勉強したっきり触ってなかったGASについて勉強していく。

まず思いついたのは、Googleドライブ上のファイルが更新されたら通知を飛ばすみたいな処理。

ちょっと調べただけでも、たくさんそんな様なソースが出てきた。

試しに新規のGoogle Apps Scriptを作って張り付けたらサクサク動いた・・・チョー便利。。。

 

でも、これじゃなんの勉強にもならないので試しに動かしたソースを咀嚼してみる。

今回試したのはこちら。

「Google Driveでフォルダ内のファイルが更新されたらメール通知を送るGoogle Apps Script」

 

2019/09/24時点でアップされていたコードはこんな感じ↓↓↓

//対象とするGoogleDriveフォルダのID　ブラウザでアクセスしてURL見れば分かる
var TARGET_FOLDER_ID = "";
//更新日時を記録するのスプレッドシートのID　ブラウザでアクセスしてURL見れば分かる
var UPDATE_SHEET_ID = "";
//スプレッドシートのシート名（下に表示されるタブのやつ）
var UPDATE_SHEET_NAME = "シート1";
var SEND_MAIL_ADDRESS = ["name@example.co.jp","name2@example.co.jp"]

function updateCheck() {
  var targetFolder = DriveApp.getFolderById(TARGET_FOLDER_ID);
  var folders = targetFolder.getFolders();
  var files = targetFolder.getFiles();

  //フォルダ内を再帰的に探索してすべてのファイルIDを配列にして返す
  function getAllFilesId(targetFolder){
    var filesIdList = [];
    
    var files = targetFolder.getFiles();
    while(files.hasNext()){
      filesIdList.push(files.next().getId());
    }
    
    var child_folders = targetFolder.getFolders();
    while(child_folders.hasNext()){
      var child_folder = child_folders.next();
      //Logger.log( 'child_folder :' + child_folder );

      //Logger.log('getAllFilesId(child_folder):'+ getAllFilesId(child_folder));
      filesIdList = filesIdList.concat( getAllFilesId(child_folder) );
    }
    return filesIdList;
  }
  //Logger.log('getAllFilesId(targetFolder):' + getAllFilesId(targetFolder));
  var allFilesId = getAllFilesId(targetFolder);
  var lastUpdateMap = {};
  //Logger.log(folders)
  allFilesId.forEach(
    function( value, i ){
      var file =DriveApp.getFileById( value );
      lastUpdateMap[file.getName()] = {lastUpdate : file.getLastUpdated(), fileId: file.getId()};
    }
  );          
 
  // スプレッドシートに記載されているフォルダ名と更新日時を取得。
  var spreadsheet = SpreadsheetApp.openById(UPDATE_SHEET_ID);
  var sheet = spreadsheet.getSheetByName(UPDATE_SHEET_NAME);
  //Logger.log(sheet)
  var data = sheet.getDataRange().getValues();
  //Logger.log('data: ' + data)
  // 取得したデータをMapに変換。
  var sheetData = {};
  for (var i = 0; i < data.length; i++) {
    sheetData[data[i][0]] = {name : data[i][0], lastUpdate : data[i][1], rowNo : i + 1};
  }

  // 実際のフォルダとスプレッドシート情報を比較。
  var updateFolderMap = [];
  for (key in lastUpdateMap) {
    if( UPDATE_SHEET_ID == lastUpdateMap[key].fileId ){
      continue;
    }
    if(key in sheetData) {
      // フォルダ名がシートに存在する場合。
      if(lastUpdateMap[key].lastUpdate > sheetData[key].lastUpdate) {
        // フォルダが更新されている場合。
        sheet.getRange(sheetData[key].rowNo, 2).setValue(lastUpdateMap[key].lastUpdate);
        sheet.getRange(sheetData[key].rowNo, 3).setValue(lastUpdateMap[key].fileId);
        updateFolderMap.push({filename:key, lastUpdate:lastUpdateMap[key].lastUpdate, fileId:lastUpdateMap[key].fileId});
      }
    } else {
      // フォルダ名がシートに存在しない場合。
      var newRow = sheet.getLastRow() + 1;
      sheet.getRange(newRow, 1).setValue(key);
      sheet.getRange(newRow, 2).setValue(lastUpdateMap[key].lastUpdate);
      sheet.getRange(newRow, 3).setValue(lastUpdateMap[key].fileId);
      updateFolderMap.push({filename:key, lastUpdate:lastUpdateMap[key].lastUpdate, fileId:lastUpdateMap[key].fileId});
    }
  }
  //Logger.log('updateFolderMap:' + updateFolderMap)
  // 新規及び更新された情報をメール送信。
  var updateText = "";
  for( key in updateFolderMap ){
    item = updateFolderMap[key];
    updateText += 
     item.filename + '　更新日時：' + Utilities.formatDate(item.lastUpdate, "JST", "yyyy-MM-dd HH:mm:ss") + '\n' 
    + DriveApp.getFileById(item.fileId).getUrl() + "\n\n"
  }
  
  if (updateFolderMap.length != 0) {
    SEND_MAIL_ADDRESS.forEach(function(o,i) {
      MailApp.sendEmail(SEND_MAIL_ADDRESS[i],targetFolder.getName() + "更新連絡通知",
                        "【" + targetFolder.getName() + "】が更新されました。\n\n"+
                        updateText
                        );
    });
  }
}

 

１．まず、考え方を理解する。

Excelマクロの延長で勉強しようとして、最初に「あっ・・・ふぅーん。」ってなったところ。

今回、ファイルが「上書き保存」されたタイミングで通知が欲しいと思った。

Excelマクロで言うと、WorkbookのAfterSaveイベントあたりになんか仕込むかーって感じだけど

スプレッドシートでは、Simple Eventsとしてハンドルできるのが５種類しかない。

サーッとイベントの名前だけ見て思うよね。

「こりゃ、ファイル更新のイベントは取れないな」って。

で、今回引用したコードがどうなってるかっていうとスプレッドシートから通知をキックするんじゃなくて個別にスクリプトを定期実行させて、ポーリング監視みたいにして管理用のスプレッドシート上で個別にファイルの最終更新日時を管理してるんですね。

Googleドライブは、サーバ上でデータ管理してるんだなーって実感しました。

なるほど、NASでExcelファイルを管理するのとは訳が違うぜ。

 

２．使ってるAPIを見ていく。

そう長いコードじゃないけども、Googleリソース上で、以下の権限を必要としてる。

どれも、スクリプトの冒頭で可変項目として纏められてるパラメータ使う部分ですね。

 

Googleドライブ　フォルダの取得

DriveApp.getFolderById(TARGET_FOLDER_ID);

IDは、URLから取れる長いトコ。

取得されたオブジェクトからgetFoldersとかgetFilesでフォルダやファイルの一覧が取得できるのは直感的でスッと入ってくるね。

そこから順番にファイルを取得するには、.hasNext()でループチェックしつつ.next()して取る感じですか。
Fileオブジェクトからは、.getName()でファイル名取って.getId()でID取って更新日時は.getLastUpdated()を取得する感じですね。

 

スプレッドシートの操作

SpreadsheetApp.openById(UPDATE_SHEET_ID);

こちらも、IDはURLから取れる長いトコね。

で、取得されるのはExcelで言うとこのWorkbookみたいなオブジェクトだから次にシートを取得してるね。

spreadsheet.getSheetByName(UPDATE_SHEET_NAME);

シートからデータ取る時には、次の構文でセルのデータが２次元配列的な感じで取得される訳ね。

sheet.getDataRange().getValues();

あとの更新されてるか比較する処理は、Javascriptまんまだからそのまま読めるね。

 

日付フォーマット変換

Utilities.formatDate(item.lastUpdate, "JST", "yyyy-MM-dd HH:mm:ss")

プレーンなJavascriptだと日付の変換処理ってメンドクサイけど、Utilitiesっていうのでサクッと日付変換してますね。

覚えとこ。仕様はこんな感じらしい。（Utilities.formatDate）

 

直リンクURLの取得

DriveApp.getFileById(item.fileId).getUrl()

最初の方で、GoogleドライブAPI使ってFoldersとFiles取得してたけど、逆にIDから直リンクURL取得するにはこれ使うみたいだ。覚えとこう。

 

Gmailを送る

SEND_MAIL_ADDRESS.forEach(function(o,i) { MailApp.sendEmail(SEND_MAIL_ADDRESS[i],targetFolder.getName() + "更新連絡通知", "【" + targetFolder.getName() + "】が更新されました。\n\n"+ updateText ); });

MailApp.sendEmailの引数は、順番に「送信先」、「件名」、「本文」になってるみたいだね。

直感的でわかりやすいじゃないの。

 

API便利だなー。。。

モバイルバージョンを終了