Comparing two list of rows based on Data in a selected cell using Smartsheet API

Question

I want to compare two list of row data. Right now I want to see if the two list contain the same title in their respective cell values.

What methods with using Smartsheet API C# could I use to sort through the list and compare each select element in each row?

I already have a column name table to search for the column name and reference the actual column id. But I can not seem to fathom how?

Any input would be helpful and I'm sorry if I sound plain dumb but I usually do not ask for help.

I have two sheets in Smartsheet. One sheet contains all the data that is given and as it goes through a process of acceptance or rejection. If completely accepted it is given a status of "Moved to Project". When the code runs it will place all rows with that status to a List that will then be used to move and compare against other list.

The Moved to Project List will be compared to our Project Management Active List.

I am stuck at trying to compare cell values through the API and maybe I'm just looking at it wrong. I've tried Enum Except to compare list but it is not working and I'm thinking I will need to create a nested loop to sort through and compare each element.

        foreach (Row row in rowsToCompare)
        {
            Cell PMOPName = getPMOCellByColumnName(row, "Project Name");
            foreach (Row innerrow in rowsToMove)
            {

                Cell MainTitle = getCellByColumnName(innerrow, "Title");

                if (PMOPName.DisplayValue == MainTitle.DisplayValue)
                {
                    Console.WriteLine("Yes");
                }
                else
                    Console.WriteLine("No");
            }
        }



    static Cell getCellByColumnName(Row row, string columnName)
    {
        return row.Cells.FirstOrDefault(cell => cell.ColumnId == 
        columnMap[columnName]);
    }

    static Cell getPMOCellByColumnName(Row row, string columnName)
    {
        return row.Cells.FirstOrDefault(cell => cell.ColumnId == 
        columnMapPMO[columnName]);
    }
}

Whenever there is a match of title and project name it should output yes and if not a no.

But instead I get a Unhandled Exception: System.ArgumentNullException: Value cannot be null.

I've pinpointed it to the nested loop. I'm sure I just did something stupid.

EDIT: So this is the definition of the map and how it get it's data.

static Dictionary<string, long> columnMap = new Dictionary<string, long>(); 

static Dictionary<string, long> columnMapPMO = new Dictionary<string, 
long();

// Build column map for later reference
   foreach (Column column in sheet.Columns)
      columnMap.Add(column.Title, (long)column.Id);

   foreach (Column column in pmosheet.Columns)
      columnMapPMO.Add(column.Title, (long)column.Id);

EDIT 2: Confirming with Tim the code works but in my instance it is still coming up with an error so I will place the code that I currently have as a whole to see if possible the other functions could be causing issues.

