集計作業を自動化して1回の作業を2時間以上短縮した話

Posted at 2019-12-13

はじめに

オールアバウトナビのシステム部で働いている @k_shiota です。

この記事は、All About Group（株式会社オールアバウト） Advent Calendar 2019 13日目の記事です。
オールアバウトナビのシステム部では目安箱を用意してチームで定常作業の効率化を行っています。

その中で定期的にやっている作業を自動化出来ないかと相談がありましたので、その対応を紹介したいと思います。

ヒアリング

話を聞いてみると、定期的に数値をコピペしてエクセルに貼り付ける作業を何十回か繰り返すということでした。
どのような作業か順を追って説明が可能なので、自動化することができそうです。
少なくとも、同じ作業の回数を減らすことができそうです。

問題点

ヒアリングの内容から下記の問題点がありました。

数値取得のAPIが存在しない
- ログインが必要
- CSVダウンロードできるがまとめてダウンロードは出来ない
- おそらくスクレイピングする必要がある
コピペの連続で苦痛
- 作業に3時間かかる

問題点へのアプローチ

エクセル管理をスプレッドシート管理にする
- 自分がエクセルでVBAを使うことになれていないため
- これは許可をもらって問題なし
- GASを使える
数値取得はスクレイピングで行う
- 【重要】アクセス先の許可をもらって問題なし
ログインが必要
- スプレッドシートで実行するGASだけで出来ないか
  - できそうだけど手間がかかりそう
    - cookieを取得したりヘッダーをいじったり。。。
    - GASではなく他の仕組みでできないか
- 取得した数値を返すAPIを作る
  - なるべくサーバ管理をしたくない
    - サーバレスにしたい
    - Google Cloud Functions
  - Google Cloud Functions + スクレイピングで検索
    - Puppeteerが使える！

というわけで実装をかんたんにまとめると下記になりました

GAS + Google Cloud Functionsのそれぞれの役割
- GAS
  - スプレッドシートの操作
- Google Cloud Functions
  - 数値取得のスクレイピング
  - Puppeteerを使う

実装

ヒアリングと調査が終わり形にできそうなので、ここから実装になります。

GAS

GASでは

スプレッドシートから必要な情報を取得してAPIに投げる
APIから返ってきたデータをスプレッドシートに埋め込む

ということをやっています。

このスクリプトのfunction putDataFromSpreadsheet()をボタンに割り当てて実行させています。（参考）

以下スクリプトの抜粋です。

getData.gs

/**
* 表から数値を取得
* Google Cloud FunctionsのAPIを叩く
*
* @param string $site_id ('3131313,3131314')
* @param string $start_date ('2019/10/02')
* @param string $end_date ('2019/10/03')
* @return array
*/
function getDataFromSiteName(site_ids, start_date, end_date){
    try {
        var result = UrlFetchApp.fetch('https://asia-northeast1-xxxx.cloudfunctions.net/xxxxxxxxx-scraping?site_id=' + site_ids + '&start_date=' + start_date + '&end_date='+ end_date)
    } catch (e) {
        Logger.log(e);
        return null
    }
    return JSON.parse(result.getContentText())
}

/**
* スプレッドシートを作成
*
* @param string $title
* @return string
*/
function createSpreadsheet(title){
    return SpreadsheetApp.create(title).getId()
}

/**
* スプレッドシートのリンクを表示
*
* @param string $spreadsheet_id
*/
function showSpreadsheetlink(spreadsheet_id){
    showAnchor('Data','https://docs.google.com/spreadsheets/d/' + spreadsheet_id)
}

/**
* ポップアップでリンクを表示
*
* @param string $name
* @param string $url
*/
function showAnchor(name,url) {
    var html = '<html><body><a href="'+url+'" target="blank" onclick="google.script.host.close()">'+name+'</a></body></html>'
    var ui = HtmlService.createHtmlOutput(html)
    SpreadsheetApp.getUi().showModelessDialog(ui,"選択したIDのデータを取得")
}

/**
* スプレッドシートを作成
*
* @return array {site_ids, start_date, end_date}
*/
function getCellData() {
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
    var sheet = spreadsheet.getActiveSheet()
    var selectRange = sheet.getActiveRange()
    var range = sheet.getRange(selectRange.getRow(), selectRange.getColumn(), selectRange.getLastRow() - selectRange.getRow() + 1)
    var site_ids = range.getValues()
    range = sheet.getRange("L2")
    var start_date = range.getValue()
    range = sheet.getRange("M2")
    var end_date = range.getValue()
    return {site_ids: site_ids, start_date: start_date, end_date: end_date}
}

