見出し画像

VBA＆GAS＆デザイン100本ノック1-10

SONO

エクセルのVBA、GoogleSheetsのGAS100本ノックトレーニングで実際に使用したソースをご紹介します。

1）Sub ＆ function

↓↓↓↓　VBA　↓↓↓↓

Sub 朝のあいさつ()
   ' A2セルに文字を出力'
   Range("A2") = "おはようヽ(´ｴ`)ﾉ"
End Sub

↓↓↓↓　GAS　↓↓↓↓

function goodMorning(){
 // A2セルに文字を出力
 SpreadsheetApp.getActiveSheet().getRange(2,1).setValue("おはようヽ(´ｴ`)ﾉ")
}

2）Range ＆ Value

↓↓↓↓　VBA　↓↓↓↓

Sub 文字色の変更()
   ' B2を取得し、テキストを代入
   Range("B2").Value = "こんにちはヽ(´ｴ`)ﾉ"
   ' C2を取得し、テキストを代入
   Range("C2").Value = "こんばんはヽ(´ｴ`)ﾉ"
   ' B2のテキストを青にする
   Range("B2").Font.ColorIndex = 5
   ' C2のテキストを赤にする
   Range("C2").Font.ColorIndex = 3
End Sub

↓↓↓↓　GAS　↓↓↓↓

function chgfColor() {
 const ss = SpreadsheetApp.getActiveSpreadsheet();
 let sheet = ss.getActiveSheet();
 
 // B2を取得し、テキストを代入
 let rngB2 = sheet.getRange(2,2).setValue("こんにちはヽ(´ｴ`)ﾉ");
 // C2を取得し、テキストを代入
 let rngC2 = sheet.getRange(2,3).setValue("こんばんはヽ(´ｴ`)ﾉ");

 // B2のテキストを青にする
 rngB2.setFontColor("#0000ff");
 // C2のテキストを赤にする
 rngC2.setFontColor("#ff0000"); 
}

3）const ＆ let

↓↓↓↓　GAS　↓↓↓↓

function const_let_test() {
 for (let i = 1; i <= 5; i++) {
   //　ログに出力
   console.log(i);
 }
}

4）indent整形

5）Rows.Count,End ＆ getLastRow

↓↓↓↓　VBA　↓↓↓↓

Sub getLastrow()
   Dim Lastrow As Long
   ' 最終行を取得
   Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
   ' メッセージボックスに最終行を表示
   MsgBox "最終行は" & Lastrow & "行目です（｀・ω・´）b"
End Sub

↓↓↓↓　GAS　↓↓↓↓

function getLastRow() {
 const ss = SpreadsheetApp.getActiveSheet();
 const msg = ss.getRange(2, 4)
 // 最終行を取得
 const lastRow = ss.getLastRow();
 // 最終行を表示
 msg.setValue("最終行は" + lastRow + "行目です（｀・ω・´）b");
}

6）Pictures.Insert ＆ insertImage

↓↓↓↓　VBA　↓↓↓↓

Sub 画像挿入()
   With ActiveSheet.Pictures.Insert("C:\Program Files\bookletS\image_P1.jpg")
       ' 画像位置の指定
       .Top = Range("A4").Top
       .Left = Range("A4").Left
       ' 画像サイズの指定
       .Width = 300
       .Height = 300
   End With
End Sub

↓↓↓↓　GAS　↓↓↓↓

function insertImage() {
 const ss = SpreadsheetApp.getActiveSheet();
 // 挿入するGoogleドライブの画像URL
 const pic = "https://drive.google.com/uc?export=download&id=★★画像ID★★"
 // 画像位置、サイズの指定  
 ss.insertImage(pic, 1, 4).setHeight(300).setWidth(300);
}

7）getName

↓↓↓↓　VBA　↓↓↓↓

Sub フォルダ内ファイル名を取得()
   Dim getName As String, cnt As Long
   ' フォルダパスを指定
   Const Path As String = "C:\Program Files\bookletS\"
   ' 条件に一致するファイルを取得
   getName = Dir(Path & "*.JPG,*.jpg")
   Do While getName <> ""
       cnt = cnt + 1
       Cells(cnt, 1) = getName
       getName = Dir()
   Loop
End Sub

↓↓↓↓　GAS　↓↓↓↓

function getFileName() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 //　出力シート名を取得
 var sheet = ss.getSheetByName('シート2');
 //　フォルダのファイル情報を取得
 var files = DriveApp.getFolderById('フォルダID').getFiles();
 var gdFiles = [];
 while (files.hasNext()) {
   var file = files.next();
   var name = file.getName();
   gdFiles.push([name]);
 }
 // ログに出力
 console.log(gdFiles);

 // A1セルを起点にして一括書き込み
 sheet.getRange(1, 1, gdFiles.length, gdFiles[0].length).setValues(gdFiles);
}

8）IMPORTRANGE＆QR作成

