如何将嵌套JSON转换为CSV以对接Google Sheets及JSON API？

Great question! Dealing with nested JSON to CSV can be tricky when tools mess up your variable names—let’s break down the best approaches, especially tailored for your Google Sheets + JSON API workflow:

This method lets you fully control how nested fields are converted to CSV columns, so variable names stay predictable. It’s perfect for prepping your JSON before importing to Sheets.

import pandas as pd
import json

# Load your nested JSON file
with open('your_data.json', 'r') as f:
    raw_data = json.load(f)

# Flatten nested structures with a clear separator (e.g., "." for hierarchy)
flattened_df = pd.json_normalize(raw_data, sep='.')

# Save to CSV—columns will retain hierarchy like `user.name` or `shipping.address.city`
flattened_df.to_csv('clean_flattened_data.csv', index=False)

The sep='.' ensures nested fields are named consistently, so you won’t get random renaming like you did with generic tools. Import this CSV into Google Sheets, and every column maps directly to your original JSON structure.

If you want to skip local tools entirely, use Apps Script to import and flatten JSON directly in Google Sheets. This keeps everything in your workflow without third-party middlemen.

Here’s a custom script to flatten nested JSON and populate your sheet:

function importAndFlattenJSON(jsonString) {
  const activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = JSON.parse(jsonString);

  // Recursive function to flatten nested objects
  const flatten = (obj, prefix = '') => {
    let result = {};
    for (const key in obj) {
      if (typeof obj[key] === 'object' && obj[key] !== null && !Array.isArray(obj[key])) {
        Object.assign(result, flatten(obj[key], `${prefix}${key}.`));
      } else {
        result[`${prefix}${key}`] = obj[key];
      }
    }
    return result;
  };

  // Handle array or single object input
  const flattenedRows = Array.isArray(data) ? data.map(item => flatten(item)) : [flatten(data)];
  
  // Write headers and data to the sheet
  const headers = Object.keys(flattenedRows[0]);
  activeSheet.clear();
  activeSheet.appendRow(headers);
  
  flattenedRows.forEach(row => {
    const rowValues = headers.map(header => row[header]);
    activeSheet.appendRow(rowValues);
  });
}

To use this:

• Open your Google Sheet, go to Extensions > Apps Script
• Paste the code, save the project
• Run the function (you’ll need to grant permissions) or pass a JSON string as a custom cell function

Once your data is in Sheets and you’re editing it directly, use another Apps Script to deploy a real-time JSON API. This way, any edits to the sheet automatically update the API response.

function doGet() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const sheetData = sheet.getDataRange().getValues();
  const headers = sheetData[0];
  const jsonOutput = [];

  // Convert sheet rows back to nested JSON (reversing the flattening step)
  for (let i = 1; i < sheetData.length; i++) {
    const row = sheetData[i];
    const rowObj = {};
    
    headers.forEach((header, index) => {
      const keys = header.split('.');
      let currentLevel = rowObj;
      
      // Rebuild nested objects from column names
      for (let k = 0; k < keys.length - 1; k++) {
        if (!currentLevel[keys[k]]) currentLevel[keys[k]] = {};
        currentLevel = currentLevel[keys[k]];
      }
      currentLevel[keys[keys.length - 1]] = row[index];
    });
    
    jsonOutput.push(rowObj);
  }

  // Return JSON response
  return ContentService.createTextOutput(JSON.stringify(jsonOutput))
    .setMimeType(ContentService.MimeType.JSON);
}

Deploy this as a Web App:

• In Apps Script, go to Deploy > New deployment
• Select "Web app", set access to "Anyone, even anonymous"
• Copy the deployed URL—this is your JSON API endpoint, which updates automatically when you edit the sheet.

The key advantage of these methods is that you control exactly how nested fields are mapped to CSV columns and back to JSON, eliminating the random variable renaming you encountered with generic tools.

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