Update a Cell with C# and Sheets API v4

google sheets api update cell
google sheets rest api
google sheets api v4 update
google sheets api rename sheet
google sheets api append row
google sheets api formula
get data from google sheets api
google sheets api query

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

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

        }
    }
}

Method: spreadsheets.values.update | Sheets API, PUT https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/ response should include the values of the cells that were updated. Determines if the update response should include the values of the cells that were updated. By default, responses do not include the updated values. If the range to write was larger than the range actually written, the response includes all values in the requested range (excluding trailing empty rows and columns).

I think you just have access to read your sheet because I can see :

static string[] Scopes = { SheetsService.Scope.SpreadsheetsReadonly };

Try to change "SheetsService.Scope.SpreadsheetsReadonly" by "SheetsService.Scope.Spreadsheet" or by "SheetsService.Scope.Drive". Maybe it's another syntax... If it's not a problem of authorization I can't help you, sorry... Good luck my friend.

Ps: On Xcode (objective-c) you must reset the simulator after modification of scope. For you (C#) I don't know.

Updating Spreadsheets | Sheets API, Aside from the data contained in its cells, a spreadsheet includes many other types of data, such The batchUpdate method lets you update any of these spreadsheet details. v4/spreadsheets/ spreadsheetId :batchUpdate. For cells with literals, this is the same as the userEnteredValue. This field is read-only. formattedValue string The formatted value of the cell. This is the value as it's shown to the user. This field is read-only. userEnteredFormat object ( CellFormat ) The format the user entered for the cell.

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

Google Sheets API (v4) and C# – Updating Cells – Pushpin Designs, Google Sheets API (v4) and C# – Updating Cells. Writing an application in C# and interacting with the Google Sheets API? We've found  This is code snippet. I have successfully connected to Google Sheets API v4 using C#. I found out that when a cell is blank or empty. I get this error: System.ArgumentOutOfRangeException: 'Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index' As you can see Input ID in cell E3 cell is

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


    }
}

Create, update & clear filters + Get filtered rows, In the code sample here, we're only exposing rows hidden by filter. https://​developers.google.com/sheets/api/reference/rest/v4/  The Sheets API allows you to update the formatting of cells and ranges within spreadsheets. The examples on this page illustrate how some common formatting operations can be achieved with the API. You can find more conditional formatting examples in the Conditional Formatting recipe page. In these examples, the placeholders spreadsheetId and

First Create value range object like given below:

Google.Apis.Sheets.v4.Data.ValueRange requestBody = new Google.Apis.Sheets.v4.Data.ValueRange();

.NET Core. Google Sheets API, read\write\update - equisept, In this tutorial we're going to explore Google Sheets API. Apis.Sheets.v4. remember about downloaded *.json ? It's time to move it Note: make sure your C# language version is set to at least 7.1 , it's needed for async Main. Browse the .NET reference documentation for the Google Sheets API. Read the Developer's guide for the Google API Client Library for .NET. Interact with this API in your browser using the APIs Explorer for the Google Sheets API. Downloading the library. Install the NuGet package: Google.Apis.Sheets.v4.

Google Sheets Read Write Update Operations using Dotnet Core , remove operations from Google Sheets using Dotnet Core Console Apllication C#. So, run this command. dotnet add package Google.Apis.Sheets.v4 You can Update a specific Cell in your Spreadsheet as well. Here're  Category Description; Add (and Duplicate): Add new objects (sometimes based on old ones, as in the Duplicate requests). Update (and Set): Update certain properties of an object, usually leaving the old properties alone (whereas Set requests will overwrite the prior data).

Create And Update Google SpreadSheet Via Google API.NET Library, Method to get the range of row and column from where we need to add data to the spreadsheet -- if there are no previous values added, start from  How can I do this? Right now I'm able to read like in the example with out any problems, and I've only found one other reference to v4 C#. I tried going back to v3, but all documents strongly suggest using v4. Has anyone been able to do this? Here's all I've been able to do so far: // Create Google Sheets API service.

google-api-dotnet-client/Google.Apis.Sheets.v4.cs at master, Google APIs Client Library for .NET. Contribute to googleapis/google-api-dotnet-​client development by creating an account on GitHub. googlesheets4 provides an R interface to Google Sheets via the Sheets API v4. It is a reboot of an earlier package called googlesheets. Did I miss googlesheets1 through 3? The idea is to name the package after the corresponding version of the Sheets API. In hindsight, the original googlesheets should have been googlesheets3.

Comments
  • 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?
  • Hello @Slim, This solution helped me but i want any solution which creates file on googleDrive Can you please help me!
  • 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/…
  • You are correct would definitely not be read only. I have changed to "SheetsService.Scope.Spreadsheet".
  • 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'.