この記事について
非エンジニアがスプレッドシートの処理を自動化したりするときに役立った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頑張って欲しいです...。