Update Specific Columns on certain rows with values?

[LeoJHarris]

We have a use case where we want to update specific columns on some given rows of a google sheet.

We are already reading from a google sheet using the sheets API but finding it very cumbersome on trying to actually update cells. Essentially we are reading from a google sheet, automating some tasks with other 3rd parties using sheet information and then coming back to update the google sheet on some cells depending how the automated jobs ran and whether or not they succeeded

Can this library support this functionality?

[SteveWinward]

@LeoJHarris. Yes, you can definitely do that with this library. One thing to be aware of. Google Sheets API does throttle how many calls you can make against the API, so if you are doing a lot of calls, you may need to build in some wait logic to avoid hitting the throttle limits.

I updated the Sample Project in this source code to do something similar to what you are asking here => https://github.com/SteveWinward/GoogleSheetsWrapper/blob/main/src/GoogleSheetsWrapper.SampleClient/Program.cs

The main logic is here below. Please read this projects main README.md to see how to implement strongly typed models to do this.

var records = respository.GetAllRecords();

foreach (var record in records)
{
    try
    {
        Foo(record.TaskName);
        record.Result = true;
        record.DateExecuted = DateTime.UtcNow;

        var result = respository.SaveFields(
            record,
            r => r.Result,
            r => r.DateExecuted);
    }
    catch (Exception ex)
    {
        record.Result = false;
        record.ErrorMessage = ex.Message;
        record.DateExecuted = DateTime.UtcNow;

        var result = respository.SaveFields(
            record,
            r => r.Result,
            r => r.ErrorMessage,
            r => r.DateExecuted);
    }
}

The SampleRecord class looks like this,

public class SampleRecord : BaseRecord
{
    [SheetField(
            DisplayName = "Task",
            ColumnID = 1,
            FieldType = SheetFieldType.String)]
    public string TaskName { get; set; }

    [SheetField(
            DisplayName = "Result",
            ColumnID = 2,
            FieldType = SheetFieldType.Boolean)]
    public bool Result { get; set; }

    [SheetField(
            DisplayName = "Error",
            ColumnID = 3,
            FieldType = SheetFieldType.String)]
    public string ErrorMessage { get; set; }

    [SheetField(
            DisplayName = "DateExecuted",
            ColumnID = 4,
            FieldType = SheetFieldType.DateTime)]
    public DateTime DateExecuted { get; set; }
}

The SampleRepository class looks like this,

namespace GoogleSheetsWrapper.SampleClient
{
    public class SampleRepository : BaseRepository<SampleRecord>
    {
        public SampleRepository() { }

        public SampleRepository(SheetHelper<SampleRecord> sheetsHelper)
            : base(sheetsHelper) {}
    }
}

[LeoJHarris]

Thanks you for the comprehensive answer and sample provided! 👍I did run into a simple error I think, that is that on the sheetHelper.Init("jsonCredentials")

System.InvalidOperationException: 'Error creating credential from JSON or JSON parameters. Unrecognized credential type .'

I have credentials.json file that was working previously when interfacing with the google API NuGet (working):

        using (FileStream stream = new(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "credentials.json"), FileMode.Open, FileAccess.Read))
        {
            const string credPath = "token.json";
            credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
                GoogleClientSecrets.FromStream(stream).Secrets,
                _scopes,
                "user",
                CancellationToken.None,
                new FileDataStore(credPath, true)).Result;

            Console.WriteLine("Credential file saved to: " + credPath);
        }

        _sheetsService = new(new BaseClientService.Initializer()
        {
            HttpClientInitializer = credential,
            ApplicationName = "Discovery Sample",
        });

From your sample I tried this (Threw exception at sheetHelper.Init(jsonCredsContent)):

// Get the Google Spreadsheet Config Values
        var serviceAccount = #########;
        var documentId = _appSettings?.GoogleSheetsID;
        var jsonCredsPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "credentials.json");

        // In this case the json creds file is stored locally, but you can store this however you
        // want to (Azure Key Vault, HSM, etc)
        var jsonCredsContent = System.IO.File.ReadAllText(jsonCredsPath);

        // Create a new SheetHelper class
        var sheetHelper = new SheetHelper<GoogleSheetProfile>(documentId, serviceAccount, "Staging");
        sheetHelper.Init(jsonCredsContent);

The credentials.json are the same as before but oddly throwing an error from in this library 😕

Is there something I am doing wrong?

[LeoJHarris]

Might be worth pointing out the format of the json is, downloaded directly from google:

{
    "installed": {
        "client_id": "################.apps.googleusercontent.com",
        "project_id": "#####",
        "auth_uri": "https://accounts.google.com/o/oauth2/auth",
        "token_uri": "https://oauth2.googleapis.com/token",
        "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
        "client_secret": "###########",
        "redirect_uris": [
            "http://localhost"
        ]
    }
}

[SteveWinward]

@LeoJHarris. For this library you need to create a service account and authenticate as the service account. This is why one of the parameters of the SheetsHelper class is the service account identity.

Take a look at the notes I put together on authentication here,

https://github.com/SteveWinward/GoogleSheetsWrapper?tab=readme-ov-file#authentication

There's also a good summary of how to configure this and grant the service account access to the Google spreadsheet in this article below,

https://robocorp.com/docs/development-guide/google-sheets/interacting-with-google-sheets#create-a-google-service-account

[SteveWinward]

@LeoJHarris I’m going to close this issue out. If you still have issues please reopen this one or create a new one.

[LeoJHarris]

Great thanks! Working now, will test the sample code you provided but thanks heaps this is a really great library! 👍 perfect fit for our application 🥇

[SteveWinward]

Awesome. If you have any feedback for the library feel free to share. We also accept pull requests too for library updates.