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

admin管理员组

文章数量:1279206

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?

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?

• c#
• google-sheets
• google-sheets-api
• http-status-code-500

Share Improve this question Follow edited Feb 25 at 4:34 TheMaster 50.8k77 gold badges6969 silver badges9999 bronze badges asked Feb 24 at 19:59 MrSnrubMrSnrub 47544 silver badges1515 bronze badges 8

• 1 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 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 Commented Feb 24 at 22:31
• 1 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 Commented Feb 24 at 23:27
• Are you using the new table feature? If so, remove it. – TheMaster Commented Feb 25 at 4:42
• What is the new table feature? – MrSnrub Commented Feb 25 at 12:09

 |  Show 3 more comments

1 Answer 1

Sorted by: Reset to default 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?

本文标签： Cannot sort table by column using Google Sheets API in CStack Overflow