How to fetch data from Smartsheet in Power Apps canvas apps

Hello everyone and welcome to my blog. In today’s blog I will show how can query Smartsheet data in canvas apps.

The first step is to connect to Smartsheet. Open your Smartsheet and look for the smartsheet url.

The URL contain the SmartSheet Id, the portion after https://app.smartsheet.com/sheets. Copy it. We will need it to fetch data from SmartSheet.

The next step is to connect to the Smartsheet data source.

You will be asked to sign-in to Smartsheet. Enter the appropriate credentials and establish a connection.

So far so good. Till this point, it is quite easy. Now comes the tough part which is to fetch data from the Smartsheet.

My Smartsheet contain four columns.

1. Task
2. Description
3. Assigned To
4. Status

I wanted to show the items of this Smartsheet in a Gallery control. I set the Items property of the Gallery to the following formula

Smartsheet.GetSheetData("<smartsheet_id>",{columns:"Task,Description,Assigned To,Status"}).value

Observe the syntax to retrieve the columns. You need to specify the column list separated by comma.

While I was expecting Smartsheet to return me the columns I queried for, it didn’t. In-fact what is returned is basically dynamic data which is untyped.

We need to now convert the untyped data to typed values. In the OnVisible of the screen, I will query the data from Smartsheet and store it inside a collection. Below is the formula to accomplish that.

ClearCollect(
    collSmartsheetItems,
    ForAll(
        Smartsheet.GetSheetData(
            "<sheet_id>",
            {columns: "Task,Description,Status"}
        ).value,
        With(
            {Item: ThisRecord.Value},
            {
                Task: Text(Item.Task),
                Description: Text(Item.Description),

                AssignedTo: Text(Item.'Assigned To'),
                Status: Text(Item.Status)
            }
        )
    )
)

Replace <sheet_id> with appropriate Smartsheet Id copied at the first step. The code is pretty simple. Just be cautious when you try to typecast the untyped data to appropriate type. The current formula only uses the Text() function. However if the value is DateTime, you may need to use the appropriate type, example – DateTimeValue.

And there you go. You now have the untyped data in typed format with the desired properties which you can bind to the Gallery or table control.

Hope this helped. You will also like the below posts.

Debajit Dutta