调用SmartSheet API遇UnexpectedRequestError的排查与解决

问题场景 #

需要获取Smartsheet表格的列ID与显示名称对应关系，调用client.Sheets.get_columns(sID, include_all=True)时持续抛出UnexpectedRequestError (<PreparedRequest [GET]>, None)错误。使用环境为公司指定的VSCode + Python 3.7，已安装Python、Jupyter、Intellicode扩展，为解决其他问题额外安装了urllib3==1.26.6。

初始代码如下：

import smartsheet as ss
import os
import logging

# 指定访问令牌
os.environ['SMARTSHEET_ACCESS_TOKEN'] = "xxx"

# 初始化政务版API客户端
client = ss.Smartsheet(api_base=ss.__gov_base__)

# 设置日志级别为CRITICAL
logging.getLogger('smartsheet').setLevel(logging.CRITICAL)

# 开启API错误抛出异常
client.errors_as_exceptions(True)

# 定义表格ID
sID = "xxx"

action = client.Sheets.get_columns(sID, include_all=True)
columns = action.data
for col in columns:
    print(col.id)
    print(col.title)
    print('')

报错详情 #

曾用try-except捕获异常时出现过ImportError! Could not load api or model class __iter__，该错误已自行消失。后续更新代码后出现403 Forbidden错误，完整报错栈如下：

(<PreparedRequest [GET]>, None)
OSError: Tunnel connection failed: 403 Forbidden

During handling of the above exception, another exception occurred:

urllib3.exceptions.MaxRetryError: HTTPSConnectionPool(host='api.smartsheetgov.com', port=443): Max retries exceeded with url: /2.0/sheets/xxx/columns?includeAll=True (Caused by ProxyError('Cannot connect to proxy.', OSError('Tunnel connection failed: 403 Forbidden')))

During handling of the above exception, another exception occurred:

requests.exceptions.ProxyError: HTTPSConnectionPool(host='api.smartsheetgov.com', port=443): Max retries exceeded with url: /2.0/sheets/xxx/columns?includeAll=True (Caused by ProxyError('Cannot connect to proxy.', OSError('Tunnel connection failed: 403 Forbidden')))

The above exception was the direct cause of the following exception:

  File "C:\...\ss_fetch.py", line 22, in <module>
    action = sh.Sheets.get_columns(sID, include_all=True)
smartsheet.exceptions.UnexpectedRequestError: (<PreparedRequest [GET]>, None)

排查与解决过程 #

• 切换标准Smartsheet API与政务版API，均出现相同错误
• 联系公司IT部门，确认问题不在本地网络或代理配置，指向Smartsheet侧
• 联系Smartsheet官方支持，未定位到具体原因
• 最终定位为Smartsheet安全证书编写问题，通过调整代理与证书的交互方式后，问题得以解决。

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