如何通过Google Sheets API获取Autofit自动适配行高？

Hey there, I’ve dealt with this exact frustration using the Google Sheets API before—let’s break down why this happens and how to fix it.

The RowMetadata.PixelSize property only returns manually set row heights. When you use Autofit, Google Sheets calculates the row height dynamically at render time based on cell content, and this computed value isn’t stored in the RowMetadata that the API returns by default. That’s why you’re getting the standard default height instead of the actual Autofit-adjusted one.

1. Double-Check Your API Request Fields #

First, make sure you’re requesting the right fields in your spreadsheets.get call. Sometimes restricted field parameters can prevent the API from returning the computed Autofit height (even if it exists in the backend). Update your request to explicitly include the row metadata fields:

var request = service.Spreadsheets.Get("<<spreadsheetId>>");
request.Ranges = namedRanges;
request.IncludeGridData = true;
// Explicitly request row metadata pixel size to ensure it's included
request.Fields = "sheets(data(rowMetadata(pixelSize)),properties)";
var spreadsheet = request.Execute();

// Now try fetching the row height again
var rowHeight = spreadsheet.Sheets[0].Data[0].RowMetadata[0].PixelSize.Value;

If the row was already Autofit, this might pull the actual computed height—sometimes the default response omits it if fields aren’t specified clearly.

2. Calculate Row Height Manually #

If the API still doesn’t return the correct value, you’ll need to calculate the row height yourself based on the cell content. This requires matching the font settings from your Google Sheet (font family, size, weight, etc.) to get accurate results. Here’s a C# example using System.Drawing to measure text:

First, add the System.Drawing.Common NuGet package to your project. Then use this helper method:

using System.Drawing;

public static float GetAutofitRowHeight(string cellContent, float cellWidth, float fontSize, string fontFamily = "Arial")
{
    // Match your Google Sheet's font settings here
    using var font = new Font(fontFamily, fontSize, FontStyle.Regular);
    using var bitmap = new Bitmap(1, 1);
    using var graphics = Graphics.FromImage(bitmap);
    
    // Measure the text wrapped to the cell's width
    var textBounds = graphics.MeasureString(cellContent, font, (int)cellWidth);
    // Add a small padding to match Google Sheets' Autofit behavior
    return textBounds.Height + 8;
}

You’ll need to fetch the cell content from the grid data and the column width to use this method.

3. Use Google Apps Script as a Bridge #

For the most accurate result, use Google Apps Script to fetch the actual rendered row height (since it has direct access to the Sheet’s client-side computed values), then call that script from your C# code.

Step 1: Create the Apps Script #

1. Open your Google Sheet, go to Extensions > Apps Script
2. Replace the default code with this:

function doGet(e) {
  const spreadsheetId = e.parameter.spreadsheetId;
  const sheetName = e.parameter.sheetName;
  const rowIndex = parseInt(e.parameter.rowIndex);
  
  try {
    const spreadsheet = SpreadsheetApp.openById(spreadsheetId);
    const sheet = spreadsheet.getSheetByName(sheetName);
    const rowHeight = sheet.getRowHeight(rowIndex);
    
    return ContentService.createTextOutput(JSON.stringify({ rowHeight }))
      .setMimeType(ContentService.MimeType.JSON);
  } catch (err) {
    return ContentService.createTextOutput(JSON.stringify({ error: err.message }))
      .setMimeType(ContentService.MimeType.JSON);
  }
}

3. Deploy the script as a web app:
   • Click Deploy > New deployment
   • Select Web app as the type
   • Set Execute as to your account, Who has access to Anyone, even anonymous (or restrict as needed)
   • Copy the web app URL

Step 2: Call the Web App from C# #

Add this code to your C# project to fetch the row height:

using System.Net.Http;
using System.Threading.Tasks;

public static async Task<int> GetAutofitRowHeightFromAppsScript(string webAppUrl, string spreadsheetId, string sheetName, int rowIndex)
{
    using var httpClient = new HttpClient();
    var requestUrl = $"{webAppUrl}?spreadsheetId={spreadsheetId}&sheetName={sheetName}&rowIndex={rowIndex}";
    var response = await httpClient.GetStringAsync(requestUrl);
    
    // Parse the JSON response (use Newtonsoft.Json or System.Text.Json)
    var result = System.Text.Json.JsonSerializer.Deserialize<RowHeightResponse>(response);
    return result.RowHeight;
}

// Helper class for deserialization
public class RowHeightResponse
{
    public int RowHeight { get; set; }
    public string Error { get; set; }
}

This will give you the exact Autofit-adjusted row height that’s displayed in the Google Sheet.

• Start with adjusting your API request fields—this is the simplest fix if it works.
• Manual calculation is good for offline scenarios but requires precise font matching.
• Apps Script is the most reliable method for getting the exact rendered height.

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