Google Sheets API (v4) and C# – Updating Cells

Writing an application in C# and interacting with the Google Sheets API?
We’ve found information on that can be an unfortunately hit or miss kind of situation out there – so we thought we’d share a little bit of what we’ve found to work quite well. In our example we’re going to assume you just want to update a particular cell, on a Google Sheet you’ve created.
Google actually does a really nice job of giving us a .NET Quickstart Guide but in some cases you want to do a little more – and if you’re new to C# or .NET development you might be scratching your head going “okay, where do I go from here?”
Well, with this example below, you can see pretty clearly how to go about creating something that will update a cell on a Google Sheet.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 |
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 { static string[] Scopes = { SheetsService.Scope.Spreadsheets}; static string ApplicationName = "<MYSpreadsheet>"; //update this! 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!"); } } } |
Assuming you’ve successfully completed that .NET quickstart – consider this code above a nice example of how to update a cell on a spreadsheet.
The key parts you will want to pay attention to is this section here:
1 2 3 4 5 6 |
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" }; |
As you can see you need to update the variable spreadsheetId2 to match your Google spreadsheets ID.
You will need to update the variable range2 to reflect the cell range you’re trying to update.
You will need to update the variable oblist to contain the values you want updated on the spreadsheet as well.
Now if you want to paste the values exactly as they are into the spreadsheet, no problem. The line that controls how that works is this one:
1 |
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW; |
There are essentially two variations of this:
.RAW means the values the user has entered will not be parsed and will be stored as-is. So if your value is “=SUM(A1:A3)”, it will be displayed exactly like that – it will not convert it to a formula.
.USER_ENTERED means the values will be parsed as if the user typed them into the UI. Numbers will stay as numbers, but strings may be converted to numbers, dates, etc. following the same rules that are applied when entering text into a cell via the Google Sheets UI. If you have forumula’s and stuff that you want converted – this would be the option for you.
This kind of gives you a rough ‘bare bones’ example of how you can interact with Google Sheets API v4 within a C# environment – specifically with regards to adding/changing cells on a particular spreadsheet.
If you have other cool snippets you’d like to share – show us in the comments!
Posted on: December 15, 2016, by : adminc