c# - Updating a docs.google.com cell is updating the wrong cell, has anyone ran into this issue before? - Stack Overflow

Using Google.Apis.Sheets.v4 in C#, appending a value onto specific cells is causing the value to go to the last unused row in column A. I'm able to update AK15 with a value but updating AK6 isn't working. I've updated 50+ columns in AI-AL but cells AK6, AK5, and AJ4 is causing this issue every time I try to update them. Has anyone else had this issue?

UserCredential credential;
using (var stream =
        new FileStream(ReadXMLFileForSettings.Configs["gCredLocation"], FileMode.Open, FileAccess.Read))
{
    // The file token.json stores the user's access and refresh tokens, and is created
    // automatically when the authorization flow completes for the first time.
    string credPath = ReadXMLFileForSettings.Configs["TokenLocation"];
    credential = await GoogleWebAuthorizationBroker.AuthorizeAsync(
        GoogleClientSecrets.Load(stream).Secrets,
        Scopes,
        "user",
        CancellationToken.None,
        new FileDataStore(credPath, true));
}

// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
    HttpClientInitializer = credential,
    ApplicationName = "Check Underperformance VAs"
});

int counter = 0;
foreach (var monthCounter in MonthCounters)
{
    int row = monthCounter.batch - 65;

    //get the column Letter based on 
    int column = (monthCounter.year - 2022) * 12;
    string columnLetter = string.Empty;

    while (column > 0)
    {
        column--;  // Decrement to make it 0-indexed
        columnLetter = (char)(column % 26 + 'A') + columnLetter;
        column /= 26;
    }

    string Cell = "Underperformance Heat Map!" + columnLetter + row;

    counter++;

    var newValue = new ValueRange()
    {
        Values = new[] { new[] { counter.ToString() } }
    };

    var updateRequest = service.Spreadsheets.Values.Append(newValue, SpreadsheetId, Cell);
    updateRequest.ValueInputOption = (SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum?)SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;

    // Get the response from the update request
    var result = await updateRequest.ExecuteAsync().ConfigureAwait(false);
    Logging.WriteToLog($"Updated {Cell} - {counter}");
}

Using Google.Apis.Sheets.v4 in C#, appending a value onto specific cells is causing the value to go to the last unused row in column A. I'm able to update AK15 with a value but updating AK6 isn't working. I've updated 50+ columns in AI-AL but cells AK6, AK5, and AJ4 is causing this issue every time I try to update them. Has anyone else had this issue?

UserCredential credential;
using (var stream =
        new FileStream(ReadXMLFileForSettings.Configs["gCredLocation"], FileMode.Open, FileAccess.Read))
{
    // The file token.json stores the user's access and refresh tokens, and is created
    // automatically when the authorization flow completes for the first time.
    string credPath = ReadXMLFileForSettings.Configs["TokenLocation"];
    credential = await GoogleWebAuthorizationBroker.AuthorizeAsync(
        GoogleClientSecrets.Load(stream).Secrets,
        Scopes,
        "user",
        CancellationToken.None,
        new FileDataStore(credPath, true));
}

// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
    HttpClientInitializer = credential,
    ApplicationName = "Check Underperformance VAs"
});

int counter = 0;
foreach (var monthCounter in MonthCounters)
{
    int row = monthCounter.batch - 65;

    //get the column Letter based on 
    int column = (monthCounter.year - 2022) * 12;
    string columnLetter = string.Empty;

    while (column > 0)
    {
        column--;  // Decrement to make it 0-indexed
        columnLetter = (char)(column % 26 + 'A') + columnLetter;
        column /= 26;
    }

    string Cell = "Underperformance Heat Map!" + columnLetter + row;

    counter++;

    var newValue = new ValueRange()
    {
        Values = new[] { new[] { counter.ToString() } }
    };

    var updateRequest = service.Spreadsheets.Values.Append(newValue, SpreadsheetId, Cell);
    updateRequest.ValueInputOption = (SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum?)SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;

    // Get the response from the update request
    var result = await updateRequest.ExecuteAsync().ConfigureAwait(false);
    Logging.WriteToLog($"Updated {Cell} - {counter}");
}

• c#
• google-sheets
• google-api

Share Improve this question Follow asked Jan 30 at 1:04 MeneghiniMeneghini 18711 gold badge22 silver badges1414 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

The issue was having service.Spreadsheets.Values.Append. If the cell already had a value in it, the API will not update the cell.

Changed:

var updateRequest = service.Spreadsheets.Values.Append(newValue, SpreadsheetId, Cell);
    updateRequest.ValueInputOption = (SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum?)SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;

to:

var updateRequest = service.Spreadsheets.Values.Update(newValue, SpreadsheetId, Cell);
updateRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;