我可以使用以下代码检索和更新Google表格中的值:
private void btnUpdate_Click(object sender, EventArgs e)
{
if (cbYards.Text == "Select Yard")
{
MessageBox.Show(@"Please select a yard.");
return;
}
UserCredential credential;
using (var stream =
new FileStream("client_secret.json", FileMode.Open, FileAccess.Read))
{
string credPath = System.Environment.GetFolderPath(
System.Environment.SpecialFolder.Personal);
credPath = Path.Combine(credPath, ".credentials/sheets.googleapis.com-dotnet-quickstart.json");
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
Console.WriteLine("Credential file saved to: " + credPath);
}
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
IList<IList<Object>> list = new List<IList<Object>>() { };
for (var i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
var formula = "=IFERROR(VLOOKUP(B"+(i+2)+",Names!$A$2:$B,2,FALSE),"No Record")";
List<object> lists = new List<object>() { formula, dataGridView1.Rows[i].Cells[0].Value.ToString(),
dataGridView1.Rows[i].Cells[1].Value.ToString() };
list.Add(lists);
}
var range = cbYards.Text+"!A2:C";
ValueRange VRange = new ValueRange();
VRange.Range = range;
VRange.Values = list;
//ValueRange response = request.Execute();
ValueRange valueRange = new ValueRange();
valueRange.MajorDimension = "COLUMNS";
SpreadsheetsResource.ValuesResource.UpdateRequest upd = service.Spreadsheets.Values.Update(VRange, spreadsheetId, range);
upd.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
UpdateValuesResponse response = upd.Execute();
}
我的问题是如何在当前使用的Google表格中创建新表格。我以为我需要做的就是更换
SpreadsheetsResource.ValuesResource.UpdateRequest upd = service.Spreadsheets.Values.Update(VRange, spreadsheetId, range);
自
SpreadsheetsResource.ValuesResource.UpdateRequest upd = service.Spreadsheets.create();
但显然这是错误的...
我不明白如何实现文档中的说明 方法:电子表格.创建
JSon 与我将如何用 C# 编码有什么关系?非常感谢您的帮助。
更新我在这里找到了这个,但它并不完整,因为它仍然给我一个错误
其他信息:对象引用未设置为对象的实例。
这是更新的代码:
private void button1_Click(object sender, EventArgs e)
{
UserCredential credential;
using (var stream =
new FileStream("client_secret.json", FileMode.Open, FileAccess.Read))
{
string credPath = System.Environment.GetFolderPath(
System.Environment.SpecialFolder.Personal);
credPath = Path.Combine(credPath, ".credentials/sheets.googleapis.com-dotnet-quickstart.json");
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
Console.WriteLine("Credential file saved to: " + credPath);
}
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
string sheetName = string.Format("{0} - {1}-{2}", cbYards.Text, fromDate.Value.ToShortDateString(), toDate.Value.ToShortDateString());
var myNewSheet = new Google.Apis.Sheets.v4.Data.Spreadsheet();
myNewSheet.Properties = new SpreadsheetProperties();
myNewSheet.Properties.Title = sheetName;
var newSheet = service.Spreadsheets.Create(myNewSheet).Execute();
}
客户端库中有一个用于创建的方法,您只需将新的工作表对象传递给它。不要忘记在分配标题之前实例化属性(下面的第 2 行)
var myNewSheet = new Google.Apis.Sheets.v4.Data.Spreadsheet();
myNewSheet.Properties = new SpreadsheetProperties();
myNewSheet.Properties.Title = "Daimtos awsom sheet";
var awsomNewSheet= service.Spreadsheets.Create(myNewSheet).Execute();
注意:我同意你的观点,文档页面是无用的。
以下代码段正在我这边工作:
var addSheetRequest = new AddSheetRequest();
addSheetRequest.Properties = new SheetProperties();
addSheetRequest.Properties.Title = sheetName;
BatchUpdateSpreadsheetRequest batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest();
batchUpdateSpreadsheetRequest.Requests = new List<Request>();
batchUpdateSpreadsheetRequest.Requests.Add(new Request { AddSheet = addSheetRequest });
var batchUpdateRequest = service.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, SpreadsheetId);
batchUpdateRequest.Execute();
您获得空引用的原因是,您不能拥有没有工作表的电子表格。
var myNewSheet = new Google.Apis.Sheets.v4.Data.Spreadsheet();
myNewSheet.Properties = new SpreadsheetProperties();
myNewSheet.Properties.Title = sheetName;
var sheet = new Sheet();
sheet.Properties = new SheetProperties();
sheet.Properties.Title = "Sheet1";
myNewSheet.Properties.Sheets = new List<Sheet>() { sheet };
var newSheet = service.Spreadsheets.Create(myNewSheet).Execute();
更新后的解决方案运行良好。但是我遇到了"请求没有足够的身份验证范围"的问题,因为存储的凭据允许只读访问。我通过删除用户凭据(以前存储在/Users/yourUserName/.credentials/sheets.googleapis.com-projectName/*中)并再次执行应用程序以获取新凭据解决了该问题