/**
* スプレッドシートに書き込む
*/
function putDataFromSpreadsheet(){
    var cell_data = getCellData()
    var spreadsheet_id = createSpreadsheet(Utilities.formatDate( cell_data.start_date, 'Asia/Tokyo', 'yyyy/MM/dd') + '-' + Utilities.formatDate( cell_data.end_date, 'Asia/Tokyo', 'yyyy/MM/dd') + 'site data')
    var spreadsheet = SpreadsheetApp.openById(spreadsheet_id)
    var response = getDataFromSiteName(cell_data.site_ids.join(','), Utilities.formatDate( cell_data.start_date, 'Asia/Tokyo', 'yyyy/MM/dd'), Utilities.formatDate( cell_data.end_date, 'Asia/Tokyo', 'yyyy/MM/dd'))
    if (response == null) {
        SpreadsheetApp.getUi().alert('APIからエラーが帰ってきたため処理できません。再度実行してください。')
        return
    }
    for (var i = 0; i <　response.length; i++) {
        spreadsheet.insertSheet(response[i].site_id)
        var sheet = spreadsheet.getSheetByName(response[i].site_id)
        sheet.getRange(1, 1).setValue(response[i].site_id)
        sheet.getRange(2, 1, 1, response[i].header.length).setValues([response[i].header])
        sheet.getRange(3, 1, response[i].data.length, response[i].header.length).setValues(response[i].data)
    }
    showSpreadsheetlink(spreadsheet_id)
}

Google Cloud Functions

Cloud FunctionsではPuppeteerを使って

サイトにログインをする
GASから受け取った情報を使ってページを遷移する
遷移したページの情報を取得する（Tableからデータを取得）
数値をJSONにまとめて返却する

ということをやっています

function_scraping_index.js

const puppeteer = require('puppeteer')
let page
const site_ids = ''
const start_date = ''
const end_date = ''

/**
* puppeteerのブラウザ用意
*
* @return object
*/
async function getBrowserPage() {
  // Launch headless Chrome. Turn off sandbox so Chrome can run under root.
  const browser = await puppeteer.launch({ args: ['--no-sandbox'] })
  return browser.newPage();
}

exports.getSiteData = async (req, res) => {

  if (!page) {
    page = await getBrowserPage();
  }
  const site_ids = req.query['site_id'].split(',')
  const start_date = encodeURIComponent(req.query['start_date'])
  const end_date = encodeURIComponent(req.query['end_date'])

  const navigationPromise = page.waitForNavigation()
  // 検索クエリとともにサイトへアクセス
  await page.goto(`https://example.com/login`, {
    waitUntil: 'networkidle2'
  });
  await navigationPromise
  // IDとPASSWORDをフォームに入力
  await page.type('input[name="login_form[email_address]"]', process.env.EMAIL)
  await page.type('input[name="login_form[password]"]', process.env.PASSWORD)

  // ログインボタンをクリック
  await page.click('button[type="submit"]')

  // 画像表示まで間隔が開くので待つ
  await page.waitFor(500)

  // 繰り返す
  lists = []
  for(let site_id of site_ids) {
    await gotoUrl(site_id, start_date, end_date)
    await navigationPromise
    const site_name = await page.evaluate(getSiteName)
    const header_result = await page.evaluate(getHeadDataTable)
    const result = await page.evaluate(getDataTable)
  
    lists.push({site_id: site_name, header: header_result, data: result})
  }

  // レスポンスで返す
  res.set('Content-Type', 'application/json');
  res.send(lists);
};

/**
* ページの遷移
*
* @param string $site_id ('3131313')
* @param string $start_date ('2019/10/02')
* @param string $end_date ('2019/10/03')
*
* @return object
*/
async function gotoUrl(site_id, start_date, end_date) {
  return page.goto(`https://example.com/analysis/daily/?from%5D=${start_date}&to%5D=${end_date}&ids%5D%5B%5D=${site_id}`, { waitUntil: 'domcontentloaded', timeout: 20000 });
}

/**
* サイト名を取得
*
* @return string
*/
function getSiteName() {
  const selector = "body > div.container > p > b"
  return document.querySelector(selector).textContent
}

/**
* ページ内のtableの数値を取得
*
* @return array
*/
function getDataTable() {
  let grabFromRow = (row, child) => row
    .querySelector(`td:nth-child(${child})`)
    .innerText // ====> if a value is 'null', I get an error here!
    .trim();

  const rows = "table > tbody > tr"

  const data = []
  const t = document.querySelectorAll(rows)
  for (const r of t) {
    data.push([
      grabFromRow(r, 1).trim(),
      grabFromRow(r, 2).trim(),
      grabFromRow(r, 3).trim(),
      grabFromRow(r, 5).trim()
    ]);
  }
  return data
}

/**
* ページ内のtableのヘッダー情報を取得
*
* @return array
*/
function getHeadDataTable() {
  let grabFromRow = (row, child) => row
    .querySelector(`th:nth-child(${child})`)
    .innerText // ====> if a value is 'null', I get an error here!
    .trim();

  const rows = "table > thead > tr"

  const t = document.querySelectorAll(rows)
  for (const r of t) {
    return [
      grabFromRow(r, 1).trim(),
      grabFromRow(r, 2).trim(),
      grabFromRow(r, 3).trim(),
      grabFromRow(r, 5).trim()
    ];
  }
}

package.json

{
  "name": "getSiteData",
  "description": "website web scraping with Puppeteer on Cloud Function ",
  "main": "index.js",
  "keywords": [
    "puppeteer"
  ],
  "license": "MIT",
  "dependencies": {
    "puppeteer": "^1.18.1"
  }
}