Cannot sort table by column using Google Sheets API in C#

Question

I have a C# program that can fetch add and edit and remove values in my Google sheet. But I can't seem to sort the records in the table correctly. Here is my code:

public bool SortGoogleSheet(
  int i_sorted_column_index,
  int i_start_row_index,
  int i_end_row_index,
  int i_start_col_index,
  int i_end_col_index,

  string str_sort_direction,
  ref bool b_error_occurred)
{
  Program.LogIt("Begin SortGoogleSheet()");

  string sheetname = "Items";

  int i_sheet_id = GetSheetId(_sheetsService, _spreadsheetId, sheetname);
 
  BatchUpdateSpreadsheetRequest busReq = new BatchUpdateSpreadsheetRequest();
  SortRangeRequest srr = new SortRangeRequest();
  GridRange gr = new GridRange();
  SortSpec ss = new SortSpec();
  Request req = new Request();

  gr.SheetId = i_sheet_id;
  gr.StartRowIndex = i_start_row_index;
  gr.EndRowIndex = i_end_row_index;
  gr.StartColumnIndex = i_start_col_index;
  gr.EndColumnIndex = i_end_col_index;
  srr.Range = gr;
  ss.SortOrder = str_sort_direction;

  // Sort by CompositeForSorting field
  ss.DimensionIndex = i_sorted_column_index;

  if (!b_error_occurred)
  {
    try
    {
      srr.SortSpecs = new List<SortSpec>() { ss };
      req.SortRange = srr;
      busReq.Requests = new List<Request>() { req };
      SpreadsheetsResource.BatchUpdateRequest bur = _sheetsService.Spreadsheets.BatchUpdate(
        busReq, _spreadsheetId);

      if (bur == null)
      {
        Program.LogIt("ERROR: Could not create BatchUpdate object.");
        b_error_occurred = true;
      }

      if (!b_error_occurred)
      {
        Program.LogIt("Executing...");
        BatchUpdateSpreadsheetResponse busr = bur.Execute();
        Program.LogIt("Done.");
      }
    }
    catch (Exception e)
    {
      Program.LogIt("ERROR: Could not sort records. Exception: " + e.Message + ", " + e.StackTrace);
      b_error_occurred = true;
    }
  } // end if (!b_error_occurred)

  Program.LogIt("end SortGoogleSheet()");

  return !b_error_occurred;
} // end public bool SortGoogleSheet(ref bool b_error_occurred)

I call the function with the following values:

• i_sorted_column_index: 14 (zero-based index, so corresponds with column O, the 15th column in the spreadsheet)
• i_start_row_index: 1 (zero-based index. I assume you skip a row because you don't want to sort the header names row?)
• i_end_row_index: 10536 (zero-based index. The last row in the table is row #10537.)
• i_start_col_index: 0
• i_end_col_index: 14 (column O, the fifteenth column in the table, is the last column in the table)
• str_sort_direction: "ASCENDING"

The call to BatchUpdateSpreadsheetResponse busr = bur.Execute(); returns the following error:

ERROR: Could not sort records. Exception: Google.Apis.Requests.RequestError
Internal error encountered. [500]
Errors [
    Message[Internal error encountered.] Location[ - ] Reason[backendError] Domain[global]
]
,    at Google.Apis.Requests.ClientServiceRequest`1.<ParseResponse>d__35.MoveNext() in C:\Apiary\2021-09-08.15-52-39\Src\Support\Google.Apis\Requests\ClientServiceRequest.cs:line 
258
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Google.Apis.Requests.ClientServiceRequest`1.Execute() in C:\Apiary\2021-09-08.15-52-39\Src\Support\Google.Apis\Requests\ClientServiceRequest.cs:line 180

What does this mean and how do I fix it?

Answer 1

I moved the sort field to the middle of the table and it worked! The Execute() statement worked with no error. Then I moved the sort field to other places in the table and everything worked unless it was the last (fifteenth) column. Apparently you can't sort if the field you want to sort the table by is the last field in the table?