見出し画像

VBA＆GAS＆デザイン100本ノック11-20

SONO

Twitterで私がご紹介している、日常生活の中から飛び出したマクロや事務作業をしていて便利だなと思ったマクロをまとめました。いざという時に使えるようポケットに入れておくと便利な技。今回は、VBA＆GAS＆デザイン100本ノックの11本目から20本目のスクリプトを掲載します。

11）Fomula

A2セルの日付を起点として以下値を1ずつ加算し、曜日もそれに合わせて変更する仕様の簡易カレンダーです。表計算カレンダーでは、集計ができたり追加したい項目を自由にカスタマイズできるのが良いところ。

↓↓↓↓　VBA　↓↓↓↓

Sub カレンダー作成()
   
   'セルに文字を出力
   Range("A1") = "日付"
   Range("B1") = "曜日"
   
   'セルに関数を出力
   Range("A3:A32").Formula = "=A2+1"               'A2を起点に1ずつ値を追加
   Range("B2:B32").Formula = "=TEXT(A2,""aaa"")"   'Aセルを参照し、Bセルに曜日を出力
   
End Sub

12）CalendarApp,getEvents

文字が詰まっている予定表の場合、セル幅の広い一覧にして見た方が読みやすいことも。Googleカレンダーに入力済みの内容を、期間を指定してシートに呼び出すことができます。

画像2

↓↓↓↓　GAS　↓↓↓↓

const Calendar = '*****@gmail.com'; //カレンダーID（Gmailアドレスを入力）
function getCalendar() {
 const calendar = CalendarApp.getCalendarById(Calendar);
 const startTime = new Date('2021/08/06 00:00:00');　//データ取得開始日を入力
 const endTime = new Date('2021/08/07 24:00:00');  　//データ取得終了日を入力

 const events = calendar.getEvents(startTime, endTime);

 const values = [];
 for (const event of events) {
   const record = [
     event.getStartTime(),
     event.getTitle(),
   ];
   values.push(record);
 }
 SpreadsheetApp.getActiveSheet().getRange(2, 1, values.length, values[0].length).setValues(values);
}

13）UrlFetchApp

BセルにURLを入力すると、WEB上の記事タイトルを取得できるスクリプトです。タイトルを修正しても自動で表示が更新されるのはいいですね！
ちなみに多くの記事を管理するWEBサイトでは、文字修正にかかる時間を軽減できるので、このスクリプトと似たような機能を持つショートコードを作成し、既存内容の変更を自動反映させる手法をとることがあります。


画像4

↓↓↓↓　GAS　↓↓↓↓

function getTitle(url) {
 let httpReq = UrlFetchApp.fetch(url);

 let titRegexp = /<title>([\s\S]*?)<\/title>/i;
 let match = titRegexp.exec(httpReq.getContentText());
 let title = match[1];

 title = title.replace(/(^\s+)|(\s+$)/g, "");
 return(title);
}​

14）Chart

EXCELでは、データ範囲をクリックして「Alt+F1」のショートカットキーを押すだけで、表を一瞬にしてグラフ化できる優れた機能があります。

画像3

上記のグラフは、VBAでは下記のように書くと表示できます。EXCELでの「Alt+F1」は、このグラフ作成のマクロをショートカットキーとして登録しているということなんですね！

↓↓↓↓　VBA　↓↓↓↓

Sub グラフ作成()

   With ActiveSheet.Shapes.AddChart.Chart
       .ChartType = xlColumnClustered
       .SetSourceData Source:=Sheets("Sheet3").Range("A2:E7")
   End With

End Sub

15）Rotation

画像4

シートに挿入した画像を回転するには、「図の書式設定」や回転マークをマウスでクルッとすれば自由に角度を設定できます。
画像を15度回転させるスクリプトは下記。もう少し応用を効かせて連続で+15度ずつ回転させられるようにしたい。

↓↓↓↓　VBA　↓↓↓↓

Sub 画像の回転()
   
   Dim img As Shape
   
   '1番目の図形を選択
   Set img = ActiveSheet.Shapes(1)
   
   '画像を15度回転する
       With img
           .Rotation = 15
       End With
       
End Sub

16）setFont,setBorder

これまたちょっとマニアック。列や行幅、その中に入力する文字、セルカラーなどを一度に指定しちゃうマクロ。

画像5

getSheetByNameで「家系図」というシートに対して使えるように設定しています。フォントのサイズやカラー、斜体、太字、セル色、位置をカスタマイズ可能です。

『家族で綴る未来への手紙』を作れないだろうか、なんて考えていたらこんなソースが出来上がりました。自分の名前を記入して、メモ欄を使って、子孫へ手紙を繋げるってどうだろう？　タイムカプセルみたいで面白いような気がする。

↓↓↓↓　GAS　↓↓↓↓

function kakeizu() {
 const ss = SpreadsheetApp.getActiveSpreadsheet();
 const sheet = ss.getSheetByName('家系図');　// シート名「家系図」のシートを取得

 const rng = sheet.getRange(1, 1, 5, 30);
 rng.setVerticalText(true);　//縦書き
 rng.setValue('氏名');　// 指定範囲にテキスト文字「氏名」を設定
 rng.setFontSize(16); // フォントサイズ
 rng.setFontFamily("Meiryo"); // フォントスタイル
 rng.setFontStyle("italic");　// フォントを斜体にする
 rng.setFontWeight("bold");　// フォントを太字にする
 rng.setFontColor("#000000"); 　// フォントカラー
 rng.setHorizontalAlignment("center");　// フォントの水平位置
 rng.setVerticalAlignment("top");　// フォントの垂直位置
 rng.setBackground("");　// セル色を設定

 // 罫線の設定（top, left, bottom, right, vertical, horizontal, color, style）
 rng.setBorder(true, true, true, true, true, true, "#c95438", SpreadsheetApp.BorderStyle.DASHED);

 // 1行目から30行目まで150ピクセルの高さに設定
 sheet.setRowHeights(1, 30, 150);

 // シートの最初の30列の幅を50pxに設定
 for (let i = 1; i <= 30; i++) {
   sheet.setColumnWidth(i, 60);
 }
}

