Adding note to cell in google sheets c#

Question

Is it possible to add notes to cells using Google? Apis.Sheets.v4?

It seems possible in python: Is it possible to use the Google Spreadsheet API to add a comment in a cell? but I've had no success duplicate it to c#, Insert a comment in a Google Sheet with google-sheets-api describes how to add it using an https call, but I would much rather use the google c# library than start sending HTTP calls myself if at all possible. It would be a huge help if anyone know-how, or can point me towards a working example.

Thanks in advance.

Using Rafa Guillermos advice i got it to work.

        public async void AddNote(string sheet, int column, int row, int sheetId, string noteMessage)
        {
            await Task.Delay(1);

            var requests = new List<Request>();


            // Grid range for a single cell, end column, and row have to be +1, otherwise, sheet throws error trying to write outside bounds.
            var gridRange = new Google.Apis.Sheets.v4.Data.GridRange
            {
                EndColumnIndex = column + 1, StartColumnIndex = column, EndRowIndex = row + 1, StartRowIndex = row, SheetId = sheetId
            };

            // Building a request for update cells.
            var request = new Google.Apis.Sheets.v4.Data.Request();
            request.UpdateCells = new Google.Apis.Sheets.v4.Data.UpdateCellsRequest();
            request.UpdateCells.Range = gridRange;
            request.UpdateCells.Fields = "note";
            request.UpdateCells.Rows = new List<Google.Apis.Sheets.v4.Data.RowData>();
            request.UpdateCells.Rows.Add(new Google.Apis.Sheets.v4.Data.RowData());
            request.UpdateCells.Rows[0].Values = new List<Google.Apis.Sheets.v4.Data.CellData>();
            request.UpdateCells.Rows[0].Values.Add(new Google.Apis.Sheets.v4.Data.CellData());
            request.UpdateCells.Rows[0].Values[0].Note = noteMessage;

            requests.Add(request);

            var requestBody = new Google.Apis.Sheets.v4.Data.BatchUpdateSpreadsheetRequest();
            requestBody.Requests = requests;

            var service = _authenticatorService.GetSheetsService(new[] { SheetsService.Scope.Spreadsheets} );

            var batchRequest = service.Spreadsheets.BatchUpdate(requestBody, _spreadsheetId);
            batchRequest.Execute();
        }

_authenticatorService gives an authenticated sheet service to work with.

score 0 · Accepted Answer · edited Nov 05 '21 at 05:15

0

Answer:

In exactly the same way as python, you need to build your note as a batch request in C#.

Code Snippets:

You need to build your data request as list like:

List<Data.Request> requests = new List<Data.Request>();

and assign the values to the request body for the batch:

Data.BatchUpdateSpreadsheetRequest requestBody = new Data.BatchUpdateSpreadsheetRequest();
requestBody.Requests = requests;

before building the request object:

SpreadsheetsResource.BatchUpdateRequest request = sheetsService.Spreadsheets.BatchUpdate(requestBody, spreadsheetId);

and execute the request:

Data.BatchUpdateSpreadsheetResponse response = request.Execute();

More Information:

You can read about spreadsheets.batchUpdate here with a C# example code at the bottom of the page here.

A JSON representation of the request resource can be found here which follows the same structure as the answer you linked here.

References:

edited Nov 05 '21 at 05:15

Nimantha

6,405
6
28
69

answered Mar 19 '20 at 13:47

Rafa Guillermo

14,474
3
18
54

Thanks for the quick reply :) Where in your code do you add the note? I use api v4, do you use another version that lets you add notes to ValueRange? Also what is your valueInputOption? it's a string so it would be nice to know what you used. – Malte Schou-Jensen Mar 19 '20 at 14:03
You need to make the request an [List object](https://learn.microsoft.com/en-us/dotnet/api/system.collections.generic.list-1?view=netframework-4.8) in the same form as the way the structure is needed as per the request documentation. Did you look at the links I provided? The .NET example provided by Google walks you through the process quite nicely. – Rafa Guillermo Mar 19 '20 at 14:42
I've read them before and after you've sent your solution. You use Data.BatchUpdateValuesRequest requestBody = new Data.BatchUpdateValuesRequest(); in your reply, was it meant as a new BatchUpdateSpreadsheetRequest()? sheetsService.Spreadsheets.BatchUpdate does not accept the former. If not and you meant to use .Spreadsheets.Values.BatchUpdate instead, what ValueInputOption do you use? – Malte Schou-Jensen Mar 19 '20 at 14:54
You are correct, my apologies I pasted the wrong line in. I have fixed that now. You need to build the `requests` with the `List.Add`, each object you add being a [`Data.Request` object](https://developers.google.com/resources/api-libraries/documentation/sheets/v4/csharp/latest/classGoogle_1_1Apis_1_1Sheets_1_1v4_1_1Data_1_1Request.html). – Rafa Guillermo Mar 19 '20 at 15:08
thanks for the help, got it to work. Can't upvote your solution as this is my first post, but I'll update the question with the solution and hope someone else can use it and upvote your response. – Malte Schou-Jensen Mar 20 '20 at 07:52

Adding note to cell in google sheets c#

1 Answers1

Answer:

Code Snippets:

More Information:

References: