How to Integrate Bolt.new with Google Data Studio (Looker Studio)

1// components/LookerStudioEmbed.tsx
2'use client';
3import { useState } from 'react';
4
5interface LookerStudioEmbedProps {
6  reportUrl: string;
7  title?: string;
8  height?: number;
9  className?: string;
10}
11
12export function LookerStudioEmbed({
13  reportUrl,
14  title = 'Analytics Report',
15  height = 600,
16  className = '',
17}: LookerStudioEmbedProps) {
18  const [loading, setLoading] = useState(true);
19  const [error, setError] = useState(false);
20
21  // Ensure the URL uses the embed format
22  const embedUrl = reportUrl.includes('/embed/')
23    ? reportUrl
24    : reportUrl.replace('/reporting/', '/embed/reporting/');
25
26  const handleLoad = () => setLoading(false);
27  const handleError = () => {
28    setLoading(false);
29    setError(true);
30  };
31
32  return (
33    <div className={`relative w-full rounded-lg overflow-hidden border border-gray-200 ${className}`}>
34      {/* Header */}
35      <div className="flex items-center justify-between px-4 py-3 bg-white border-b border-gray-200">
36        <h3 className="text-sm font-medium text-gray-700">{title}</h3>
37        <a
38          href={reportUrl}
39          target="_blank"
40          rel="noreferrer"
41          className="text-xs text-blue-600 hover:text-blue-800 flex items-center gap-1"
42        >
43          Open in Looker Studio ↗
44        </a>
45      </div>
46
47      {/* Loading skeleton */}
48      {loading && !error && (
49        <div
50          className="absolute inset-0 top-10 bg-gray-50 flex items-center justify-center"
51          style={{ height }}
52        >
53          <div className="flex flex-col items-center gap-3">
54            <div className="w-8 h-8 border-2 border-blue-600 border-t-transparent rounded-full animate-spin" />
55            <span className="text-sm text-gray-500">Loading report...</span>
56          </div>
57        </div>
58      )}
59
60      {/* Error state */}
61      {error && (
62        <div
63          className="flex flex-col items-center justify-center bg-gray-50 gap-3"
64          style={{ height }}
65        >
66          <p className="text-sm text-gray-600">Report could not load.</p>
67          <p className="text-xs text-gray-500">Ensure the report is shared as &quot;Anyone with the link can view&quot;</p>
68          <a
69            href={reportUrl}
70            target="_blank"
71            rel="noreferrer"
72            className="text-sm text-blue-600 hover:underline"
73          >
74            Open report directly
75          </a>
76        </div>
77      )}
78
79      {/* Iframe */}
80      {!error && (
81        <iframe
82          src={embedUrl}
83          style={{ height, display: loading ? 'none' : 'block' }}
84          className="w-full"
85          title={title}
86          allowFullScreen
87          onLoad={handleLoad}
88          onError={handleError}
89          sandbox="allow-same-origin allow-scripts allow-popups allow-forms"
90        />
91      )}
92    </div>
93  );
94}

1// lib/ga4.ts
2import crypto from 'crypto';
3
4const GA4_API_BASE = 'https://analyticsdata.googleapis.com/v1beta';
5
6let tokenCache: { token: string; expiresAt: number } | null = null;
7
8async function getServiceAccountToken(): Promise<string> {
9  if (tokenCache && Date.now() < tokenCache.expiresAt - 60_000) {
10    return tokenCache.token;
11  }
12
13  const keyRaw = process.env.GOOGLE_SERVICE_ACCOUNT_KEY;
14  if (!keyRaw) throw new Error('GOOGLE_SERVICE_ACCOUNT_KEY is not configured');
15
16  const serviceAccount = JSON.parse(keyRaw) as {
17    client_email: string;
18    private_key: string;
19    token_uri: string;
20  };
21
22  const now = Math.floor(Date.now() / 1000);
23  const header = Buffer.from(JSON.stringify({ alg: 'RS256', typ: 'JWT' })).toString('base64url');
24  const payload = Buffer.from(JSON.stringify({
25    iss: serviceAccount.client_email,
26    scope: 'https://www.googleapis.com/auth/analytics.readonly',
27    aud: serviceAccount.token_uri || 'https://oauth2.googleapis.com/token',
28    exp: now + 3600,
29    iat: now,
30  })).toString('base64url');
31
32  const signingInput = `${header}.${payload}`;
33  const sign = crypto.createSign('RSA-SHA256');
34  sign.update(signingInput);
35  const signature = sign.sign(
36    serviceAccount.private_key.replace(/\\n/g, '\n'),
37    'base64url'
38  );
39  const jwt = `${signingInput}.${signature}`;
40
41  const tokenResponse = await fetch('https://oauth2.googleapis.com/token', {
42    method: 'POST',
43    headers: { 'Content-Type': 'application/x-www-form-urlencoded' },
44    body: new URLSearchParams({
45      grant_type: 'urn:ietf:params:oauth:grant-type:jwt-bearer',
46      assertion: jwt,
47    }),
48  });
49
50  const tokens = await tokenResponse.json() as { access_token?: string; error?: string };
51  if (!tokens.access_token) {
52    throw new Error(`GA4 auth failed: ${tokens.error || 'unknown error'}`);
53  }
54
55  tokenCache = { token: tokens.access_token, expiresAt: Date.now() + 3600 * 1000 };
56  return tokens.access_token;
57}
58
59export async function ga4Query<T = unknown>(body: Record<string, unknown>): Promise<T> {
60  const accessToken = await getServiceAccountToken();
61  const propertyId = process.env.GA4_PROPERTY_ID;
62  if (!propertyId) throw new Error('GA4_PROPERTY_ID is not configured');
63
64  const response = await fetch(
65    `${GA4_API_BASE}/properties/${propertyId}:runReport`,
66    {
67      method: 'POST',
68      headers: {
69        Authorization: `Bearer ${accessToken}`,
70        'Content-Type': 'application/json',
71      },
72      body: JSON.stringify(body),
73    }
74  );
75
76  if (!response.ok) {
77    const error = await response.json().catch(() => ({})) as { error?: { message: string } };
78    throw new Error(error.error?.message || `GA4 API error ${response.status}`);
79  }
80
81  return response.json() as Promise<T>;
82}

