この記事について

非エンジニアがスプレッドシートの処理を自動化したりするときに役立ったtipsを記載してます。

エンジニアさんへ依頼せずとも自動化したい
数値を自動集計して、毎朝KPIの通知をslackに投稿したい
データの更新・最新化を人手を介さず実行したい

といった文脈で使っています。

データ取得・制御・変更

シートの指定方法

let sheet = SpreadsheetApp.getActiveSpreadsheet();

先頭にあるシートが対象になる。
シートを指定したい場合は、

let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('{シート名}');

などでシート名を指定する。

また以降に記載する「sheet」は、

let sheet = SpreadsheetApp.getActiveSpreadsheet();

で取得した状態を前提にする。

値(セル)の読み込み

シートの範囲指定

let range = sheet.getRange('A2:AW2');
let rangeValue = range.getValues();

で範囲に存在するものが複数取得することができる。( 返り値は2次元配列。公式ドキュメントはこちら )

単一取得(ひとつのセル)だけならば、

range.getValue();

で取得する。

追加・編集系

シートの最後の行に値を追加する

let lists = sheet.getRange("A1:Z1").getValues();
sheet.appendRow(lists[0]);

引数は配列で渡します。公式ドキュメントはこちら

シートの値を更新する

sheet.getRange("A2").setValue('無効');

setValue()を使って指定したセルに値を設定します。(既に値があれば上書き)

setValues()もありますが、こちらは引数に配列を渡すことで、指定した範囲を一括変更できます。
公式ドキュメントはこちら

その他 (判定・ループ系)

いつも忘れるので備忘で。

判定

if (a > 0) { '処理記載' }

if (a > 0) {
   result = '処理1';
 } else {
   result = '処理2';
 }

ループ

  for(var i=0; i<5; i++){
    Logger.log(i)
  }

範囲を指定して値を取得したものを表示

let targetValues = sheet.getRange("A1:B1000").getValues();

for(var i=0; i<targetValues.length; i++){
  // 1行単位の取得
  getRowValues = targetValues[i];

  // 単一セルまで取得したいとき (A列)
  getFirstCellValue = targetValues[i][0];
}

通知系

数値の自動更新をslackなどに投稿したいときに使う内容です。

function postToSlack(messages) {
  // slackで発行したwebhookのURLを記載
  let postUrl = 'https://hooks.slack.com/services/xxxxxx';
  let icon = ':heavy_check_mark:';  // 通知時に表示されるアイコン

  let message = messages.join('\n');

  let jsonData =
  {
     "icon_emoji": icon,
     "text" : message
  };
  let payload = JSON.stringify(jsonData);

  let options =
  {
    "method" : "post",
    "contentType" : "application/json",
    "payload" : payload
  };

  UrlFetchApp.fetch(postUrl, options);
}

送りたい内容を引数で渡すことで、slackで指定したチャンネルへ投稿される。(投稿先はwebhookの設定時に指定 )

webhook発行はこちらを参考に。

その他Tips

営業日判定

Google公式カレンダーから祝日を取得して日付が営業日(土日祝以外)の場合にtrueを返します。

// 営業日判定
let targetDate = new Date();
isWorkday(targetDate) // 営業日ならtrue、営業日で無いならfalse

function isWorkday(checkDay) {

  // checkDay の曜日を確認、週末は休む (false)
  let restOrWork = ["REST","mon","tue","wed","thu","fri","REST"]; // 日〜土
  if (restOrWork[checkDay.getDay()] == "REST" ) {
    return false;
  }; 

  // 祝日カレンダーを確認する
  let calJpHolidayUrl = "ja.japanese#holiday@group.v.calendar.google.com";
  let calJpHoliday = CalendarApp.getCalendarById (calJpHolidayUrl);
  if (calJpHoliday.getEventsForDay (checkDay).length != 0) {
    // その日に予定がなにか入っている = 祝祭日 = 営業日じゃない (false)
    return false;
  } ;

  // 全て当てはまらなければ営業日 (True)
  return true;
}

参考にさせていただいた記事はこちら → 土日は通知されたくない！平日・営業日だけ動く GAS (Google Apps Script) を作る

パーセント表記

小数点二桁までを表示するパーセント表記を作成するときに使う
割り算したい値、「a」「b」があったとして、'2.11%'といった表記にしたいときにつかう。

// パーセント表記
function percent(a, b) {
  p = a / b * 100;
  returnValue = Math.floor( p * Math.pow( 10, 2 ) ) / Math.pow( 10, 2 );
  return returnValue + '%';
}

四捨五入

単純に四捨五入した値を取りたいときにつかう。

// 四捨五入(小数点第二位)
function roundNum(num){
  return Math.round(num * 100) / 100
}

数値をカンマ区切りに

「1000」 → 「1,000」

// 3桁カンマ区切りとする.
function comma(num) {
  return String(num).replace( /(\d)(?=(\d\d\d)+(?!\d))/g, '$1,');
}

こちらの記事を参考にさせていただきました。

最後に

まとめ

手作業を減らすことで新たな時間を生み出し、違う仕事ができるようになる。という点で、少しでもGASの理解を深めたことは業務の生産性に直結している印象です。
ぜひ仕事の幅を広げたい。もっと考える時間が欲しいのに、手作業に追われている...。と感じている、非エンジニアの方のお役に立てればと思います。

余談

余談ですが、公式ドキュメントであるGoogleDeveloperがググってもあんまり上位表示されません。
公式ドキュメント
もうちょっとSEO頑張って欲しいです...。

日々の業務をする上で役立った・よく使うGASのtips