25

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); } } }
Improve this question

6 Answers 6

Reset to default
32

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!"); } } }
Improve this answer
Sign up to request clarification or add additional context in comments.

3 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/…
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;
Improve this answer

Comments

1

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();
Improve this answer

Comments

1

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) { } }
Improve this answer

Comments

0

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.

Improve this answer

2 Comments

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'.
0

First Create value range object like given below: 

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

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.