如何讀取 Google Sheet

One-minute read
HowTo

設定 Google Sheet API Link to heading

  1. 到 https://console.cloud.google.com/ 建立新專案。
  2. 在新專案下
    • 建立憑證，目前是採用 服務帳戶 的設定並下載金鑰 client_secret.json

    將服務帳戶加到 sheet 共享名單中

    • 啟用 Google Sheet API，如果沒啟用會跳出以下錯誤

    Google Sheets API has not been used in project XXXX before or it is disabled. Enable it by visiting

  3. 使用以下程式碼 GoogleSheetsHelper
public class GoogleSheetsHelper
{
    public SheetsService Service { get; set; }
    private const string APPLICATION_NAME = "name";
    private static readonly string[] Scopes = { SheetsService.Scope.Spreadsheets, SheetsService.Scope.SpreadsheetsReadonly };

    public GoogleSheetsHelper()
    {
        InitializeService();
    }

    private void InitializeService()
    {
        var credential = GetCredentialsFromFile();
        Service = new SheetsService(new BaseClientService.Initializer()
        {
            HttpClientInitializer = credential,
            ApplicationName = APPLICATION_NAME
        });
    }

    private GoogleCredential GetCredentialsFromFile()
    {
        GoogleCredential credential;

        using (var stream = new FileStream("client_secret.json", FileMode.Open, FileAccess.Read))
        {
            credential = GoogleCredential.FromStream(stream).CreateScoped(Scopes);
        }
        return credential;
    }
}
  1. 外部實作
private void DoSomething()
{
    GoogleSheetsHelper gsh = new GoogleSheetsHelper();
    var _gsValues = gsh.Service.Spreadsheets.Values;
    var range = $"{sheetName}!A:B";
    var request = _gsValues.Get(sheetId, range);
    var response = request.Execute();
    IList<IList<object>> values = response.Values;
    //values is what you want
}

reference Link to heading