概要
こんにちは、Offers を運営している株式会社 overflow のバックエンドエンジニアの shun です。今回は、GAS（Google Apps Script）のコードの書き方と、ゴリゴリに GAS を書きまくってきた知見から少しのテクニックを紹介できればと思います。
今の時代、エンジニアリングを利用した業務自動化を実装するのは必ずしもエンジニアだけではないと思っています。ちょっとしたデイリー業務, 対応漏れ確認 など、サクッと自分の業務のサポートをしてくれる相方を、職種問わずに自分自身で実装ができる世界になっています。その大きな協力者になるのが今回ご紹介する GAS（Google Apps Script）となります。
GAS（Google Apps Script） とは？
GAS（Google Apps Script） とは、Google が開発した JavaScript 記法を踏襲したスクリプト言語を実行できるプラットフォームのことです。Gmail アカウントがある人ならば、誰でも利用できます。
GAS（Google Apps Script）入門！
任意のブラウザの URL 記述欄にて sheets.new を実行
sheets.new とは、ログイン中の Google アカウントのマイドライブに新規のスプレッドシートを作成するものです。
今回は説明しませんが、sheets.new があるなら slides.new , docs.new もあります。
メニュー > 拡張機能 > Apps Script を選択
sheets.new にて、新規のスプレッドシートが作成され表示されていると思います。次は GAS のエディタを表示します。
エディタが表示される（はず）
初めてエディタを開いた時は、以下のようになるはずです。
コードを実行してみよう
エディタが開けたので、コード.gs に対してプログラムを書き、実行結果を確認してみましょう。
function myFunction() {
console.log("Hello Google Apps Script");
}
次に、command + r でこの MyFunction を実行すると、以下のように出力結果が出てきます。よく使うのでぜひ覚えておいてください。
基本的なコードの書き方
下準備
sheets.new で作成したシート名「シート 1」を、「社員スキルシート」と名称変更し、データを入れておきます。（弊社ではこのようなシートはございません w）
スプレッドシートのデータを取得する
const getSheetData = () => {
const book = SpreadsheetApp.getActiveSpreadsheet()
const sheet = book.getSheetByName("社員スキルシート")
const data = sheet.getDataRange().getValues()
return data
}
console.log(getSheetData())
実行結果 #> [
[ '社員ID', '社員名', 'コミュニケーション力', 'おもてなし力', '業務遂行スピード', '打たれ強さ', 'タスク管理力' ],
[ 1, 'hoge丸', 5, 1, 2, 0, 5 ],
[ 2, 'fuga丸', 1, 2, 5, 5, 5 ],
[ 3, 'piyo丸', 3, 3, 4, 4, 5 ],
[ 4, 'おじゃる丸', 5, 5, 5, 5, 5 ]
]
スプレッドシートのデータをフィルタリングする
const sheetData = getSheetData()
// コミュニケーションが2以上、業務遂行スピードが3以上のデータにフィルターかけ
const filterSheetData = (data) => {
return data.filter((d, index) => {
return index > 1 && d[2] >= 2 && d[4] >= 3
})
}
console.log(filterSheetData(sheetData))
実行結果 #> [
[ 3, 'piyo丸', 3, 3, 4, 4, 5 ],
[ 4, 'おじゃる丸', 5, 5, 5, 5, 5 ]
]
スプレッドシートのデータから欲しいデータに整形する
const sheetData = getSheetData()
const filtered = filterSheetData(sheetData)
const parseSheetData = filtered => {
return filtered.map(d => {
return {
id: d[0],
name: d[1],
communicationSkill: d[2],
omotenasiSkill: d[3],
workingSpeedSkill: d[4],
mentalSkill: d[5],
taskManagementSkill: d[6]
}
})
}
console.log(parseSheetData(filtered))
実行結果 #> [
{ id: 3,
name: 'piyo丸',
communicationSkill: 3,
omotenasiSkill: 3,
workingSpeedSkill: 4,
mentalSkill: 4,
taskManagementSkill: 5
},
{ id: 4,
name: 'おじゃる丸',
communicationSkill: 5,
omotenasiSkill: 5,
workingSpeedSkill: 5,
mentalSkill: 5,
taskManagementSkill: 5
}
]
コードからスプレッドシートの値を変更する
const updateCellValue = userName => {
const book = SpreadsheetApp.getActiveSpreadsheet()
const sheet = book.getSheetByName("社員スキルシート")
const targetUserIndex = sheet.findIndex(d => {
return d[1] === userName
})
// index + 1が実際の行番号になる
const targetRowNum = targetUserIndex + 1
const targetColumnNum = 3
sheet.getRange(targetRowNum, targetColumnNum).setValue(1)
}
// hoge丸のコミュニケーションスキルが下がったので1にする
updateCellValue('hoge丸')
実行後、社員スキルシート > hoge 丸行のコミュニケーション列の値が 1 に変わります。
テクニック集
スラッシュ区切りのファイル名で階層化
コード.gs を Practices/Main.gs とリネームすると、ディレクトリ構成がわかりやすくなります。ファイルが多くなりすぎたときに有効です。
列名から index 取得
スプレッドシート運用してると、運用者による列移動、列追加は避けて通れません。そうなると、列指定時にマジックナンバーで評価するのが怖くなってきます。
想定していたセルの値ではない箇所が変更・参照され、原因探しに時間とられます。そこで、列名から具体的な列番号を取得してしまおうということです。
上記で紹介した スプレッドシートのデータをフィルタリングする を書き換えてみましょう。
const sheetData = getSheetData()
const header = sheetData[0] // ヘッダーを取得
const filterSheetData = (data) => {
return data.filter((d, index) => {
return index > 1
&& d[header.indexOf('コミュニケーション力')] >= 2 // マジックナンバーからリーダブルなコードへ
&& d[header.indexOf('業務遂行スピード')] >= 3 // // マジックナンバーからリーダブルなコードへ
})
}
setValue ではなく setValues を使う
上での紹介時に普通に使っていた getRange や setValue などは実は Google が提供する API を叩いてるだけなので、リクエストを乱暴に送っては処理が遅くなります。
ちなみにですが、デフォルトで GAS の処理実行時間は最大6 分となっています。同じ結果を出力するのであれば、速度が速いものを選択する必要があります。
setValue はあるひとつの cell に対して値を set する API です。それに対し、 setValues は一括で指定した cell 範囲に値を入れます。
1000 行のデータの特定の列データを更新したいとすると、 setValue を利用すると少なくとも 1000 回 API リクエストが飛びます。しかし setValues を使用すると 1 回の API リクエストで済むため、速度の向上に役立ちます。
Bad な例）
const insertData = [
[1],[2],[3]........[1000]
]
const book = SpreadsheetApp.getActiveSpreadsheet()
const sheet = book.getSheetByName("1000行データがあるシート")
insertData.forEach((d, index) => {
const realRowNum = index + 1
sheet.getRange(realRowNum, 1).setValue(d) // getRange + setValueが1000回ずつ実行され、遅いし負荷高いしよくない
})
Good な例）
const insertData = [
[1],[2],[3]...（中略）...[1000]
]
const book = SpreadsheetApp.getActiveSpreadsheet()
const sheet = book.getSheetByName("1000行データがあるシート")
// A1セルから、A1000までの範囲を指定
sheet.getRange(1, 1, insertData.length, insertData[0].length).setValues(insertData)
シャッフルなど、自力で書くには面倒な処理はライブラリに頼る
例えば社員名簿から全社員を取得して、並び順をシャッフルしたいとします。よし書くぞー！って思っても、以下のスクリプトのように意外と面倒です。
const shuffle = array => {
let currentIndex = array.length, randomIndex;
while (currentIndex != 0) {
randomIndex = Math.floor(Math.random() * currentIndex)
currentIndex--
[array[currentIndex], array[randomIndex]] = [array[randomIndex], array[currentIndex]]
}
return array
}
console.log(shuffle([1,2,3,4,5,6]))
実行結果 #> [2,6,5,1,3,4]
これを書くためには、競プロスキル的なものが必要になります。
また、その辺のコピペを貼り付けて後から見た時に理解不能となる可能性が高まります。
私がよく使うのは gas-underscore という、 JS ライブラリの Underscore.js を GoogleAppScript 上でも利用可能にしたライブラリです。
使い方は簡単で、GAS の画面から「ライブラリ」横のプラスボタンをクリックし「スクリプト ID」部分に「1PcEHcGVC1njZd8SfXtmgQk19djwVd2GrrW1gd7U5hNk033tzi6IUvIAV」を入れて追加するだけです。
あとはコード上の一番上で const _ = Underscore.load() を記述すれば準備完了。
前置きが長くなりましたが、上記のシャッフル処理をライブラリに頼って書いてみましょう。
const _ = Underscore.load()
console.log(_.shuffle([1,2,3,4,5,6]))
実行結果 #> [2,6,5,1,3,4]
超簡単！
定数などは、まとめてファイル管理しておく
これはテクニックというよりは、メンテナーのための工夫です。
定数とはその名の通り一定の値なので、私は Setting.gs というファイルに極力定義するよう心がけております。
const EMPLOYMENT_SKILL_SHEET_NAME = '社員スキルシート'
const MENTION_CHANNEL = "#slack_notification"
const MENTION_CHANNEL_TEST = "#test_slack_notification"
.
.
.
ちなみに GAS の場合は別ファイルの import などは不要なため、別のファイルから EMPLOYMENT_SKILL_SHEET_NAME を呼び出しても正常に認識されます。
まとめ
今回は GAS（Google Apps Script）の入門から基本的なコードの書き方についてまとめました。エンジニアでない方も、エンジニアの方でも業務の効率化は必要不可欠なため、「あれ、これ毎日やってる作業自動化できたりするかな？」と思う節がある方は是非 GAS に触れてみてください。
少々長くなりましたが、最後まで読んで頂き、ありがとうございました。「いいね」していただけると記事執筆の励みになりますので、参考になったと思った方は是非よろしくお願いします！
次回は Slack や Notion など、他サービスとの連携した GAS の作成方法について書こうと思います。
関連記事
副業転職の Offers 開発チームがお送りするテックブログです。【エンジニア積極採用中】カジュアル面談、副業からのトライアル etc 承っております💪 jobs.overflow.co.jp
Discussion
console.log(getSheetData) ではなく
console.log(getSheetData()) が正しい。
@iwbjpさん
ご指摘ありがとうございます！
修正いたしました！
初めまして。
こちらで紹介されている「スラッシュ区切りのファイル名で階層化」のテクニックはまだ使えるのでしょうか。
私の環境では上手く階層化されず、そのままスラッシュが入ったファイル名になってしまいます。
@GawaiinTIKさん
初めまして。執筆者です。
コメントを受けまして、こちらで先ほど試してみたところ、使えなくなってましたね。。
2023年11月27日の時点では機能していたようです。（参考）
GASのリリースノート的に、12月・1月のリリースでデグレ or 機能提供を外されたかもしれません。
返信ありがとうございます。
この情報がここでしか見つけられず、とてもいい機能だっただけに残念です…
調べていただいてありがとうございました。
いえいえ、コメント頂き嬉しかったです！
私個人としてもこの機能は使い続けたかったです、、残念。。