0

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: Google Sheet Structure

Could someone help to point out where I did wrong? Thank you.

BabyHai
  • 89
  • 1
  • 9

0 Answers0