改！広告収益分配ツールを改善してみた

develop

GAS JavaScript WordPress

2026/09/18

こんにちは。Kalsarikannintのフロントエンド担当・daiです。

約1年前、Google AdSenseの審査に通過したタイミングで、~~このKalsarikannint運営2人がケンカしないために~~「広告収益分配ツール」を作りました。

仕組みとしては、Google Analyticsから月次のページ別PVを取得し、AdSenseの売上金額を取得して、執筆者ごとのPV割合で収益を分配するというものです。すべてGoogle系のサービスで完結するので、Google Apps Scriptでスプレッドシートに集計する形にしていました。

v1の課題点と改善策

ただ、実際に運用する前提で見直してみると、いくつか気になるところがありました。

レポートが見づらい

スプレッドシートで、まっさらなシートにレイアウトも含めてすべてGASで描画させていたので、~~面倒くさくて~~凝ったデザインにしていなかったのが原因です。

今回からは、テンプレートのシートをあらかじめ作っておき、それをコピーして月ごとのシートを作成、そこに値だけを入れ込む仕様に変更しました。

記事ページ以外も集計される

トップページやカテゴリ内記事一覧ページなど、記事本体ではないページも集計の対象になってしまっていました。

今回は正規表現などを使って、以下のルールで、純粋な記事ページだけを集計の対象とするようにしました。

/{カテゴリ}/{記事ID} でパーマリンク設定しているので、 /{文字列}/{数値} の形式のURLのみを対象とする
ただし、 /page/2 のようにページネーションで遷移した先も同様の構成になるので、文字列 == page は除外としました

執筆者名を手入力しないといけない

新しい記事を見つけたら、「記事一覧」シートに追加され、執筆者名は僕が毎月手動で入力する仕様にしていました。

WordPressではAPIで /wp-json/wp/v2/posts で記事情報を JSON 形式で取得でき、そこに author の ID が載るので、そこを参照して自動で執筆者まで取得するように改善しました。これでノータッチで集計が完了するようになりました。

スプシを見に行かないとレポートが読めない

そもそも、スプレッドシート内で完結させていたので、スプシにアクセスしないとデータを見ることができませんでした。

今回の改修のついでに Slack 通知を実装したので、大枠は Slack で確認することができるようになりました。毎月通知が来るので、しばらく記事書けてないときのいいケツ叩きになってくれそうですw

▲ Slackに通知されるメッセージ。意外と「読まれた記事」がお互い良い刺激になりそう。

改修後のソースコード全文

ということで、改修後のGASは以下のようになりました。


/***** 設定 ********************************************/
const CONFIG = {
  GA4_PROPERTY_ID: '',                   // GA4 Property ID
  CURRENCY_FORMAT: '¥#,##0',
  PAGE_LIST_SHEET_NAME: 'ページ一覧',
  TEMPLATE_SHEET_NAME: 'template',       // テンプレートシートの名前
  DETAIL_START_ROW: 12,                  // 月間PV明細のデータ開始行
  ADSENSE_ACCOUNT_NAME: 'accounts/pub-', // AdSense アカウント
  SITE_DOMAIN: 'kalsari.net',
  OVERWRITE_PAGE_LIST_TITLES: false,
  SLACK_WEBHOOK_URL: 'https://hooks.slack.com/services/YOUR/WEBHOOK/URL' // Slack Webhook URL
};
/******************************************************/

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('月次集計')
    .addItem('先月分の明細を作成', 'runMonthly')
    .addItem('毎月10日に自動実行を設定', 'createMonthlyTrigger')
    .addToUi();
}

/** メイン：先月分のシートを作成 */
function runMonthly() {
  const ss = SpreadsheetApp.getActive();
  const {start, end, ym} = getPrevMonthRange_();

  // 1) 前月 AdSense 売上（合計）
  const earnings = fetchAdsenseEarnings_(start, end, CONFIG.SITE_DOMAIN); 

  // 2) 前月 GA4 取得＆記事URLフィルタ＆集計
  const aggregated = fetchAndAggregateGa4_(start, end);

  if (aggregated.length === 0) {
    SpreadsheetApp.getUi().alert('対象となる記事URLのデータが見つかりませんでした。');
    return;
  }

  // 3) ページ一覧シートを更新
  upsertPageList_(aggregated);

  // 4) 明細シート生成（templateを複製して作成）
  const newSheet = buildMonthlySheet_(ss, ym, earnings, aggregated);

  // 5) Slack へレポート送信
  if (CONFIG.SLACK_WEBHOOK_URL && !CONFIG.SLACK_WEBHOOK_URL.includes('YOUR/WEBHOOK/URL')) {
    sendSlackReport_(ss, newSheet, ym);
  }
}

