GA4 API×GASで実現する高度なレポート自動化｜LP×チャネル分析の完全スクリプト公開

const propertyId = 'YOUR_GA4_PROPERTY_ID'; // ← 数字のみ（例: 265085688）
const sheetId = 'YOUR_SPREADSHEET_ID'; // ← スプレッドシートID

  const serviceAccount = {
    "type": "service_account",
    "project_id": "your-project-id",
    "private_key_id": "xxxxxxxxxxxxxxxxxxxx",
    "private_key": "-----BEGIN PRIVATE KEY-----\\nYOUR_KEY\\n-----END PRIVATE KEY-----\\n",
    "client_email": "your-service-account@your-project.iam.gserviceaccount.com",
    "client_id": "123456789012345678901",
    "auth_uri": "https://accounts.google.com/o/oauth2/auth",
    "token_uri": "https://oauth2.googleapis.com/token",
    "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
    "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/your-service-account%40your-project.iam.gserviceaccount.com"
  };

function runGa4ReportWithServiceAccount() {
  const propertyId = 'YOUR_GA4_PROPERTY_ID'; // ← 数字のみ（例: 265085688）
  const sheetId = 'YOUR_SPREADSHEET_ID';     // ← スプレッドシートID
  const sheetName = 'GA4データ出力';          // ← 任意のシート名（存在しなければ自動作成）

  const serviceAccount = {
    "type": "service_account",
    "project_id": "your-project-id",
    "private_key_id": "xxxxxxxxxxxxxxxxxxxx",
    "private_key": "-----BEGIN PRIVATE KEY-----\\nYOUR_KEY\\n-----END PRIVATE KEY-----\\n",
    "client_email": "your-service-account@your-project.iam.gserviceaccount.com",
    "client_id": "123456789012345678901",
    "auth_uri": "https://accounts.google.com/o/oauth2/auth",
    "token_uri": "https://oauth2.googleapis.com/token",
    "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
    "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/your-service-account%40your-project.iam.gserviceaccount.com"
  };

  const now = new Date();
  const lastMonth = new Date(now.getFullYear(), now.getMonth() - 1, 1);
  const endOfLastMonth = new Date(now.getFullYear(), now.getMonth(), 0);
  const startDate = Utilities.formatDate(lastMonth, Session.getScriptTimeZone(), 'yyyy-MM-dd');
  const endDate = Utilities.formatDate(endOfLastMonth, Session.getScriptTimeZone(), 'yyyy-MM-dd');

  const jwt = createJwt(
    { alg: 'RS256', typ: 'JWT' },
    {
      iss: serviceAccount.client_email,
      scope: 'https://www.googleapis.com/auth/analytics.readonly',
      aud: serviceAccount.token_uri,
      exp: Math.floor(Date.now() / 1000) + 3600,
      iat: Math.floor(Date.now() / 1000)
    },
    serviceAccount.private_key
  );

  const tokenResponse = UrlFetchApp.fetch(serviceAccount.token_uri, {
    method: 'post',
    contentType: 'application/x-www-form-urlencoded',
    payload: {
      grant_type: 'urn:ietf:params:oauth:grant-type:jwt-bearer',
      assertion: jwt
    }
  });

  const accessToken = JSON.parse(tokenResponse.getContentText()).access_token;

  const url = `https://analyticsdata.googleapis.com/v1beta/properties/${propertyId}:runReport`;

  const payload = {
    dateRanges: [{ startDate, endDate }],
    dimensions: [
      { name: 'landingPage' },
      { name: 'sessionDefaultChannelGroup' }
    ],
    metrics: [
      { name: 'sessions' },
      { name: 'engagementRate' }
    ],
    dimensionFilter: {
      andGroup: {
        expressions: [
          {
            filter: {
              fieldName: 'sessionDefaultChannelGroup',
              stringFilter: {
                value: 'Organic Search',
                matchType: 'EXACT'
              }
            }
          },
          {
            filter: {
              fieldName: 'landingPage',
              stringFilter: {
                value: '/movie/',
                matchType: 'CONTAINS'
              }
            }
          }
        ]
      }
    }
  };

  const response = UrlFetchApp.fetch(url, {
    method: 'post',
    contentType: 'application/json',
    headers: {
      Authorization: 'Bearer ' + accessToken
    },
    payload: JSON.stringify(payload)
  });

  const json = JSON.parse(response.getContentText());

  const spreadsheet = SpreadsheetApp.openById(sheetId);
  let sheet = spreadsheet.getSheetByName(sheetName);
  if (!sheet) {
    sheet = spreadsheet.insertSheet(sheetName);
  } else {
    sheet.clearContents();
  }

  const headers = json.dimensionHeaders.map(h => h.name)
    .concat(json.metricHeaders.map(h => h.name));
  sheet.appendRow(headers);

  if (json.rows && json.rows.length > 0) {
    json.rows.forEach(row => {
      const dims = row.dimensionValues.map(d => d.value);
      const metrics = row.metricValues.map((m, i) => {
        return i === 1  // engagementRate
          ? (parseFloat(m.value) * 100).toFixed(2) + '%'
          : m.value;
      });
      sheet.appendRow(dims.concat(metrics));
    });
  } else {
    sheet.appendRow(['該当データなし']);
  }

  Logger.log('GA4データ出力完了');
}

function createJwt(header, claimSet, privateKey) {
  const encode = (obj) =>
    Utilities.base64EncodeWebSafe(JSON.stringify(obj)).replace(/=+$/, '');
  const encodedHeader = encode(header);
  const encodedClaim = encode(claimSet);
  const signatureInput = `${encodedHeader}.${encodedClaim}`;
  const signature = Utilities.base64EncodeWebSafe(
    Utilities.computeRsaSha256Signature(signatureInput, privateKey)
  ).replace(/=+$/, '');
  return `${signatureInput}.${signature}`;
}