【GAS】Yahoo広告の運用データをスプレッドシートに自動取得する方法（v19対応）

https://biz-oauth.yahoo.co.jp/oauth/v1/authorize?response_type=code
&client_id=CLIENT_ID
&redirect_uri=REDIRECT_URI
&scope=yahooads
&state=THIS_VALUE_SHOULD_BE_UNIQUE_PER_REQUEST

curl -X GET \
https://biz-oauth.yahoo.co.jp/oauth/v1/token?grant_type=authorization_code
&client_id=CLIENT_ID
&client_secret=CLIENT_SECRET
&redirect_uri=REDIRECT_URI
&code=AUTH_CODE

/**
 * Yahoo!広告 検索広告 API v19 対応版
 */
function main() {
  const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('db');
  if (!ss) throw new Error("シート 'db' が見つかりません。");

  // --- 設定情報 ---
  const clientId = "****";
  const clientSecret = "****";
  const refreshToken = "****";

  // MCCアカウントを利用する場合
  const mccAccountId = "****"; // MCCアカウントID（x-z-base-account-id用）
  const targetAccountId = "****"; // データを取得したい広告アカウントID

  // 1. アクセストークン取得 (POST)
  const accessToken = yahooAdLogin(clientId, clientSecret, refreshToken);

  // 2. レポート作成ジョブの追加 (v19)
  const reportName = "Search_Report_" + Utilities.formatDate(new Date(), "JST", "yyyyMMdd_HHmmss");
  const createReportResponse = yahooAdCreateSearchReport(accessToken, mccAccountId, targetAccountId, reportName);
  const reportJobId = createReportResponse.rval.values[0].reportDefinition.reportJobId;
  console.log(`Search Report Job Created: ${reportJobId}`);

  // 3. レポート作成完了までループで確認 (最大2分)
  let isCompleted = false;
  for (let i = 0; i < 12; i++) {
    isCompleted = yahooAdCheckSearchReportStatus(accessToken, mccAccountId, targetAccountId, reportJobId);
    if (isCompleted) break;
    console.log("レポート作成中... (10秒待機)");
    Utilities.sleep(10000);
  }

  if (!isCompleted) throw new Error("レポート作成がタイムアウトしました。");

  // 4. レポートの取得とパース
  const reportData = yahooAdDownloadSearchReport(accessToken, mccAccountId, targetAccountId, reportJobId);

  if (reportData && reportData.length > 0) {
    const lastRow = ss.getRange("B:B").getValues().filter(String).length;
    const targetRow = lastRow + 1;
    ss.getRange(targetRow, 1, reportData.length, reportData[0].length).setValues(reportData);
    console.log(`${reportData.length}件のデータを追記しました。`);
  } else {
    console.log("対象期間にデータがありませんでした。");
  }
}

/** 共通ヘッダー生成 */
function getSearchHeaders(accessToken, mccAccountId) {
  return {
    'Authorization': `Bearer ${accessToken}`,
    'x-z-base-account-id': mccAccountId,
    'Content-Type': 'application/json'
  };
}

/** 認証: アクセストークン取得 */
function yahooAdLogin(clientId, clientSecret, refreshToken) {
  const url = "https://biz-oauth.yahoo.co.jp/oauth/v1/token";
  const options = {
    method: 'post',
    payload: {
      grant_type: "refresh_token",
      client_id: clientId,
      client_secret: clientSecret,
      refresh_token: refreshToken,
    }
  };
  const response = UrlFetchApp.fetch(url, options);
  return JSON.parse(response.getContentText()).access_token;
}

/** 検索広告レポート作成 (v19) */
function yahooAdCreateSearchReport(accessToken, mccAccountId, targetAccountId, reportName) {
  const url = "https://ads-search.yahooapis.jp/api/v19/ReportDefinitionService/add";
  const payload = {
    accountId: targetAccountId,
    operand: [{
      reportName: reportName,
      reportType: "CAMPAIGN",
      reportDateRangeType: "YESTERDAY",
      reportDownloadFormat: "CSV",
      reportDownloadEncode: "UTF8",
      reportLanguage: "JA",
      reportCompressType: "NONE",
      reportIncludeDeleted: "TRUE",
      fields: [
        "CAMPAIGN_NAME", "DAY", "CLICKS", "CONVERSIONS", "COST",
        "IMPS", "CLICK_RATE", "AVG_CPC", "CONV_RATE", "CONV_VALUE"
      ]
    }]
  };
  const options = {
    method: 'post',
    headers: getSearchHeaders(accessToken, mccAccountId),
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  };
  const response = UrlFetchApp.fetch(url, options);
  const json = JSON.parse(response.getContentText());
  if (response.getResponseCode() !== 200) throw new Error("Create Error: " + response.getContentText());
  return json;
}

/** 検索広告レポートステータス確認 (v19) */
function yahooAdCheckSearchReportStatus(accessToken, mccAccountId, targetAccountId, reportJobId) {
  const url = "https://ads-search.yahooapis.jp/api/v19/ReportDefinitionService/get";
  const options = {
    method: 'post',
    headers: getSearchHeaders(accessToken, mccAccountId),
    payload: JSON.stringify({ accountId: targetAccountId, reportJobIds: [reportJobId] })
  };
  const response = UrlFetchApp.fetch(url, options);
  const status = JSON.parse(response.getContentText()).rval.values[0].reportDefinition.reportJobStatus;
  if (status === "COMPLETED") return true;
  if (status === "FAILED") throw new Error("レポート作成失敗");
  return false;
}

/** 検索広告レポートダウンロードと整形（最適化版） */
function yahooAdDownloadSearchReport(accessToken, mccAccountId, targetAccountId, reportJobId) {
  const url = "https://ads-search.yahooapis.jp/api/v19/ReportDefinitionService/download";
  const options = {
    method: 'post',
    headers: getSearchHeaders(accessToken, mccAccountId),
    payload: JSON.stringify({ accountId: targetAccountId, reportJobId: reportJobId })
  };
  const response = UrlFetchApp.fetch(url, options);
  const csvData = Utilities.parseCsv(response.getContentText());
  if (csvData.length <= 1) return [];
  csvData.shift();
  return csvData.filter(row =>
    row.length > 0 && row[0] !== "" && row[0] !== "Total" && row[0] !== "合計"
  ).map(row => [
    row[0], row[1],
    Number(row[2]) || 0, Number(row[3]) || 0, Number(row[4]) || 0,
    Number(row[5]) || 0, row[6],
    Number(row[7]) || 0, row[8],
    Number(row[9]) || 0
  ]);
}