JDev.Templates.GoogleSheetsDB 1.0.2

Using this template requires you to use a Service Account to access your Google Sheets spreadsheets. 
Please review the Getting Started section for more details on how to set this up.

Jdev.Template.GoogleSheetsDB

A highly dynamic .NET 10.0 Web API that uses Google Sheets as a NoSQL database.

Unlike rigid hard-coded wrappers, this template uses Reflection and Custom Attributes to dynamically map your C# models to Spreadsheet columns. This means you can add new tables and columns instantly without rewriting the core repository logic.

🚀 Key Features

• Zero Boilerplate: Generic Repository pattern handles all CRUD operations.

• Dynamic Mapping: 1-to-1 mapping between C# Models and Sheet Tabs.

• Reflection-Based: No manual parsing; attributes define the structure.

• Swagger UI: Test your sheet data instantly.

🚀 Getting Started

1. Google Cloud Setup

To allow this API to read/write to your sheets without a human signing in every time, we use a Service Account. Think of this as a "Robot User" that acts on behalf of your API.

Why is this required? Standard Google logins require browser pop-ups (OAuth). Since this is a backend API running on a server, it cannot pop up a browser. Instead, it uses a cryptographic key (Service Account) to prove its identity silently.

Step-by-Step Instructions:

1. Create a Project:

   • Go to the Google Cloud Console.

   • Click the project dropdown (top left) and select New Project.

   • Name it (e.g., MySheetDB) and click Create.

2. Enable the Sheets API (Crucial Step):

   • Why? Google blocks all APIs by default. You must explicitly turn on the "Switch" for Sheets.

   • Go to APIs & Services > Library.

   • Search for "Google Sheets API".

   • Click Enable.

3. Create the Service Account:

   • Go to IAM & Admin > Service Accounts.

   • Click + CREATE SERVICE ACCOUNT (top center).

   • Service account name: Enter something like sheet-updater-bot.

   • Click Create and Continue.

   • (Optional) Grant this service account access to project: You can skip this (click Done). We will grant access directly on the Spreadsheet later.

4. Generate the Key (The Password):

   • In the Service Accounts list, you will see your new account. Click on its Email Address (e.g., sheet-updater-bot@...).

   • Navigate to the KEYS tab (top menu bar).

   • Click ADD KEY > Create new key.

   • Select JSON (Radio button) and click Create.

   • Action: A .json file containing your secret keys will automatically download to your computer.

5. Install the Key:

   • Open the downloaded JSON file with a text editor (Notepad, VS Code).

   • Copy the entire content.

   • Open google-sheet-credentials.json in your Visual Studio project.

   • Paste the content, replacing the dummy data.

2. Google Sheet Setup

Now that your "Robot" exists, you need to invite it to your spreadsheet.

1. Create the Sheet:

   • Go to sheets.google.com and create a new blank spreadsheet.

2. Invite the Robot:

   • Open your google-sheet-credentials.json file again.

   • Copy the value inside "client_email" (it looks like an email address).

   • In your Google Sheet, click the big Share button (top right).

   • Paste the client email into the box.

   • Ensure the role is set to Editor (so the API can write data).

   • Click Send (unchecked "Notify people" if you want).

3. Get the ID:

   • Look at your browser's address bar. The Spreadsheet ID is the long string of random characters between /d/ and /edit.

   • Example URL: https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit

   • The ID: 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms

4. Configure the API:

   • Open google-sheet-config.json in your project.

   • Paste the ID into the "SpreadsheetId" field.

📚 How to Add New Tables (Dynamic Models)

Because this solution uses Reflection, adding a new "Table" does not require changing the core service code. Just create a class and map it!

In this architecture, 1 Class Model = 1 Sheet Tab.

Step 1: Create the Model

Create a class in the Models folder. You must implement the ISheetEntity interface and use the [SheetColumn] attribute to map properties to spreadsheet columns.

Required Interface:

public interface ISheetEntity
{
     string Id { get; set; }
     public DateTime? CreatedAt { get; set; }
     public DateTime? UpdatedAt { get; set; }
}

Example Product.cs:

using GoogleSheetsCrudAPI.Attributes;
using GoogleSheetsCrudAPI.Models;

public class Product : ISheetEntity
{
    // Index 1 = Column A. This ID is required for CRUD operations.
    [SheetColumn(Index = 1, DisplayName = "ID", FieldType = SheetColumnFieldType.String)]
    public string Id { get; set; }

    [SheetColumn(Index = 2, DisplayName = "Product Name", FieldType = SheetColumnFieldType.String)]
    public string Name { get; set; }

    [SheetColumn(Index = 3, DisplayName = "Price", FieldType = SheetColumnFieldType.Currency)]
    public double? Price { get; set; }

    [SheetColumn(Index = 4, DisplayName = "In Stock", FieldType = SheetColumnFieldType.Boolean)]
    public bool IsInStock { get; set; }

    // Required by ISheetEntity
    [SheetColumn(Index = 5, DisplayName = "Created At", FieldType = SheetColumnFieldType.DateTime)]
    public DateTime? CreatedAt { get; set; }

    // Required by ISheetEntity
    [SheetColumn(Index = 6, DisplayName = "Updated At", FieldType = SheetColumnFieldType.DateTime)]
    public DateTime? UpdatedAt { get; set; }
}

Attribute Reference:

• Index: The 1-based column index (1=A, 2=B, 3=C, etc.).
• DisplayName: The header text in the first row of the sheet.
• FieldType: Helps the service parse data correctly (String, Number, Currency, PhoneNumber, DateTime, Boolean).

Step 2: Create the Sheet Tab

1. Open your Google Spreadsheet.
2. Add a new Tab (Sheet) at the bottom.
3. Rename the Tab (e.g., "Products").
4. Create the Header Row (Row 1) ensuring the order matches your Index attributes (e.g., A=ID, B=Name...).

Step 3: Map the Configuration

Open google-sheet-config.json and add an entry connecting your C# Model Name to the Sheet Tab Name.

{
  "SpreadsheetId": "your-spreadsheet-id-here",
  "SheetMappings": {
    "Record": "Sheet1",       // Example Mapping
    "Product": "Products"     // <--- Your New Mapping
  }
}

Step 4: Create the Controller

Create a new controller in Controllers/ProductController.cs.

[ApiController]
[Route("api/[controller]")]
public class ProductController : ControllerBase
{
    private readonly IGoogleSheetService<Product> _service;

    public ProductController(IGoogleSheetService<Product> service)
    {
        _service = service;
    }

    [HttpGet]
    public async Task<IActionResult> GetAll()
    {
        var data = await _service.GetAllAsync();
        return Ok(data);
    }
    
    // Implement other actions...
}

🎓 What You Will Learn From This Solution

This isn't just a template; it's a masterclass in modern C# techniques. By using this project, you will learn:

1. C# Reflection & Attributes:

• How to create custom attributes ([SheetColumn]).
• How to use Reflection to read properties at runtime and map them to dynamic data sources (like Google Sheets rows).

2. Google APIs Integration:

• How to authenticate using Service Accounts.
• How to navigate the Google Sheets API (reading ranges, appending rows, batch updates).

3. Generic Repository Pattern:

• How to write a single IGoogleSheetService<T> that works for any model (Product, User, Order, etc.).

4. Dependency Injection:

• Clean architecture setup for injecting services and configurations.