Google Apps ScriptでPodcastエピソードのメタ情報を自動取得

Podcastの編集を業務委託する OR される方が増えてきてます。
そんな中、ギャラの計算方法も「1月あたりいくら」「1本あたりいくら」「1分あたりいくら」など多様なパターンがあるんじゃないかと。

ちなみに僕は、受注時も発注時も分数計算でやることが多いんですが、この計算がめちゃくちゃ面倒だったので、自動計算してくれるアプリを作りました。

こんな感じになります。

もしよかったら、記事の一番下から投げ銭できるんで、投げ銭ください。

では、ChatGPTくんによる説明をご覧ください。

こんにちは！
今回は、Google Apps Scriptを使ってPodcastのエピソード情報をRSSフィードから自動取得し、Googleスプレッドシートに自動的に記載する方法をご紹介します。

1. このスクリプトの特徴

・RSSフィードからデータ取得

RSSフィードに記載されたPodcastエピソードの情報（GUID、タイトル、公開日時、再生時間など）を取得します。

・期間指定によるフィルタリング

スクリプトプロパティに設定した開始日時（START_DATE）と終了日時（END_DATE）をもとに、対象期間内のエピソードだけをシートに反映します。

両方未設定の場合： 全エピソード取得
開始日時のみ設定： 指定日時以降のエピソード取得
終了日時のみ設定： 指定日時以前のエピソード取得

・柔軟な秒数丸め設定

エピソードの再生時間（RSSで取得した形式）を分単位に計算する際、丸め方をスクリプトプロパティの ROUNDING_RULE で指定可能です。
設定できる丸めルールは以下の3種類：

ルール1：30秒未満は切り捨て、30秒以上は切り上げ
例：1分29秒 → 1分、1分36秒 → 2分
ルール2：秒は常に切り捨て
例：1分50秒 → 1分
ルール3：秒が0でなければ切り上げ
例：1分00秒 → 1分、1分01秒 → 2分

スクリプトプロパティに設定がなければ、デフォルトはルール1が適用されます。

・重複防止＆視覚的な補助

既に取得済みのエピソードはGUIDで判定してスキップ。
また、偶数月のエピソード行には背景色を付け、視認性を向上させています。

2. スクリプトプロパティの設定

スクリプトエディタの「ファイル」→「プロパティ」→「スクリプトのプロパティ」で、以下のキーと値を設定してください。

RSS_URL：RSSフィードのURL
START_DATE（任意）：例 “20250101”（開始日時）
END_DATE（任意）：例 “20250131”（終了日時）
ROUNDING_RULE（任意）：1, 2, または 3（設定しなければデフォルトは 1）

3. スプレッドシートの準備

シート名は「EP LIST」 にしてください。
また、以下のような列構成がおすすめです。

A1セル：実行時の「更新日時」が記録される
行2（ヘッダー行）
- A列：GUID
- B列：タイトル
- C列：配信日（yyyy/MM/dd 形式）
- D列：再生時間（分）（丸めルールに基づいて計算）
- E列：元の再生時間（例：00:30:00）
行3以降：各エピソードのデータが追加されます

4. 完成したコード全文

function findLastRow(sheet) {
  const range = sheet.getRange('A2:E');
  const values = range.getValues();
  
  for (let row = 0; row < values.length; row++) {
    if (values[row].every(cell => cell === '')) {
      return row + 1; // 空行が見つかったら、その一つ前の行を最終行として返す
    }
  }
  
  return values.length + 1; // 全て埋まっている場合は最終行を返す
}

function calculateDuration(parts, roundingMode) {
  let baseMinutes, seconds;
  if (parts.length === 3) {
    baseMinutes = parts[0] * 60 + parts[1];
    seconds = parts[2];
  } else if (parts.length === 2) {
    baseMinutes = parts[0];
    seconds = parts[1];
  } else {
    return 0;
  }
  
  switch (roundingMode) {
    case 1: // 30秒未満は切り捨て、30秒以上は切り上げ
      return seconds >= 30 ? baseMinutes + 1 : baseMinutes;
    case 2: // 秒は常に切り捨て
      return baseMinutes;
    case 3: // 秒が0でなければ切り上げ
      return seconds > 0 ? baseMinutes + 1 : baseMinutes;
    default:
      return seconds >= 30 ? baseMinutes + 1 : baseMinutes;
  }
}

