Python自动化导出Amplitude报表至谷歌表格时JSON解析失败求助

错误信息 #

Error: Failed to parse JSON response: Expecting value: line 1 column 1 (char 0)
Failed to fetch data for [RAW] Amplitude Totals - Viewed (.*) Page
Error: Failed to parse JSON response: Expecting value: line 1 column 1 (char 0)
Failed to fetch data for [RAW] Amplitude Totals - Viewed Product Page
Error: Failed to parse JSON response: Expecting value: line 1 column 1 (char 0)
Failed to fetch data for [RAW] Amplitude Totals - Product Added
Error: Failed to parse JSON response: Expecting value: line 1 column 1 (char 0)
Failed to fetch data for [RAW] Amplitude Totals - Checkout Started
Error: Failed to parse JSON response: Expecting value: line 1 column 1 (char 0)
Failed to fetch data for [RAW] Amplitude Totals - Order Completed

用户提供的代码 #

import requests
import base64
import gspread
from google.oauth2 import service_account
import json


def fetch_amplitude_data(api_key, secret_key, start_time, end_time, report_url):
    url = report_url
    params = {
        'start': start_time,
        'end': end_time
    }
    credentials = f"{api_key}:{secret_key}"
    encoded_credentials = base64.b64encode(credentials.encode('utf-8')).decode('utf-8')
    headers = {
        'Authorization': f'Basic {encoded_credentials}'
    }

    response = requests.get(url + '?format=json', params=params, headers=headers)


    if response.status_code == 200:
        try:
            data = response.json()
            return data
        except json.decoder.JSONDecodeError as e:
            print(f"Error: Failed to parse JSON response: {e}")
            return None
    else:
        print(f"Error: Failed to fetch data from Amplitude. Status code: {response.status_code}")
        return None


api_key = ''
secret_key = ''
start_time = '2023-04-01T00:00:00Z'
end_time = '2023-05-16T23:59:59Z'

report_urls = [
    'https://analytics.amplitude.com/magbak/chart/new/coqje4ni',
    'https://analytics.amplitude.com/magbak/chart/new/uxszho05',
    'https://analytics.amplitude.com/magbak/chart/new/edf4hjtn',
    'https://analytics.amplitude.com/magbak/chart/new/vxed7m7e',
    'https://analytics.amplitude.com/magbak/chart/new/mpe5vnf0'
]

tab_names = [
    '[RAW] Amplitude Totals - Viewed (.*) Page',
    '[RAW] Amplitude Totals - Viewed Product Page',
    '[RAW] Amplitude Totals - Product Added',
    '[RAW] Amplitude Totals - Checkout Started',
    '[RAW] Amplitude Totals - Order Completed'
]

scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
credentials = service_account.Credentials.from_service_account_file('C:/Users/Administrator/Downloads/second-caster-373808-fcf096bc469f.json.', scopes=scope)

client = gspread.authorize(credentials)

spreadsheet = client.open('[MagBak GP Pixel vs MagBak Native Pixel] Audit (April 18, 2023 - May 15, 2023) ')

for i, report_url in enumerate(report_urls):
    amplitude_data = fetch_amplitude_data(api_key, secret_key, start_time, end_time, report_url)
    if amplitude_data is not None:
        # Select the respective tab
        tab = spreadsheet.worksheet(tab_names[i])

        # Clear existing data in the tab
        tab.clear()

        # Update the tab with the fetched data
        tab.update([amplitude_data])
        print(f"Data updated in {tab_names[i]}")
    else:
        print(f"Failed to fetch data for {tab_names[i]}")

问题排查与解决方案 #

1. 核心错误：使用前端页面URL而非Amplitude API端点 #

你当前使用的report_urls是Amplitude网页端的图表页面地址，这类地址返回HTML页面而非JSON数据，直接导致解析失败。需改用Amplitude官方导出API：

• 单个图表导出的正确API端点格式：https://analytics.amplitude.com/api/3/chart/{chart_id}/export
• 其中{chart_id}是你原URL末尾的字符串（如coqje4ni）

2. 参数拼接方式错误 #

不要直接在URL后拼接?format=json，应将format参数加入params字典，避免URL格式混乱：

params = {
    'start': start_time,
    'end': end_time,
    'format': 'json'
}
# 调用时直接使用修正后的API端点，无需手动拼接参数
response = requests.get(url, params=params, headers=headers)

3. 添加调试代码查看真实响应 #

在JSON解析失败时，打印response.text可查看服务器返回的真实内容，帮助定位问题：

except json.decoder.JSONDecodeError as e:
    print(f"Error: Failed to parse JSON response: {e}")
    print(f"Response content preview: {response.text[:500]}")  # 打印前500字符
    return None

4. 服务账号文件路径错误 #

你的Google服务账号文件路径末尾多了一个.：'C:/Users/Administrator/Downloads/second-caster-373808-fcf096bc469f.json.'，需去掉该点，否则会导致文件找不到。

修正后的核心函数示例 #

def fetch_amplitude_data(api_key, secret_key, start_time, end_time, chart_id):
    # 使用正确的API端点
    url = f"https://analytics.amplitude.com/api/3/chart/{chart_id}/export"
    params = {
        'start': start_time,
        'end': end_time,
        'format': 'json'
    }
    credentials = f"{api_key}:{secret_key}"
    encoded_credentials = base64.b64encode(credentials.encode('utf-8')).decode('utf-8')
    headers = {
        'Authorization': f'Basic {encoded_credentials}'
    }

    response = requests.get(url, params=params, headers=headers)

    if response.status_code == 200:
        try:
            data = response.json()
            return data
        except json.decoder.JSONDecodeError as e:
            print(f"Error: Failed to parse JSON response: {e}")
            print(f"Response content preview: {response.text[:500]}")
            return None
    else:
        print(f"Error: Failed to fetch data from Amplitude. Status code: {response.status_code}")
        print(f"Response content: {response.text}")
        return None

修正后的图表ID使用方式 #

把原URL列表替换为纯图表ID列表：

chart_ids = [
    'coqje4ni',
    'uxszho05',
    'edf4hjtn',
    'vxed7m7e',
    'mpe5vnf0'
]

# 调用时传入chart_id
for i, chart_id in enumerate(chart_ids):
    amplitude_data = fetch_amplitude_data(api_key, secret_key, start_time, end_time, chart_id)
    # 后续逻辑不变

内容的提问来源于stack exchange，提问作者user21913868