Python调用Sheety API写入时Google表格时长显示不准确问题

Google表格通过Sheety接口同步运动数据时，duration时长字段出现代码未定义的异常时间格式，需要保持传入JSON的原值不变。

原问题代码 #

exercise_endpoint = "https://trackapi.nutritionix.com/v..
workout = input("What workouts did you do:")

query = {"query":workout}
header = {"x-app-id":APP_ID,
          "x-app-key":API_KEY}

exercise_response = requests.post(url=exercise_endpoint, json=query, headers=header)
date = datetime.now().strftime("%d/%m/%Y")
time = datetime.now().strftime("%H:%M:%S")

sheet_post_endpoint = 'https://api.sheety.co/..
sheety_header = {"Authorization": "Bearer ..."}
response_as_dict = exercise_response.json()
workouts = response_as_dict['exercises']
for workout in workouts:
    # id = workout['tag_id']
    exercise = workout["name"].title()
    duration = workout["duration_min"]
    calories = workout["nf_calories"]

routine = {"workout":
                   {
                    # "id": id,
                    "date": date,
                    "time": time,
                    "exercise": exercise,
                    "duration": duration,
                    "calories": calories}
               }
print(routine)
sheet_post_response = requests.post(url=f"{sheet_post_endpoint}", json=routine, headers=sheety_header)
print(sheet_post_response.text)

get_response_url = "https://api.sheety.co/..../workoutsTracking/workouts"
print(requests.get(get_response_url).json())

• Nutritionix接口返回的duration_min是浮点数类型，Sheety写入Google表格时，会自动将纯数字类型的时长值识别为时间类型，自动追加时分秒格式，导致出现代码未定义的异常显示。
• 现有代码循环逻辑存在缺陷：多运动场景下循环会覆盖前序运动的赋值，最终仅会上传最后一条运动记录。

• 格式规避：将duration转为字符串类型后再传入，无法触发表格的自动格式识别规则，就会保留原值显示。按需选择保留小数或取整即可：

  # 保留原始小数格式
  duration = str(workout["duration_min"])
  # 取整后转字符串
  duration = str(round(workout["duration_min"]))
  

• 逻辑修正：将Sheety POST请求移入循环内部，保证所有运动条目都能正常上传：

  for workout in workouts:
      exercise = workout["name"].title()
      duration = str(workout["duration_min"])
      calories = workout["nf_calories"]
  
      routine = {"workout":
                         {
                          "date": date,
                          "time": time,
                          "exercise": exercise,
                          "duration": duration,
                          "calories": calories}
                     }
      sheet_post_response = requests.post(url=sheet_post_endpoint, json=routine, headers=sheety_header)
      print(sheet_post_response.text)
  

• 表格配置修正：手动将Google表格中duration列的单元格格式设置为「纯文本」或「普通数字」，避免后续写入数据被自动转换格式。

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