841-biborokuWebフロントエンドの備忘録

GASを使用してスプレッドシートデータを自動的にグループごとに罫線で囲むスクリプト作成

この記事の一部はChatGPTで文章校正をしています。

スプレッドシートでデータを整理する際、見やすさを向上させるためには、罫線や背景色の設定が欠かせません。

全体に罫線を引くのは簡単ですが、項目ごとに罫線で囲むことや、後から行が増えたり移動したりする場合の管理は面倒です。そこで、自動で罫線を引くGoogle Apps Script(GAS)を作成しました。

※特定のデータ入力形式を想定したGASになります。
※デプロイ手順は省きます。

左)罫線未設定 右)GASの自動罫線を適応した状態

出来上がりのイメージ

  • 大・中分類はグループとして複数のセルをまとめて罫線で囲む
  • 指定列以降は全てのセルに罫線を引くように設定
  • 入力画面から指定列を設定できるようにする

完成したコード

コード.gs

//01. カスタムメニューの作成とダイアログの実行
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Menu')
    .addItem('自動で罫線を引く', 'showDialog')
    .addToUi();
}

//02. ダイアログ画面Page.htmlを表示させる
function showDialog() {
  const html = HtmlService.createHtmlOutputFromFile('Page')
    .setWidth(400)
    .setHeight(180);
  SpreadsheetApp.getUi()
    .showModalDialog(html, '自動で罫線を引く');
}

//04. アルファベットを数字に変換して返す
function letterToColumn(letter) {
  let column = 0;
  const length = letter.length;
  for (var i = 0; i < length; i++) {
    column += (letter.toUpperCase().charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
  }
  return column;
}

//03. 罫線を自動で引く
function optimizeBorders(specifiedColumn) { 
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = sheet.getLastRow();
  const lastColumn = sheet.getLastColumn();
  const specifiedColumnNumber = letterToColumn(specifiedColumn);  //指定列のアルファベットを数字に変換

  //データのある範囲の上下左右に罫線+内側の垂直に罫線
  sheet.getRange(1, 1, lastRow, lastColumn).setBorder(true, true, true, true, true, false);

  //指定した列以降のすべてのセルに罫線を引く
  sheet.getRange(1, specifiedColumnNumber, lastRow, lastColumn - specifiedColumnNumber + 1).setBorder(null, null, null, null, null, true);

  const data = sheet.getDataRange().getValues();

  for (var row = 1; row < lastRow; row++) {
    // 各行をチェックして罫線を引く
    for (var column = 1; column <= specifiedColumnNumber; column++) {
      // セルが空白でない場合に自身の下に罫線を引く
      if (
        (data[row][column - 1] !== '') ||
        (column !== 1 && data[row][column - 1] === '' && data[row][column - 2] !== '') ||
        (column !== 1 && data[row - 1][column - 1] === '' && data[row - 1][column - 2] !== '')
      ) {
        sheet.getRange(row, column, 1, 1).setBorder(null, null, true, null, null, null);
      }
    }
  }
}

Page.html

<p>自動でグループ判定し罫線を引くスクリプトです。<br>
すべての列のセルに罫線を引く場合は、列のアルファベットを指定してください。<br>
  <em>例)AA</em> 指定列以降の上下左右に罫線が適用されます。
</p>
<div class="box">
  <table>
    <tr>
      <td>
        列名<span class="small">(アルファベット)</span>
      </td>
      <td>
        <input type="text" max="4" name="specifiedColumn" id="specifiedColumn" />
      </td>
      <td>
        <input type="button" value="実行する" onclick="executeBorderFunction(this)" />
      </td>
    </tr>
  </table>
</div>
<script>
//05. 関数の実行と処理結果のダイアログ表示
  function executeBorderFunction(button) {
    const specifiedColumn = document.getElementById("specifiedColumn").value;
    button.value = "実行中...";

    google.script.run.withSuccessHandler(function(){
      button.value = "完了";
      google.script.host.close();
    }).optimizeBorders(specifiedColumn);
  }
</script>
<style>
  p {
    font-size: 80%;
  }

  .box {
    margin-top: 10px;
    padding: 15px;
    background: #f9fbfd;
    border-radius: 5px;
  }

  .small {
    font-size: 70%;
  }

  table td {
    padding: 0 5px;
  }

  input[type="text"] {
    padding: 0 10px;
    width: 80px;
    height: 35px;
    box-sizing: border-box;
  }

  input[type="button"] {
    height: 35px;
    border: 0;
    padding: 0 10px;
    background: #c2e7ff;
    border-radius: 5px;
    box-sizing: border-box;
  }
</style>

01. カスタムメニューの作成とダイアログの実行