17）prompt（uiオブジェクト）

GASで問題集を作成してみました。メニューに表示される『問題集』→『出題』でテストが開始できます。

まずは、Aセルに問題、Bセルに解答を入力し、スクリプトを登録。

画像6

↓↓↓↓　GAS　↓↓↓↓

// シートを開いたタイミングで実行
//function onOpen() {
//  const sheet = SpreadsheetApp.getActiveSpreadsheet(); // 現在開いているGoogleスプレッドシート
//  const myMenu = [];
//  myMenu.push({ name: "出題", functionName: "showTest" }); // メニュー項目の登録
//  sheet.addMenu("問題集", myMenu); // メニューに追加
//}

// ランダムに問題を出現させる
function showTest() {
 const ui = SpreadsheetApp.getUi();
 const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 const lastRow = sheet.getLastRow();
 const row = Math.floor((lastRow - 1) * Math.random()) + 2;
 const question = sheet.getRange(row, 1);　//【問題】の取得
 const answer = sheet.getRange(row, 2);　　//【答え【】の取得

 // 問題を出す
 const response = ui.prompt("問題 " + (row - 1), question.getValue(), ui.ButtonSet.OK);
 // OKを押したら入力内容と正解を表示する
 const complete = ui.alert(
   "答え",
   "【問題】\n" + question.getValue() + "\n\n【入力】\n" + response.getResponseText() + "\n\n【答え】\n" + answer.getValue(), ui.ButtonSet.OK);

}

＜実際にテスト問題をやってみよう＞
『出題』をクリックすると、問題がランダム表示されます。回答欄に答えを入力し「OK」を押すと、問題、自分が入力した答え、正答がポップアップ表示されます。

画像7

18）StrConv

下記は、EXCELに入力されている全角数字を半角数字に修正したい時に使えるマクロ。

↓↓↓↓　VBA　↓↓↓↓

Sub 全角数字を半角に統一()

   Dim num As Range
   Dim i As Integer
   Dim repData As Variant
       For Each num In Selection
           repData = ""
           For i = 1 To Len(num.Value)
               If Mid(num.Value, i, 1) Like "[０-９]" Or Mid(num.Value, i, 1) Like "－" Then
                   repData = repData & StrConv(Mid(num.Value, i, 1), vbNarrow)
               Else
                   ansData = repData & Mid(num.Value, i, 1)
               End If
           Next i
           num.Offset(0, 1).Value = repData
       Next num
End Sub

画像8

ちなみに関数ならば、
A2セルの全角文字をB2セルに半角文字で出力したい場合、
=ASC(A2)
と入れればOK。
逆に半角文字を全角文字にしたい場合には、
=JIS(A2)
です。
覚えておくと便利です、ね！

19）sort

データを昇順にソートするスクリプトです。マクロ内にソート機能を埋め込みたい時にはこちらを追加しよう。

↓↓↓↓　GAS　↓↓↓↓

function sort() {
 const ss = SpreadsheetApp.getActiveSheet();
 const lastRow = ss.getLastRow();
 const lastCol = ss.getLastColumn();
 
 //ソート範囲を指定し、昇順にソートする
 ss.getRange(2, 1, lastRow, lastCol).sort(1);
}

20）getMessagesForThreads

Gmailから条件指定して日時、送信元、件名、内容を取り出せるスクリプトです。スレッドをひとつひとつ開封することなく内容を表示することができます。メールをあとから検索したり整理する際、キーポイントとなるタイトル付けは意外と大切でセンスがいりますね💨

画像9

↓↓↓↓　GAS　↓↓↓↓

function searchMail() {

 // Gmailから特定条件で検索してメールを取得
 const serchStr = '("BASEからのお知らせ")';　//「BASEからのお知らせ」の条件でメールを検索
 const pickThreads = GmailApp.search(serchStr, 0, 100);　//100件までメールを取得
 // スレッドからメールを取得
 const picMsgs = GmailApp.getMessagesForThreads(pickThreads);

 const valMsgs = [];

 // 各メールから日時、送信元、件名、内容を取り出す
 for (var i = 0; i < picMsgs.length; i++) {
   valMsgs[i] = [];
   valMsgs[i][0] = picMsgs[i][0].getDate();
   valMsgs[i][1] = picMsgs[i][0].getFrom();
   valMsgs[i][2] = picMsgs[i][0].getSubject();
   valMsgs[i][3] = picMsgs[i][0].getPlainBody();
 }

 // スプレッドシートに出力
 if (picMsgs.length > 0) {
   SpreadsheetApp.getActiveSheet().getRange(2, 1, i, 4).setValues(valMsgs);
 }
}

今号のマクロはいかがでしたか？
作成していて気づいたのは、時々、これはマクロをわざわざ作らないでも関数でいいんじゃない？ってこと。個人で使用するのではなく、職場において共同作業でマクロを使う場合には、特に注意したい事項。手の込んだスクリプトを修正するには作成した人が仕様書を残しておかないと、引き継ぎに苦労してしまうので、みんなに優しいマクロを作れる開発者になりたい、と思うのでした。


この記事が気に入ったらサポートをしてみませんか？