I try to implement a feature to update Google Sheet by a button click in a MAUI app. Below are the related code:
GoogleSheetHelper class:
using Google.Apis.Auth.OAuth2;
using Google.Apis.Services;
using Google.Apis.Sheets.v4;
using Newtonsoft.Json;
using System.Reflection;
using Data = Google.Apis.Sheets.v4.Data;
public class GoogleSheetsHelper
{
public SheetsService Service { get; set; }
private const string APPLICATION_NAME = "TrackerDB";
private const string SPREADSHEET_ID = "XXXXXXXX";
private static readonly string[] Scopes = { SheetsService.Scope.Spreadsheets };
public GoogleSheetsHelper()
{
InitializeService();
}
private void InitializeService()
{
Service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = GetCredentialsFromFile(),
ApplicationName = APPLICATION_NAME
});
}
private GoogleCredential GetCredentialsFromFile()
{
GoogleCredential credential;
var assembly = Assembly.GetExecutingAssembly();
using var stream = assembly.GetManifestResourceStream("TrackerMAUI.Resources.Raw.client_secret.json");
credential = GoogleCredential.FromStream(stream).CreateScoped(Scopes);
return credential;
}
public string UpdateData(List<IList<object>> data)
{
string range = "Items!A2:C";
string valueInputOption = "USER_ENTERED";
List<Data.ValueRange> updateData = new List<Data.ValueRange>();
var dataValueRange = new Data.ValueRange();
dataValueRange.Range = range;
dataValueRange.Values = data;
updateData.Add(dataValueRange);
Data.BatchUpdateValuesRequest requestBody = new Data.BatchUpdateValuesRequest();
requestBody.ValueInputOption = valueInputOption;
requestBody.Data = updateData;
var request = Service.Spreadsheets.Values.BatchUpdate(requestBody, SPREADSHEET_ID);
Data.BatchUpdateValuesResponse response = request.Execute();
return JsonConvert.SerializeObject(response);
}
}
The Model, I am using the MVVM community tool to fullfill MVVM requirements:
public class PartTag
{
[PrimaryKey, AutoIncrement]
public int ID { get; set; }
public string modelID { get; set; }
public string location { get; set; }
}
The button click event:
private void OnCounterClicked(object sender, EventArgs e)
{
GoogleSheetsHelper gsh = new GoogleSheetsHelper();
var objectList = new List<object>() {
new PartTag { ID=1,modelID="JDNCH98789",location="Airport XY"},
new PartTag { ID=2,modelID="OPKJI97511",location="Airport XY"}
};
var rangeData = new List<IList<object>> { objectList };
gsh.UpdateData(rangeData);
}
The error I got is:
Google.GoogleApiException: 'The service sheets has thrown an exception. HttpStatusCode is BadRequest.
Invalid data[0]: Invalid values[1][0]: struct_value {
fields {
key: "ID"
value {
number_value: 1.0
}
}
fields {
key: "location"
value {
string_value: "Airport XY"
}
}
fields {
key: "modelID"
value {
string_value: "JDNCH98789"
}
}
}
'
The google sheet structure is as below:
Could someone help to point out where I did wrong? Thank you.