如何通过API提取Looker Studio数据？解决API访问及数据血缘追踪问题

一、核心差异说明 #

Looker Studio API的认证逻辑和BigQuery完全不同：

• 常规BigQuery服务账号认证方式不适用，因为Looker Studio资源默认绑定用户权限，服务账号无直接访问权限。
• 必须采用OAuth 2.0授权码流程；如果是Google Workspace域管理员，可给服务账号配置域范围委派，模拟域内用户访问资源。

二、Python脚本实现方案 #

1. 前置准备 #

• 到Google Cloud控制台创建桌面应用类型的OAuth客户端ID，下载credentials.json。
• 安装依赖：

  pip install google-auth google-auth-oauthlib google-auth-httplib2 google-api-python-client
  

2. 代码示例 #

from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build

# Looker Studio只读权限范围
SCOPES = ['https://www.googleapis.com/auth/datastudio.readonly']

def get_looker_studio_service():
    creds = None
    # 优先读取已授权的token文件
    try:
        creds = Credentials.from_authorized_user_file('token.json', SCOPES)
    except FileNotFoundError:
        # 首次授权生成token
        flow = InstalledAppFlow.from_client_secrets_file('credentials.json', SCOPES)
        creds = flow.run_local_server(port=0)
        with open('token.json', 'w') as f:
            f.write(creds.to_json())
    return build('datastudio', 'v1', credentials=creds)

def extract_bigquery_sources(dashboard_id):
    service = get_looker_studio_service()
    report = service.reports().get(reportId=dashboard_id).execute()
    data_sources = report.get('dataSources', [])
    bq_sources = []
    
    for ds in data_sources:
        ds_ref = ds.get('dataSourceReference')
        if ds_ref and ds_ref.get('type') == 'BIG_QUERY':
            bq_details = ds_ref.get('bigQueryDataSource')
            bq_sources.append({
                'project': bq_details.get('projectId'),
                'dataset': bq_details.get('datasetId'),
                'table': bq_details.get('tableId')
            })
    return bq_sources

# 使用示例，替换为你的仪表盘ID
dashboard_id = '你的仪表盘ID'
print("关联的BigQuery表：", extract_bigquery_sources(dashboard_id))

三、App Script实现方案 #

1. 前置配置 #

• 创建新的Google Apps Script项目，编辑appsscript.json添加权限：

  {
    "oauthScopes": ["https://www.googleapis.com/auth/datastudio.readonly"],
    "runtimeVersion": "V8"
  }
  

2. 代码示例 #

function getDashboardBQSources(dashboardId) {
    const apiUrl = `https://datastudio.googleapis.com/v1/reports/${dashboardId}`;
    const options = {
        method: 'get',
        headers: { Authorization: `Bearer ${ScriptApp.getOAuthToken()}` },
        muteHttpExceptions: true
    };
    const response = UrlFetchApp.fetch(apiUrl, options);
    const reportData = JSON.parse(response.getContentText());
    const bqSources = [];
    
    (reportData.dataSources || []).forEach(ds => {
        const dsRef = ds.dataSourceReference;
        if (dsRef && dsRef.type === 'BIG_QUERY') {
            const bqDs = dsRef.bigQueryDataSource;
            bqSources.push({
                projectId: bqDs.projectId,
                datasetId: bqDs.datasetId,
                tableId: bqDs.tableId
            });
        }
    });
    return bqSources;
}

// 测试调用
function test() {
    const dashboardId = '你的仪表盘ID';
    console.log(getDashboardBQSources(dashboardId));
}

四、常见问题排查 #

• 权限不足：确保授权账号拥有目标仪表盘的查看权限；使用服务账号时，需在Workspace Admin控制台配置域范围委派，并添加对应权限范围。
• API未启用：到Google Cloud控制台搜索并启用「Looker Studio API」。
• 服务账号模拟访问：如果用服务账号，需指定要模拟的域内用户邮箱，示例代码：

  from google.oauth2 import service_account
  
  SCOPES = ['https://www.googleapis.com/auth/datastudio.readonly']
  creds = service_account.Credentials.from_service_account_file(
      'service-account-key.json',
      scopes=SCOPES,
      subject='domain-user@your-domain.com'  # 替换为域内用户邮箱
  )
  

内容的提问来源于stack exchange，提问作者C G O