Go to Qiita Advent Calendar 2024 Top
LoginSignup
15
7

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

All About Group（株式会社オールアバウト）Advent Calendar 2019

Day 13
@k_shiotain株式会社オールアバウト

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

Posted at

はじめに

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

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

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

ヒアリング

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

問題点

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

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

問題点へのアプローチ

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

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

  • 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を使ってソースを管理しているバージョンです

スクリーンショット 2019-12-13 11.42.59.png スクリーンショット 2019-12-13 11.43.14.png スクリーンショット 2019-12-13 11.43.28.png

実際やってみて

良かった点

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

改善したい点

  • すべてを自動化出来ていない
    • 多少のコピペは必要
  • GASがアカウントに紐付いてしまう問題
    • 管理者がいなくなったりしたときの対応が必要
  • GASのバージョン管理が出来ない

まとめ

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

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

参考サイト

15
7
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
15
7

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?