Google Sheet Import Integration

Last Updated: 2025-06-20

This guide walks you through integrating Google Sheets with a C# API, enabling manual or automated data imports from spreadsheets. In this example a simple Azure function is used.

1. Prerequisites

  • A Google Workspace or personal Google account with access to the target Sheet in Google Drive
  • A C# API project with the Google.Apis.Auth and Google.Apis.Sheets NuGet packages installed

2. Enable the Google Sheet API

  1. Log in to the Google Cloud Console
  2. Create a new project or select an existing one
  3. Go to APIs & Services > Dashboard
  4. Search for and select Google Sheets API
  5. Click Enable if it is not already enabled

3. Create a Service Account

  1. In the Google Cloud Console, go to APIs & Services > Credentials
  2. Click + Create Credentials and select Service account
  3. Enter a name and description, then click Create
  4. Assign a role (e.g., Basic > Viewer or Editor), then click Continue
  5. Skip or complete the optional steps, then click Done to complete the creation.
  6. On the Credentials page, click Edit on the newly created service account
    • Note of the email address
  7. Open the Keys tab
  8. Click Add Key > Create new key
  9. Select JSON format and click Create to download the private key file
  10. Store the key securely (e.g., in 1Password)
    • Note the expiration date of the key

4. Grant Sheet Access to the Service Account

  1. Open the Google Sheet
    • Copy the Sheet ID from the URL: https://docs.google.com/spreadsheets/d/{ID}/edit
    • Note the sheet name and column range, formatted like Sheet1!A:Z
  2. Click Share in the top-right corner
  3. Add the service account email and assign appropriate permissions (Viewer, Editor, etc.)
  4. Uncheck Notify people
  5. Click Share to grant access

5. Configuring the C# API

Note that below is a basic example of how to implement the functionaltiy. This should be adjusted based on requirements.

5.1. Config appsetting.json

For simplicity, the private key JSON is stored here to showcase the implmentation

{
  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "UseDevelopmentStorage=true",
    "FUNCTIONS_WORKER_RUNTIME": "dotnet-isolated",
    "GOOGLE_SHEETS_CREDENTIALS": "{ PRIVATE KEY JSON CONTENT }",
    "GOOGLE_SHEETS_SPREADSHEET_ID": "REDACTED",
    "GOOGLE_SHEETS_SHEET_RANGE": "Sheet1!A:Z"
  }
}

5.2. Azure Function (GoogleSheetsFunction.cs)

using Example.Google.Sheet.Model;
using Google.Apis.Auth.OAuth2;
using Google.Apis.Services;
using Google.Apis.Sheets.v4;
using Microsoft.Azure.Functions.Worker;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;

namespace Example.Google.Sheet;

public class GoogleSheetsFunction
{
    private readonly ILogger<GoogleSheetsFunction> _logger;

    public GoogleSheetsFunction(ILoggerFactory loggerFactory)
    {
        _logger = loggerFactory.CreateLogger<GoogleSheetsFunction>();
    }

    [Function("GoogleSheetsFunction")]
    public async Task Run([TimerTrigger("0 */1 * * * *")] TimerInfo myTimer)
    {
        try
        {
            _logger.LogInformation("Google Sheets import function started at: {Time}", DateTime.UtcNow);

            // Retrieve configuration values from environment variables
            string jsonText = Environment.GetEnvironmentVariable("GOOGLE_SHEETS_CREDENTIALS");
            string spreadsheetId = Environment.GetEnvironmentVariable("GOOGLE_SHEETS_SPREADSHEET_ID");
            string sheetRange = Environment.GetEnvironmentVariable("GOOGLE_SHEETS_SHEET_RANGE");

            // Validate required settings
            if (string.IsNullOrEmpty(jsonText) || string.IsNullOrEmpty(spreadsheetId) || string.IsNullOrEmpty(sheetRange))
            {
                _logger.LogError("Missing required environment variables: GOOGLE_SHEETS_CREDENTIALS, GOOGLE_SHEETS_SPREADSHEET_ID, or GOOGLE_SHEETS_SHEET_RANGE.");
                return;
            }

            // Authenticate using Service Account credentials
            // For demo purposes, the private key content is taken from appsetting.json. 
            // Using a JSON file location will also work e.g. GoogleCredential.FromFile("path/to/credentials.json")
            var credential = GoogleCredential.FromJson(jsonText)
                .CreateScoped(SheetsService.Scope.SpreadsheetsReadonly);

            var service = new SheetsService(new BaseClientService.Initializer()
            {
                HttpClientInitializer = credential,
                ApplicationName = "AzureFunction-GoogleSheets"
            });

            // Read Data from Google Sheets
            var request = service.Spreadsheets.Values.Get(spreadsheetId, sheetRange);
            var response = await request.ExecuteAsync();
            var values = response.Values;

            if (values == null || values.Count == 0)
            {
                _logger.LogWarning("No data found in the Google Sheet.");
                return;
            }

            // Convert rows into list of PatientRecord objects
            List<PatientRecord> patientRecords = [];

            foreach (var row in values)
            {
                try
                {
                    var patient = new PatientRecord
                    {
                        PatientRef = row.Count > 0 ? row[0]?.ToString() ?? "" : "",
                        Case = row.Count > 1 ? row[1]?.ToString() ?? "" : "",
                        TimeStamp = row.Count > 2 && DateTime.TryParse(row[2]?.ToString(), out var date) ? date : DateTime.MinValue,
                        Assessment = row.Count > 3 ? row[3]?.ToString() ?? "" : "",
                        PaymentAgreement = row.Count > 4 ? row[4]?.ToString() ?? "" : "",
                        ClientName = row.Count > 5 ? row[5]?.ToString() ?? "" : "",
                        ClientHouseNumberStreet = row.Count > 6 ? row[6]?.ToString() ?? "" : "",
                        ClientTownCity = row.Count > 7 ? row[7]?.ToString() ?? "" : "",
                        ClientCounty = row.Count > 8 ? row[8]?.ToString() ?? "" : "",
                        ClientPostcode = row.Count > 9 ? row[9]?.ToString() ?? "" : "",
                        ClientEmailAddress = row.Count > 10 ? row[10]?.ToString() ?? "" : "",
                        ClientPhoneNumber = row.Count > 11 ? row[11]?.ToString() ?? "" : "",
                        RelationshipToPatient = row.Count > 12 ? row[12]?.ToString() ?? "" : ""
                    };

                    patientRecords.Add(patient);
                }
                catch (Exception ex)
                {
                    _logger.LogError($"Error processing row: {JsonConvert.SerializeObject(row)} | {ex.Message}");
                }
            }

            // Log the data (Can be replaced with Azure SQL insert)
            foreach (var patient in patientRecords)
            {
                _logger.LogInformation($"Imported Patient: {JsonConvert.SerializeObject(patient)}");
            }

            _logger.LogInformation("Google Sheets import function completed successfully.");
        }
        catch (Exception ex)
        {
            _logger.LogError($"Error in Google Sheets Function: {ex.Message}");
        }

        if (myTimer.ScheduleStatus is not null)
        {
            _logger.LogInformation($"Next timer schedule at: {myTimer.ScheduleStatus.Next}");
        }
    }
}

Note: This example uses array indices. In practical cases, consider using header names for better resilience to column order changes.

Conclusion

By following this guide, your C# API should now be able to securely retrieve and process data from Google Sheets using a service account and Azure Functions.

Further Reading