Trying to export private spreadsheet using SheetsService.HttpClient

Question

I'm trying to export a Google Spreadsheet as a .xlsx file. To do so, I've started using Google.Apis.Sheets.v4 library. There I'm creating SheetsService and using Google.Apis.Services.IClientService.HttpClient to do the request to the Google API. https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key={0}&gid={1}&exportFormat=xlsx

It works correctly with public documents, but I'm facing an issue with private spreadsheets. For authentication, my application uses a service account with the public key, and from the spreadsheets UI, I gave access to that document for that account. The fun thing is I can request information from that spreadsheet via Google.Apis.Sheets.v4.SpreadsheetsResource.Get method.

My question is: Why I can't access a document via HttpClient and can do that via SheetsService?

The code sample I use to do the call:

using (var service = GoogleSheetsHelper.GetService(settings))
{
  var exportUrl = "https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key={0}&gid={1}&exportFormat=xlsx";
  var requestResult = await service.HttpClient.GetAsync(exportUri).ConfigureAwait(false);
  if (!requestResult.IsSuccessStatusCode)
  {
    throw new Exception();
  }

  return await requestResult.Content.ReadAsByteArrayAsync().ConfigureAwait(false);
}

What is also interesting - requestResult.IsSuccessStatusCode is always true! Sometimes it just returns html with the google authentication page instead of .xlsx file.

Have you checked if your scopes are setup properly? You can download the public ones because they do not require authentication and you can get information about the private sheet because your scopes are set up that way. — Jescanellas
Thanks for your comment! Currently, I have only one scope SheetsService.Scope.SpreadsheetsReadonly. During debug, I've also tried SheetsService.Scope.Spreadsheets, but no luck here. I'll try Drive scopes, maybe they are responsible for the file export. — Dmitrii Gvozdev
@Jescanellas, no, I've just tried to set scopes to SheetsService.Scope.Spreadsheets, SheetsService.Scope.Drive and it didn't work with two widest scopes. Probably that is not the cause of an issue — Dmitrii Gvozdev

Kessy Kessy · Accepted Answer · 2019-12-04T11:44:01

You should not use the Sheets Service for this, instead you should use the Drive Service with the scope DriveService.Scope.Drive and to download and export the sheet you have to use the Drive API with it's export method.

Trying to export private spreadsheet using SheetsService.HttpClient

1 Answers