はじめに

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

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

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

ヒアリング

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

問題点

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

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

問題点へのアプローチ

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

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"
  }
}

設定は以下のようになっています。
※Cloud Source Repositoriesを使ってソースを管理しているバージョンです

実際やってみて

良かった点

すべてサーバレスなので着手しやすくて便利
作業が楽になった
- 1時間以内で終わるようになった
GASだけでは出来ないけどAPIを作ればできそうなどいろんな応用がききそう

改善したい点

すべてを自動化出来ていない
- 多少のコピペは必要
GASがアカウントに紐付いてしまう問題
- 管理者がいなくなったりしたときの対応が必要
GASのバージョン管理が出来ない
- Chromeの拡張で無理やりバージョン管理
- claspが便利そうなので使いたい

まとめ

利用できるまで時間をかけずに出来て作業時間もかなり短縮できました。
いろいろなサービスを組み合わせることで出来ることが増えます。
（できれば一つのサービスで完結できるとよいのですが）

この実装後にRPAツールが社内で使えるようになったので置き換えても良さそう

参考サイト

Puppeteer
- https://qiita.com/howdy39/items/2f355fea8340a35aa5da
- https://qiita.com/tomi_linka/items/a68cf7840c3da002c6e0