Update a Cell with C# and Sheets API v4

26,814

Solution 1

Here is a working example using a console to write a singe cell. Thank you everyone who assisted !!

using Google.Apis.Auth.OAuth2;
using Google.Apis.Sheets.v4;
using Google.Apis.Sheets.v4.Data;
using Google.Apis.Services;
using Google.Apis.Util.Store;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace GoogleSheetsAPI4_v1console
{
    class Program
    {
        // If modifying these scopes, delete your previously saved credentials
        // at ~/.credentials/sheets.googleapis.com-dotnet-quickstart.json
        static string[] Scopes = { SheetsService.Scope.Spreadsheets}; // static string[] Scopes = { SheetsService.Scope.SpreadsheetsReadonly };
        static string ApplicationName = "<MYSpreadsheet>";

        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);


                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,
            });



            String spreadsheetId2 = "<my spreadsheet ID>";
            String range2 = "<my page name>!F5";  // update cell F5 
            ValueRange valueRange = new ValueRange();
            valueRange.MajorDimension = "COLUMNS";//"ROWS";//COLUMNS

            var oblist = new List<object>() { "My Cell Text" };
            valueRange.Values = new List<IList<object>> { oblist };

            SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, spreadsheetId2, range2);
            update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
            UpdateValuesResponse result2 = update.Execute();

            Console.WriteLine("done!");

        }
    }
}

Solution 2

For anyone else that finds this thread. I got the exception below:

Exception Details: Google.GoogleApiException: Google.Apis.Requests.RequestError 'valueInputOption' is required but not specified [400] Errors [ Message['valueInputOption' is required but not specified] Location[ - ] Reason[badRequest] Domain[global]]

Solved with:

SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, spreadsheetId2, range2);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
UpdateValuesResponse result2 = update.Execute();

Source:

https://stackoverflow.com/a/38841369/3850405

However this caused my data to get a single quote (') before the value I entered, like this:

enter image description here

After reading the documentation I saw that the examples used "valueInputOption": "USER_ENTERED":

https://developers.google.com/sheets/api/samples/writing#write_to_multiple_ranges

After switching to ValueInputOptionEnum.USERENTERED everything worked as expected.

updateRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;

Solution 3

The file "Google.Apis.Sheets.v4.SpreadsheetsResource.cs" contains error: url "https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range}" not contains "?valueInputOption=RAW". Use this code temporarily:

String spreadsheetId = "rihhgfkjhgufhgHUGUJKGHUItgUIGUIgui";
String range = "Config!A2:E";
ValueRange valueRange=new ValueRange();
valueRange.Range = range;
valueRange.MajorDimension = "ROWS";//"ROWS";//COLUMNS
var oblist=new List<object>(){12,3,4,5u,6};
valueRange.Values = new List<IList<object>> {oblist};

var url = "https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range}?valueInputOption=RAW".Replace("{spreadsheetId}",spreadsheetId).Replace("{range}", HttpUtility.UrlEncode(range));

JObject json = null;
using (var client = new WebClient())
{
    client.Headers.Set("Authorization", "Bearer " + credential.Token.AccessToken);
    client.Headers.Set(HttpRequestHeader.AcceptEncoding, "gzip, deflate");
    client.Headers.Set(HttpRequestHeader.UserAgent, "myprogram database updater google-api-dotnet-client/1.13.1.0 (gzip)");
    ServicePointManager.Expect100Continue = false;
    var jsonstr = JsonConvert.SerializeObject(valueRange);
    var jsontemp = JObject.Parse(jsonstr);
    jsontemp.Remove("ETag");
    jsonstr = jsontemp.ToString();
    try
    {
        var res = client.UploadString(url, "PUT", jsonstr);
        json = JObject.Parse(res);
    }
    catch (Exception)
    {


    }
}

Solution 4

If you are using this tutorial https://developers.google.com/sheets/quickstart/dotnet Ensure you comment out the following line of code.

credPath = Path.Combine(credPath, ".credentials/sheets.googleapis.com-dotnet-quickstart.json");

Also this is what I used syntax wise and got it working.

String spreadsheetId2 = "<put yours here>";
String range2 = "<againyours>!F5";
ValueRange valueRange = new ValueRange();
valueRange.MajorDimension = "COLUMNS";//"ROWS";//COLUMNS

var oblist = new List<object>() { "ello" };
valueRange.Values = new List<IList<object>> { oblist };

SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, spreadsheetId2, range2);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
UpdateValuesResponse result2 = update.Execute(); 
Share:
26,814

Related videos on Youtube

Slim
Author by

Slim

Updated on August 20, 2020

Comments

  • Slim
    Slim about 3 years

    Does anyone have a good C# example for updating a cell with the v4 API?

    I have the get cell values c# example from the developer website working with Google Sheets API v4. I am trying to modify the example to update a cell with a value of "Tom". I'm stuck on the settings for SpreadSheets.Values.Update.

    using Google.Apis.Auth.OAuth2;
    using Google.Apis.Sheets.v4;
    using Google.Apis.Sheets.v4.Data;
    using Google.Apis.Services;
    using Google.Apis.Util.Store;
    using System;
    using System.Collections.Generic;
    using System.IO;
    using System.Linq;
    using System.Text;
    using System.Threading;
    using System.Threading.Tasks;
    
    namespace GoogleSheetsAPI4_v1console
        {
        class Program
        {
            // If modifying these scopes, delete your previously saved credentials
            // at ~/.credentials/sheets.googleapis.com-dotnet-quickstart.json
            static string[] Scopes = { SheetsService.Scope.Spreadsheet };
            static string ApplicationName = "TestSpreadsheet";
    
            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 = "<<myspreadsheetid>>";
                String range = "Sheet1!D5";  // single cell D5
                String myNewCellValue = "Tom";
                SpreadsheetsResource.ValuesResource.UpdateRequest request = service.Spreadsheets.Values.Update(<<what goes here?>>, spreadsheetId, range);
    
                // Prints the names and majors of students in a sample spreadsheet:
                // https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
                ValueRange response = request.Execute();
                IList<IList<Object>> values = response.Values;
                Console.WriteLine(values);
            }
        }
    }
    
  • Slim
    Slim over 7 years
    You are correct would definitely not be read only. I have changed to "SheetsService.Scope.Spreadsheet".
  • Slim
    Slim over 7 years
    I'm stuck on the syntax for Spreadsheets.Values.Update(<<what goes here?>>, spreadsheetId, range); parameters 'spreadsheetId' and 'range' are strings, while the first paremeter is listed as needing to be a 'body'.
  • mcmillab
    mcmillab almost 7 years
    this works well, but how does it translate to be part of a BatchUpdate? ie, if I wanted to update 2 (named) ranges in one request, how would I do that?
  • Rushabh Master
    Rushabh Master over 6 years
    Hello @Slim, This solution helped me but i want any solution which creates file on googleDrive Can you please help me!
  • WheretheresaWill
    WheretheresaWill about 5 years
    Thanks for this. I struggled setting up the access and service account, so wrote a blog post in case it helps someone: medium.com/@williamchislett/…