In C# how do I go through a Google Sheets document and write into a specific cell

Question

Following a tutorial, I have set up everything that needs to be set up for Google Sheets Api v4. In my Google Sheets documetnt, I have names of students in the first column, and in my second column I want to put their GPA. In my code, I made two variables that the user inputs, string name and string gpa. I want to go through column A, look for that name and insert that GPA next to it. I know I should probably use a for loop to go through the column, and compare every cell with the string the user typed, but nothing I tried so far worked.

I wrote a simple method that can get entries, for now it only prints but that can easily be changed:

static void ReadEntries() 
    {
        var range = $"{sheet}!A1:F10";
        var request = service.Spreadsheets.Values.Get(SpreadsheetId, range);

        var response = request.Execute();
        var values = response.Values;

        if(values != null && values.Count > 0) 
        {
            foreach(var row in values) 
                {
                    Console.WriteLine("{0} | {1}", row[0], row[1]);
                }
        }
        else 
            {
                Console.WriteLine("No data found");
            }
    }

and a method that can update a specific cell:

static void UpdateEntry() 
    { 
        var range = $"{sheet}!B2"; //example
        var valueRange = new ValueRange();

        var objectList = new List<object>() { "updated" };
        valueRange.Values = new List<List<object>> { objectList };

        var updateRequest = service.Spreadsheets.Values.Update(valueRange, SpreadsheetId, range);
        updateRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
        var updateResponse = updateRequest.Execute();
    }

EDIT: I need help with making a for loop to go through my A column and find the student with the same name. I know how to update a cell. I just don't know how to find a cell that needs updating.

Does this answer your question? [Update a Cell with C# and Sheets API v4](https://stackoverflow.com/questions/37462887/update-a-cell-with-c-sharp-and-sheets-api-v4) — ChoKaPeek, Nov 20 '20 at 10:40
Nope. They already know which cell to update. I don't. I need to make a loop through my column A to see which cell in column B needs updating. — Kaji, Nov 20 '20 at 10:52

score 1 · Answer 1 · answered Nov 20 '20 at 11:29

Sounds like you are very close. You already have the value you are searching in row[0] in the loop, so all you need to track the row number through your loop.

if (values != null && values.Count > 0)
{
    int rowNo =0;
    foreach (var row in values)
    {
        rowNo ++;
        Console.WriteLine("{0} | {1}", row[0], row[1]);
     
        if (row[0].ToString() == "John")
        {
             string rangeToUpdate = $"{sheet}!B{rowNo}:B{rowNo}";
             ...  
        }
    }
}

You could also change from using a foreach to a standard for loop.

This seems to be exactly what I'm looking for. I'll test it as soon as I can. Than you so much!! — Kaji, Nov 20 '20 at 11:55

ChoKaPeek · Answer 2 · 2020-11-20T10:38:17.857

0

I'm not experienced in the .NET client library of the Sheets API.

However, having used the Sheets API with the node and python client libraries, I can point you to the documentation you should follow. This is the official API documentation, with code examples for each language having a Google-provided client library.

For example, here is the spreadsheets.values.update documentation that you use, with a code example for C#.

On to the question then:

According to the json representation of a ValueRange, ValueRange.Range does not seem optional even though it is redundant. You might need to add ValueRange.Range = range; in your code.
Plus, you are using SpreadsheetsResource.ValuesResource.AppendRequest instead of SpreadsheetsResource.ValuesResource.UpdateRequest in the definition of your ValueInputOption.

Let me know if it helped!

Update This also seems to be a duplicate of Update a cell with C# and Sheets API V4

edited Nov 20 '20 at 10:38

answered Nov 20 '20 at 10:32

ChoKaPeek

151
9

The problem with that Update a cell with C# link is that they already knows what cell he needs to update (F5 in their case). My problem is that I don't know how to find the cell I need. Let's say I want to find a student named John. John is somewhere in the document, in column A, but I don't know what row. I need to make a loop through the whole A looking for "John". I need something like for(...) { if(row[x][y] == "John) { update() – Kaji Nov 20 '20 at 10:48
This for loop must go in your "get" function. You want to find the indices x, y where row[x][y] == "John". Once you do, you can craft a range from these indices and update(). Relatively to "John", the cell to update might be [x, y + 1] or [x + 1, y], but the point is you do know it. – ChoKaPeek Nov 20 '20 at 11:52

In C# how do I go through a Google Sheets document and write into a specific cell

2 Answers2

Linked