Google api for c# reading empty Google sheet cell cause out of bound exception

Question

I am trying to load Google sheet data using SSIS script component.

Here is the Google sheet:

Here is the script in SSIS component (type of source) to read the sheet

        using System;
        using System.Data;
        using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
        using Microsoft.SqlServer.Dts.Runtime.Wrapper;
        using Google.GData;
        using Google.GData.Client;
        using Google.GData.Extensions;
        using Google.GData.Spreadsheets;

        [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
        public class ScriptMain : UserComponent
        {

            public override void PreExecute()
            {
                base.PreExecute();
            }

            public override void PostExecute()
            {
                base.PostExecute();

            }

            public override void CreateNewOutputRows()
            {
                //Create new Spreadsheet service

                SpreadsheetsService GoogleExcelService;
                GoogleExcelService = new SpreadsheetsService("Spreadsheet-Vikash-Test-App");
                //Connect with Google Spreadsheet services using Google account credentials
                GoogleExcelService.setUserCredentials("xxxx@gmail.com", "somepassword");

                //Create a new Spreadsheet Query
                SpreadsheetQuery query = new SpreadsheetQuery();
                //Pass the SpreadsheetQuery to Spreadsheet Feed
                SpreadsheetFeed myFeed = GoogleExcelService.Query(query);

                //The Spreadsheet Feed connects with Googel Spreadsheet services and returns feed. Each Feed represents one Google Spreadsheet.

                foreach (SpreadsheetEntry mySpread in myFeed.Entries)
                {
                    //Run the code logic if the Spreadsheet name is ProductDetails
                    if (mySpread.Title.Text == "testForSSIS")
                    {

                        WorksheetFeed wfeed = mySpread.Worksheets;
                        foreach (WorksheetEntry wsheet in wfeed.Entries)
                        {
                            //Run the code logic if worksheet name is Sheet1
                            if (wsheet.Title.Text == "Sheet1")
                            {

                                AtomLink atm = wsheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);

                                ListQuery Lquery = new ListQuery(atm.HRef.ToString());

                                ListFeed LFeed = GoogleExcelService.Query(Lquery);

                                //Each ListFeed represents a row so naviage through each listfeed and add them into Output Buffer.
                                foreach (ListEntry LmySpread in LFeed.Entries)
                                {
                                    Output0Buffer.AddRow();
                                    //Output0Buffer.strProduct = LmySpread.Elements[0].Value;
                                    //Output0Buffer.intQuantity = LmySpread.Elements[1].Value;

                                    Output0Buffer.AgeName = LmySpread.Elements[0].Value;
                                    Output0Buffer.LowerBound = Int32.Parse(LmySpread.Elements[1].Value);
                                    Output0Buffer.UpperBound = Int32.Parse(LmySpread.Elements[2].Value);


                                }

                            }
                        }
                    }
                }

            }

        }

I am no c# programmer but a BI developer using a little bit script here and there. But I guess you can probably run it independently without SSIS.

So the problem is for example I remove the 40 under Upperbound so that cell is empty. The script will report index out of bound exception. I have done some debugging and found when that cell is empty the row now just contains 2 elements, so this line will generate the error because now LmySpread.Elements[2] does not exist.

                                    Output0Buffer.UpperBound = Int32.Parse(LmySpread.Elements[2].Value);

I guess this is more of Google API than C# question. I am not sure if there is way to specify how many elements to read etc.

Answer 1

Output0Buffer.AgeName = LmySpread.Elements[0].Value;
if (LmySpread.Elements.Length >= 2)
{
    Output0Buffer.LowerBound = Int32.Parse(LmySpread.Elements[1].Value);
}
if (LmySpread.Elements.Length >= 3)
{
    Output0Buffer.UpperBound = Int32.Parse(LmySpread.Elements[2].Value);
}

Just check the number of elements in the row, and don't access the ones that aren't there!