Read & Update Smartsheet Data Using C# Console Application

Introduction

In this article, we will see how you can read the data from Smartsheet from a C# console application. This is required when building a system where we need some data from Smartsheet to process further functionalities of the integrated system. If you are not aware of Smartsheet, then here is the definition from Wikipedia:

Smartsheet is a software as a service offering for collaboration and work management, developed and marketed by Smartsheet Inc. It is used to assign tasks, track project progress, manage calendars, share documents, and manage other work, using a tabular user interface.

Install smartsheet-csharp-sdk package for your Console Application

Open visual studio and create a new C# console application. Here I am using the latest Visual Studio 2022 and .Net Framework 6.0.

Right-click on your project and select Manage NuGet Packages

In NuGet Package Manager, go to the browse screen and search for smartsheet. You will see smartsheet-csharp-sdk package.

Select and install the package for your project.

Once the package is installed successfully, you can see this in your solution under the Packages folder.

How to get Smartsheet Id

You can get Smartsheet id from the browser. Open your Smartsheet in the browser and copy the numeric value after filterId query string.

Get Smartsheet API Access Token

Before accessing Smartsheet data from the C# application, you need to have a Smartsheet API access token. So first, you have to generate an API access token for your Smartsheet. For this, you must click on the Account icon in the left navigation of your Smartsheet.

Select Apps & Integrations

Go to API Access and click on Generate new access token.

On Generate API Access Token Give API Access Token Name

Click on the OK button, and you will get your API key. Copy this token to some safe place, as this will access all your account’s data.

Using Smartsheet API Access Token in Visual Studio solution

Now you have API Access Token; you can use this in your visual studio project. You have to go to project properties and open Debug-> General. Click on the Open debug launch profiles UI link.

This will open the profile window.

You can add your access token as an environment variable. You can check your environment variable in the launchSettings.json file of your project in the Properties folder:

{
  "profiles": {
    "ReadSmartSheetData": {
      "commandName": "Project",
      "environmentVariables": {
        "SMARTSHEET_ACCESS_TOKEN": "<APIAccessTokenValue>"
      }
    }
  }
}

Update the code to read data from Smartsheet

Now, open your Program.cs file of your console application project and add nuget reference to smartsheet-csharp-sdk

• using Smartsheet.Api;
• using Smartsheet.Api.Models;
• using Smartsheet.Api.OAuth;

Add the following code to your Program.cs file. In GetSheet, you have to change the sheet id with your sheet id.

class Program
{
    // The API identifies columns by Id, but it's more convenient to refer to column names. Map from friendly column name to column Id
    static Dictionary<string, long> columnMap = new Dictionary<string, long>();
    // Main function
    static void Main(string[] args)
    {
        // Initialize client. Uses API access token from environment variable SMARTSHEET_ACCESS_TOKEN
        SmartsheetClient smartsheet = new SmartsheetBuilder().Build();
        // Load the entire sheet- change sheet id with your sheet id
        Sheet sheet = smartsheet.SheetResources.GetSheet(88888433322444, null, null, null, null, null, null, null);
        Console.WriteLine("Loaded " + sheet.Rows.Count + " rows from sheet: " + sheet.Name);
        // Build column map for later reference
        foreach (Column column in sheet.Columns)
            columnMap.Add(column.Title, (long)column.Id);
        foreach (Row row in sheet.Rows)
        {
            getRowData(row);
        }
        Console.WriteLine("Done (Hit enter)");
        Console.ReadLine();
    }
    //Function to get data from each Smartsheet row
    static void getRowData(Row sourceRow)
    {
        // Find cell we want to read
        Cell firstNameCell = getCellByColumnName(sourceRow, "First Name");
        Console.WriteLine("First Name: " + firstNameCell.DisplayValue);
    }
    //Function to get cell value from Smartsheet row for given column name.
    static Cell getCellByColumnName(Row row, string columnName)
    {
        return row.Cells.First(cell => cell.ColumnId == columnMap[columnName]);
    }
}

Code to update data in Smartsheet

static void Main(string[] args)
{
    // Initialize client. Uses API access token from environment variable SMARTSHEET_ACCESS_TOKEN
    SmartsheetClient smartsheet = new SmartsheetBuilder().Build();
    // Load the entire sheet
    Sheet sheet = smartsheet.SheetResources.GetSheet(88888433322444, null, null, null, null, null, null, null);
    Console.WriteLine("Loaded " + sheet.Rows.Count + " rows from sheet: " + sheet.Name);
    // Build column map for later reference
    foreach (Column column in sheet.Columns)
        columnMap.Add(column.Title, (long)column.Id);
    // Accumulate rows needing update here
    List<Row> rowsToUpdate = new List<Row>();
    foreach (Row row in sheet.Rows)
    {
        Row rowToUpdate = evaluateRowAndBuildUpdates(row);
        if (rowToUpdate != null)
            rowsToUpdate.Add(rowToUpdate);
    }
    // Finally, write all updated cells back to Smartsheet
    Console.WriteLine("Writing " + rowsToUpdate.Count + " rows back to sheet id " + sheet.Id);
    smartsheet.SheetResources.RowResources.UpdateRows(sheet.Id.Value, rowsToUpdate);
    Console.WriteLine("Done (Hit enter)");
    Console.ReadLine();
}
 * TODO: Replace the body of this loop with your code
 * This *example* looks for rows with a "Status" column marked "Complete" and sets the "Remaining" column to zero
 *
 * Return a new Row with updated cell values, else null to leave unchanged
 */
static Row evaluateRowAndBuildUpdates(Row sourceRow)
{
    Row rowToUpdate = null;
    // Find cell we want to examine
    Cell statusCell = getCellByColumnName(sourceRow, "Status");
    if (statusCell.DisplayValue == "Complete")
    {
        Cell remainingCell = getCellByColumnName(sourceRow, "Remaining");
        // Skip if "Remaining" is already zero
        if (remainingCell.DisplayValue != "0")
        {
            Console.WriteLine("Need to update row # " + sourceRow.RowNumber);
            var cellToUpdate = new Cell
            {
                ColumnId = columnMap["Remaining"],
                Value = 0
            };
            var cellsToUpdate = new List<Cell>();
            cellsToUpdate.Add(cellToUpdate);
            rowToUpdate = new Row
            {
                Id = sourceRow.Id,
                Cells = cellsToUpdate
            };
        }
    }
    return rowToUpdate;
}
// Helper function to find cell in a row
static Cell getCellByColumnName(Row row, string columnName)
{
    return row.Cells.First(cell => cell.ColumnId == columnMap[columnName]);
}

Summary

In this article, I discussed how to install smartsheet-csharp-sdk NuGet package in our Visual Studio project. How to get Smartsheet Id and Smartsheet API Access Token to authorize Smartsheet data access. We also checked how to use Smartsheet API Access Token in the Visual Studio solution. Finally, we looked at the C# program code to read data from Smartsheet and the C# program code to update data in Smartsheet.