0

So, I am trying to append into a Google sheet using the Sheets API v4 through a C# project. But, I am getting an exception saying - Object reference not set to an instance of an object at my response.

I am passing all the required parameters for my request method.

class Program
{
    static string[] Scopes = { SheetsService.Scope.Spreadsheets, SheetsService.Scope.DriveFile, SheetsService.Scope.Drive };
    static string ApplicationName = "Google Sheets API .NET Quickstart";
    static void Main(string[] args)
    {
        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);
        }

        // Create Google Sheets API service.
        var service = new SheetsService(new BaseClientService.Initializer()
        {
            HttpClientInitializer = credential,
            ApplicationName = ApplicationName,
        });

        // Define request parameters.
        String spreadsheetId = "1sxMw83gkCDvRpzTYXQOKg2VMI-LetU7INrtXgsLZ0uo";
        String range = "Sheet1";

        ValueRange vr = new ValueRange();
        IList<Object> c = new List<Object> { "TESTING CELL" };
        IList<IList<Object>> abc = new List<IList<Object>>();
        abc.Add(c);
        vr.Values = abc;

        SpreadsheetsResource.ValuesResource.AppendRequest request = service.Spreadsheets.Values.Append(vr, spreadsheetId, range);
        SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum valueInputOption = (SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum) 2;
        SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum insertDataOption = (SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum) 2;
        request.ValueInputOption = valueInputOption;
        request.InsertDataOption = insertDataOption;

        AppendValuesResponse response = request.Execute();
        Console.WriteLine(Newtonsoft.Json.JsonConvert.SerializeObject(response));
    }
}

Update

Just remove the insertDataOption and the appending will work. It is not a mandatory field.

Gautam Gadipudi
  • 21
  • 5
  • That's **not** a compiler message, it's an _exception_. And that exception has a _stack trace_ that tells you exactly _where_ the problem occurs. By debugging you can see _which_ variable is null and then investigate why it is like that. – René Vogt Apr 23 '18 at 12:02
  • Got the mistake! The insertDataOption was not being mapped to it's property although it's an enum type. But, since insertDataOption is not a mandatory propery of the request, if you don't include it, the appending works like a charm! :) – Gautam Gadipudi Apr 25 '18 at 08:57

0 Answers0