1. 创建Google APIs项目
访问Google API控制台,点创建项目
比如项目名称叫“Google Sheets API TEST”
2. 启用API和服务
确保在“Google Sheets API TEST”项目下,点“启用API和服务”
找到并选择“Google Sheets API”
3. 创建凭据
把私钥名字改成“Google Sheets API TEST.json”,回头好用些
4. 创建Google Sheets
新建Google Sheets,比如叫“Google api test”。记下ID,回头代码里要用,这里是10lYeKozl1aR1EXf-Q0Vw2rf7oDnAFN4Rs9eB5iVR3fI
打开Google Sheets API TEST.json,找到client_email:
共享“Google api test”,给serviceeditor@circular-unity-278004.iam.gserviceaccount.com编辑权限
把工作表名改为Sheet1,回头代码里要用
5. 创建解决方案
在VS2017创建控制台应用,解决方案叫GSAPITEST
通过NuGet安装Google API包
使用命令
PM> Install-Package Google.Apis.Sheets.v4
添加Visual C#类,GoogleSheetsHelper.cs,代码如下
using Google.Apis.Auth.OAuth2;
using Google.Apis.Sheets.v4;
using Google.Apis.Sheets.v4.Data;
using Google.Apis.Services;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Dynamic;
namespace GoogleSheetsHelper
{
public class GoogleSheetsHelper
{
static string[] Scopes = {
SheetsService.Scope.Spreadsheets };
static string ApplicationName = "GoogleSheetsHelper";
private readonly SheetsService _sheetsService;
private readonly string _spreadsheetId;
public GoogleSheetsHelper(string credentialFileName, string spreadsheetId)
{
var credential = GoogleCredential.FromStream(new FileStream(credentialFileName, FileMode.Open)).CreateScoped(Scopes);
_sheetsService = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
_spreadsheetId = spreadsheetId;
}
public List<ExpandoObject> GetDataFromSheet(GoogleSheetParameters googleSheetParameters)
{
googleSheetParameters = MakeGoogleSheetDataRangeColumnsZeroBased(googleSheetParameters);
var range = $"{googleSheetParameters.SheetName}!{GetColumnName(googleSheetParameters.RangeColumnStart)}{googleSheetParameters.RangeRowStart}:{GetColumnName(googleSheetParameters.RangeColumnEnd)}{googleSheetParameters.RangeRowEnd}";
SpreadsheetsResource.ValuesResource.GetRequest request =
_sheetsService.Spreadsheets.Values.Get(_spreadsheetId, range);
var numberOfColumns = googleSheetParameters.RangeColumnEnd - googleSheetParameters.RangeColumnStart