毎月やる作業の効率化のために、GAS(Google Apps Script)でプログラムを書いている。
csvファイルをスプレッドシート化して、フィルタをかけて複数シートを作り印刷できたら便利だなと思って制作した。
シートのレイアウトをGASで自動調整するようにしたので、そこで使ったメソッドをまとめた。
スプレッドシートのレイアウトを調整する
以下の操作をGASで行なった。
- 行の高さ・列の幅を調整する
- テキスト折り返し指定する
- 表示形式を数値フォーマットに変更する
- 交互の背景色を適用する
それぞれメソッドを使うときに、扱うのがSheetクラスのオブジェクトなのか、Rangeクラスのオブジェクトなのかを注意する。
(1)行の高さ・列の幅を調整する
sh.setRowHeights()
sh.setColumnWidths()
行の高さを変更するときはsetRowHeights()
, 列の幅を変更するときはsetColumnWidths()
を、Sheetオブジェクトに対して使う。
▼スクリプト例
const sh = SpreadsheetApp.getActiveSheet(); // A. データの入ってる範囲まとめて変更する sh.setRowHeights(1, sh.getLastRow(), 34); // 行の高さを「34」に指定 sh.setColumnWidths(1, sh.getLastColumn(), 100); // 列の幅を「100」に指定 // B. 1行・1列だけを変更する sh.setRowHeights(5, 34); // 5行目の高さを「34」に指定 sh.setColumnWidths(5, 100); // E列(=5列目)の高さを「100」に指定
複数行・列をまとめて調整したいときは、例Aのように第二引数に行数・列数を指定すれば良い。
ちなみに今回使わなかったけど、セルの中身に合わせた自動の高さ・幅調整は、同じくSheetクラスの autoResizeColumns()
, autoResizeRows()
メソッドが使える。
(2)テキスト折り返し指定する
range.setWrapStrategy()
テキスト折り返しを指定したい時は、Rangeオブジェクトに対してsetWrapStrategy()
を使う。
引数には、SpreadsheetApp.WrapStrategy.WRAP
を指定すると、テキスト折り返しになる。
ちなみにスプレッドシートメニューのこれ。
▼スクリプト例
const sh = SpreadsheetApp.getActiveSheet(); const range = sh.getRange('A:A') range.setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP); // A列をテキスト折り返しに指定
WRAP
の部分は、それぞれ以下に変えると他の方式も選べる。
- はみ出す: OVERFLOW
- 折り返す: WRAP
- 切り詰める:CLIP
(3)表示形式を数値フォーマットに変更する
range.setNumberFormat()
Rangeオブジェクトに対してsetNumberFormat()
を使うと、そのセル範囲のフォーマットを指定できる。
引数には、フォーマットの文字列を指定する。日付・時間、金額など指定できる。
おそらく、日付・時間はGASのUtilities.formatDateの指定文字と同じで、他の数字はスプレッドシートの指定文字と同じっぽい?
スプレッドシートのメニューのこれ。
▼スクリプト例
const sh = SpreadsheetApp.getActiveSheet(); const range1 = sh.getRange('A:A'); // A列のRange const range2 = sh.getRange('B:B'); // B列のRange range1.setNumberFormat('MM/dd'); // A列を日付フォーマット「MM/dd」に指定 range2.setNumberFormat('#,##0'); // B列をカンマ区切りの金額フォーマットに指定
(4)交互の背景色を適用する
applyRowBanding()
指定範囲に交互の背景色を指定したい場合は、Rangeオブジェクトに対してapplyRowBanding()
を使う。
引数にテーマカラーやヘッダー・フッターをつけるかどうかも指定できる。引数なしでデフォルト(グレー・ヘッダーのみ)。
▼スクリプト例
const sh = SpreadsheetApp.getActiveSheet(); const range = sh.getDataRange(); // データの入っている全範囲 sh.getRange(1, 1, sh.getLastRow(), sh.getLastColumn()).applyRowBanding(); // 交互の背景色を指定
まとめ
スプレッドシートにデータが入っている状態から、新たにデータを整理したシート生成→フォーマット指定まで自動化ができた。
次は印刷用に複数シートをまとめて1つのpdf化をしたい。