1// app/api/analytics/overview/route.ts
2import { NextResponse } from 'next/server';
3import { ga4Query } from '@/lib/ga4';
4
5interface GA4Row {
6  dimensionValues: Array<{ value: string }>;
7  metricValues: Array<{ value: string }>;
8}
9
10interface GA4ReportResponse {
11  rows?: GA4Row[];
12  rowCount?: number;
13  dimensionHeaders?: Array<{ name: string }>;
14  metricHeaders?: Array<{ name: string; type: string }>;
15}
16
17function parseGA4Rows(
18  report: GA4ReportResponse
19): Array<Record<string, string>> {
20  if (!report.rows) return [];
21  const dimHeaders = (report.dimensionHeaders || []).map((h) => h.name);
22  const metHeaders = (report.metricHeaders || []).map((h) => h.name);
23
24  return report.rows.map((row) => {
25    const obj: Record<string, string> = {};
26    row.dimensionValues.forEach((d, i) => { obj[dimHeaders[i]] = d.value; });
27    row.metricValues.forEach((m, i) => { obj[metHeaders[i]] = m.value; });
28    return obj;
29  });
30}
31
32export async function GET() {
33  try {
34    const [summaryReport, trendsReport, pagesReport] = await Promise.all([
35      // Summary metrics
36      ga4Query<GA4ReportResponse>({
37        dateRanges: [{ startDate: '30daysAgo', endDate: 'today' }],
38        metrics: [
39          { name: 'sessions' },
40          { name: 'screenPageViews' },
41          { name: 'totalUsers' },
42          { name: 'bounceRate' },
43          { name: 'averageSessionDuration' },
44        ],
45      }),
46      // Daily trend
47      ga4Query<GA4ReportResponse>({
48        dateRanges: [{ startDate: '30daysAgo', endDate: 'today' }],
49        dimensions: [{ name: 'date' }],
50        metrics: [{ name: 'sessions' }, { name: 'totalUsers' }],
51        orderBys: [{ dimension: { dimensionName: 'date' } }],
52      }),
53      // Top pages
54      ga4Query<GA4ReportResponse>({
55        dateRanges: [{ startDate: '30daysAgo', endDate: 'today' }],
56        dimensions: [{ name: 'pagePath' }],
57        metrics: [{ name: 'screenPageViews' }, { name: 'sessions' }],
58        orderBys: [{ metric: { metricName: 'screenPageViews' }, desc: true }],
59        limit: 10,
60      }),
61    ]);
62
63    const summaryRows = parseGA4Rows(summaryReport);
64    const summary = summaryRows[0] || {};
65
66    const trends = parseGA4Rows(trendsReport).map((row) => ({
67      date: row.date,
68      sessions: parseInt(row.sessions || '0', 10),
69      users: parseInt(row.totalUsers || '0', 10),
70    }));
71
72    const pages = parseGA4Rows(pagesReport).map((row) => ({
73      path: row.pagePath,
74      pageviews: parseInt(row.screenPageViews || '0', 10),
75      sessions: parseInt(row.sessions || '0', 10),
76    }));
77
78    return NextResponse.json({
79      summary: {
80        sessions: parseInt(summary.sessions || '0', 10),
81        pageviews: parseInt(summary.screenPageViews || '0', 10),
82        users: parseInt(summary.totalUsers || '0', 10),
83        bounceRate: parseFloat(summary.bounceRate || '0'),
84        avgDuration: parseFloat(summary.averageSessionDuration || '0'),
85      },
86      trends,
87      pages,
88    });
89  } catch (err) {
90    const message = err instanceof Error ? err.message : 'Analytics fetch failed';
91    return NextResponse.json({ error: message }, { status: 500 });
92  }
93}