Can Power Apps use the Smartsheet connector to update cell values in a Smartsheet?

Eric Brown

I wrote a Power App that accesses the data in Smartsheet cells. A Smartsheet contains a table full of Rows. And each record in that table includes some data and then a table with the custom columns/cells. So you can't just connect to a Smartsheet. Instead, I loop through the Smartsheet and build a local collection that feels more like a table. It works great, and I'm happy to share the code.

What I would like to do now is be able to update the value in a Smartsheet cell from my Power App. I've been unsuccessful so far, but before I really bang my head against the wall to figure it out I wanted to confirm that the Power Apps Connector for Smartsheet supports record updates. In particular, the Sheet contains a table of Rows. The Rows contain a table of Cells. The cell table is a list of [ColumnId, value, displayValue]. I want to be able to update a record in the cell table to modify the value in that cell in the Smartsheet.

Is this supported?

Eric Brown

Genevieve P.

Hi @Eric Brown

Based on Microsoft's documentation for the Smartsheet connector, it looks like you can only add new rows with Power Apps and not update current ones in this way. You can see a list of all the actions you can take with this connector on Microsoft's documentation page, here: Microsoft Docs / Connectors / Connector reference / Smartsheet / Actions

I found a discussion in the Microsoft Power Automate Community where other users asked for something similar and one member used the Smartsheet API as a potential solution. See: Update a row in SmartSheet.

Cheers,

Genevieve

Matt Krieger

Hi @Eric Brown ,

I am interested in the code of how you looped through the Smartsheet Sheet Data's Columns and Rows to build up a usable table. Can you share that? I'm having a hard time wrapping my head around working with the data that is returned from the Smartsheet connector.

Thanks,

Matt

sgarm

Thanks @Eric Brown for asking this question. I concur with @Matt Krieger's question -- would you please share your code or the outline in PA? The are so few examples and such little documentations out there. Thank you, Sarah

Neil Watson

@Eric Brown it would be great if you could share the code you mentioned, please?

Lewis Waters

@Eric Brown echoing Neil and sgarm! Would be interested in learning how you did this...

Eric Brown

Here's the routine that pulls data from the Smartsheet. Looking at it now, I could optimize it by not having to repeat the same LookUp over and over, looking up the ColumnID for my Taxonomy and Level columns.

Anyway, hope this helps. I'm loving Power Apps so much.

// Get the Sheet id

Set (TaxonomySheetID, LookUp(Smartsheet.ListSheets().data,name="FD Taxonomy",id));

// Get the names and ids of the columns

Clear (ColumnIdTable);

ForAll (Smartsheet.GetColumns(TaxonomySheetID).data,

  Collect (ColumnIdTable,

    {ColumnName:ThisRecord.title,ColumnID:ThisRecord.id,ColumnType:ThisRecord.type}));

// Get a copy of the table of taxonomy entries

Clear (TechTaxonomy);

ForAll(Smartsheet.GetSheet(TaxonomySheetID).rows,

  Collect (TechTaxonomy, 

    {RowNumber:ThisRecord.rowNumber,

    TaxonomyName:LookUp(ThisRecord.cells,LookUp(ColumnIdTable,ColumnName="Taxonomy",ColumnID)=ThisRecord.columnId,displayValue),

    Level:LookUp(ThisRecord.cells,LookUp(ColumnIdTable,ColumnName="Level",ColumnID)=ThisRecord.columnId,displayValue)

    }

  )

);

G_Rant.

All,

This is what I did to read a table in power apps form Smart Sheets not as dynamic as above, but still a way to pull the data into PA:

1. You need to enable dynamic schema in the app's settings under experimental feature. (You need to do this because the function Smartsheet.GetSheetData returns a Dynamic Schema. Other wise PA cant interpret that data type you pull in)
2. To read my table I used this function. NOTE: YOU NEED TO CLICK THE ENABLE SCHEMA BUTTON IN THE BOTTOM OF THE FORMULA BAR.

Filter(

  Ungroup(

    Table(

      Smartsheet.GetSheetData(xxxxxxxxxxxxxxxx)

    ),

    "value"

  ), 'Action Type' = Table_Filter.Selected.Result

)

xxxx. = my smartsheet ID from the smartsheet properties window on my table

'Action Type' = a row in my table I want to filter by

Table_Filter = a dropdown on my app that has the Action Types

"value" = the array that the function returns

NOTE: I had to add a filter to display data in a table, I don't know why, because it would pop up in the preview window fine.

realexample

Does anyone know who to connect the Power Apps to Smartsheet Datasource. I tried the Smartsheet connector and it ignores it

granpa_chair

@G_Rant. @Eric Brown Could you please help how to compose Smartsheet.InsertRow() function? I'm trying for 3 days but it just simply doesn't want to work.

What I've got it just error saying that Smartheet expects UntypedObject instead of Record, Text or whatever I want to insert.

here's link to my question
https://community.powerplatform.com/forums/thread/details/?threadid=e5d75d60-8160-ef11-bfe3-7c1e520db39e

Eric Brown

@granpa_chair I posted the answer in the PowerPlatform thread. Repeating here for convenience.

Your "Row Post" object (the second argument in the Insert Row call) needs to be well-formed JSON. Try something like this: 

Smartsheet.InsertRow(
    "SheetID", // The ID of your Smartsheet
    {
        "cells": [
            {
                "columnId": "ColumnID1", // The ID of the column
                "value": "Value1" // The value to insert
            },
            {
                "columnId": "ColumnID2",
                "value": "Value2"
            }
            // Add more columns as needed
        ]
    }
)

granpa_chair

https://community.smartsheet.com/discussion/comment/450806#Comment_450806

Unfortunatelly this doesn't work. I've got few messages:

• "Unexpected characters. The formula contains the 'cells' symbol where the 'Ident' symbol is expected."

• "Unexpected characters. The formula contains the 'cells' symbol where the 'Colon' symbol is expected."

• "Expected colon. A colon (:) was expected at this point in the formula."

• "Unexpected characters. The formula contains the 'Colon' symbol where the 'CurlyClose' symbol is expected."

• "Unexpected characters. The formula contains the 'Colon' symbol where the 'ParenClose' symbol is expected."

• "Unexpected characters. The symbols are used in the formula in an unexpected way."

Code that I used:

Smartsheet.InsertRow(

    "1920494682634852";

    { 

      "cells": [

            { 

              "columnId": "4266183311448892";

                "value": "Test"

            }

        ]

    }

)

Eric Brown

Apologies for posting an answer without testing it myself.

Here's a solution that actually works.

// Cerate a record that you want to insert. Use the column names from your table in Smartsheets
// In this example, I have columns named "Project" and "Owner".
// They are both text fields. I have not played with any other field types yet.
Set(SSRow,
    {
        Project: "Value1"
    }
);

// Convert this record into an an untyped object, which is what the connector expects
Set(unSSRow, ParseJSON(JSON(SSRow)));

// Insert the row
Smartsheet.InsertRow(
    SheetId,
    unSSRow
);