/** 毎月10日 9:00 に実行するトリガーを作成 */
function createMonthlyTrigger() {
  const found = ScriptApp.getProjectTriggers().some(t => t.getHandlerFunction() === 'runMonthly');
  if (!found) {
    ScriptApp.newTrigger('runMonthly').timeBased().onMonthDay(10).atHour(9).create();
  }
  SpreadsheetApp.getUi().alert('毎月10日9:00に実行するトリガーを設定しました。');
}

/* ========= AdSense（前月売上合計） ================== */
function fetchAdsenseEarnings_(start, end, domain) {
  const account = getAdsenseAccountName_();
  const s = toParts_(start);
  const e = toParts_(end);

  const tryOnce = (dimensionName) => {
    const params = {
      dateRange: 'CUSTOM',
      'startDate.year':  s.y,
      'startDate.month': s.m,
      'startDate.day':   s.d,
      'endDate.year':    e.y,
      'endDate.month':   e.m,
      'endDate.day':     e.d,
      metrics: ['ESTIMATED_EARNINGS'],
      dimensions: [dimensionName],
      filters: [`${dimensionName}==${domain}`]
    };
    const res = AdSense.Accounts.Reports.generate(account, params);
    if (res && res.footer && res.footer.totals && res.footer.totals.length) {
      return parseFloat(res.footer.totals[0].value || 0) || 0;
    }
    if (res && res.rows && res.rows.length && res.rows[0].cells && res.rows[0].cells.length) {
      return parseFloat(res.rows[0].cells[0].value || 0) || 0;
    }
    return 0;
  };

  try {
    let val = tryOnce('OWNED_SITE_DOMAIN_NAME');
    if (val !== 0) return val;
    return tryOnce('DOMAIN_NAME');
  } catch (err) {
    const fallbackParams = {
      dateRange: 'CUSTOM',
      'startDate.year':  s.y,
      'startDate.month': s.m,
      'startDate.day':   s.d,
      'endDate.year':    e.y,
      'endDate.month':   e.m,
      'endDate.day':     e.d,
      metrics: ['ESTIMATED_EARNINGS'],
      dimensions: ['OWNED_SITE_DOMAIN_NAME']
    };
    const res = AdSense.Accounts.Reports.generate(account, fallbackParams);
    const rows = (res && res.rows) ? res.rows : [];
    for (const r of rows) {
      const cells = r.cells || [];
      const dimVal = cells[0] && cells[0].value;
      const metricVal = cells[1] && cells[1].value;
      if (dimVal === domain) return parseFloat(metricVal || 0) || 0;
    }
    return 0; 
  }
}

function toParts_(ymd) {
  const [y, m, d] = ymd.split('-').map(n => parseInt(n, 10));
  return { y, m, d };
}

function getAdsenseAccountName_() {
  if (CONFIG.ADSENSE_ACCOUNT_NAME) return CONFIG.ADSENSE_ACCOUNT_NAME;
  const resp = AdSense.Accounts.list({pageSize: 50});
  if (!resp.accounts || resp.accounts.length === 0) {
    throw new Error('AdSenseアカウントが見つかりません。権限とAPI有効化を確認してください。');
  }
  return resp.accounts[0].name;
}