function fetchAndUpdatePodcastData() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("EP LIST");
  if (!sheet) {
    Logger.log('シート「EP LIST」が見つかりません');
    return;
  }
  
  // スクリプトプロパティから各プロパティを取得
  const scriptProperties = PropertiesService.getScriptProperties();
  const url = scriptProperties.getProperty("RSS_URL");
  if (!url) {
    Logger.log("RSS_URLがスクリプトプロパティに設定されていません。");
    return;
  }
  
  // 開始日時と終了日時（例："20250101"の形式）を取得
  const startDateStr = scriptProperties.getProperty("START_DATE");
  const endDateStr = scriptProperties.getProperty("END_DATE");
  let startDate = null;
  let endDate = null;
  
  if (startDateStr) {
    startDate = new Date(startDateStr.substr(0,4) + '-' + startDateStr.substr(4,2) + '-' + startDateStr.substr(6,2));
  }
  
  if (endDateStr) {
    endDate = new Date(endDateStr.substr(0,4) + '-' + endDateStr.substr(4,2) + '-' + endDateStr.substr(6,2));
  }
  
  // ROUNDING_RULE：1, 2, または 3（設定がなければデフォルトは1）
  const roundingRuleStr = scriptProperties.getProperty("ROUNDING_RULE");
  let roundingRule = 1;
  if (roundingRuleStr) {
    roundingRule = parseInt(roundingRuleStr, 10);
    if (isNaN(roundingRule) || roundingRule < 1 || roundingRule > 3) {
      roundingRule = 1;
    }
  }
  
  // A〜E列の最終行を見つける
  const lastRow = findLastRow(sheet);
  
  // スプレッドシートの既存のGUIDを取得（重複登録防止）
  let existingGUIDs = [];
  if (lastRow > 2) {
    existingGUIDs = sheet.getRange(3, 1, lastRow - 2).getValues().flat();
  }
  
  const response = UrlFetchApp.fetch(url);
  const xml = response.getContentText();
  const document = XmlService.parse(xml);
  const root = document.getRootElement();
  const ns = XmlService.getNamespace("itunes", "http://www.itunes.com/dtds/podcast-1.0.dtd");
  const items = root.getChild("channel").getChildren("item");
  
  let newData = [];
  
  items.forEach(item => {
    const guid = item.getChildText("guid");
    
    // 既存のGUIDがある場合はスキップ
    if (existingGUIDs.includes(guid)) return;
    
    const title = item.getChildText("title");
    const pubDateText = item.getChildText("pubDate");
    const pubDate = new Date(pubDateText);
    
    // 開始日時、終了日時によるフィルタリング
    if (startDate && pubDate < startDate) return;
    if (endDate && pubDate > endDate) return;
    
    const formattedDate = Utilities.formatDate(pubDate, Session.getScriptTimeZone(), "yyyy/MM/dd");
    
    const durationElement = item.getChild("duration", ns);
    let durationInMinutes = 0;
    let originalDuration = '';
    
    if (durationElement) {
      originalDuration = durationElement.getText();
      const parts = originalDuration.split(':').map(Number);
      durationInMinutes = calculateDuration(parts, roundingRule);
    }
    
    // 新しいデータを配列に追加
    newData.push([guid, title, formattedDate, durationInMinutes, originalDuration, pubDate]);
  });
  
  // 配信日時（pubDate）で昇順に並び替え
  newData.sort((a, b) => a[5] - b[5]);
  
  // 新しいデータが存在する場合のみシートに書き込む
  if (newData.length > 0) {
    const startRow = lastRow + 1;
    const range = sheet.getRange(startRow, 1, newData.length, 5);
    range.setValues(newData.map(row => [row[0], row[1], row[2], row[3], row[4]]));
    
    // 偶数月の行に背景色を付ける
    newData.forEach((row, index) => {
      const pubDate = new Date(row[5]);
      const month = pubDate.getMonth() + 1;
      if (month % 2 === 0) {
        const lastColumn = sheet.getLastColumn();
        sheet.getRange(startRow + index, 1, 1, lastColumn).setBackground('#A7C6ED');
      }
    });
  }
  
  // 最終更新日時をA1セルに記入
  const now = new Date();
  const formattedNow = Utilities.formatDate(now, Session.getScriptTimeZone(), "yyyy/MM/dd HH:mm:ss");
  sheet.getRange('A1').setValue('更新日時: ' + formattedNow);
}

5. おわりに

このスクリプトを利用することで、RSSフィードからPodcastのエピソードデータを自動で取得し、指定した期間・丸めルールに基づいてGoogleスプレッドシートに反映できます。

ぜひお試しください！ご不明な点や改善のアイディアがあれば、コメントでシェアしていただけると嬉しいです。

« 前の記事