Accessing Google Spreadsheets with C# Using Google Data API

Accessing Google Spreadsheets with C# using Google Data API

According to the .NET user guide:

Download the .NET client library:

Add these using statements:

using Google.GData.Client;
using Google.GData.Extensions;
using Google.GData.Spreadsheets;

Authenticate:

SpreadsheetsService myService = new SpreadsheetsService("exampleCo-exampleApp-1");
myService.setUserCredentials("jo@gmail.com", "mypassword");

Get a list of spreadsheets:

SpreadsheetQuery query = new SpreadsheetQuery();
SpreadsheetFeed feed = myService.Query(query);

Console.WriteLine("Your spreadsheets: ");
foreach (SpreadsheetEntry entry in feed.Entries)
{
    Console.WriteLine(entry.Title.Text);
}

Given a SpreadsheetEntry you've already retrieved, you can get a list of all worksheets in this spreadsheet as follows:

AtomLink link = entry.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);

WorksheetQuery query = new WorksheetQuery(link.HRef.ToString());
WorksheetFeed feed = service.Query(query);

foreach (WorksheetEntry worksheet in feed.Entries)
{
    Console.WriteLine(worksheet.Title.Text);
}

And get a cell based feed:

AtomLink cellFeedLink = worksheetentry.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);

CellQuery query = new CellQuery(cellFeedLink.HRef.ToString());
CellFeed feed = service.Query(query);

Console.WriteLine("Cells in this worksheet:");
foreach (CellEntry curCell in feed.Entries)
{
    Console.WriteLine("Row {0}, column {1}: {2}", curCell.Cell.Row,
        curCell.Cell.Column, curCell.Cell.Value);
}

.Net How to create a Google Spreadsheet in Google Drive?

From I am using a service account / credentials when making the requests to the API, could this be the problem?, I thought that your issue might be due to this.

When new Spreadsheet is created with the method of "Method: spreadsheets.create" in Sheets API, the Spreadsheet is created to the root folder of Google drive of the service account. The Google Drive of service account is different from your Google Drive. By this, you cannot directly access to the Spreadsheet created by the service account using your browser. I think that this is the reason of your issue.

When you want to open the created Spreadsheet using your browser, how about the following patterns?

Pattern 1:

After the Spreadsheet was created with the service account, it shares the created Spreadsheet with your Google account.

In order to share the Spreadsheet with your Google account, you can achieve this using the method of "Permissions: create" in Drive API.

Pattern 2:

At first, create a folder in your Google Drive, and shares the created folder with the email of service account as the writer, and then, create new Spreadsheet to the folder.

In this case, I think that you can use the following 2 methods.

1. Directly create new Spreadsheet to the specific folder using the method of "Files: create" in Drive API.

2. Create new Spreadsheet using the method of "Method: spreadsheets.create" in Sheets API, and move the Spreadsheet to the specific folder (your shared folder) using the method of "Files: update" in Drive API.

Pattern 3:

Create new Spreadsheet using OAuth2 instead of the service account.

About the authorization process and sample script for OAuth2, you can see ".NET quickstart" of the official document. Ref About the request to API, you can use your script.

Pattern 4:

If you want to achieve your goal by a simple script, for example, how about using Web Apps as the wrapper API? In this case, the flow is as follows.

1. Create Web Apps.
   
   • In this case, Google Apps Script is used. By this, Spreadsheet is created to your Google Drive.
2. When you use Web Apps, you can access to the Web Apps by HTML request.
3. At Web Apps side, when it is accessed from the client side, new Spreadsheet is created using Google Apps Script.

In this case, you can also achieve without using the access token. Of course, you can access to Web Apps with the access token. Ref1, Ref2

References:

• Method: spreadsheets.create
• Permissions: create
• Files: create
• Files: update

Using Google Sheets API without a cloud platform project

• To consume Sheets API, you must identify the caller using Oauth 2.0 and in the end a Google Cloud Platform project is needed. More information here
• Quoting: When your application requests public data, the request doesn't need to be authorized, but does need to be accompanied by an identifier, such as an API key., so even if you are fetching data from a publicly available Sheets you still need to identify the caller.
• To register and generate credentials (OAuth / API key) a Google Cloud Platform project is needed

How can I access public spreadsheets with the Google Docs C# API?

The Google Docs List API doesn't seem setup for that (see ahab's answer).

---

Related Topics