【GAS】スプレッドシートで列幅調整と罫線付の綺麗な表を簡単作成【操作画像付】

インターネットやデータベースなどからデータを抽出して表に貼り付けてデータを整理したい場面はあるかと思います。

そんな操作を仕事や勉強で頻繁に行う必要がある人も結構多いはず。

そんな時、いちいち列幅を調整して枠線を付ける段階的な作業ってめんどくさいですよね。

Googleスプレッドシートでいろんなデータを扱う際、列幅を調整したり、枠線を付けたり、一発で表を見やすい形にしたくありませんか？

GASで超簡単に綺麗な表を作成するマクロを実装してみましたので、目的に合った場合はぜひコピーして使ってみてください。

百聞は一見に如かず、一目瞭然、一発でイメージが湧き、使い方がわかるようなGIFアニメ付きです。

Googleスプレッドシートの列幅と罫線GASマクロ操作イメージ

マクロを登録して、下記の画像操作と同じようにやってみてください。

列幅と罫線GASマクロの主な仕様

細かい処理はコメントで補足してるので、ザックリとした仕様を箇条書きで記載します。

列幅と罫線GASマクロのソースコード

表作成効率を爆上げしたい方は、下記のソースコードを以下↓↓↓のリンクの内容に従ってコピーしてご使用ください。

【GAS】Googleスプレッドシートでマクロの使い方入門【動画付】

Googleの「Google Apps Script」通称GAS。GoogleマップやGメールなど、いろんなGoogle関連のサービスと自由に連携できたり、独自のWebアプリを開発することもできる、いろんな可能性を秘めたワクワクの...

caymezon.com

2019.05.02

目的に合っていない場合は適当にカスタマイズしてみてください。GASマクロの勉強にも少しは役立つと思います。

