Handling hyperlinks in the Google Sheets C# SDK

December 11, 2020

Recently, when writing a script to import data from Google Sheets into a database using the C# SDK, I came across a problem handling pasted hyperlinks in the source spreadsheet. The solution in the end was, thankfully, fairly straightforward, but Google’s documentation was quite poor, and I found little useful information on the web. Hopefully this post will save a few developers some time (and prevent a few grey hairs in the process).

As Martin Hawksey explains in his blog, Google Sheets (and indeed Excel) handles hyperlinks in three different ways:

• Plain text that Google sheets detects as links
• The HYPERLINK formula
• Hidden links – scenarios such as copy/pasting links from a website to Google Sheets

The SDK handles the first two types of link without problems, but when it comes to the third kind, it simply returns the link title, losing the link address! This seems a particularly poor design, but there we are.

In Martin’s example, the C# SDK would process the first two links correctly, but would only return HOME for the third link, losing the address entirely.

Fortunately, the REST API can be made to return both the title and the address, by specifying the sheets/data/rowData/values/userEnteredValue and sheets/data/rowData/values/hyperlink fields:

$ curl \
  'https://sheets.googleapis.com/v4/spreadsheets/1UAAnqHrIL9fvVSq626NcoBcPwJ5U-jrnmoqeV6pFBD0?ranges=B1%3AB3&fields=sheets%2Fdata%2FrowData%2Fvalues%2FuserEnteredValue%2Csheets%2Fdata%2FrowData%2Fvalues%2Fhyperlink&key=[YOUR_API_KEY]' \
  --header 'Authorization: Bearer [YOUR_ACCESS_TOKEN]' \
  --header 'Accept: application/json' \
  --compressed

The response will look something like this:

{
  "sheets": [
    {
      "data": [
        {
          "rowData": [
            {
              "values": [
                {
                  "userEnteredValue": {
                    "stringValue": "https://developers.google.com/apps-script/"
                  },
                  "hyperlink": "https://developers.google.com/apps-script/"
                }
              ]
            },
            {
              "values": [
                {
                  "userEnteredValue": {
                    "formulaValue": "=HYPERLINK(\"https://developers.google.com/apps-script/\", \"This is a HYPERLINK\")"
                  },
                  "hyperlink": "https://developers.google.com/apps-script/"
                }
              ]
            },
            {
              "values": [
                {
                  "userEnteredValue": {
                    "stringValue": "HOME"
                  },
                  "hyperlink": "https://developers.google.com/apps-script/"
                }
              ]
            }
          ]
        }
      ]
    }
  ]
}

By reverse-engineering the SDK, I was able to determine how it builds up the request, and found that it exposes the authenticated HTTP client that it uses to send requests. From there, it was a simple matter to construct the appropriate request, grab the client, and parse the response JSON:

// Configure and authorise service
using var stream = new FileStream("path/to/credentials", FileMode.Open, FileAccess.Read, FileShare.Read);

var credential = (ServiceAccountCredential)GoogleCredential.FromStream(stream).UnderlyingCredential; // this example is for a service account, but you could also use web authorisation
credential = new ServiceAccountCredential(new ServiceAccountCredential.Initializer(credential.Id)
{
    User = "MyUsername",
    Key = credential.Key,
    Scopes = new[] { SheetsService.Scope.Spreadsheets }
});

var options = new BaseClientService.Initializer
{
    HttpClientInitializer = credential,
    ApplicationName = "MyApplication",
};

var sheetsService = new SheetsService(options);

// Define the request, specifying the 'hyperlink' field
var request = sheetsService.Spreadsheets.Get(mySpreadsheetId);
request.Ranges = new Repeatable<string>(new[] { myRange });
request.Fields = "sheets/data/rowData/values/userEnteredValue,sheets/data/rowData/values/hyperlink";

// Make the http request, and parse the response
using var httpRequest = request.CreateRequest();
var response = await sheetsService.HttpClient.SendAsync(httpRequest);
var content = await response.Content.ReadAsStringAsync();
var rows = JsonConvert.DeserializeObject<GoogleSheetsResponse>(content); // Newtonsoft.Json

The response structure has a bit of nesting, so I defined the following classes to deserialise it into:

public class GoogleSheetsResponse
{
    public Sheet[] Sheets { get; set; }
}

public class Sheet
{
    public Data[] Data { get; set; }
}

public class Data
{
    public RowData[] RowData { get; set; }
}

public class RowData
{
    public Value[] Values { get; set; }
}

public class Value
{
    public UserEnteredValue UserEnteredValue { get; set; }
    public string Hyperlink { get; set; }

    public string GetString() => UserEnteredValue?.StringValue ?? UserEnteredValue?.NumberValue?.ToString();

    public long GetNumber() =>
        UserEnteredValue?.NumberValue ??
            (long.TryParse(UserEnteredValue?.StringValue, out var parsed) ? parsed : (long?)null) ??
                throw new InvalidOperationException();
}

public class UserEnteredValue
{
    public string StringValue { get; set; }
    public long? NumberValue { get; set; }
}

So there we have it. I do hope Google update their SDK to handle this, or at least improve the documentation, but for now, we have a viable workaround.

In my next post, I’ll be continuing the Google Sheets theme, looking at how to convert Excel workbooks into Google spreadsheets.