Google Sheets API C# sorting functionality throws internal server error

Other Questions Google Sheets
1

I’m working on a C# application that manages data in Google Sheets using the API. The app can successfully read, write, update and delete data from my spreadsheet. However, when I try to implement column sorting, I keep getting an internal server error (500). Here’s my sorting method:

public bool ApplyColumnSort(
  int targetColumnIndex,
  int firstRowIndex,
  int lastRowIndex,
  int leftColumnIndex,
  int rightColumnIndex,
  string orderDirection,
  ref bool hasError)
{
  Console.WriteLine("Starting ApplyColumnSort method");

  string worksheetName = "Products";

  int worksheetId = RetrieveSheetId(_googleSheetsService, _documentId, worksheetName);
 
  BatchUpdateSpreadsheetRequest batchRequest = new BatchUpdateSpreadsheetRequest();
  SortRangeRequest sortRequest = new SortRangeRequest();
  GridRange rangeToSort = new GridRange();
  SortSpec sortSpecification = new SortSpec();
  Request apiRequest = new Request();

  rangeToSort.SheetId = worksheetId;
  rangeToSort.StartRowIndex = firstRowIndex;
  rangeToSort.EndRowIndex = lastRowIndex;
  rangeToSort.StartColumnIndex = leftColumnIndex;
  rangeToSort.EndColumnIndex = rightColumnIndex;
  sortRequest.Range = rangeToSort;
  sortSpecification.SortOrder = orderDirection;

  sortSpecification.DimensionIndex = targetColumnIndex;

  if (!hasError)
  {
    try
    {
      sortRequest.SortSpecs = new List<SortSpec>() { sortSpecification };
      apiRequest.SortRange = sortRequest;
      batchRequest.Requests = new List<Request>() { apiRequest };
      SpreadsheetsResource.BatchUpdateRequest updateRequest = _googleSheetsService.Spreadsheets.BatchUpdate(
        batchRequest, _documentId);

      if (updateRequest == null)
      {
        Console.WriteLine("Failed to create batch update request");
        hasError = true;
      }

      if (!hasError)
      {
        Console.WriteLine("Processing request...");
        BatchUpdateSpreadsheetResponse response = updateRequest.Execute();
        Console.WriteLine("Request completed");
      }
    }
    catch (Exception ex)
    {
      Console.WriteLine("Sorting failed with error: " + ex.Message + ", " + ex.StackTrace);
      hasError = true;
    }
  }

  Console.WriteLine("ApplyColumnSort method finished");

  return !hasError;
}

I’m calling this method with these parameters:

  • targetColumnIndex: 14 (column O)
  • firstRowIndex: 1 (skipping header row)
  • lastRowIndex: 10536
  • leftColumnIndex: 0
  • rightColumnIndex: 14
  • orderDirection: “ASCENDING”

The Execute() call throws a 500 internal server error with “backendError” as the reason. What could be causing this issue and how can I resolve it?

2

Your targetColumnIndex is probably out of bounds. You set it to 14, but your range only goes from leftColumnIndex 0 to rightColumnIndex 14 - that’s columns A through N (0-13). You’re trying to sort by column O (14), which doesn’t exist in your range. Either bump your rightColumnIndex to include column O, or change your targetColumnIndex to something between 0-13. I’ve hit this same backend error before when the sort column wasn’t inside the grid range. Google Sheets API gives pretty unhelpful error messages for this stuff.

3

your sortOrder value’s wrong - use the SortOrder.ASCENDING enum, not the string “ASCENDING”. the google sheets api is picky about that. also, that range is massive (10k+ rows). test with a smaller chunk first to see if it’s a data size issue or your actual code.

4

Had this exact problem in production last year. Your range dimensions are off - you’ve got rightColumnIndex set to 14 and targetColumnIndex also at 14, but the API expects rightColumnIndex to be exclusive (so one past the last column you want). Change rightColumnIndex to 15. Also, double-check that lastRowIndex of 10536 doesn’t go beyond your actual data. The API throws internal server errors when it tries sorting empty rows. I started validating that the range has real data before running any sort - saves a lot of headaches.