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?

Side note: please consider to re-read minimal reproducible example guidance on posting code - it looks like 90% of code in the question is not needed - you should inline all constants, remove unnecessary logging and check. Note that you've posted code on SO (where only code related to the issue should be shown) and not on Code Review where complete working production level code should be posted. — Alexei Levenkov
– Alexei Levenkov, Commented Feb 24 at 21:18
When is the error occurring? If at start of code the table may be still under construction. During construction of a table the number of row is -1 until the table columns are added to the table and then set to zero. So adding a check for number of rows > 0 may help — jdweng
– jdweng, Commented Feb 24 at 22:31
How can I possibly provide a reproducible example? I can't create another blank Google sheet with another C# program and upload all of that.The error occurs at the line BatchUpdateSpreadsheetResponse busr = bur.Execute(); — MrSnrub
– MrSnrub, Commented Feb 24 at 23:27

MrSnrub · Accepted Answer · 2025-02-25 19:24:19Z

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?

answered Feb 25 at 19:24

MrSnrub

4754 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related