/* ========= GA4取得 ＆ URL判定 ＆ 集計 ==================== */
function fetchAndAggregateGa4_(start, end) {
  const propertyName = `properties/${CONFIG.GA4_PROPERTY_ID}`;
  const req = {
    dateRanges: [{ startDate: start, endDate: end }],
    dimensions: [{ name: 'pageLocation' }, { name: 'pageTitle' }],
    metrics: [{ name: 'screenPageViews' }],
    limit: 100000
  };

  let rows = [];
  try {
    const res = AnalyticsData.Properties.runReport(req, propertyName);
    rows = res.rows || [];
  } catch (e) {
    throw new Error('GA4のデータ取得に失敗しました。\n' + e);
  }

  const map = new Map();

  for (let i = 0; i < rows.length; i++) {
    const rawUrl = rows[i].dimensionValues[0].value || '';
    const title = rows[i].dimensionValues[1].value || '';
    const views = parseInt(rows[i].metricValues[0].value || '0', 10);

    if (!rawUrl) continue;

    // 1. 正規表現による記事URL判定
    const match = rawUrl.match(/\/([^\/]+)\/(\d+)(?:\/)?(?:[?#].*)?$/);
    if (!match) continue;

    const category = match[1];
    if (category.toLowerCase() === 'page') continue;

    // 2. URL正規化
    const canonical = rawUrl
      .replace(/^https?:\/\//i, 'https://')
      .replace(/(https:\/\/)(?:www\.)?/i, '$1')
      .replace(/[?#].*$/, '')
      .replace(/\/+$/, '');

    // 3. マップに集計
    const prev = map.get(canonical);
    if (prev) {
      prev.views += views;
      if (views > prev.maxViews) {
        prev.maxViews = views;
        if (title) prev.title = title;
      }
    } else {
      map.set(canonical, { title: title, views: views, maxViews: views });
    }
  }

  const out = Array.from(map.entries()).map(([u, o]) => [u, o.title, o.views]);
  out.sort((a, b) => b[2] - a[2]);
  return out;
}

/* ========= ページ一覧シートの更新 ==================== */
function upsertPageList_(aggregated) {
  const ss = SpreadsheetApp.getActive();
  const name = CONFIG.PAGE_LIST_SHEET_NAME;
  let sh = ss.getSheetByName(name);
  if (!sh) {
    sh = ss.insertSheet(name);
    sh.getRange(1,1).setValue('★ページ一覧');
    sh.getRange(3,1,1,3).setValues([['ページ','タイトル','執筆者']]).setFontWeight('bold');
  }

  const last = sh.getLastRow();
  let existingVals = [];
  const existingMap = new Map();

  if (last >= 4) {
    existingVals = sh.getRange(4, 1, last - 3, 3).getValues();
    for (let i = 0; i < existingVals.length; i++) {
      let url = existingVals[i][0];
      if (!url) continue;

      let cu = url.replace(/^https?:\/\//i, 'https://')
                  .replace(/(https:\/\/)(?:www\.)?/i, '$1')
                  .replace(/[?#].*$/, '')
                  .replace(/\/+$/, '');
      
      existingVals[i][0] = cu;
      existingMap.set(cu, { index: i, title: existingVals[i][1] || '', author: existingVals[i][2] || '' });
    }
  }

  const adds = [];
  let hasMissingAuthor = false;

  for (let i = 0; i < aggregated.length; i++) {
    const [cu, title] = aggregated[i];
    const e = existingMap.get(cu);
    
    if (!e) {
      adds.push([cu, title || '', '']);
      existingMap.set(cu, { index: -1, title: title || '', author: '' });
      hasMissingAuthor = true;
    } else {
      const shouldOverwrite = CONFIG.OVERWRITE_PAGE_LIST_TITLES 
        ? (title && title !== e.title) 
        : (!e.title && title);
        
      if (shouldOverwrite) {
        existingVals[e.index][1] = title;
        e.title = title;
      }

      if (!e.author) {
        hasMissingAuthor = true;
      }
    }
  }

  if (hasMissingAuthor) {
    const authorMap = fetchWpAuthorsSingleCall_();

    existingMap.forEach((item, url) => {
      if (!item.author) {
        const fetchedAuthor = authorMap[url];
        if (fetchedAuthor) {
          item.author = fetchedAuthor;
          if (item.index >= 0) {
            existingVals[item.index][2] = fetchedAuthor;
          } else {
            const addRow = adds.find(r => r[0] === url);
            if (addRow) addRow[2] = fetchedAuthor;
          }
        }
      }
    });
  }

  if (last >= 4) {
    sh.getRange(4, 1, existingVals.length, 3).setValues(existingVals);
  }
  
  if (adds.length > 0) {
    const nextRow = last >= 4 ? last + 1 : 4;
    sh.getRange(nextRow, 1, adds.length, 3).setValues(adds);
  }

  sh.setFrozenRows(3);
}

/** WordPress REST API から執筆者名を取得 */
function fetchWpAuthorsSingleCall_() {
  const resultMap = {};
  const apiUrl = `https://${CONFIG.SITE_DOMAIN}/wp-json/wp/v2/posts?per_page=100&_embed=author`;

  try {
    const response = UrlFetchApp.fetch(apiUrl, {
      muteHttpExceptions: true,
      headers: { 'User-Agent': 'Mozilla/5.0' }
    });

    if (response.getResponseCode() === 200) {
      const posts = JSON.parse(response.getContentText());
      for (let i = 0; i < posts.length; i++) {
        const post = posts[i];
        const rawUrl = post.link || '';
        
        // 著者名の取得
        let authorName = '';
        if (post._embedded && post._embedded.author && post._embedded.author.length > 0) {
          authorName = post._embedded.author[0].name || post._embedded.author[0].slug || '';
        }

        if (rawUrl && authorName) {
          // URL正規化
          const cu = rawUrl
            .replace(/^https?:\/\//i, 'https://')
            .replace(/(https:\/\/)(?:www\.)?/i, '$1')
            .replace(/[?#].*$/, '')
            .replace(/\/+$/, '');

          resultMap[cu] = authorName;
        }
      }
    }
  } catch (e) {
    console.error('WordPress REST API 取得エラー: ' + e);
  }

  return resultMap;
}

/* ========= 明細シートの作成（templateコピー ＆ 2シート目に移動） ========================= */
function buildMonthlySheet_(ss, ym, earnings, details) {
  const ex = ss.getSheetByName(ym);
  if (ex) ss.deleteSheet(ex);

  const templateSh = ss.getSheetByName(CONFIG.TEMPLATE_SHEET_NAME);
  if (!templateSh) {
    throw new Error(`テンプレートシート「${CONFIG.TEMPLATE_SHEET_NAME}」が見つかりません。事前に作成してください。`);
  }

  const sh = templateSh.copyTo(ss);
  sh.setName(ym);

  ss.setActiveSheet(sh);
  ss.moveActiveSheet(2);

  const startRow = CONFIG.DETAIL_START_ROW || 12;

  sh.getRange('B4').setValue(earnings);

  if (details.length) {
    sh.getRange(startRow, 1, details.length, 3).setValues(details);

    const formulas = [];
    for (let i = 0; i < details.length; i++) {
      const currentRow = startRow + i;
      formulas.push([
        `=IF(A${currentRow}="","",IFERROR(VLOOKUP(A${currentRow},'${CONFIG.PAGE_LIST_SHEET_NAME}'!A:C,3,FALSE),""))`
      ]);
    }
    sh.getRange(startRow, 4, details.length, 1).setFormulas(formulas);
  }
  
  SpreadsheetApp.flush();

  return sh;
}

/* ========= Slack レポート送信 ========================= */
function sendSlackReport_(ss, sh, ym) {
  const [year, month] = ym.split('-');

  SpreadsheetApp.flush();

  const displayVals = sh.getRange('A1:G14').getDisplayValues();

  const totalEarnings = displayVals[3][1] || '0';
  const tatsuEarnings = displayVals[3][6] || '0';
  const daiEarnings   = displayVals[4][6] || '0';

  const rankingIcons = [':crown:', ':two:', ':three:'];
  let articleLines = [];

  for (let i = 0; i < 3; i++) {
    const rowIdx = 11 + i;
    const url = displayVals[rowIdx][0];
    const title = displayVals[rowIdx][1];
    const pv = displayVals[rowIdx][2];
    const author = displayVals[rowIdx][3];

    if (url) {
      const icon = rankingIcons[i];
      const authorText = author ? author : '未設定';
      articleLines.push(`${icon} <${url}|${title}> by *${authorText}* (PV *${pv}*)`);
    }
  }

  const spreadsheetUrl = ss.getUrl();

  const message = `kalsarikannint の ${year} 年 ${month} 月 のPV・収益レポートをお届けします。
━━━━━━━━━━
 *読まれた記事* :rocket: 
━━━━━━━━━━
${articleLines.join('\n')}

━━━━━━
 *収益* :tada:
━━━━━━
見込み収益: *${totalEarnings}* 円

このレポートの詳細は <${spreadsheetUrl}|スプレッドシート> で確認できます。`;

  const payload = JSON.stringify({ text: message });
  const options = {
    method: 'post',
    contentType: 'application/json',
    payload: payload
  };

  UrlFetchApp.fetch(CONFIG.SLACK_WEBHOOK_URL, options);
}

/* ========= 日付ユーティリティ ======================= */
function getPrevMonthRange_() {
  const today = new Date();
  const firstThis = new Date(today.getFullYear(), today.getMonth(), 1);
  const lastPrev = new Date(firstThis - 1);
  const firstPrev = new Date(lastPrev.getFullYear(), lastPrev.getMonth(), 1);
  const pad = n => ('0' + n).slice(-2);
  const start = `${firstPrev.getFullYear()}-${pad(firstPrev.getMonth()+1)}-01`;
  const end = `${lastPrev.getFullYear()}-${pad(lastPrev.getMonth()+1)}-${pad(lastPrev.getDate())}`;
  const ym = `${firstPrev.getFullYear()}-${pad(firstPrev.getMonth()+1)}`;
  return { start, end, ym };
}

仕組みはできた。あとは書くだけだ….