/* 列幅自動調整_表枠線付加 */
function colWidthChoseiBoderAdd() {
  
  // 確認メッセージフラグ
  var confirmMsgFlg = 0; // 0:確認メッセージを表示しない、1:確認メッセージを表示する
  
  // 列幅調整フラグ
  var colWidthChoseiFlg = 1 // 0:列幅調整しない、1:列幅調整する
  // 枠線付加フラグ
  var boderAddFlg = 1 // 0:枠線付加しない、1:枠線付加する
  // 終了位置判定区分
  var endPointkbn = 1 // 1:開始位置から行・列データ全て、2:開始位置から連続値最終位置まで
  // 列幅自動調整_表枠線付加処理
  colWidthChoseiBoderAddShori(confirmMsgFlg,colWidthChoseiFlg,boderAddFlg,endPointkbn);
  
}
////////////////////////////////////////////////////////////////////////////////
/* 列幅自動調整_表枠線付加処理 */
function colWidthChoseiBoderAddShori(confirmMsgFlg,colWidthChoseiFlg,boderAddFlg,endPointkbn) {

  //////////概要//////////
  //表の列幅を自動調整して枠線を付加する
  
  //////////前提//////////
  //1行目タイトル、2行目以降がデータの表を対象する
  //複数行・複数列の表を対象とする（1行のみ、または1列のみの場合はチェック処理で強制終了）
  //　※終了位置によってMAXまで処理してしまうのを回避するため
  
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getActiveRange(); // 選択範囲セル
  
  //////////確認処理//////////
  if (confirmMsgFlg == 1) {
    var msg = "";
    if (colWidthChoseiFlg == 1) {
      msg = msg+"・選択範囲セル2行目以降データで列幅を自動調整します。\\n";
    }
    if (boderAddFlg == 1) {
      msg = msg+"・表データに枠線を付加し、タイトル行に色付けします。\\n";
    }
    if (endPointkbn == 1) {
      msg = msg+"・開始位置から行・列データ全てを対象とします。\\n";
    } else if (endPointkbn == 2) {
      msg = msg+"・開始位置から連続で値が存在する最後のセルまでを対象とします。\\n";
    }
    if (msg != "") {
      var msgRtn = Browser.msgBox(msg+"\\n実行してよろしいですか？",Browser.Buttons.OK_CANCEL);
      if (msgRtn == 'cancel') {return;}
    }
  }
  
  //////////開始位置//////////
  var startRow = range.getRow();    // 開始行
  var startCol = range.getColumn(); // 開始列
  //var startRowColMsg = "開始行:"+startRow+",開始列"+startCol;
  //Logger.log(startRowColMsg);

  //////////シート全体の最終位置//////////
  // シート全体の最終行（値を持つセル範囲の最終行からCtrl+↓で移動した行位置が必ずシート最終行になると判断）
  //var sheetLastRow = sheet.getRange(sheet.getLastRow(),range.getColumn())
  //                   .getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
  var sheetLastRow = sheet.getMaxRows();
  // シート全体の最終列（値を持つセル範囲の最終列からCtrl+→で移動した列位置が必ずシート最終列になると判断）
  //var sheetLastCol = sheet.getRange(range.getRow(),sheet.getLastColumn())
  //                   .getNextDataCell(SpreadsheetApp.Direction.NEXT).getColumn();
  var sheetLastCol = sheet.getMaxColumns();
  //var sheetLastRowColMsg = "シート全体の最終行:"+sheetLastRow+",シート全体の最終列"+sheetLastCol;
  //Logger.log(sheetLastRowColMsg);

  //////////終了位置//////////
  if (endPointkbn == 1) {
    // データの終了行（シート最終行から上に値を探索して値が見つかった行をデータ最終行とする(Ctrl+↑)）
    var dataLastRow = sheet.getRange(sheetLastRow,startCol)
                      .getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
    // データの終了列（シート最終列から左に値を探索して値が見つかった行をデータ最終列とする(Ctrl+←)）
    var dataLastCol = sheet.getRange(startRow,sheetLastCol)
                      .getNextDataCell(SpreadsheetApp.Direction.PREVIOUS).getColumn();
  } else if (endPointkbn == 2) {
    // データの終了行（開始行から下に空データを探索して値が見つかった行をデータ最終行とする(Ctrl+↓)）
    var dataLastRow = sheet.getRange(startRow,startCol)
                      .getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
    // データの終了列（開始列から右に空データを探索して値が見つかった列をデータ最終列とする(Ctrl+→)）
    var dataLastCol = sheet.getRange(startRow,startCol)
                      .getNextDataCell(SpreadsheetApp.Direction.NEXT).getColumn();
  }
  //var dataLastRowColMsg = "データの終了行:"+dataLastRow+",データの終了列"+dataLastCol;
  //Logger.log(dataLastRowColMsg);
  
  //////////チェック//////////
  // 行データがない（終了行がシート最終行）、または
  // 列データがない（最終列がシート最終列）場合は強制終了
  if (startRow >= dataLastRow || dataLastRow == sheetLastCol) {
    Browser.msgBox("行データがありません。");
    return;
  } else if (startCol >= dataLastCol || dataLastCol == sheetLastCol) {
    Browser.msgBox("列データがありません。");
    return;
  }
  
  //////////列幅自動調整//////////
  if (colWidthChoseiFlg == 1) {
  // 自動調整or値のバイト長からの調整、好きな方をお選びください。
    
    // 列幅自動調整 →２バイト文字（日本語）だと3/5くらいの幅になってしまうので注意
    //sheet.autoResizeColumns(startCol,dataLastCol-startCol+1);
    
    // データの値で列幅を計算して調整 →列毎に処理するためパフォーマンスが悪いので注意
    for(var i=startCol;i<=dataLastCol;i++){ // 列ループ
      var maxLenB = 1;    // 列毎の最大バイト長
      for(var j=startRow+1;j<=dataLastRow;j++){ // 行ループ
        // ※1行目をタイトル前提とし2行目以降を列幅調整の対象とする。
        if (sheet.getRange(j,i).getValue() != "") { // 空ではない場合
          //var chkVal = sheet.getRange(j,i).getValue(); // 数値などの場合、indexOfエラーとなる。
          var chkVal = sheet.getRange(j,i).getDisplayValue();
          // 改行を含む場合は先頭行で判断
          if (chkVal.indexOf("\n") != -1) {chkVal = chkVal.slice(0,chkVal.indexOf("\n"));}
          var colLenB = strLenByte(chkVal);
          if (maxLenB < colLenB) { // 最大長判定 
            maxLenB = colLenB;
          }
          //Logger.log("行:"+j+"列:"+i+"→"+strLenByte(cellVal));
        }
      }
      //Logger.log("maxLenB:"+maxLenB+","+"colWidth:"+sheet.getColumnWidth(i));
      sheet.setColumnWidth(i,maxLenB*7+(maxLenB-1)+8); // 半角だと割と綺麗に調整される（全角だと少し膨らむ）
      //sheet.setColumnWidth(i,maxLenB*7+(maxLenB-1)+8-(maxLenB*2)+15); // 狭めたい場合は適当に微調整が必要
    }
    
  }

  //////////表枠線付加//////////
  if (boderAddFlg == 1) {
    
    //////////タイトル部//////////
    sheet.getRange(startRow,startCol,1,dataLastCol-startCol+1)
    .setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP) // 折り返し
    .setVerticalAlignment('middle') // 中央に配置
    .setBackground('#CCFFCC') // 背景色（薄い緑）
     // 上・左・下・右・垂直・水平を全て実線
    .setBorder(true,true,true,true,true,true,'#000000',SpreadsheetApp.BorderStyle.SOLID);
    
    //////////データ部//////////
    sheet.getRange(startRow+1,startCol,dataLastRow-startRow,dataLastCol-startCol+1)
    // 上・左・下・右・垂直は実線
    .setBorder(true,true,true,true,true,null,'#000000',SpreadsheetApp.BorderStyle.SOLID)
    // 水平は点線
    .setBorder(null,null,null,null,null,true,'#000000',SpreadsheetApp.BorderStyle.DOTTED);
    
  }
  
  //sheet.getRange(startRow,startCol).activate(); // A1セル選択
  //sheet.getRange(1,startCol,sheetLastRow,dataLastCol-startCol+1).activate(); // →列選択にはならない
  sheet.getRange(colA1Conv(startCol)+":"+colA1Conv(dataLastCol)).activate(); // 列選択（マウスで任意調整）
  //└→列番号のみ指定のやり方がわからないので強制的にA,B,…,Z,AA,AB,…に変換して選択
  
}
////////////////////////////////////////////////////////////////////////////////
// 2バイト文字は2バイトとしてカウントする関数
function strLenByte(str) {

  var len = 0;
  str = escape(str);
  for(var i=0;i

列幅と罫線マクロのGASとExcelVBAとの違い

Excel版はコチラ。

【ExcelVBA】列幅を自動調整して枠線付の綺麗な表を簡単作成

インターネットやデータベースなどからデータを抽出してExcelに貼り付けてデータを整理したい場面はあるかと思います。そんな操作を仕事や勉強で頻繁に行う必要がある人も結構多いと思います。そんな時、いちいち列幅を調整して枠線を...

caymezon.com

2019.04.20

コーディングの内容は全然違いますが、仕様、ロジックはほとんど同じです。

ExcelからGoogleスプレッドシートに徐々にでも移行を考えている方は見比べて参考にしてみてください。

---

最後に

Googleスプレッドシートの使い方は人それぞれ、いろんなやり方があると思いますが、一例としてご紹介させていただきました。

GASマクロはExcelVBAとはかなり違いますが、JavaScriptは覚えておいて損はないプログラムだと思うのでショートカットキーなどと組み合わせてぜひ活用してみてください。

Googleスプレッドシート全ショートカットキー一覧はこちら↓↓↓

Googleスプレッドシートの使い方や機能がわかるショートカットキー全まとめ一覧【初心者こそ必見】

表計算ソフトとして、まだまだExcelのシェア率は高いですが、GoogleスプレッドシートにはExcelにはない超強力な関数があったり、Excelとは違って常に最新版を無料で使用できます。なにより、インターネットを使ったオンライン...

caymezon.com

2019.03.16

ちょっと工夫すれば、ちょっとした操作に１分かかっていた作業を１０秒でこなすことができるようになる可能性があります。

それだけでも、積み上げれば相当の工数を削減できるはずなので、ぜひ自分に合ったやり方を模索していきましょう。

---