「list」シートを作成し、以下のマクロを実行するとGoogleドライブフォルダ内のファイル情報をイメージ表示してリスト化できます。便利なので、今回、このデータを活用して100本ノックを整理し、note化することにしました(´ｴ`)b

↓↓↓↓　GAS　↓↓↓↓

function getFileList() {
 const ss = SpreadsheetApp.getActiveSpreadsheet();
 const sheet = ss.getSheetByName('list');
 const lastRow = sheet.getLastRow();
 const iRange = sheet.getRange("B3");
 const cutRange = sheet.getRange("A:F");
 const topRange = sheet.getRange("A1:F1");
 const clRange = sheet.getRange("A:F");
 clRange.clear();
 const eRange = sheet.getRange("E:E");

 //セル幅を設定
 sheet.setColumnWidth(1, 30);
 sheet.setColumnWidth(2, 200);
 sheet.setColumnWidth(3, 60);
 sheet.setColumnWidth(4, 680);
 sheet.setColumnWidth(5, 40);
 sheet.setColumnWidth(6, 330);

 //100行目まで100ピクセルの高さに設定
 sheet.setRowHeights(2, 100, 30);

 //文字折返し、センタリング設定
 cutRange.setWrapStrategy(SpreadsheetApp.WrapStrategy.CLIP);
 eRange.setHorizontalAlignment('center');

 // Googleドライブの指定フォルダにあるファイルを一括取得
 const files = DriveApp.getFolderById('★★フォルダIDを入力してください★★').getFiles();
 const gdFiles = [];
 while (files.hasNext()) {
   const file = files.next();
   const name = file.getName();
   const size = file.getSize();
   const url = file.getUrl();
   gdFiles.push([name, size, url]);
 }
 console.log(gdFiles);

 // B2セルを起点にして一括書き込み
 sheet.getRange(2, 2, gdFiles.length, gdFiles[0].length).setValues(gdFiles);

 //文字体裁
 cutRange.setFontSize(12);
 topRange.setHorizontalAlignment('center');
 topRange.setVerticalAlignment('middle');

 // 指定のセルに文字列、関数を書き込み
 sheet.getRange(1, 1).setValue('No');
 sheet.getRange(1, 2).setValue('ファイル名');
 sheet.getRange(1, 3).setValue('サイズ');
 sheet.getRange(1, 4).setValue('URL');
 sheet.getRange(1, 5).setValue('画像');
 sheet.getRange(1, 6).setValue('画像ID');
 sheet.getRange(2, 1).setFormula('=IFERROR(VALUE(REGEXEXTRACT(B2,"_P([0-9]+)")),"")');
 sheet.getRange(2, 5).setFormula('=IFERROR(IMAGE("https://drive.google.com/uc?export=download&id="&(F2),1),"") ');
 sheet.getRange(2, 6).setFormula('=IFERROR(REGEXEXTRACT(D2,"d/(.*)/view"),"")');

 //データがある行のみ数式を書き込む
 for (var i = 3; i <= lastRow; i++) {
   if (iRange.isBlank()) {
   } else {
     //コピー対象のセル範囲を選択する
     var copyRange = sheet.getRange(2, 5, lastRow, 6);
     //貼り付け先のセル範囲を選択する
     var pasteRange = sheet.getRange(3, 5, lastRow, 6);
     //コピー対象のセル範囲のデータを貼り付け先のセルにコピーする
     copyRange.copyTo(pasteRange);
     var copyRange = sheet.getRange(2, 1, lastRow, 1);
     var pasteRange = sheet.getRange(3, 1, lastRow, 1);
     copyRange.copyTo(pasteRange);
   }
 }
}

9）GetOpenFilename

↓↓↓↓　VBA　↓↓↓↓

Sub セルに合わせて連続画像挿入()

Dim w As Worksheet
Set w = ActiveSheet

w.PageSetup.PaperSize = xlPaperB4    '用紙サイズを設定
Range("A:B").RowHeight = 329         'セルの高さを設定
Columns("A:B").ColumnWidth = 54 　　 'セルの幅を設定
 
'画像を2列で表示
'ファイル読み出し用変数
Dim fileName As Variant
Dim t As Long

'画像読み込み用変数
Dim pic As Shape

'指定拡張子のファイルを開く
fileName = Application.GetOpenFilename("JPG,*.jpg", MultiSelect:=True)
'filenameの配列か確認
If IsArray(fileName) Then

     'ファイル選択数分繰り返す
     For i = 1 To UBound(fileName) Step 2  '2枚=2、3枚=3
       For ii = 1 To 2  '行方向枚数分繰り返し
       
         'オブシェクト名を省略
         With ActiveCell
           t = t + 1 'henkou
           
           '画像をセルの大きさに合わせて貼り付け
           Set pic = ActiveSheet.Shapes.AddPicture(fileName:=fileName(t), linktofile:=False, savewithdocument:=True, _
           Left:=.Left + 2, Top:=.Top + 2, Width:=.MergeArea.Width - 4, Height:=.MergeArea.Height - 4)
         End With

       '貼り付けセル位置の設定
       ActiveCell.Offset(0, 1).Activate  '列方向にアクティブセルを移動(行方向,列方向)
       If t = UBound(fileName) Then GoTo sub2  '写真が最終の時終了させる
       Next ii
       ActiveCell.Offset(1, -2).Activate  '行方向にアクティブセルを移動(行方向,列方向)     
     Next i
sub2:
 End If
End Sub

10）getMessagesForThreads

↓↓↓↓　GAS　↓↓↓↓

function searchMail() {

 // Gmailから特定条件でスレッドを検索してメールを取り出す
 const strTerms = '("Amazon" AND ' + '"Amazon.co.jpでのご注文")';
 const myThreads = GmailApp.search(strTerms, 0, 100); // 条件にマッチしたスレッドを取得
 const myMsgs = GmailApp.getMessagesForThreads(myThreads); // スレッドからメールを取得

 const valMsgs = [];

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

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

そして、今後もノックは続きます((｀･∀･´))

いいなと思ったら応援しよう！