【GAS】スプレッドシートで作成した表をGASで整形する

スポンサーリンク
Google Apps Script

スプレッドシートで表を作成した際に、自動でフォントや背景色等を整形してくれるようなツールがあったらいいなと思い、簡易的に作成してみたため、共有します。参考になれば。

表を作成

1行目が見出し、2行目以降が値になるように表を作成する。

スクリプト作成

拡張機能>Apps Script押下でスクリプトの起動を行います。

以下コードを記述します。

function SheetModify() {
var sheet = SpreadsheetApp.getActiveSpreadsheet(); //アクティブなスプレッドシートを取得
var dataRange = sheet.getDataRange().getA1Notation(); //対象のデータ範囲を取得する
var totalRange = sheet.getRange(dataRange); //対象のセル範囲を取得する
var row = totalRange.getRow(); //対象セル範囲の開始行番号を取得する
var column = totalRange.getColumn(); //対象セル範囲の開始列番号を取得する

//1行目がA1から開始されていない場合はエラーとする
var a1Data = sheet.getRange("A1");
if(a1Data.isBlank()){
  Browser.msgBox("エラー:A1セルから入力をしてください。");
  return;
};

//表全体の整形
//対象範囲に罫線を追加し、フォントをMeiryoに設定する
totalRange
  .setBorder(true,true,true,true,true,true)
  .setVerticalAlignment("center")
  .setFontFamily("Meiryo");

//見出しの整形
//1行目(見出し部分)の範囲を整形する
var rangeHeaderSet = dataRange.substring(0,4) + 1; //1行目の範囲を変数で設定する
var headerRange = SpreadsheetApp.getActiveSpreadsheet().getRange(rangeHeaderSet);
headerRange
  .setFontSize(12) //フォントサイズを12に設定
  .setFontWeight("bold") //フォントを太字に設定
  .setHorizontalAlignment("center") //文字列の水平位置を中央に設定
  .setBackground("#a6e989"); //セルの色を設定

//値の整形
//2行目以降(値)の範囲を整形する
var rangeBodySet = "A2" + dataRange.substring(2); //2行目以降の範囲を変数で設定する
var bodyRange = SpreadsheetApp.getActiveSpreadsheet().getRange(rangeBodySet);
bodyRange
  .setFontSize(10) //フォントサイズを10に設定
  .setFontWeight("normal") //フォントを通常に設定
  .setHorizontalAlignment("left") //文字列の水平位置を左寄せに設定

Browser.msgBox("整形が完了しました。");
}

これで準備は完了しました。
実際に実行してみます。

実行結果

スクリプト上の「実行」を押下すると、

スプレッドシート上ダイアログが表示されるので「OK」を押下。

以下のように表が整形されます。

少し解説

①見出し部分の整形処理

コード

//見出しの整形
//1行目(見出し部分)の範囲を整形する
var rangeHeaderSet = dataRange.substring(0,4) + 1; //Point①
var headerRange = SpreadsheetApp.getActiveSpreadsheet().getRange(rangeHeaderSet); //Point②
headerRange
  .setFontSize(12) //Point③
  .setFontWeight("bold") //Point④
  .setHorizontalAlignment("center") //Point⑤
  .setBackground("#a6e989"); //Point⑥

Point①
表の1行目の範囲を変数で設定しています。dataRangeには「A1:C11」のデータ範囲が入っており、その中からsubstringメソッドで「A1:C」の部分を抽出しています。
Cの後ろにくっつける数字は1行目想定なので、ベタ書きで1を結合。

Point②
対象セルの操作を行うためにRangeクラスをPoint①の範囲で設定する。

Point③
対象のRangeクラス.setFontSize(数値)でフォントサイズの設定が可能。
()内には指定したい文字サイズを入力する。

Point④
対象のRangeクラス.setFontWeight()でフォントの太字設定が可能。
()内に”bold”で太字、”normal”で通常に設定できる。

Point⑤
対象のRangeクラス.setHorizontalAlignment()で文字列の水平位置の設定ができる。
()内に”center”で中央揃えになる。

Point⑥
対象のRangeクラス.setBackground()でセル内の背景色の設定ができる。
()内に任意の色を設定する。

参考:
String.prototype.substring()
https://developer.mozilla.org/ja/docs/Web/JavaScript/Reference/Global_Objects/String/substring

【Google Apps Script(GAS)】文字のサイズ、スタイル、色、太さ、斜体、位置や、セルの色や罫線の設定
https://vba-gas.info/gas-font-border

②値部分の整形処理

コード

//値の整形
//2行目以降(値)の範囲を整形する
var rangeBodySet = "A2" + dataRange.substring(2); //Point①
var bodyRange = SpreadsheetApp.getActiveSpreadsheet().getRange(rangeBodySet);
bodyRange
  .setFontSize(10) //フォントサイズを10に設定
  .setFontWeight("normal") //フォントを通常に設定
  .setHorizontalAlignment("left") //文字列の水平位置を左寄せに設定

Point①
表の値部分(2行目以降)の範囲を変数で設定しています。開始はA2前提なのでベタ書きで設定し、終了セルはdataRangeには「A1:C11」のデータ範囲から、substringメソッドで「:C11」を抽出し、結合。

※フォントサイズの設定等の部分は重複するため省略

③エラー処理

コード

//1行目がA1から開始されていない場合はエラーとする
var a1Data = sheet.getRange("A1"); //Point①

if(a1Data.isBlank()){ //Point②
  Browser.msgBox("エラー:A1セルから入力をしてください。");
  return; //Point③
};

Point①
A1のセル範囲の情報を取得し、a1Dataの変数に格納。

Point②
isBlank()メソッドで対象セルが空白かどうかを確認する。
a1Dataが空白なら処理を終了するような仕様。

Point③
処理をここで終了するために記載。

参考:
セル範囲が空白かどうか判定する:isBlank()【GAS】
https://gsuiteguide.jp/sheets/isblank/

終わりに

今回はコンテナバインドスクリプトで作ってみたのですが、どんなスプレッドシートでも共通して利用できるように改修はしていこうと思います。

読んでいただきありがとうございました!

コメント

タイトルとURLをコピーしました