CODEWITHSUNDEEP
c#google-sheets-api
Terris
Terris

Reputation: 25

Access standard spreadsheet functions with Sheets API in C#

I'm currently working on a small application with C# and .NET, where I'm trying to mimic a spreadsheet with a graphical interface while simultaneously updating a spreadsheet in Google Drive.

I'll need to use some of the functions and I was trying to find in the documentation if it was possible to access the native functions through the API, but unsuccessfully. I was also trying to do the simple hack of inserting the function as a string directly into a cell, like this (sorry about the usage of a list, I just copied it from another function where I tried filling up multiple cells at the same time),

var valuesToAppend = new List<object>() { "=MAX(A1:A5)" };

but then I also passed the quotation marks which made it useless.

It might be because it's Saturday and my head haven't worked properly, but I haven't found any solution to this. Is there someone out there who know if I can access the methods easily or if I need to go the long way and implement the functions and do the calculations locally?

Edit: To clarify, what I was hoping to do is insert formulas like =MAX(range), =SUM(range) in a cell from my server through the Sheets API.

Upvotes: 0

Views: 1336

Answers (1)

Jon Skeet
Jon Skeet

Reputation: 1503419

Yes, you can absolutely do this. You need to specify the ValueInputOption as USERENTERED for the update request - and you can request to get the content of the cell back (as the computed value) in the response.

So something like:

var valueRange = new ValueRange { Values = new[] { new object[] { formula } } };
var request = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, cell);
request.ValueInputOption = ValueInputOptionEnum.USERENTERED;
request.IncludeValuesInResponse = true;
request.ResponseValueRenderOption = ResponseValueRenderOptionEnum.UNFORMATTEDVALUE;
var response = request.Execute();
Console.WriteLine($"Result: {response.UpdatedData.Values[0][0]}");

Here's a complete example containing that code:

using Google.Apis.Auth.OAuth2;
using Google.Apis.Services;
using Google.Apis.Sheets.v4;
using Google.Apis.Sheets.v4.Data;
using System;
using System.IO;
using System.Threading;
using static Google.Apis.Sheets.v4.SpreadsheetsResource.ValuesResource.UpdateRequest;

class Program
{
    static void Main(string[] args)
    {
        // Set-up part...
        if (args.Length != 4)
        {
            Console.WriteLine("Required values: <client secrets file> <spreadsheet-ID> <cell> <formula>");
            Console.WriteLine("Example: <secrets.json> <sheet ID> E1 =MAX(A1:D1)");
            return;
        }
        var secretsFile = args[0];
        var spreadsheetId = args[1];
        var cell = args[2];
        var formula = args[3];

        var secrets = File.ReadAllBytes(secretsFile);

        // Don't usually use Task<T>.Result, but this is just a demo console app...
        // we shouldn't have any deadlock worries.
        var credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
            new MemoryStream(secrets),
            new[] { SheetsService.Scope.Spreadsheets },
            "user", // Key in file store
            CancellationToken.None).Result;

        var service = new SheetsService(new BaseClientService.Initializer
        {
            HttpClientInitializer = credential,
            ApplicationName = "Google Sheets API demo"
        });

        // Interesting bit :)
        var valueRange = new ValueRange { Values = new[] { new object[] { formula } } };
        var request = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, cell);
        // Pretend we're a user typing in this value...
        request.ValueInputOption = ValueInputOptionEnum.USERENTERED;
        // Get the computed value back in the response
        request.IncludeValuesInResponse = true;
        // Retrieve the value unformatted (so as a number in the JSON)
        // rather than either the uncomputed value, or the formatted value as a string
        request.ResponseValueRenderOption = ResponseValueRenderOptionEnum.UNFORMATTEDVALUE;
        var response = request.Execute();
        Console.WriteLine($"Result: {response.UpdatedData.Values[0][0]}");
    }
}

Upvotes: 3

Related Questions