GA4データ取得

コード.gs

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  // カスタムメニュー
  ui.createMenu('GA4取得')
    .addItem('ディメンション・メトリクス一覧を取得', 'getMetadataPrompt')
    .addItem('データを取得', 'fetchReports')
    .addToUi();
}

// プロパティIDを入力
function getMetadataPrompt() {
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt('プロパティIDを入力してください:');
  const propertyId = response.getResponseText();
  
  if (!propertyId) {
    ui.alert('プロパティIDが入力されていません。');
    return;
  }

  getMetadata(propertyId);
}

// メトリクス一覧の取得
const getMetadata = (propertyId) => {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  
  try {
    // APIへリクエストを送りレスポンスを受け取る
    const metadata = AnalyticsData.Properties.getMetadata(`properties/${propertyId}/metadata`);
    const { dimensions, metrics } = metadata;
    
    // ディメンション情報をシートに出力
    const dimensionsSheet = ss.getSheetByName(`dimensions_${propertyId}`) || ss.insertSheet(`dimensions_${propertyId}`);
    dimensionsSheet.clear();
    const dimensionValues = [['Category', 'API Name', 'UI Name', 'Description'], ...dimensions.map(dimension => [
      dimension.category, dimension.apiName, dimension.uiName, dimension.description
    ])];
    dimensionsSheet.getRange(1, 1, dimensionValues.length, dimensionValues[0].length).setValues(dimensionValues);
    
    // メトリクス情報をシートに出力
    const metricsSheet = ss.getSheetByName(`metrics_${propertyId}`) || ss.insertSheet(`metrics_${propertyId}`);
    metricsSheet.clear();
    const metricValues = [['Category', 'API Name', 'UI Name', 'Description'], ...metrics.map(metric => [
      metric.category, metric.apiName, metric.uiName, metric.description
    ])];
    metricsSheet.getRange(1, 1, metricValues.length, metricValues[0].length).setValues(metricValues);
    
  } catch (e) {
    Browser.msgBox("ERROR", e.message, Browser.Buttons.OK);
  }
}

const fetchReports = () => {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const configSheet = ss.getSheetByName('Configuration');
  const lastColumn = configSheet.getLastColumn();
  const configData = configSheet.getRange(1, 2, 10, lastColumn - 1).getValues();  // B1〜最終列までのデータを取得

  configData[0].forEach((_, col) => {
    const reportName = configData[0][col];
    const propertyId = configData[1][col];
    const startDate = configData[2][col];
    const endDate = configData[3][col];
    
    const metrics = configData[4][col] ? configData[4][col].split(',').map(metric => AnalyticsData.newMetric().setName(metric.trim())) : [];
    const dimensions = configData[5][col] ? configData[5][col].split(',').map(dimension => AnalyticsData.newDimension().setName(dimension.trim())) : [];

    const limit = configData[7][col] ? parseInt(configData[7][col], 10) : 1000;

    const report = fetchData(propertyId, metrics, dimensions, startDate, endDate, limit);
    if (report) {
      const sheet = ss.getSheetByName(reportName) || ss.insertSheet(reportName);
      outputToSheet(report, sheet);
    }
  });
}

const fetchData = (propertyId, metrics, dimensions, startDate, endDate, limit) => {
  try {
    const dateRange = AnalyticsData.newDateRange();
    dateRange.startDate = startDate;
    dateRange.endDate = endDate;

    const request = AnalyticsData.newRunReportRequest();
    request.dimensions = dimensions;
    request.metrics = metrics;
    request.dateRanges = [dateRange];
    request.limit = limit;

    return AnalyticsData.Properties.runReport(request, `properties/${propertyId}`);
  } catch (e) {
    console.error('Failed with error: %s', e.message);
    return null;
  }
}

const outputToSheet = (report, sheet) => {
  if (!report || !report.rows) {
    console.error('No rows returned.');
    return;
  }

  // シートの既存データをクリア
  sheet.clear();

  const headers = [...report.dimensionHeaders.map(header => header.name), ...report.metricHeaders.map(header => header.name)];
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  
  const rows = report.rows.map(row => [
    ...row.dimensionValues.map(dimensionValue => dimensionValue.value),
    ...row.metricValues.map(metricValue => metricValue.value)
  ]);
  
  sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);
}