スプレッドシートで使うGAS③

エクセルなどのテンプレートをスプレッドシートにインポートしている場合に、入力の自動化などで起こりがちな不具合対策として必要になってくるGASです。

指定したシートをアクティブにする

const ss = SpreadsheetApp.getActiveSpreadsheet();

const sh = ss.getSheetByName('対象のシート名').activate();

最終行/列を取得

let lastrow = sh.getLastRow();// 最終行

let lastclm = sh.getLastColumn();//最終列※最終列が結合されている場合に少なく表示されるので注意

設定されている数値書式を取得(３桁区切りなど)

let cell = 'M10';//M10セルの書式を取得 

let nf = sh.getRange(cell).getNumberFormat();

よく使う書式

const range = sh.getRange(cell);

テキスト形式: range.setNumberFormat('アットマーク') (書式なしのプレーンテキト)

日付形式: range.setNumberFormat('yyyy/MM/dd')

日時形式: range.setNumberFormat('yyyy/MM/dd HH:mm:ss')

パーセント: range.setNumberFormat('0.00%') (小数点以下2桁のパーセント)

通貨形式: range.setNumberFormat('¥#,##0') (円記号付き整数)

数値（桁区切り）: range.setNumberFormat('#,##0')

数値(色指定あり)：range .setNumberFormat("#,##0; [Red]-#,###")//マイナスの場合に赤で表記

数値（小数点）: range.setNumberFormat('0.00') (小数点以下2桁)

数式取得

 var formulas = sh.getRange("M10").getFormula();//M10セルに設定されている数式を取得

 let formulas = sh.getRange("B1:C6").getFormulas();//複数行まとめて２次元配列で取得

セル罫線(色・タイプ)取得

色の取得

let cell = 'A30';

let cb = sh.getRange(cell).getBorder(); 

let cellBorderColor = cb.getLeft().getColor().asRgbColor().asHexString();// セルA30の左側の罫線の色を16進数表記で取得

  getTop()//上

  getLeft()//左

  getRight()//右

  getBottom()//下

タイプ(実線、破線など)の取得

borderType = cb.getLeft().getBorderStyle().name();//  SOLID など

罫線の色・タイプの設定例

sh.getRange(lastrow,1,1,lastclm)

.setBorder(true,true,true,true,false,false,'339966',SpreadsheetApp.BorderStyle.SOLID);

書式：setBorder(上、左、下、右、垂直、水平、色、スタイル)

スタイル

DOTTED 点線

DASHED 破線

SOLID 実線

SOLID_MEDIUM 中実線

SOLID_THICK 太い実線

DOUBLE 2重線

セルの結合

let mergeCell = sh.getRange("M30:N30");//M30とN30を結合

mergeCell.mergeAcross();

文字の折り返し・行高自動調整

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sh = ss.getActiveSheet();

  let cell = sh.getRange("M10");//適用させるセルがM10

  cell.setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP); //折り返す設定

※折り返し設定のみの場合

  sh.autoResizeRows(10, 1);//10行目から1行(10行目のみ)に行高の自動調整を適用

※折り返し・行高自動調整の両方を適用

画像の埋め込み

画像がgoogleドライブにある場合

let cell = sh.getRange("D31");//セル指定

const imgId = 'イメージＩＤ';

let fileurl = "ｈｔｔｐｓ://drive.google.com/uc?export=download&id=" + imgId;

let formula = '=IMAGE("' + fileurl + '")';

cell.setValue(formula);

なぜかGASが使えない場合

テンプレをインポートすると下図のようにGASが使えない場合は、インポートしたファイルがテンプレートではない可能性があります。テンプレートとして保存しなおした上でインポートしてみてください。