Learn more
 

LINQ to Google Sheets Data

Ready to get started?

Download a free trial:

Download Now

Learn more:

Google Sheets ADO.NET Provider

Easily connect .NET applications with real-time data from spreadsheets stored in Google Docs. Use Google Sheets to manage the data that powers your applications.

In this article

Related articles


LINQ provides general-purpose query facilities in .NET Framework 3.0 and above and provides one easy way to programmatically access data through from CData ADO.NET Data Providers. This example uses LINQ to access information from the Google Sheets Data Provider.

This article demonstrates how to use LINQ to access Google Sheets tables through the CData ADO.NET Data Provider for Google Sheets. To do this you will LINQ to Entity Framework, which is used to generate the connection and can be used with any CData ADO.NET Data Providers to access data via LINQ.

See the help documentation for a guide to setting up an EF 6 project to use the provider.

  1. In a new project in Visual Studio, right-click on the project and choose to add a new item. Add an ADO.NET Entity Data Model.
  2. Choose EF Designer from Database and click Next.
  3. Add a new Data Connection, and change your data source type to "CData Google Sheets Data Source".

  4. Enter your data source connection information.

    You can connect to a spreadsheet by providing authentication to Google and then setting the Spreadsheet connection property to the name or feed link of the spreadsheet. If you want to view a list of information about the spreadsheets in your Google Drive, execute a query to the Spreadsheets view after you authenticate.

    ClientLogin (username/password authentication) has been officially deprecated since April 20, 2012 and is now no longer available. Instead, use the OAuth 2.0 authentication standard. To access Google APIs on behalf on individual users, you can use the embedded credentials or you can register your own OAuth app.

    OAuth also enables you to use a service account to connect on behalf of users in a Google Apps domain. To authenticate with a service account, you will need to register an application to obtain the OAuth JWT values.

    See the Getting Started chapter in the help documentation to connect to Google Sheets from different types of accounts: Google accounts, Google Apps accounts, and accounts using two-step verification.

    Below is a typical connection string:

    Spreadsheet=MySheet;InitiateOAuth=GETANDREFRESH
  5. If saving your entity connection to App.Config, set an entity name. In this example we are setting GoogleSheetsEntities as our entity connection in App.Config.
  6. Enter a model name and select any tables or views you would like to include in the model.

Using the entity you created, you can now perform select , update, delete, and insert commands. For example:

GoogleSheetsEntities context = new GoogleSheetsEntities(); var ordersQuery = from orders in context.Orders select orders; foreach (var result in ordersQuery) { Console.WriteLine("{0} {1} ", result.Id, result.Shipcountry); }

See "LINQ and Entity Framework" chapter in the help documentation for example queries of the supported LINQ.