【VBA+GAS】ローカルで保存したエクセルを(ほぼ全自動で)Google Driveに保存する
うちの会社はM365とGsuiteのどちらも使っています。
(ちなみに利用頻度はOffice＞＞＞＞＞＞｜超えられない壁｜＞Gsuite)
最近ひそかに(？)Gsuiteが便利なストレージ代わりとして使われることが多くなってきて、
「ローカルで管理しているマスタのエクセルファイルを他部署とも共有できるようにDrive上にアップロードできないか？」
というご要望が…
この手のものはなるべく二重管理したくねぇ…
そのままファイル共有すると壊されそうだし、
スプレッドシートでマスタを管理するとエクセル開きながら作業ができない上に、
弊社ではフォルダやSharepointへのアクセス権の管理が面倒なのでDriveとなりました。
まぁまぁ頻繁に更新されるファイルなので、
「チっ、いちいちChrome開いてアップロードすんのクソめんどくせぇな・・・」
と思っていたら自動化できたので書きます。
ツールの構成
※VBA⇒GASで処理します。
■VBAの部分(保存と同時に実行)
①マスタファイルのエクセルが所定のフォルダ内で保存されたら
②そのエクセルを添付としてOutlookからGmailあてにメール送る
VBAの部分で参考にしたサイト
この記事のタイトルが「ほぼ全自動で」となっているのは
VBAからメールを送ったときにOutlookにセキュリティ上の問題で送信するかどうかの確認を取られるからです。
下のリンクに対処法がのってますが私の環境では情シスにがちがちに管理されていたのでそもそもいじれませんでした。
まぁ、設定しても万が一ウイルスに感染したときに怖いのでこのままにしておくのが無難です。
■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); } }
【バッチファイル+GAS】PCの初回起動時にGASを動かして全自動でTeamsに出勤を通知
前回の記事では半自動でGASを使ってTeamsに投稿するまで紹介しました。
いちいち出勤時にスクリプトを手動で動かすのも面倒なので、今回は全自動でPCの初回起動時にGASを動かせるように設定します。
PCの初回起動時に動かす
PCの起動ごとに動かすにはスタートアップ機能を利用してバッチファイルを作ればよさそうですが
私の会社のクソケチPCはスペックがあかんため突然クラッシュして再起動することがあります。
なのでスタートアップに登録するだけだと意図せず一日に何度も出勤報告をしてしまうはめに。。
そこでWindowsのイベントビューア―のログをみにいって、
その日の初回のログインだった場合にGASを叩きに行くことにしました。
参考にさせていただいた記事はこちら。
qiita.com
スクリプトは↓にありますが、今日に絞ったログの行数をみて1だった場合に動かすようにします。
::配列つかえなかったので、LoginTimeの行数をみてログイン回数を変数に格納 for /f "tokens=3 usebackq" %%i in (`find /c "Date:" LoginTime.txt`) do SET RESULT=%%i
↑find /c "Date:" LoginTime.txtの結果が「----- Date: 1」のようになるのでtokens=3で3要素目を取り出します。
GASを叩きに行く部分
前回の記事でdoGet関数を書いて
webアプリケーションとして導入としてデプロイすることでURLからGASを叩けるようにしました。
curl -L "デプロイしたwebアプリのURL"
でたたいてみたところなんかエラーが出る…。
原因を探っていくと弊社内のgsuite設定では組織内のアカウントでログインした状態からしか叩けないようになっておりました(😿)。
なので、かなりダサいですがChromeを一度開いて叩きに行きます。
今回は実装していませんがChrome開きっぱなしになるので、taskkillとかで処理が終わったら閉じるようにするのもいいかも。
::初回の起動ならGASを実行 if %RESULT% ==1 (echo 初回の起動です && "C:\Program Files (x86)\Google\Chrome\Application\chrome.exe" --new-window "デプロイしたwebアプリのURL")
完成したスクリプトがこちら
※このバッチファイルをスタートアップに登録します。
@echo off ::今日の日付を取得 set yy=%date:~0,4% set mm=%date:~5,2% set dd=%date:~8,2% set date1=%yy%-%mm%-%dd% ::1日前の日付を計算する（GMTになおすため） set /a dd=%dd%-1 set dd=00%dd% set dd=%dd:~-2% set /a ymod=%yy% %% 4 if %dd%==00 ( if %mm%==01 (set mm=12&& set dd=31&& set /a yy=%yy%-1) if %mm%==02 (set mm=01&& set dd=31) if %mm%==03 (set mm=02&& set dd=28&& if %ymod%==0 (set dd=29)) if %mm%==04 (set mm=03&& set dd=31) if %mm%==05 (set mm=04&& set dd=30) if %mm%==06 (set mm=05&& set dd=31) if %mm%==07 (set mm=06&& set dd=30) if %mm%==08 (set mm=07&& set dd=31) if %mm%==09 (set mm=08&& set dd=31) if %mm%==10 (set mm=09&& set dd=30) if %mm%==11 (set mm=10&& set dd=31) if %mm%==12 (set mm=11&& set dd=30) ) set date2=%yy%-%mm%-%dd% ::ログイン情報を取得 wevtutil qe System /f:Text /q:"*[System[(EventID=7001) and TimeCreated[@SystemTime>='%date2%T15:00:00.000Z' and @SystemTime<='%date1%T14:59:59.999Z']]]" > LoginTime.txt ::LoginTimeの行数をみてログイン回数を変数に格納 for /f "tokens=3 usebackq" %%i in (`find /c "Date:" LoginTime.txt`) do SET RESULT=%%i del LoginTime.txt ::初回の起動ならGASを実行 if %RESULT% ==1 (echo 初回の起動です && "C:\Program Files (x86)\Google\Chrome\Application\chrome.exe" --new-window "デプロイしたwebアプリのURL")
【GAS】半自動でTeamsに出勤を通知
毎朝Teamsへ出勤報告をしないといけないのですが、
いちいちチーム>チャンネルを開く＞題名と出勤報告を入れるのが若干面倒・・・
なんとかTeamsへ自動で投稿する機能はないかと調べてみたところ
以下の方法を発見しました
自動投稿の方法
・power automate の利用
⇒弊社では利用不可
・webhookの利用
⇒弊社では利用不可
・botの作成
⇒弊社では利用不可
・メールで投稿
⇒社用メールのドメインなら利用可能(これだっ)
メールで投稿してみる
管理者が制限している範囲内では利用可能でした。
(うちの会社の設定では社用ドメインのメールからなら投稿可能でした。)
ただし弊社では外部ドメインあてのメールは誤送信防止策として
社内メールサーバに10分ひっかかるためオンタイムでの投稿にならないという微妙な感じに…。
そこで社内サーバにひっかからないように私用メール(社用メールをエイリアスで設定)→Teamsに投稿
であたかも社内メールアドレスから送信した風にしようとしましたが、Gmailの設定が変わったのか
設定時に社用メールのIDPWを求められたため断念…。
(gmailのようなフリーアドレスから社用メールを装って送るため、さすがに情シスに怒られそうなきがした…)
結局社用のgsuiteアドレスからエイリアス設定(以前設定したのでIDPWの入力は不要)で社用メールアドレスを装って送ることにしました。(ちなみに社用のgsuiteアドレスも社内サーバにひっかかり10分の遅延がある)
で、以前ブログで書いたiphoneのショートカットでGASを叩く方法を設定して半自動で投稿することができました。
ここまでするなら普通に投稿したほうが早そう。
スクリプト
function doGet() {
const strTo = PropertiesService.getScriptProperties().getProperty('TEAMS_MAIL');const strFrm = PropertiesService.getScriptProperties().getProperty('MY_MAIL');
var today = new Date();
var todayDate = today.getMonth() + 1 + "/"+ today.getDate();
var strSubject = todayDate + "みかんの勤務"
var strBody = "勤務開始"
/* メールを送信 */
GmailApp.sendEmail(
strTo,
strSubject,
strBody,
{
from: strFrm ,
name:'★自動投稿★'
}
);
}
【bubbleでアプリ開発】1.基本画面
来月のノンプロ研の定例会のテーマがノーコードアプリ開発の特集なので
(そういえば昨年2時間くらい触って挫折したな、、と思いだし、、)
今日は超久しぶりにbubbleへログインしました。
そもそもノーコードWebアプリ開発とは
通常はがりがりとコードを書かないといけないWebアプリも
・めちゃ簡単にマウス操作でフロント側のデザインができる
・バックエンドも簡単に設定・管理できる
・データベースが用意されてる
・サーバー用意しなくていい
と、比較的ハードル低く素人でも(？)Webアプリを開発できます
とりあえずUdemy受講
昨年うっすらチュートリアル(地図上で地名を調べられるアプリの作成)をすすめたけど、
bubble自体のそもそも機能もなにが作れるかもわからない状態なので
Udemyでこちらのコースを受講。。。
動画で出てくる画面のUIが微妙に今のUIと若干違うのでちょっと注意かも。
最初にアプリの名前を決めますが、ほかのユーザーと重複した場合は利用できません。
簡単なものだとはじかれるので注意です。
bubbleの基本画面
名前を決定したあとはアプリを作る基本画面にうつりますがとにかくボタンが多い！！
動画を見ながら復習用にまとめたのがこちら。
ちなみにこちら、講師が3分くらいで一通り紹介していました。
あ、Pluginsのところは支払い機能に限らずいろんな機能を追加できます(キャプションミスった)
詳しいタブの説明は公式ドキュメントにてどうぞ。
ちょこちょこまとめつつブログ更新していこうとおもったのですが、動画10時間あって3分で1記事かけたのでこれからどうしようか悩み中。。
つづきはまたこんど。
(「1.基本画面」とかいかにも連載風に書いたけど、更新されたらラッキー)
プログラミング入門以前の基礎知識の多さよ。。。
本日ようやくUdemyにてこちらのコースを修了しました。
いや、長かった…8.5時間って。
PCのハードウェアの基本的な説明から
OS、ソフトウェアの開発手法やデータの扱い方
セキュリティやメディアの種類などかなり多岐にわたる内容でした。
講師の方がテキスト読み上げるので動画はなかなか単調ですが、IT音痴なので知らないこともたくさんあって結構おもしろかったです。
講座の概要に一度は学習に挫折した経験のある方向けって書いてあるんですが、たしかにそうかも。
データの持ち方とか、文字コードとかって急に出てきてもわかりづらいし、Webアプリとかページ作るうえではセキュリティについても知っておいたほうがいい。
でもいきなりプログラミングする前に見るには内容が多すぎてちょっとこの動画だけで挫折する気がした。
(やっぱし何かを作り上げる中でキーとなる技術や知識をひとつひとつ学習していくN予備校ってカリキュラムのつくりがすごいな…最近さぼってるけど)
ある程度写経したけど？となったとき、自分で何かをつくるくらいに見るのが一番理解深まるかなと思った。
でも勉強すすめていくともっと大きな沼(必要な知識の多さ)に飲み込まれて大変なことになるんだよな。。。
左右分離式キーボードと忌まわしきCapsLockの無効化
2月に在宅が始まってから、肩こりを少しでも軽減させようと
自宅用と会社用に左右分離型キーボード(barocco md770)を購入しました♪
私にとっては初めてのメカニカルキーボード。
触り心地
コロナの状況でなかなか実機を触りに行けなかったので
思い切ってオンラインで赤軸(カスカス音)と青軸(カチャカチャ音)どちらもチョイスしてぽちり。
個人的には青軸のほうが打っていてたのしいけど、
テレビ会議とかでは必ずミュートにしないといけないので若干面倒。
ミュート忘れると必ず誰かに怒られる。。。
ちなみにキーボード光るのかっこいいかなと思ったけれど、
暗いところで使うことないのでその要素は全くいらなかったです。
あと赤軸のほうは光るけど会社で光らすのはちょっと勇気がいる。
(ところでなんでオタクは光るの好きなんだろう…っておもったらこんな記事があった)
ホームポジションを習得
左右強制的に分かれるのでホームポジションが劇的に改善しました。
(今まではFまで右手で打っていた…)
どんなにめちゃくちゃでもこのキーボードなら2週間もあれば治るはず(多分)。
タイピングスピードもe-typingで半月で35から270くらいになりました。
肩こりのその後
結論から言うと、100パーセントではないけどまあまあよくなった！
というのもコロナの状況でスマホとタブレットをいじる時間が増えてしまったので劇的な改善はなし。。。
もともと左右のパーツを橋渡しするために電話線のようなTypeCケーブルが付属でついているのですが、
距離が短いので胸をもっと開かせるために1mのTypeCケーブルを購入したのがよかったのかも。
常に胸が開いているからか、胸と脇の間が超凝って具合悪くなったりするのはなくなりました。
慣れないUS配列
日本語切り替えがAlt+～になるのが少々面倒ですが、
一番面倒だったのがShiftとCapsLockの押し間違え。
この3か月で何回もパスワード入力画面で間違えまくり、イライラ。
何のために残したのか本当にわからん(英文で長文タイトル書かないし…)。
そこでさきほど(やっと)キー配置を変更したり無効にできるフリーソフト
KeySwapを使ったところCapsLockが無効化されイライラも一瞬で解決。
もっと早くしっておけばよかったー。
ついでに超うざいInsertも無効にしておいた。
(キーボードのマクロでも変更できるっぽいけどKeySwapのほうが手っ取り早かった)
Windows10でも問題なく動作しましたが、再起動する必要ありです。
あこがれのラズパイを購入～セキュリティ回り初期設定編～
前回の記事からの続きで今日はいよいよラズパイ起動からの
セキュリティ回りの初期設定です。
※自分のための備忘録のため詳しい設定などは参考記事を参照してください。
▼(参考)前記事
今回は以下のセキュリティ設定をしました
- ①デフォルトpiのパスワード変更
- ②rootのパスワードを変更
- ③デフォルトpiのユーザー名変更
- ④SSH有効設定・ポート番号変更
セキュリティ設定について
①デフォルトpiのパスワード変更
SDカードを入れてラズパイを起動すると、
最初に初期設定画面が出るのでそこで変更すればOKです。
(ほかにも言語設定・Wifi設定・OSアップデート確認など項目あり)
※デフォルトがID:pi PW:raspberrypiなので簡単にハッキングされてしまう可能性あり
後から行う場合には
$ sudo raspi-config
で設定画面を表示して 1 Change User Password でパスワードの再設定
②rootのパスワードを変更
$ sudo passwd root
うっかり自分が重要なファイルを変更しないようにも設定すべき。
③デフォルトpiのユーザー名変更
これがハマった！
初心者なので「ラズパイ セキュリティ 初期設定」でぐぐると
一番最初にこちらの記事がでてくるのですが、
新しいユーザーを作成⇒管理者権限持たせる⇒新しいユーザーでログインしてpiを削除
するという流れなのにpiユーザーがなんらかの処理で使われてしまい削除できず
もやもやしていたところこちらの記事を発見
新しいユーザーを作成⇒管理者権限持たせる⇒新しいユーザーでログインしてpiを削除
↓ではなく
一時的に新しいユーザーを作成⇒管理者権限持たせる⇒piを改名⇒一時的なユーザーを削除
でうまくいきました。たどり着くまでが長かった…。
久しぶりにvimで設定ファイルを変更したりして、コマンド打ったりしたのでこちらも参考にさせていただきました。(かろうじて入力モードだけ覚えていた)
④SSH有効設定・ポート番号変更
実際の接続についはまた次回記事を書く予定です。
ポート番号の設定については不正アクセスの記事が怖くなったので
$ sudo vi /etc/ssh/sshd_config
であいているポート(49152-65535)へ変更しました。
インターネットがある時代に生まれてよかった…。
一人じゃ絶対に解決できなかった。
