.NET Core. Google Sheets API, read\write\update

 n this tutorial we’re going to explore Google Sheets API. We’ll configure google infrastructure from scratch before diving into the code. Let’s get started!

Pre-requisites

• Google account
• .NET Core

Configuring Google infrastracture

We’ll start by exploring Google console service.

Google API setup

• go to https://console.developers.google.com and accept Terms of Service

If you’ve never opened this link before, a following window will be presented (or something similar)

It’s a main hub for all Google services, a starting point so to say. Here we need to do a couple of things

• click on Select a project then NEW PROJECT

• choose any name you like, Location leave as default

• the project is being created, wait a sec

Project creating

Project created

• click on a created project

So, here it is — the dashboard! Here we can configure many different things for the project. For this tutorial, we’re only interested in APIs part.

• choose Go to APIs overview, then ENABLE APIS AND SERVICES

• in the search box look for Google Sheets API

• click ENABLE and wait a bit, it needs some time to be activated

Now it’s time to set-up credentials.

Credentials

To start making requests, we must create Credentials. There are different types of Credentials that can be created. If you want to go a bit deeper, go here. For this tutorial, we’ll be using Google Service Account credentials as it’s the easiest way to configure it. Also, it can be used from any application type like console (the current), MVC, Web API etc.

• select Credentials on project’s page, then Manage service account

• click CREATE SERVICE ACCOUNT

• choose any name you like (e.g. mediumtestaccount was used as an example)

• create a role by selecting Project -> Owner

• click CREATE KEY

• choose JSON and click CREATE. Download and save the credentials, we’ll need it later (e.g. mediumtestproject-241919–9a307c8d9811.json)

• click DONE

• account and key are created. Next, copy an email, in my case it’s mediumtestaccout@mediumtestproject-241919.iam.gserviceaccount.com

• now it’s time to create a spreadsheet with some data. Create anything you like, I’ve created the following file

• one important step is left. We need to share the created sheet with an account’s email which was created previously (i.e. mediumtestaccout@mediumtestproject-241919.iam.gserviceaccount.com)

Don’t forget to press Send!

Phew! Congrats, the main setup is done. Let’s move on to a project one!

The Project setup

Now it’s time to create a .NET Core project. In this section, the project template with needed dependencies will be created.

• create a new folder (e.g. google-spreadsheets-api) and move into it (e.g. cd google-spreadsheets-api). Create a console app with the following command

dotnet new console

• add Google API Sheets package

dotnet add package Google.Apis.Sheets.v4

• remember about downloaded *.json? It’s time to move it inside the project. For example, mine is called mediumtestproject-241919–9a307c8d9811.json, just copy it into the root folder and rename it to google-credentials.json
• next, edit *.csproj. Append the following code. Credentials file will always be included with the final build

<ItemGroup>
  <Content Include="google-credentials.json">
    <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
  </Content>
</ItemGroup>

The Code

We’ll start with reading a data and then the writing.

• copy spreadsheet id from a created… spreadsheet

• add the actual code. For now let’s try to read the data from a spreadsheet

Note: make sure your C# language version is set to at least7.1, it’s needed for async Main

using System;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using Google.Apis.Auth.OAuth2;
using Google.Apis.Services;
using Google.Apis.Sheets.v4;namespace GoogleApiExample
{
    class Program
    {
        private static readonly string[] Scopes = { SheetsService.Scope.Spreadsheets };
        private const string SpreadsheetId = "1bSU7TCa8xOiBlaWf2qncewyMUclO1HzEyiSzAs0Tqqw";
        private const string GoogleCredentialsFileName = "google-credentials.json";
        /*
           Sheet1 - tab name in a spreadsheet
           A:B     - range of values we want to receive
        */
        private const string ReadRange = "Sheet1!A:B";        static async Task Main(string[] args)
        {
            var serviceValues = GetSheetsService().Spreadsheets.Values;
            await ReadAsync(serviceValues);
        }        private static SheetsService GetSheetsService()
        {            
            using (var stream = new FileStream(GoogleCredentialsFileName, FileMode.Open, FileAccess.Read))
            {
                var serviceInitializer = new BaseClientService.Initializer
                {
                    HttpClientInitializer = GoogleCredential.FromStream(stream).CreateScoped(Scopes)
                };
            return new SheetsService(serviceInitializer);
       }       private static async Task ReadAsync(SpreadsheetsResource.ValuesResource valuesResource)
       {
            var response = await valuesResource.Get(SpreadsheetId, ReadRange).ExecuteAsync();
            var values = response.Values;            if (values == null || !values.Any())
            {
                 Console.WriteLine("No data found.");
                 return;
            }             var header = string.Join(" ", values.First().Select(r => r.ToString()));
             Console.WriteLine($"Header: {header}");
 
             foreach (var row in values.Skip(1))
             {
                 var res = string.Join(" ", row.Select(r => r.ToString()));
                 Console.WriteLine(res);
             }
        }
    }
}

Let’s run it!

Read

Now it’s time to check the writing capabilities. It’ll be hardcoded for the demo purposes. We’ll just add more code, we won’t be rewriting the existing one.

class Program
{
    // the previous code is above
    private const string WriteRange = “A5:B5”;    static async Task Main(string[] args)
    {
        // the previous code is above
        await WriteAsync(serviceValues);
        await ReadAsync(serviceValues);
    }    private static async Task WriteAsync(SpreadsheetsResource.ValuesResource valuesResource)
    { 
        var valueRange = new ValueRange { Values = new List<IList<object>> { new List<object> { “stan”, 18 } } };
        var update = valuesResource.Update(valueRange, SpreadsheetId, WriteRange);
        update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
        var response = await update.ExecuteAsync();
        Console.WriteLine($”Updated rows: {response.UpdatedRows}”); 
    }
}

Here, we’re adding additional raw and then reading it all again.

Write\Read

As we see, a new row is written and read again. You can use Update API for both cases: writing new and updating existing values. There are also BatchGet and BatchUpdate variations.

The project link — https://github.com/semuserable/blog-google-spreadsheets-api

Thank you for reading!

Nguồn: https://medium.com/@semuserable/net-core-google-sheets-api-read-write-5edd919868e3