GASで6GBも容量を食っていたGmailの既読メールを大量に削除してやりました！
Googleドライブのストレージをみてびっくり！
最近Googleドライブの容量が10GB超えたなぁ。
15GB超えたら追加容量毎月払わなきゃかなぁと気になっていました。
そこで、改めてGoogleドライブのストレージから容量の内訳をみてびっくり！
なんとGmailだけで6.8GBも食っているではありませんか( TДT)
私はてっきり何年もGoogleフォトに溜め込んだ写真の容量だと思っていたのですが、諸悪の根源はジャンクメールでしたよ(´Д⊂ヽ
それもそうですよね。
毎日100通以上メール来てるのが365日。
少なくとも年に36500通は受け取ってるわけです。
少なくとも5年以上はGmail毎日利用してるので、6.8GB。。。
一体何通溜まってるんだヽ(×_×)ノ
というわけで、早速ジャンクメールを削除しようと
older_than:90d label:inbox -is:starred
でフィルターして、選択、削除。。。。
ううう。。。50件ずつしか削除できない（泣）
とりあえず効率よくジャンクメールを削除する方法をググった
50件ずつではなく、一括で削除する方法はないのか？
ググってたどりついたのがスレッドごとの削除。
atmarkit.itmedia.co.jp
早速PCでGmail開いて実行してみたんですが。。。。
一括削除でも処理時間の制限があるのか4800件程度しか削除できません。
にしても4800件削除で0.2GB削れてるので
6.8GBということは、およそ163,200通ものメールが溜まっているということに！（*-*）
や～、メール一通って数KBとたかをくくってましたが、その数KBも溜まりに溜まるとギガ超えるんですね＾＾；
ほんとびっくりです。
4800件ずつ削除で34回。
おっしゃ！やるぞ！！！
・・・・と意気込んだものの
2回削除して以来メール減りませんよ（・・？
多分クラウド側の処理が追い付かないのかなぁ？？
とてもじゃないけど34回も大量削除技は使えません。
そうだ！GASがあるじゃないか！
Googleアプリ操作といえばGASでしょう！！
GASでなんとか一括削除できんもんかな？と、またググる。
親切にもGASでGmail削除のソースを置いてくれているサイトがありました✨
zenn.dev
よし！このソースでいかせていただこう（＾＾）
とりあえず label を '受信トレイ' に設定したおバカ💦
動くわけがなく。 'inbox' なんですよね。
さて、サクサク実行。。。
というわけにはいかず、デフォルトの500件ずつでは処理が途中で止まるので、300件に設定しなおしました。
今度こそサクサク実行！
おお！Gmailのゴミ箱に削除されたメールが入ってました＾＾
第一日目
15分に一度300件ずつ削除なので、一日に28,800通削除してくれますよ（＾＾）放置放置♪
6日くらいで終わる計算ですね＾＾楽しみ✨
。。。と、次の日の15時頃。
メールが削除されなくなる。
おや、と思いログを見たら。。。。
止まってる・・・・！！！（T▽T）
あれですね、Google制限です（--；
トリガーの処理が90分超えちゃいました(;・∀・)
万事休すです。
ここまでで確保できた容量
さて、制限が解除されるまで寝てよう。。。
第二日目
さて！制限が解除されたところで仕切り直し。
15分に一度だと制限がかかるので、トリガーを30分に一度に設定しなおして再チャレンジ！
30分に一度300件ずつで一日14,400件ずつ削除していきます。
GASを動かすこと約10日間
I mada it!!
とうとうGBからMB来ました～～！！
～まとめ～
スレッドの一括削除では２回ほどで処理が止まる。
GASでコツコツ削るのみ！！
セルに入力するだけで自動的に動くRPAマクロをVBAで作ろう！
【今回の問題】
日付セルを更新すると、日報の休みの日に「定休日」という文字を入れる。
自動的に文字入力。VBAでRPA！
VBAを使うとさまざまな作業が自動化できてしまうわけですが、このVBAによる自動化って、かっこよくいうとRPAなんですね。
RPAとはRobotic Process Automationの略です。
ロボティクス？
ロボットが仕事するの？
という印象を持たれる方もいるかもしれませんが、このロボティクスには、VBAのような自動化をするプログラムも含まれます。
とにかく、パソコンやロボットに仕事を丸投げして自動化してしまえということです。
そういってしまうと簡単に思えますが、RPAの仕組みを作るまでがエンジニアの腕の見せ所です。
今回はVBAのシート関数を使って、セルを書き換えたり、クリックしただけで処理をしてくれるRPAマクロの書き方を紹介します。
シート関数の用意をしよう
開発タブからVBAエディターを開きます。
目的のシートをダブルクリック
左、赤丸からWorksheet選択
Declarationsのドロップダウン
ダブルクリックで変化→BeforeDoubleClick
セルの値変更で変化→Change
それぞれ選択するとエディタにはこのように記入されます。
これで自動化の下準備は完了です。
自動化のためのコードを書いていこう
セルの値を変更したり、クリックしたりのあとにマクロを実行する。そんなプログラムを書きます。
手順は次の通り
- シート関数の下準備
- セルの値の変更や、クリックの後実行させたいマクロを書く
- シート関数から2のマクロを呼び出す
以上です。
下準備はもう説明済みなので、2のマクロを書いていきましょう。
日付セルが日曜日のときに、３列目のセルに定休日という文字を入れるマクロを作りました。
ちなみに日付ですが、一番上の日付だけを入力すれば、それ以下の日付が自動で入力されるようになっています。
これはVBAを使わなくても、エクセル関数でサクッといけちゃうんですね。
vba-tokepi.hateblo.jp
Sub offday() Dim i As Long Dim wd As Long For i = 3 To 33 wd = Weekday(Cells(i, 2)) If wd = 1 Then Cells(i + 1, 3).Value = "定休日" Cells(i + 1, 3).Font.Color = vbRed Cells(i + 1, 3).Font.Size = 18 End If i = i + 1 Next End Sub
Weekday関数が1の時は日曜日です。なので、Weekday関数を代入しているwdが1の時に、3列目のセルに「定休日」という文字列を入れる処理が出来上がりました。
次はシート関数で、このマクロがA3:A4で日付を変更するだけで動作するようにします。
自動化の最強呪文VBAがこちら
シート関数がどのようにすれば動作するのか？
それは、つぎのコードを書くだけ。
If Not Application.Intersect(Target, セル範囲) Is Nothing Then
Ifから始まれば、必ず終わりはEnd Ifで閉めるのですが、このコードとEnd Ifの間で2のマクロを呼び出してあげればいいだけです。
次のようなシンプルなコードで、セルA3:A4の日付を書き換えれば、休日の3列目のセルに自動的に「定休日」の文字が入ります。
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Set rng = Range("A3:A4") If Not Application.Intersect(Target, rng) Is Nothing Then Call offday End If End Sub
ここで、シート関数の準備の際に、Declarationsのドロップダウンを、ダブルクリックで変化→BeforeDoubleClickで選択していた場合、セルA3:A4をダブルクリックすると、休日の3列目のセルに「定休日」という文字列が入力されるマクロが出来上がります。
以上、VBAの基礎知識と、シート関数の設定、そして
If Not Application.Intersect(Target, セル範囲) Is Nothing Then
のコードがあれば、セルの値を変更したり、ダブルクリックするだけで動作するマクロが作れますよ！というお話でした。
日報を快適にするVBA～日付の入力、操作
【今回の問題】
この日報に、ボタンクリックで日付を入れよ。
日付の操作に便利な関数を紹介します
日付を入れるマクロを作ろうと思ったときに、真っ先に思い浮かんだのがWeekday関数でした。
Weekday関数を理解するのに役に立つ問題がこれ！
マクロVBA練習問題 問題12
excel-ubara.com
書き方は weekday(日付,週の最初の日) です。
曜日に応じて1～7の数字を返してくれます。
デフォルトは日曜日が1。週の最初の日をvbMondayにすると月曜日が1に、vbWednesdayにすると水曜日が1になります。
今回はweekday関数を使ってこのような日付入力ボタンを作りました。
月曜日の日付を枠内に入力すると、月曜日から一週間分の日付を記入してくれます。
月曜日以外の日付を書くと「月曜日ではありません」というアラートが出ます。
ソースはこちら。
Sub 日付記入() Dim w As Long Dim wday As Date w = Weekday(Range("P2"), vbMonday) wday = Range("P2") If w = 1 Then For i = 4 To 29 Cells(i, 1).MergeArea = wday wday = wday + 1 i = i + 4 Next Else MsgBox "月曜日ではありません" End If End Sub
P2は日付を入力するセルです。vbMondayは使わなくてもw=2を指定すれば月曜日になりはするのですが、なんだかw=1にしたかったので、あえてvbMondayを使いました。
わざわざVBAを使わなくても、関数で簡単にできることもある
せっかく日付入力マクロを作りはしたのですが、利用者さんからは使いにくいと不評でした💦
そこで、ある利用者さんが日報の改訂版を作ってくれました。
なんと、簡単な関数で日付入力の自動化ができてしまうんですね。
最初の日付を2021/7/27形式で入力すれば、残りの日付はずらっと自動的に入力されます。
さて、どうやったのかというとまずA4セルの日付を絶対参照。そこに一日たつごとに$A$4+1，$A$4+2，$A$4+3・・・と入力しておけばいいだけです。
Excelの日付に数字を足せば何日後という機能は便利ですよね！
VBAで何とかしよう！と工夫しだすと、簡単にできてしまう関数の存在を忘れてしまうことがよくあります。
数字だけVBAで指定してあげればvloolupでほとんどの項目に入力できる納品書なんかもあります。
VBAを学ぶと同時にExcelの関数も学んでおくと事務方で重宝されますよ✨
私がExcel関数を使いこなすのに役に立った本を二冊紹介しますね。
一冊目は「Excel最高の学び方」
vlookupやcountifsがメインですが、この二つの関数でほとんどの集計作業は出来てしまうという、何度でも読み返したい価値のある本です。
二冊目は「できるExcelデータベース」
Excelでのデータ処理がほとんどできるようになってしまう本です。セルや関数の扱いに詳しくなります。
Excelでできることはスプレッドシートでもやりたい！VBA対GAS
【今回の問題】
Excelで作った日報マクロをGoogleのスプレッドシートでも作ってください。
Excelからスプレッドシートに移行するときの勝手の違い
今年6月から新しい職場で頑張っています。在宅ですけどね。
日報がかなりめんどくさくて、VBAで自動化できる余地が十分にあったので、次の作業を自動化させてもらいました。
- 一週間分の日付を自動入力（VBA）
- 一週間分のデータを消去する（VBA）
- ドロップダウンで時間を入力できるようにする（入力規則）
スタッフさんの反応はとても好意的で、結構喜んでもらえました！
そのうえ、次のようなオファーがありました。
「スプレッドシートでできない？」
これは嬉しいオファーでしたね。勤務時間にGAS（Google Apps Script）の学習をやっていいよ！というオファーですからね(^^♪
身に余るほどのやりがいを感じながら、嬉々として取り組ませていただきました。
VBAはVisual Basic GASはJavaScript
Excelもスプレッドシートも機能としては似たようなものなのですが、マクロのような機能を備えさせるとなると、やり方は全然違います。
そもそもマクロを組む言語自体が違います。
Excelはみなさんご存じVBAでマクロを組みますが、スプレッドシートの場合はGASという言語を使います。
GASという言語というと、語弊があるかもしれません。GASはJavaScriptで書かれているので、遠回しに言うと、スプレッドシートのマクロはJavaScriptで書きます。
実際に、VBAで書いたものと全く同じプログラムをスプレッドシートに実装させてみたのですが、あまりの勝手の違いに頭がくるくるなりました💦
例えばA1セルに「Hello World!」と表示させたい場合、ソースはこんなにも違ってきます。
まずはVBAでやってみましょう。
Sub A1テスト() Cells(1, 1).Value = "Hello World!" End Sub
簡単簡単♪
ところが、GASだとこのようになるんですね。
function myFunction() { const ss = SpreadsheetApp.getActiveSheet(); ss.getRange(1,1).setValue("Hello World!"); }
なんじゃこりゃ～～～？！！
VBAはセルと入力文字だけ入れてあげれば、簡単に「Hello World!」が表示できますが、それだけのためにGASではなんと長々とコードを書かされてしまうのでしょう💦
GASのコードは2行にまたがっていますが、別に定数ssに代入しなくても
function myFunction() { SpreadsheetApp.getActiveSheet().getRange(1,1).setValue("Hello World!"); }
このように1行で書いてもいいんですね。
ただ、セルを変更するときなどに備えて、SpreadsheetApp.getActiveSheet()は、定数または変数に代入しておいたほうが使い勝手がいいので最初のコードのようにしました。
しかし、この長いコードにも長所はあるもので、必ずシートを指定してあげなければいけないから、VBAのようにあいまいなセル指定で他のシートで誤作動を起こすなんていうことがないんですね。
慣れてくれば、GASも使い勝手のいい言語かもしれません。
日報を快適にするVBA～結合セルのデータクリア
【今回の問題】
このような日報の一週間分のデータを消去してください。
この日報をみて、まず愕然としました。
結合セルだらけではないですか💦
結合セルのデータはどうやって消去したらいいの？
早速試行錯誤してみました。
まずは結合セルをすべて範囲で指定してデータをクリア。
とりあえず、最初の日付と業務の報告をクリアしてみましょう。
Sub データのクリア() Range(Cells(4, 1), Cells(5, 1)).ClearContents Range(Cells(5, 2), Cells(6, 5)).ClearContents End Sub
お？！問題なくデータをクリアできますね。
ではfor文を使ってすべての日付をクリアしてみましょう。
Sub データのクリア() Dim i As Long For i = 4 To 29 Range(Cells(i, 1), Cells(i + 1, 1)).ClearContents Range(Cells(i + 1, 2), Cells(i + 2, 5)).ClearContents i = i + 4 Next End Sub
これも問題なく動いてくれました。
それにしてもRangeの中にさらにCellsを書き込む方法だと見栄えがすっきりしませんね。
始点と終点の二つのセルを書かなきゃいけないというのも、いちいち面倒です。
結合セルは4つもあるので、無駄にセルをカウントすることなく、なんとかすっきりさせたいものです。
そこで、結合セルの扱いについてググってみたのですが、便利なプロパティを発見しましt。
MergeAreaです。
結合セルの一番左上のセルを指定してMergeAreaプロパティをくっつけてやると、指定したセルを含むすべての結合セルを選択してくれます。
Sub データのクリア() Cells(4, 1).MergeArea.ClearContents Cells(5, 2).MergeArea.ClearContents End Sub
これでいいんですね。
ちょっとすっきりして見えませんか？
セルを一つしか指定しなくていいので、らくちんです。
というわけで、一週間分の日報データを消去するマクロを書いたのがこちら。
Sub データ削除テスト() Dim i As Long, j As Long Dim selectedButton As Long selectedButton = MsgBox("データを消しますがいいですか？", Buttons:=vbYesNo) If selectedButton = vbYes Then For i = 4 To 29 Cells(i, 1).MergeArea.ClearContents Cells(i + 1, 2).MergeArea.ClearContents Cells(i + 1, 6).MergeArea.ClearContents Cells(i + 4, 1).ClearContents Cells(i + 4, 2).ClearContents Cells(i + 4, 3).ClearContents Cells(i + 4, 4).MergeArea.ClearContents Cells(i + 4, 6).ClearContents Cells(i + 4, 8).ClearContents Cells(i + 4, 9).ClearContents Cells(i + 4, 11).ClearContents i = i + 4 Next ElseIf selectedButton = vbNo Then Exit Sub Else Exit Sub End If End Sub
ボタンを押すと一週間分のデータを消去するという仕様にしますが、ボタンを押したらいきなり消去では、間違ってボタンを押しちゃったときに取り返しつかないですよね。
誤動作確認のために、メッセージボックスで「データを消しますがいいですか？」と念押しすることにしました。
ユーザー目線大事です。マクロにも優しさと配慮を心がけましょう。
結合セルのデータを消去するには2パターンの書き方があることがわかりました。
これで、一週間分の日報のデータを消去して使いまわしができますね。
ただ、消去して上書き保存してしまうと、前のデータが残らないので、まず名前を付けて保存で、新たなファイルとして保存してからデータを消去するようにしましょう。
VBA ファイルの操作とDir()
【今回の問題】
マクロVBA練習問題 練習問題18（Dir関数の練習）
excel-ubara.com
ファイルの中にあるファイルのファイル名をセルに転記していきます
今回も、いきなり回答をみることはせず、試行錯誤。
まず、C:\Userの中にあるファイルのファイル名をA列に転記していくコードから書いていきました。
ファイル名の取得はディレクトリ関数Dir()を使います。
変数 = Dir("ファイルへのパス&拡張子")
これで変数を代入するとファイル名になります。
ファイル名を取得してDo WhileでC:\Userの中にある全部のファイルの名前を取得すればいいんだよな。
ということで、こういうコードを書きましたが、無限ループしました💦
Sub 練習問題18_1() '注意！無限ループします！ Dim memo As Worksheet Dim file As Variant Dim wb As Workbook Dim folderPath As String Dim fileExtension As String Set memo = Worksheets("練習18") folderPath = "C:\User" fileExtension = "*.xls" file = Dir(folderPath & "\" & fileExtension) i = 3 Do While file <> "" Set wb = Workbooks.Open(folderPath & "\" & file) memo.Cells(i, 1) = wb.Name wb.Close SaveChanges:=False i = i + 1 Loop End Sub
ファイルを次々に読み込ませていくにはどうしたらよいのだろう？
はてさて、セルはi = i + 1でどんどん次に行ってくれますが、ファイルをどんどん次のものを読ませるにはどうしたらよいのだろう？
ということで、あきらめて回答をみました。
なんてことはない。i = i + 1のあとに file = Dir()としてやれば次のファイルを読んでくれるらしい。
このコードで、ようやくファイル名だけは転記できました。
Sub 練習問題18_1() Dim memo As Worksheet Dim file As Variant Dim wb As Workbook Dim folderPath As String Dim fileExtension As String Set memo = Worksheets("練習18") folderPath = "C:\User" fileExtension = "*.xls" file = Dir(folderPath & "\" & fileExtension) i = 3 Do While file <> "" Set wb = Workbooks.Open(folderPath & "\" & file) memo.Cells(i, 1) = wb.Name wb.Close SaveChanges:=False i = i + 1 '肝心なのはこの子 file = Dir() Loop End Sub
不思議ですよね。fileだけではダメなのかな？と思い、実際にfileだけでやってみたらアラート出ました💦
二回目以降はDir()←パスは省略。
この file = Dir() というのはファイルが存在するかどうかを確認する呪文のようなものみたいですね。
解答にも書いてありますが
これはもう、基本中の基本の組み合わせです。
なんの迷いもなく、一気にかけるようなるまで練習して下さい。
ですって。
今回はファイル名の取得のお話なので横列にシート名のコードは省いています。
ここまでシンプルにできるのか！複雑なセルの転記のタブー、RangeよりもCellsを使え。
【今回の問題】
このような表にデータを転記してください
今回の問題のソースはこちら↓
図解！ Excel VBAのツボとコツがゼッタイにわかる本 プログラミング実践編 立山秀利
6列3行のセルにデータを転記していきます。はて？どうする？
この表をパッと見せられた時、私の頭にはVlookupかOffsetしか頭に浮かびませんでした。
しかし、「Vlookupは使わないよ。」と出題者さんに言われて書いたコードがこちら。
Sub 転記2() '店舗名の行を抽出 Dim i As Long, j As Long Dim area As String 'シート名 area = Range("C2").Value j = 5 For i = 5 To Cells(Rows.Count, 1).End(xlUp).Row If Range("C2").Value = Cells(i, 3) Then Sheets(area).Range("B" & j).Offset(1, 0).Value = Cells(i, 1) Sheets(area).Range("B" & j).Value = Cells(i, 2) Sheets(area).Range("B" & j).Offset(0, 2).Value = Cells(i, 4) Sheets(area).Range("B" & j).Offset(1, 2).Value = Cells(i, 5) Sheets(area).Range("B" & j).Offset(2, 2).Value = Cells(i, 6) j = j + 3 End If Next End Sub
3行後に次の項目が来るので、変数ｊを使って、3行飛ばさせる処理をIfの最後に入れています。
これ、最初はものすごく試行錯誤しました。
For i = 5 To Cells(Rows.Count, 1).End(xlUp).Row Step 3
を最初に思い浮かべたのですが、これではうまくいかない。
For iの処理を終えた時点で、3行移動するのだから、For iの処理の最後に、記入する行頭 j に3を足してあげればいいんだ！と気づいたのは、やはりF8でひとつひとつプログラムを実行してからのことでした。
とりあえず、コードを書き終えたら、いきなりF5ではなくF8で、それぞれの処理が、意図したとおり行われているか確認しながらプログラムを動作させてみてください。
もちろん、コードを書きながら、実際にどのように動作するのか、頭の中でシュミレーションできていることが肝心です。頭の中のシュミレーションとF8でひとつづつ動かしたときの動作が同じかどうか。これで、おかしな点があれば、どこがまずいかだいたいわかります。
実はRangeを使わずにシンプルに書けるんです
最初のコードを出題者さんに提出した後、「Rangeは使わないよ。」といわれて頭をひねりました。
そこで目を付けたのが、そもそも列は固定されているし、Offsetも j を基準に動いている！という点。
ならばRange("B"&j)からオフセットしなくても、Cells(j行, 固定の列)でいけてしまいますよね。
というわけで、ここまでコードをシンプルにすることができました。
Sub 転記2() '店舗名の行を抽出 Dim i As Long, j As Long Dim area As String 'シート名 area = Range("C2").Value j = 5 For i = 5 To Cells(Rows.Count, 1).End(xlUp).Row If Range("C2").Value = Cells(i, 3) Then Sheets(area).Cells(j + 1, 2) = Cells(i, 1) Sheets(area).Cells(j, 2) = Cells(i, 2) Sheets(area).Cells(j, 4) = Cells(i, 4) Sheets(area).Cells(j + 1, 4) = Cells(i, 5) Sheets(area).Cells(j + 2, 4) = Cells(i, 6) j = j + 3 End If Next End Sub
できるかぎりシンプルに
コードがだらだらと長くなってしまったときは次のことに気を付けてみてください。
- 無駄にRangeを使っていないか
- 変数や定数に置き換えてシンプルにできる部分はないか
- Withを使ってまとめられる部分はないか
シンプルなコードの何がいいかって、あとで見た時に、長いだらだらと書いたコードよりも、動作の把握がしやすいんですね。
メンテナンスのときに作業効率がぐっと上がるのです。
さらに、それぞれの動作について 'コメント を書いておくのも、プログラムの可読性を上げるポイントです。
