无法读取电子表格，出现错误

Question

我收到错误

mscorlib 中发生了类型为"Google.GoogleApiException"的未处理异常.dll 其他信息：Google.Apis.Requests.RequestError 调用方没有权限 [403] 错误 [ 消息[呼叫者没有权限] 位置[ - ] 原因[禁止] 域[全局]

]

或

请求具有无效的身份验证凭据。预期的 OAuth 2 访问令牌、登录 Cookie 或其他有效的身份验证凭据。 请参阅 https://developers.google.com/identity/sign-in/web/devconsole-project。[401]

请让我知道我需要编辑什么

string AccountFilePath = @"C:GFAppsMy AppGoogle ServiceServiceAccountJsonCREG Service-427e7b31069c.json";
string AccountEmail = "[e-mail]";
string AccountAdminUser = "[e-mail]";
string UserToImpersonate = null;
ServiceAccountCredential credential;
var credentialParameters = NewtonsoftJsonSerializer.Instance.Deserialize<JsonCredentialParameters>(File.ReadAllText(AccountFilePath));
using (var stream = new FileStream(AccountFilePath, FileMode.Open, FileAccess.Read)) {
    credential = ServiceAccountCredential.FromServiceAccountData(stream);
}

var credentialforuser = new ServiceAccountCredential(new ServiceAccountCredential.Initializer(AccountEmail) {
    Scopes = Scopes,
    User = string.IsNullOrEmpty(UserToImpersonate) ? AccountAdminUser : UserToImpersonate,
    Key = credential.Key
} .FromPrivateKey(credentialParameters.PrivateKey));
Console.WriteLine("read from spread sheet ");
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer() {
    HttpClientInitializer = credentialforuser,
    ApplicationName = ApplicationName,
});
// Define request parameters.
String spreadsheetId = "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms";
// String spreadsheetId = "1nWdSM90mG7qT8YcOucHc_3NdmLYLpZd3FPJOF4lXMhY";
String range = "Class Data!A2:E";
SpreadsheetsResource.ValuesResource.GetRequest request =
    service.Spreadsheets.Values.Get(spreadsheetId, range);
// Prints the names and majors of students in a sample spreadsheet:
// https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
ValueRange response = request.Execute();
IList<IList<Object>> values = response.Values;
if (values != null && values.Count > 0) {
    Console.WriteLine("Name, Major");
    foreach (var row in values) {
        // Print columns A and E, which correspond to indices 0 and 4.
        Console.WriteLine("{0}, {1}", row[0], row[4]);
    }
} else {
    Console.WriteLine("No data found.");
}

Answer 1

您似乎正在使用服务帐户。 服务帐户需要预先授权。 使用服务帐号电子邮件地址与服务帐号共享您的工作表，就像您共享任何其他用户一样，然后它将有权查看您的工作表

/// <summary>
/// Authenticating to Google using a Service account
/// Documentation: https://developers.google.com/accounts/docs/OAuth2#serviceaccount
/// </summary>
/// <param name="serviceAccountEmail">From Google Developer console https://console.developers.google.com</param>
/// <param name="serviceAccountCredentialFilePath">Location of the .p12 or Json Service account key file downloaded from Google Developer console https://console.developers.google.com</param>
/// <returns>AnalyticsService used to make requests against the Analytics API</returns>
public static SheetsService AuthenticateServiceAccount(string serviceAccountEmail, string serviceAccountCredentialFilePath, string[] scopes) {
    try
    {
        if (string.IsNullOrEmpty(serviceAccountCredentialFilePath))
            throw new Exception("Path to the service account credentials file is required.");
        if (!File.Exists(serviceAccountCredentialFilePath))
            throw new Exception("The service account credentials file does not exist at: " + serviceAccountCredentialFilePath);
        if (string.IsNullOrEmpty(serviceAccountEmail))
            throw new Exception("ServiceAccountEmail is required.");
        // For Json file
        if (Path.GetExtension(serviceAccountCredentialFilePath).ToLower() == ".json")
        {
            GoogleCredential credential;
            using (var stream = new FileStream(serviceAccountCredentialFilePath, FileMode.Open, FileAccess.Read))
            {
                credential = GoogleCredential.FromStream(stream).CreateScoped(scopes);
            }
            // Create the Analytics service.
            return new SheetsService(new BaseClientService.Initializer()
            {
                HttpClientInitializer = credential,
                ApplicationName = "Sheets Service account Authentication Sample",
             });
        }
        else if (Path.GetExtension(serviceAccountCredentialFilePath).ToLower() == ".p12")
        { // If its a P12 file
            var certificate = new X509Certificate2(serviceAccountCredentialFilePath, "notasecret", X509KeyStorageFlags.MachineKeySet | X509KeyStorageFlags.Exportable);
            var credential = new ServiceAccountCredential(new ServiceAccountCredential.Initializer(serviceAccountEmail)
            {
                Scopes = scopes
            } .FromCertificate(certificate));
            // Create the Sheets service.
            return new SheetsService(new BaseClientService.Initializer()
            {
                HttpClientInitializer = credential,
                ApplicationName = "Sheets Authentication Sample",
            });
        }
        else
        {
            throw new Exception("Unsupported Service accounts credentials.");
        }
    }
    catch (Exception ex)
    {
        throw new Exception("CreateServiceAccountSheetsFailed", ex);
    }
}

链接到上述示例 https://github.com/LindaLawton/Google-Dotnet-Samples/blob/master/Samples/Google%20Sheets%20API/v4/ServiceAccount.cs