TECH PLAY女子部 Advent Calendar 2018 の記事です！
どうでもいいですが「GAS集」って爆発しそうな感じがしますね
はじめに
会社でGoogleDriveを使ってるので、GoogleSpreadsheetを毎日使っています。
GASでシートとシートのやりとりや、外部システムからシートへの転記、メールの取得などなど
実にいろんなことができますが、
実際、こういうことしたいなーと思ってGASを書き始めると、
「これいつもやってるな」とか「またこの処理か」と、結構パターン化されてるんじゃないかと思います。
自分の作業の効率化のためと、周りでもGAS使えるようになりたいって人もいるので
よく出てくるパターンをTips化できればと思っています！
Tips
末尾にレコードを追加する
/**
* 結果一覧に処理結果を追加
*
* @param sheet_name[string] 記載シート名
* @param results[array] 取得結果 ２次元配列であること
*/
function setResults(sheet_name, results) {
if (results.length === 0) {
return;
}
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(sheet_name);
var range = sheet.getRange(sheet.getLastRow()+1, 1, results.length, results[0].length);
range.setValues(results);
}
呼び出し側
var results = [
[1, 'Aさん', 'A@example.com']
,[2, 'Bさん', 'B@example.com']
];
setResults('新規ユーザ一覧', results);
```
ポイント
* 末尾 = `getLastRow()`+1 っていうのが大事
* 空配列渡せないので事前にバリデーションしておく
### 特定カラムの最終行の値を取得する
``` js
/*+
* 特定カラムの最終行の値を取得する
*
* @param sheet_name[string] 取得シート名
* @param col_num[string] 列 ex. B
*/
function getLastValue(sheet_name, col) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(sheet_name);
return sheet.getRange(col + sheet.getLastRow()).getValue();
}
```
呼び出し側
`````` js
getLastValue('新規ユーザ一覧', '0'); // A列の一番最後の値
```
ポイント
* 1行で書けそうですねｗ
* たまたま別のシートの最終行の値を参照しなくちゃいけないケースが多発したときに追加しました。
### 別シートの特定行を検索して合致したら特定行の値を返す
俗に言う`VLOOKUP`とおなじことをGASでやりたい
ex. 「企業シート：企業コード(A列)」の値が「ユーザシート：企業コード(E列)」に存在した場合、
その行の「企業シート：企業名(B列)」の内容を「ユーザシート：企業名(F列)」に転記したい
たとえば`VLOOKUP`だと参照なので取得元シートが日次で更新されたりする場合に値を残しておきたい
```js
/**
* VLOOKUPのように、シートの指定行の値を別のシートの指定の値から検索し、存在すれば同列の値を入力する
*
* 注意事項
* * 各シートともにヘッダーは1行と仮定する
*/
function setOtherSheetCol(read_sheet_name, write_sheet_name, read_key_col, read_value_col, write_key_col, write_value_col) {
var read_sheet = ss.getSheetByName(read_sheet_name);
var write_sheet = ss.getSheetByName(write_sheet_name);
// ヘッダー行は1行なので、2行目からスタートする
var start_row = 2;
// ヘッダーのみなら何もしない
var read_last_row_index = read_sheet.getLastRow();
if (read_last_row_index < start_row) {
console.log('対象なしのため実行しない');
return;
}
var read_keys_values = read_sheet.getRange(read_key_col + start_row + ':' + read_key_col + read_last_row_index).getValues();
var read_values = read_sheet.getRange(read_value_col + start_row + ':' + read_value_col + read_last_row_index).getValues();
var write_keys = write_sheet.getRange(write_key_col + start_row + ':' + write_key_col + write_sheet.getLastRow()).getValues();
// 2次元配列形式なのを1次元にする
var read_keys = [];
for (var i = 0; i < read_keys_values.length; i++) {
read_keys.push(read_keys_values[i][0]);
}
for (var i = 0; i < write_keys.length; i++) {
var key = write_keys[i][0];
var read_index = read_keys.indexOf(key);
// 合致した場合
if (read_index !== -1) {
var write_row = start_row + i;
write_sheet.getRange(write_value_col + write_row).setValue(read_values[read_index]);
}
}
}
```
呼び出し側
`````` js
// 読み取りデータ生成
var read_sheet_name = '企業シート'; // 読み取りシート名
var write_sheet_name = 'ユーザシート'; // 書き出しシート名
var read_key_col = 'A'; // 読み取りシート側の検索列
var read_value_col = 'B'; // 読み取りシート側の転記する値の列
var write_key_col = 'E'; // 書き出しシート側の検索列
var write_value_col = 'F'; // 書き出しシート側の転記先の列
setOtherSheetCol(read_sheet_name, write_sheet_name, read_key_col, read_value_col, write_key_col, write_value_col);
```
ポイント
* ご覧の通り、引数多いので気になる場合はハッシュでくくって、関数内で存在判定してもいいです
* 社内ツールなのでコードは雑ですすみません／(^o^)＼
* あと両シートのヘッダーは1行と決めてしまってるので、必要ならここも動的に変更する
* 1列で複数行の値をとってくるときでも`getValues()`使うと多次元配列になってるので、1次元配列に整形した方がいい
* 実際はもう少し検索対象に条件追加したりしたくなるので、これをベースにして対象を拡大したりif追加したりしてます
### 外部システムをスクレイピングする
他のWebページの情報を手軽にとってきてシートに吐き出したい、というとき
``` js
function getWeb() {
var options = { // 欲しい情報に応じて変える
method: "get"
};
var url = 'https://example.com/';
var response = UrlFetchApp.fetch(encodeURI(url), options); // 全角スペースなどがあると実行エラーとなるのでエンコードする
var content = response.getContentText("UTF-8");
var value_match = content.match(/<p>(この情報がほしいよ*)<\/p>/g); // 正規表現は適当
var value = (value_match !== null && value_match.length > 1) ? value_match[1] : '';
}
```
ポイント
* `content`でHTMLが取ってこれるのであとは正規表現で取得
* もちろんライブラリ使ってもOKですがたまにHTMLが壊れてると解析できなかったりするので注意
## 他にも
最近GASでやったこと
### 自作メニューを追加する
独自でこういうメニューを追加できるので
自分以外のチームのみんなに各タイミングで関数実行して欲しいときとか便利。
``` js
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('便利メニュー')
.addItem('企業情報を更新', 'updateCompanies')
.addItem('メールを送信', 'showSidebar')
.addToUi();
}
```
ポイント
* この`onOpen()`をトリガーで「シート起動時」に設定するのを忘れないように（いつも忘れる）
* さらにサブウィンドウみたいなのを開いてそこで入力とか選択とか操作させたりも可能
* ex. HTMLファイル作って、`showSidebar()`でそのHTMLを読み込むようにしておく
### 実行可能APIとして導入
いろんなシートで似た処理を書いててソースが散ってしまってる〜という場合には
自分でよく使う処理を関数化して1箇所にまとめておき、ライブラリとして呼び出すことができます。
* GASの設定で、「公開 > 実行可能APIとして導入」にて公開すると、他のGASから呼び出すことが出来ます。（同じようにWEBアプリケーションとして公開というのも可能）
* 自分以外のユーザが実行する処理が含まれている場合、権限を「自分のみ」にしてると動かないので注意してください！
## まとめ
他にもいろいろあった気がするけど…
GAS便利ですが、いろんなものを自動化とか集約しすぎて何が動いてるかわからなくなるという
弊害もあるなーと最近感じています。
業務フローはなるべくシンプルになるように心がけていきたいと思います(｀･ω･´)