きっかけ
今月頭に転職した会社で入社初日に言われました。
新入社員の入社書類をGoogleFormで収集したんだけど、それをどうやってチェックするか決めてない。
なんかいいやり方ない？
と。
ミッションはこなさねばならぬということで苦肉の策でGASを使ってやってみました。
ちなみにGASはほぼ初めて触りました😂
やりたいこと
- GoogleSpreadSheetに溜まっている銀行口座データ（口座番号や口座名義など）と、GoogleDriveに溜まっている口座番号の確認用画像データ（通帳やキャッシュカードの画像）、約７００件の確認をラクにやりたい
- データ内容は以下のような感じ
| 社員番号 | 氏名 | 金融機関名 | 支店番号 | 口座番号 | 口座名義 | 確認書類のリンク |
|---|---|---|---|---|---|---|
| ９９９９ | きーた 太郎 | きーた銀行 | 999 | 123456 | キータ タロウ | https://googledriveなんちゃらかんちゃら |
- 画像は一人ずつのフォルダに分かれているが、社員証用の写真？等他のファイルも混在している（しかもファイル名がすべて同じ。死）
- この金融機関名・支店番号・口座番号・口座名義が、アップロードされた確認書類と一致しているかチェックしたいとのこと
- 私が何もできなきゃ一件ずつクリックしてやるから、と。（700件・・・地獄）
私なりに考えたこと
- スプレッドシートのデータを配列で取得する
- 1シート1社員として、Stringデータと画像データを貼り付けていく
- 1ブック（って数えていいの？）100シートに達したら次のブックを作ってまた貼り付けていく
直面した問題
- 画像の取得と貼り付け。サイズ制限があるんですね。画像データを取得してblobにしてImgApp（ライブラリ）を使ってサイズ調整して貼り付けた
- 700件の処理にどんなに頑張っても35分かかる。＝途中で終わる。爆
- jpgとpngに混ざってたまにPDFがいる。100件に1件くらい？スプレッドシートに貼り付けられない
- 1に関してはちょっと悩みました。Qiita読んでたら一時ファイル作って削除して・・・っていう方法がいくつかありましたが、それやってると時間がかかりすぎます。一時ファイル作らなくてもできました。
- 2に関してはもう私のスキルでは無理！！ってなったので、元ファイルを２分割して２回スクリプトを流すことにしました。爆 だって結局データを作るためのスクリプトだから、何回も流すわけじゃないし、そんなに作り込むこともないかなって。（後学のために本当はやりたい。けど私の脳みそが足りない。）なんてったってGASちゃんと触り初めてまだ3日だから。笑
- 3も諦めました。（諦めが早い） 幸いPDFは数件しかないので、これはもうフォルダ開いて確認してもらうことにしました。
そんなこんなで出来上がったソースはこちら
code
function insertman() {
//サンプルリストからURLを取得し、配列に格納する
const parent_ss_id = "***************************"
const parent_ss = SpreadsheetApp.openById(parent_ss_id)
const parent_sh = parent_ss.getSheetByName("フォームの回答 1")
const list_number = parent_sh.getRange(2, 3, parent_sh.getLastRow() - 1).getValues()
const list_name = parent_sh.getRange(2, 4, parent_sh.getLastRow() - 1).getValues()
const list_bank = parent_sh.getRange(2, 5, parent_sh.getLastRow() - 1).getValues()
const list_shiten = parent_sh.getRange(2, 7, parent_sh.getLastRow() - 1).getValues()
const list_kouza = parent_sh.getRange(2, 8, parent_sh.getLastRow() - 1).getValues()
const list_meigi = parent_sh.getRange(2, 9, parent_sh.getLastRow() - 1).getValues()
const list_url = parent_sh.getRange(2, 10, parent_sh.getLastRow() - 1).getValues()
const header_list = ["社員番号", "氏名", "金融機関名", "支店番号", "口座番号", "口座名義"]
const count_list = list_number.length
let ss_count
let ss
let sheet
const r = Array.apply(null, new Array(count_list)).map(function(_, i) {
//100件ごとにスプレッドシートの作成
if (i == 0 || i % 100 == 0){
ss_count = i == 0 ? ss_count = 1 : i % 100 == 0 ? ss_count = i / 100 + 1 : ss_count = ss_count
ss = SpreadsheetApp.openById(SpreadsheetApp.create("ss_".concat(ss_count)).getId())
sheet = ss.getSheets()[0]
} else {
//スプレッドシートに新しいシートを作成
sheet = ss.insertSheet()
}
//入力値を挿入
const values = [
header_list,
[list_number[i][0],
list_name[i][0],
list_bank[i][0],
list_shiten[i][0],
list_kouza[i][0],
list_meigi[i][0]
]
]
sheet.getRange(1, 1, 2, 6).setNumberFormat('@').setValues(values)
//ドライブから画像を取得
let image = DriveApp.getFileById(list_url[i][0].indexOf(',') != -1 ? list_url[i][0].substring(0, list_url[i][0].indexOf(",")).replace("https://drive.google.com/open?id=", "").replace("https://drive.google.com/file/d/", "").replace("/view?usp=sharing", "") : list_url[i][0].indexOf("\n") != -1 ? list_url[i][0].substring(0, list_url[i][0].indexOf("\n")).replace("https://drive.google.com/open?id=", "").replace("https://drive.google.com/file/d/", "").replace("/view?usp=sharing", "") : list_url[i][0].replace("https://drive.google.com/open?id=", "").replace("https://drive.google.com/file/d/", "").replace("/view?usp=sharing", ""))
let type = image.getBlob().getContentType()
if (type == "image/jpeg" || type == "image/png") {
//画像を挿入
sheet.insertImage(ImgApp.doResize(image.getId(), 1000).blob, 1, 4)
}
})
}
感想
- 久しぶりにコード書きましたが楽しかったです！（元々IT企業でコード書いてましたが、いろいろあって退社。） 今は人事労務系の部署で給与計算しながらITサポートするっていうポジションで働き出しました。
- いろいろコードの書き方忘れてて中途半端です。すいません。きれいなコードじゃないです😂
- GASってすっごい！感動しました！インフラ系超絶苦手なのでありがたいです。
- しかしAPIもりもりごりごりに書くと遅くなるんですね。スマートに書くことを心がけるようになるから逆に良いのかな。
- これでなにか作りたいけどアイデアがないからまたQiita漁って真似っ子してなにか作ってみようと思います！
- 初心者過ぎて玄人さんから見たらアホみたいなコード書いてると思いますが、もしこれを高速化するアドバイス等ありましたらお願いします！待ってます！！