static void Main(string[] args)    
{

 SmartsheetClient ss = new SmartsheetBuilder()
             // TODO: Set your API access in environment variable 
 SMARTSHEET_ACCESS_TOKEN or else here
             .SetAccessToken(token.AccessToken)
             .Build();

        var sheet = ss.SheetResources.GetSheet(
            sheetId,                    // long sheetId
            null,                       // IEnumerable<SheetLevelInclusion> 
            includes
            null,                       // IEnumerable<SheetLevelExclusion> 
            excludes
            null,                       // IEnumerable<long> rowIds
            null,                       // IEnumerable<int> rowNumbers
            null,                       // IEnumerable<long> columnIds
            null,                       // Nullable<long> pageSize
            null                        // Nullable<long> page
        );

        var pmosheet = ss.SheetResources.GetSheet(
            copyId,
            null,
            null,
            null,
            null,
            null,
            null,
            null
        );

        // Build column map for later reference
        foreach (Column column in sheet.Columns)
            columnMap.Add(column.Title, (long)column.Id);

        foreach (Column column in pmosheet.Columns)
            columnMapPMO.Add(column.Title, (long)column.Id);

        // Accumulate rows needing update and archive here
        List<Row> rowsToMove = new List<Row>();
        List<Row> rowsToArchive = new List<Row>();
        List<Row> rowsToCompare = new List<Row>();

        //Loops through the Ideation Sheet and execute function to evaluate 
        //each row and add those row to the move list.

        foreach (Row row in sheet.Rows)
        {
            Row rowToMove = evaluateRowAndBuildUpdates(row);
            if (rowToMove != null)
            { 
                rowsToMove.Add(rowToMove);
            }
        }
        Console.WriteLine("\n");

        foreach (Row row in pmosheet.Rows)
        {
            Row rowtoCompare = compareRowandCopy(row);
            if (rowtoCompare != null)
                rowsToCompare.Add(rowtoCompare);
        }
        Console.WriteLine("\n");

        foreach (Row innerrow in rowsToMove)
        {
            Cell MainTitle = getCellByColumnName(innerrow, "Title");
            foreach (Row row in rowsToCompare)
            {

                Cell PMOPName = getPMOCellByColumnName(row, "Project Name");

                if (PMOPName.DisplayValue == MainTitle.DisplayValue)
                {
                    Console.WriteLine("Yes");
                    break;
                }
                else
                    Console.WriteLine("No");
            }
        }

          System.Environment.Exit(1); //End of Program
}

 static Row evaluateRowAndBuildUpdates(Row sourceRow)
    {
        Row rowToUpdate = null;

        // Find cell we want to examine
        Cell statusCell = getCellByColumnName(sourceRow, "Status");
        if (statusCell.DisplayValue == "Moved to Project")
        {
            Cell remainingCell = getCellByColumnName(sourceRow, "Status");
            Cell titleCell = getCellByColumnName(sourceRow, "Title");
            if (remainingCell.DisplayValue == "Moved to Project")                  
            {
                rowToUpdate = new Row
                {
                    Id = sourceRow.Id,

                };
                Console.WriteLine("Ideation");
            }

            Console.WriteLine(titleCell.DisplayValue + " ID: " + 
            sourceRow.Id.ToString());
        }
        return rowToUpdate;
    }

    static Row compareRowandCopy(Row sourceRow)
    {
        Row rowToCopy = null;

        Cell pmoStatusCell = getPMOCellByColumnName(sourceRow, "Project 
        Name");
        if (pmoStatusCell.DisplayValue != null)
        {
            rowToCopy = new Row
            {
                Id = sourceRow.Id,

            };
        }
        Console.WriteLine("PMO");
        Console.WriteLine(pmoStatusCell.DisplayValue + " ID: " + 
        sourceRow.Id.ToString());
        return rowToCopy;
    }

        static Cell getCellByColumnName(Row row, string columnName)
    {
        return row.Cells.FirstOrDefault(cell => cell.ColumnId == 
        columnMap[columnName]);
    }

    static Cell getPMOCellByColumnName(Row row, string columnName)
    {
        return row.Cells.FirstOrDefault(cell => cell.ColumnId == 
        columnMapPMO[columnName]);
    }

Answer 1

Ok, I have two sheets, the project sheet looks like this: And the job sheet containing the rows to be inserted looks like this: Here is the code:

using System;
using System.Collections.Generic;

// Add nuget reference to smartsheet-csharp-sdk (https://www.nuget.org/packages/smartsheet-csharp-sdk/)
using Smartsheet.Api;
using Smartsheet.Api.Models;
using System.Linq;

namespace sdk_csharp_sample
{
    class Program
    {
        static Dictionary<string, long> columnMap = new Dictionary<string, long>();

        static Dictionary<string, long> columnMapPMO = new Dictionary<string, long>();

        static void Main(string[] args)
        {
            // Initialize client
            SmartsheetClient ss = new SmartsheetBuilder()
                .SetHttpClient(new RetryHttpClient())
                .Build();

            heet insert = ss.SheetResources.GetSheet(...148L, null, null, null, null, null, null, null);

            Sheet pmosheet = ss.SheetResources.GetSheet(...556L, null, null, null, null, null, null, null);

            // Build column map for later reference
            foreach (Column column in insert.Columns)
                columnMap.Add(column.Title, (long)column.Id);

            foreach (Column column in pmosheet.Columns)
                columnMapPMO.Add(column.Title, (long)column.Id);

            IList<Row> rowsToCompare = pmosheet.Rows;
            IList<Row> rowsToMove = insert.Rows;

            foreach (Row innerrow in rowsToMove)
            {
                Cell MainTitle = getCellByColumnName(innerrow, "Title");
                foreach (Row row in rowsToCompare)
                {
                    Cell PMOPName = getPMOCellByColumnName(row, "Project Name");

                    if (PMOPName.DisplayValue == MainTitle.DisplayValue)
                    {
                        Console.WriteLine("Yes");
                        break;
                    }
                    else
                        Console.WriteLine("No");
                }
            }
        }

        static Cell getCellByColumnName(Row row, string columnName)
        {
            return row.Cells.FirstOrDefault(cell => cell.ColumnId ==
            columnMap[columnName]);
        }

        static Cell getPMOCellByColumnName(Row row, string columnName)
        {
            return row.Cells.FirstOrDefault(cell => cell.ColumnId ==
            columnMapPMO[columnName]);
        }
    }
}

As just a nit I modified the order of the loops so that the rows to be added forms the outer loop (assuming there are projects that may not have corresponding line items to insert that don't need to be looked at), and when I find my match for projects I exit the inner loop.

The output looks like this:

I do get all the way through the test, so it seems like your code does the trick. Maybe simplify your sample inputs so that you can verify that you get what you want. That might also tell us if it is a data driven issue.