What's the best way to fetch public Google spreadsheets using C# API?

Other Questions Google docs
1

Hey folks, I’m working on a C# project that reads Google spreadsheets. I’ve got it working for private sheets where I use login info. But now I want to grab public spreadsheets too.

I’m using the Google Docs .NET API. Right now, my code looks something like this:

var myService = new SheetService("MyApp");
myService.SetLogin(user, pass);

var query = new SheetQuery();
var feed = myService.RunQuery(query);

var mySheet = feed.Entries.FirstOrDefault(e => e.Title == sheetName);

This works fine for private sheets. But how do I grab a public sheet just using its URL? Like if I have a link like sheets.google.com/somekeyhere, what’s the best way to get that data?

Any tips or code examples would be super helpful. Thanks!

2

For public Google Sheets, you can simplify your approach significantly. Instead of using the Google Docs .NET API, consider using the Google Sheets API v4. It’s more straightforward for this purpose.

First, install the Google.Apis.Sheets.v4 NuGet package. Then, you can fetch data without authentication for public sheets:

using Google.Apis.Sheets.v4;
using Google.Apis.Services;

var service = new SheetsService(new BaseClientService.Initializer());
var spreadsheetId = "your-spreadsheet-id";
var range = "Sheet1!A1:Z1000"; // Adjust as needed

var request = service.Spreadsheets.Values.Get(spreadsheetId, range);
var response = request.Execute();
var values = response.Values;

This method is efficient and doesn’t require login credentials for public sheets. Just make sure to handle potential exceptions and adjust the range as needed for your specific use case.

3

hey there! i’ve used a different method that’s pretty slick. you can actually grab the sheet data as json instead of csv. just change the url to:

https://spreadsheets.google.com/feeds/list/{SHEET_ID}/1/public/values?alt=json

then use HttpClient to fetch it and Newtonsoft.Json to parse. super easy and gives you more flexibility than csv!

4

I’ve actually been down this road before and found a pretty neat solution. Instead of using the Google Sheets API directly, I switched to using the Google Sheets CSV export feature. It’s surprisingly simple and works great for public sheets.

Here’s the gist of what I did:

  1. Take the sheet ID from the URL (it’s that long string of characters).
  2. Construct a URL like this: https://docs.google.com/spreadsheets/d/{sheetId}/export?format=csv
  3. Use HttpClient to download the CSV data.
  4. Parse the CSV data using a library like CsvHelper.

This approach is lightweight, doesn’t require authentication for public sheets, and gives you raw data to work with. It’s been super reliable in my projects.

Just remember, this method only works for public sheets and you won’t get some of the fancier features of the Sheets API. But for basic data retrieval, it’s hard to beat the simplicity.