onOpen() 関数では、Google Sheetsのスプレッドシートを開いた際に自動的に実行されるイベントを定義しています。

この関数は、スプレッドシートのユーザーインターフェースにカスタムメニューを作成し、「自動で罫線を引く」という項目を追加しています。ユーザーはこの項目を選択することで、showDialog()が実行されます。

02. ダイアログ画面Page.htmlを表示させる

showDialog() 関数を定義し、モーダルダイアログを表示します。

HtmlService.createHtmlOutputFromFile() で 'Page' ファイルからHTMLを取得し、setWidth(400)とsetHeight(180)でダイアログのサイズを設定します。

SpreadsheetApp.getUi().showModalDialog(html, '自動で罫線を引く') でダイアログを表示します。

03. 罫線を自動で引く

03-01. データがある範囲を取得

const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const lastRow = sheet.getLastRow();
const lastColumn = sheet.getLastColumn();

アクティブなスプレッドシートを取得し、シート内で最後にデータが入力されている行と列の番号を取得します。

03-02. 一番外側を罫線で囲み垂直線を引く

sheet.getRange(1, 1, lastRow, lastColumn)でデータのある範囲を指定しています。

setBorder(true, true, true, true, true, false);

setBorder(上, 左, 下, 右, 垂直, 水平);

データの状態に関係なく、全てのセルに左右の罫線を引くために、各セルに個別に罫線を設定せず、まとめて指定した範囲内の垂直線を一括で設定しています。

03-03. 指定した列以降のすべてのセルに罫線を引く

ダイアログで入力したアルファベット(列)を数字に変換し、その列以降のセルに罫線を引く範囲を指定しています。

sheet.getRange(1, specifiedColumnNumber, lastRow, lastColumn - specifiedColumnNumber + 1)

getRange(開始行, 開始列=ダイアログで入力した列, 最終行=取得した最終行, 最終列=取得した最終列-ダイアログで入力した列+1)

setBorder(null, null, null, null, null, true)

[03-02]で設定した罫線が消えないように、上下左右・垂直にはnullを指定し、水平方向にはtrueを指定して、罫線を設定します。

03-04. 残りの行・列をループチェックして罫線を引く

  • セルが空白でない場合
  • セルが1列目でない場合で、その左隣のセルが空白で、その左隣のセルの上下にデータがある場合
  • セルが1行目でない場合で、その上のセルが空白で、その上のセルの左隣にデータがある場合

この条件の場合にセルの下に罫線を引きます。

04. アルファベットを数字に変換して返す

const specifiedColumnNumber = letterToColumn(specifiedColumn);

スプレッドシートの列はアルファベット表記のため、列番号を数える手間を省くため、数字ではなく列のアルファベットを与えて、アルファベットの列名を数字に変換するようにしました。

アルファベットの各文字を数値に変換し、26進数の基数で桁ごとに計算して、最終的な列番号を求めます。

05. 関数の実行と処理結果のダイアログ表示

ボタンがクリックされたときに実行されます。まず、指定された列のアルファベット表記を取得し、その値は specifiedColumn 変数に格納されます。次に、ボタンの値を「実行中...」に変更し、スクリプトが処理中であることをユーザーに通知します。

その後、google.script.run.withSuccessHandler() メソッドを使用して、optimizeBorders() 関数を実行します。この関数は、指定された列のアルファベット表記を引数として受け取ります。処理が完了した後、ボタンの値は「完了」に変更され、その後 google.script.host.close() を呼び出して、ダイアログを閉じます。

ダイアログは、HTML サービス インターフェースのクライアント側で google.script.host.close() を呼び出すことで閉じることができます。このダイアログは、他のインターフェースでは閉じることができません。閉じる操作は、ユーザー自身または自身で行う必要があります。

Google for Developers

実行時間

実行範囲が広いと完了するまでに時間を要します。

  • 5列10行の範囲で3~5列目を全罫線設定した場合・・・2.141秒
  • 5列10行の範囲で5列目を全罫線設定した場合・・・3.706秒
  • 4列2300行の範囲で3~4列目を全罫線設定した場合・・・8.689秒
  • 4列2300行の範囲で4列目を全罫線設定した場合・・・20.032秒

まとめ

基本的には、大分類や中分類などのグループ化する項目はデータ開始行と揃えることで、今回のGASでの自動罫線が有効に利用できます。

意図していない箇所に空白セルが混ざると、罫線の位置がずれることがありますが、データの追加や変更後に毎回罫線を修正する手間を省くために、ある程度のルールを設けて入力することで、罫線の引き直しをする手間を省くことができるようになりました。

📝参考

シェア