この記事のテーマ

この記事ではスプレッドシートとGASを利用して業務の自動化・効率化を行なった実例を紹介します。同じような業務で時短について悩んでいる方の参考になれば幸いです。

自動化した業務内容

調査対象のドメインのリストがあり、すべてのサイトを確認して以下の情報を表にまとめるという業務です。

サイトタイトル
サイトタイトルの日本語含有率
ドメインにアクセスした時のレスポンスコード
サイトの運営開始年

自動化することで、調査対象のドメイン1,000件に対して、1日１回上記の作業を毎日行えるようにするのが目的でした。

手作業で行なった場合の業務手順

ドメインにアクセスする
サイトタイトルをスプシにコピペする
サイトタイトルの日本語含有率を文字数の割合から概算する
chromeの開発者ツールからレスポンスコードを確認する
Wayback Machineでサイトの運用開始年を検索する

自動化する

それぞれの手順の自動化をGASのみで行いました。
利用した技術的要素は以下のようになります。

スプレッドシートとGASの連携

SpreadsheetApp
https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app?hl=ja

GASでサイトデータを取得

UrlFetchApp
https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app?hl=ja

GASでスクレイピング

Parser

以下のライブラリIDで検索したもの。（バージョン８を利用）

1Mc8BthYthXx6CoIz90-JiSzSafVnT6U3t0z_W3hLTAX5ek4w0G_EIrNw

解説サイトがいくつかありましたが出所が不明でした。
「GAS スクレイピング」でググったら出てきたものです。

サイトの運用開始年を検索

Wayback Machine API
https://archive.org/help/wayback_api.php

手順１：スプシから値を取得

A列のデータをすべて取得する処理です。
A列にドメインを入力しておくことで、ドメインのリストを取得できます。

SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("A1:A").getValues();

手順２：サイトデータを取得

curlのように、サイトに対してリクエストをする処理が１行で書けます。
事前にライブラリなどをインストールする必要もないのでGoogle様さまです。

var response = UrlFetchApp.fetch(url)

手順３：スクレイピング

Parserライブラリをはじめは使っていたのですが、出所が不明な点、レスポンスデータの中から特定の文字列を切り出すだけの処理だった点から、自前で関数を作成しました。
（自前と言いつつ、ChatGPTさんです、、）

function extractString(text, left, right) {
  var startIndex = text.indexOf(left);
  if (startIndex === -1) {
    return "";
  }
  startIndex += left.length;
  var endIndex = text.indexOf(right, startIndex);
  if (endIndex === -1) {
    return "";
  }
  return text.substring(startIndex, endIndex);
}

手順4：運営開始年を取得

Wayback Machineというサービスを利用します。

var url = 'http://archive.org/wayback/available?timestamp=1990&url=' + domain
var response = fetch(url)
if(!response) {
  return ''
}
var contentText = JSON.parse(response.getContentText())
var startTimestamp = contentText.archived_snapshots.closest.timestamp
return startTimestamp.substr(0, 4)

手順５：スプシに出力する

セルを指定して値を引数に渡しています。

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
sheet.getRange(cell).setValue(value);

以上です。
無料で効率的に開発ができてしまう、、GAS恐るべしでした。