自我LV1

做法如下:

1-需要在Google Cloud Console中建立一個項目，並啟用Google Sheets API

2-建立服務帳戶或API憑證，以便在C#程式碼中進行身份驗證，要先取得OAUTH2.0的同意

3-在C#專案中，使用Google.Apis.Sheets.v4庫來設定Google Sheets API客戶端，用NuGet套件管理器安裝該庫

4-按照API文件中的範例程式碼進行操作，以取得資料。

 

注意:

申請OAUTH2.0是用外部公開的方式，所以憑証下載後要保管好，不然GOOGLE帳戶就等於公開了

要讀取Google sheets的資料，還需要將建好的服務帳戶(GAMIL)，加到要讀的Google sheets權限中

CODE如下:

using Google.Apis.Auth.OAuth2;
using Google.Apis.Sheets.v4;
using Google.Apis.Sheets.v4.Data;
using Google.Apis.Util.Store;
using System;
using System.IO;
using System.Threading;
using System.Threading.Tasks;

class Program
{
    static string[] Scopes = { SheetsService.Scope.Spreadsheets };
    static string ApplicationName = "Google Sheets to Database";

    static async Task Main()
    {
        string spreadsheetId = "YourSpreadsheetId";
        string range = "Sheet1!A1:C10";
        string credentialsPath = "client_secret_126586316141-62di5sr2lu7s6lfc96d3ul4k61al0s0c.apps.googleusercontent.com.json";

        // credentialsPath文件是否存在
        if (!File.Exists(credentialsPath))
        {
            Console.WriteLine($"Credentials file not found at path: {credentialsPath}");
            return;
        }

        var credential = await GetSheetsCredential(credentialsPath);

        var service = new SheetsService(new Google.Apis.Services.BaseClientService.Initializer()
        {
            HttpClientInitializer = credential,
            ApplicationName = ApplicationName
        });

        var data = GetGoogleSheetsData(service, spreadsheetId, range);

        // SqlConnection
        using (SqlConnection connection = new SqlConnection("YourConnectionString"))
        {
            connection.Open();

            // 将WriteDataToDatabase
            WriteDataToDatabase(connection, data);
        }
    }

    static async Task<UserCredential> GetSheetsCredential(string credentialsPath)
    {
        using (var stream = new FileStream(credentialsPath, FileMode.Open, FileAccess.Read))
        {
            string credPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
            credPath = Path.Combine(credPath, ".credentials/sheets.googleapis.com-dotnet-quickstart.json");

            return await GoogleWebAuthorizationBroker.AuthorizeAsync(
                GoogleClientSecrets.Load(stream).Secrets,
                Scopes,
                "user",
                CancellationToken.None,
                new FileDataStore(credPath, true));
        }
    }

    // 讀取 Google Sheets 的方法
    static List<List<object>> GetGoogleSheetsData(SheetsService sheetsService, string spreadsheetId, string range)
    {
        return new List<List<object>>(); // 返回List
    }

    // WriteDataToDatabase
    static void WriteDataToDatabase(SqlConnection connection, List<List<object>> data)
    {       
        // ...
    }
}

 

自我LV~