How to find a real range of the sheet using C#?-CodePudding

I need help in determining the real range of my spreadsheet as I don't know the last row number as the data as it gets imported from an external source. I am creating a small program to find if a certain columns have a "null" value and send the details to Slack. Once I reach an empty row, I need to terminate the code. Attaching a small screenshot. enter image description here

public static void ReadEntries()
        {
            var range = $"{Sheet}!A3:AW28";
            SpreadsheetsResource.ValuesResource.GetRequest request =
                    _service.Spreadsheets.Values.Get(SpreadsheetId, range);

    
            
            var response = request.Execute();


            IList<IList<object>> values = response.Values;
            if (values != null && values.Count > 0)
            {
                foreach (var row in values)
                {

                    var n = 0;
                    if (row[n].ToString().Trim() == null)
                    {
                        break;
                    }
                    
                    //var rowCount = worksheet.Dimension.End.Row;

                    //Console.WriteLine("{0} | {1} | {2} | {3}", row[45], row[46], row[47], row[48]);

                    //if (row[n] == "-")
                    for (int i = 0; i <49;i  )
                    //else if (row[n].ToString().Trim()== "null" && n<49)
                    {
                        
                        
                        var client1 = new RestClient("<slack webhook>");
                        client1.Timeout = -1;
                        var request1 = new RestRequest(Method.POST);
                        var body = "<message_payload>";
                        request1.AddParameter("application/json", body, ParameterType.RequestBody);
                        
                       


                        //IRestResponse response1 = client1.Execute(request1);
                        if (row[i].ToString().Trim() == "null")
                        {
                            Console.WriteLine("OK"   i);
                        }
                        
                        else if (row[i].ToString().Trim() == "")
                        {
                            Console.WriteLine("Its Over"   i);
                        }

                        //Thread.Sleep(1000);
                        //n  ;
                    }
                    

                   

                }
            }
            else
            {
                Console.WriteLine("No data found.");
            }

Now, I have tried creating a range variable but the script runs till the end and sends the message on Slack even though there is no data in the last rows. I need it to stop once it reaches the end of the data, like 29th row in the screenshot. I have tried searching for an answer and found a line that can find the last row and probably the real range of my spreadsheet, but it does not work for some reason -

var rowCount = worksheet.Dimension.End.Row;

I have also tried to identify a blank column by converting the response cell to string like this -

 if (row[n].ToString().Trim() == null)
                    {
                        break;
                    }

But I don't think it is working correctly. I am really sorry if I did not explain it properly, english is not my first language but I will try to share more details if needed.

CodePudding user response：

if (string.IsNullOrEmpty(row[n]?.ToString().Trim()))
{
break;

string.IsNullOrWhiteSpace

Try like that

CodePudding user response：

Is this what you seek: (not sure)

function myfunk() {
  const sh  = SpreadsheetApp.getActiveSheet();
  const rg = `${sh.getName()}!${sh.getDataRange().getA1Notation()}`;
  Logger.log(rg);
  return rg;
}