Get last column in Google Sheets API v4 in c#

Question

I have seen many posts about finding the last row of a given column for Google Sheets API v4 in C#, but I can't seem to find anything about finding the last column of a given row. I didn't find any questions about this specifically - but if I'm mistaken please direct me to the right place.

In my sheet, I have headers for each column. Over time, I anticipate I will need to add or remove columns as needed - it would be great to not have to update my code every time this happens.

I'm at the beginning stages of writing my code that gathers my data from Google Sheets - but here is what I have so far. I know that I will need to change the way my variable "range" is written, just don't know what.

static void ReadEntries()
    {
        var range = $"{sheet}!A1:ET";
        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)
            {

                System.Diagnostics.Debug.WriteLine("{0} | {1} | {2}", row[0], row[1], row[2]);
            }
        }
        else
        {
            System.Diagnostics.Debug.WriteLine("No data found.");
        }
    }

EDIT: SOLVED I used the pseudo code provided by Nazi A for this. I was having issues with the if(row[col]) piece with casting and other system exceptions. It turns out foreach allows for us to not have to check if that row[col] is in range. Below is my final code in case anyone needs it in the future. I plan to let column "ET" declared in var range = $"{sheet}!A1:ET; be big enough to accommodate any future columns being added to my spreadsheet. Thanks for your help!

static void ReadEntries()
        {
            
            var range = $"{sheet}!A1:ET";
            var request = service.Spreadsheets.Values.Get(SpreadsheetId, range);
            var response = request.Execute();
            var values = response.Values;
            int max = 0;
            int currMax;
            
            if (values != null && values.Count>0)
            {
                foreach(var row in values)
                {
                    currMax = 0;
                    foreach(var col in row)
                    {
                        currMax++;
                    }

                    if (max < currMax)
                    {
                        max = currMax;
                    }
                } 
            }
            else
            {
                System.Diagnostics.Debug.WriteLine("No data found.");
            }

            System.Diagnostics.Debug.WriteLine(max);
        }

user_16118686 · Answer

So basically, you need to have a nested loop to traverse all rows and columns in values.

I have tested this psuedo code and worked but since I have no any means to run a C# code, this is all I can give to you. This is a pseudo code that should be readable to you.

var max = 0;
foreach(var row in values){
  var currMax = 0;

  foreach(var col in row){
    if(row[col]){ // as long as data exists, currMax will increment
      currMax++;
      continue;
    }
    break; // stop loop if last cell being checked is empty
  }

  if(max < currMax){ // assign the largest currMax to max
    max = currMax;
  }
}

So in this psuedo code, max will contain the value of the largest column of all rows in the range. this code above should replace your foreach call

If you have any questions, feel free to clarify below.

Get last column in Google Sheets API v4 in c#

1 Answers