如何在推送数据至Google Sheet时保留终端JSON对象打印格式？

问题 #

我有一段Python脚本能从NBA官网解析JSON数据，在终端可以干净打印标题、日期和内容。但改成推送数据到Google Sheet后，只有date字段显示正常，title和content要么为空，要么出现大量\n转义字符。已经确认Python和Sheet的连接没问题（手动添加值正常），试过str()转换但没效果，求解决办法。

原终端打印脚本 #

import requests
from bs4 import BeautifulSoup as bs
import json

headers = {
    'User-Agent': 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/104.0.5112.79 Safari/537.36'
}

url = 'https://www.nba.com/game/bkn-vs-phi-0022100993'

r = requests.get(url, headers=headers)

soup = bs(r.text, 'html.parser')

page_obj = soup.select_one('script#__NEXT_DATA__')
json_obj = json.loads(page_obj.text)
print('Title:', json_obj['props']['pageProps']['story']['header']['headline'])
print('Date:', json_obj['props']['pageProps']['story']['date'])
print('Content:', json_obj['props']['pageProps']['story']['content'])

修改后的推送脚本 #

import requests
from bs4 import BeautifulSoup as bs
import json
import gspread

gc = gspread.service_account(filename='creds.json')
sh = gc.open_by_key('1NFrhsJT7T0zm3dRaP5J8OY0FryBHy5W_wEEGvwBg58I')
worksheet = sh.sheet1

headers = {
    'User-Agent': 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/104.0.5112.79 Safari/537.36'
}

url = 'https://www.nba.com/game/bkn-vs-phi-0022100993'

r = requests.get(url, headers=headers)

soup = bs(r.text, 'html.parser')

page_obj = soup.select_one('script#__NEXT_DATA__')
json_obj = json.loads(page_obj.text)
title = (json_obj['props']['pageProps']
         ['story']['header']['headline'])
date = (json_obj['props']['pageProps']['story']['date'])
content = (json_obj['props']['pageProps']['story']['content'])

AddData = [title, date, content]
worksheet.append_row(AddData)

解决思路 #

• 排查title字段异常原因：终端打印正常但Sheet为空，先打印type(title)确认数据类型。如果是字典而非字符串，需要提取对应的字符串值；如果是None，添加默认值避免空单元格。
• 清理content的冗余格式：content里的\n是换行符，Google Sheet会直接显示，用str.replace('\n', ' ')替换成空格，再用strip()去除首尾空白。如果content是HTML结构（从终端输出看大概率是），直接用BeautifulSoup提取纯文本，避免HTML标签和转义字符。
• 统一数据格式：gspread的append_row要求每个元素是字符串类型，对title和content做非空判断，为空时设置默认文本（比如“无标题”“无内容”），确保每个元素都是有效字符串。

修改后的完整脚本 #

import requests
from bs4 import BeautifulSoup as bs
import json
import gspread

gc = gspread.service_account(filename='creds.json')
sh = gc.open_by_key('1NFrhsJT7T0zm3dRaP5J8OY0FryBHy5W_wEEGvwBg58I')
worksheet = sh.sheet1

headers = {
    'User-Agent': 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/104.0.5112.79 Safari/537.36'
}

url = 'https://www.nba.com/game/bkn-vs-phi-0022100993'

r = requests.get(url, headers=headers)
soup = bs(r.text, 'html.parser')

page_obj = soup.select_one('script#__NEXT_DATA__')
json_obj = json.loads(page_obj.text)

# 处理title：确保是字符串，空值时设默认
title_raw = json_obj['props']['pageProps']['story']['header']['headline']
title = str(title_raw).strip() if title_raw else '无标题'

# 处理date：直接使用原数据
date = json_obj['props']['pageProps']['story']['date']

# 处理content：提取纯文本，清理格式
content_raw = json_obj['props']['pageProps']['story']['content']
if content_raw:
    # 解析HTML，提取纯文本并合并空格
    content_soup = bs(content_raw, 'html.parser')
    content = content_soup.get_text(strip=True, separator=' ')
else:
    content = '无内容'

# 推送数据到Sheet
worksheet.append_row([title, date, content])

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