PyCharm中gspread实现串口数据写入Google Sheets失败问题排查

Let's break down why your Google Sheet stays empty even though logs show valid data, plus walk through the proper gspread initialization flow for writing to Sheets.

Possible Reasons Your Sheet Is Empty #

1. Missing Service Account Permissions #

This is the most common and critical issue. Your client_secret.json links to a Google Cloud service account—you must explicitly share the "EXPERIMENTS" spreadsheet with this account's email address (found in the client_email field of your JSON key file) and grant it edit access. Without this step, your code can authenticate to the API but has no permission to modify the sheet.

2. Mismatched Column Range vs. Header Placement #

Looking at your code:

• In init_experiment, you set the WaterLevel header in cell I1, but in start_experiment, you're writing data to the range A{row}:H{row}. Your get_measurements returns 8 values (including WaterLevel), so the final value gets written to column H—not I where your header lives. You are writing data, just to the wrong column!

Fix this by either:

• Moving the WaterLevel header to H1:

  # Replace in init_experiment
  # worksheet.update_acell('I1', 'WaterLevel')
  worksheet.update_acell('H1', 'WaterLevel')
  

• Or expanding the write range to include column I (and add an empty placeholder for the unused H column):

  # In start_experiment
  cell_list = worksheet.range('A' + str(worksheet_row) + ':I' + str(worksheet_row))
  # Insert empty value for column H
  measurements = list(measurements)
  measurements.insert(7, "")
  

3. Value Input Option Conflicts #

You're using 'USER_ENTERED' in update_cells. This mode tells Google Sheets to parse input as if a user typed it—if your measurements contain strings that look like formulas (e.g., starting with =), they might be hidden or fail to render. Switch to 'RAW' mode to write data exactly as-is:

worksheet.update_cells(cell_list, value_input_option='RAW')

4. Silent Serial Data Parsing Failures #

In get_measurements, you generate random values first, then overwrite them if serial data is received. If serial reading returns partial/invalid data (e.g., truncated lines), you might end up with malformed values—but your logs show "vals" so this is less likely. Adding checks for valid parsed values (e.g., numeric checks for temperatures) can prevent bad data from being written.

Proper Gspread Initialization Flow for Writing to Google Sheets #

Follow these steps to ensure your setup is robust before writing data:

1. Set Up Google Cloud Resources #

• Create a project in the Google Cloud Console.
• Enable the Google Sheets API and Google Drive API for your project.
• Create a service account, generate a JSON key file, and save it as client_secret.json in your project folder.

2. Grant Sheet Access to the Service Account #

• Open your "EXPERIMENTS" spreadsheet.
• Click "Share" in the top-right corner.
• Paste the service account's email (from the client_email field in your JSON key) and assign it Edit permissions. Click Send.

3. Initialize the Gspread Client #

import gspread
from oauth2client.service_account import ServiceAccountCredentials

# Use Sheets-specific scopes for tighter, more appropriate permissions
scope = [
    'https://www.googleapis.com/auth/spreadsheets',
    'https://www.googleapis.com/auth/drive.file'
]

# Load credentials and authorize the client
creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)
client = gspread.authorize(creds)

# Open the spreadsheet using its ID (more reliable than name, avoids conflicts)
spreadsheet = client.open_by_key('YOUR_SPREADSHEET_ID')  # Replace with your sheet's ID from the URL

4. Prepare the Worksheet #

• Create a new worksheet or fetch an existing one:

  # New worksheet
  worksheet = spreadsheet.add_worksheet(title="My Experiment", rows=10000, cols=10)
  # Or existing worksheet
  worksheet = spreadsheet.worksheet("Existing Sheet Name")
  

• Set up headers in one bulk operation (cleaner than individual update_acell calls):

  headers = ['t', 'T_in', 'T_out', 'T_aux', 'RH_in', 'RH_out', 'RH_aux', 'WaterLevel']
  worksheet.update('A1:H1', [headers])
  

5. Write Data to the Sheet #

• Use append_row for simpler single-row writes (avoids manual range setup):

  row_data = ['2024-05-20 12:00:00', 25, 22, 20, 60, 55, 58, 320]
  worksheet.append_row(row_data, value_input_option='RAW')
  

Quick Fixes for Your Code #

1. Share the "EXPERIMENTS" sheet with your service account email.
2. Align your write range with the header columns (fix the WaterLevel column mismatch).
3. Switch to 'RAW' value input option to avoid parsing issues.
4. Replace manual header setup with a bulk update call for cleaner, more maintainable code.

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