Smartsheet Connector Syntax & General Usage

[martinav]

I see that there is a Smartsheet preview connector.  I have the simple desire to read a sheet, and possibly update it.  At this point, I hav not had any success in getting anything out of it.  However, I have had success assigning what I think is a sheet to a gallery, but it does not provide expected datacards.  Any ideas on this??

Here is how I have it setup:

In the item control for a table or gallery:

Smartsheet.GetSheet(9999999999)

I get what looks like some properties or metadata, but no actual data.  The resulting table is also blank.  My Smartsheet has several hundred rows.

Here are the datacards.  None of these titles are my actual data.

f

Using this for items:

Smartsheet.GetColumns(99999999999999)

This gets a single datacard (there is also nothing populated):

[martinav]

I found something.  

I was able to get the column names.  its found by using this in the table items control:

Smartsheet.GetColumns(99999999999).data

This resulted in getting the actual column names.  Now, how do you get to the data?

[v-xida-msft · Accepted solution]

Hi @martinav ,

Based on the issue that you mentioned, and the issue is confirmed on my side.

If you want to retrieve rows from the Excel sheet stored in your smartsheet workspace, I afraid that there is no way to achieve your needs in PowerApps currently.

Currently, there is no action supported under the Smartsheet connector to be able to retrieve rows from a specific Excel sheet stored in a smartsheet.

If you would like this feature to be added in PowerApps, please submit an idea to PowerApps Ideas Forum:

https://powerusers.microsoft.com/t5/PowerApps-Ideas/idb-p/PowerAppsIdeas

As an alternative solution, I think the combination of MS Flow and PowerApps could achieve your needs. You could consider configure a flow (MS Flow) on your side, within this flow, send a HTTP request to your Smartsheet API to retrieve data from your specific Excel sheet. Then return the retrieved data back to your app.

More details about Smartsheet api to retrieve rows from a Excel sheet, please check the following article:

https://smartsheet-platform.github.io/api-docs/?shell#get-sheet 

More details about setting up an Access Token within your Smartsheet, please check the following screenshot:

You could add a connection within your app to connect to the flow created above, then fire the flow from your app. You could add a button in your app, set the OnSelect property to following:

ClearCollect(RetrievedRecords, 'FlowName'.Run())

when you press the button, the related Excel sheet data would be collected into the RetrievedRecords Collection.

Please check and see if the following blog would help in your scenario (Returning table value from a flow back to a canvas app):

https://powerapps.microsoft.com/en-us/blog/return-an-array-from-flow-to-powerapps-response-method/

Firing a flow from an app, please refer to the following video:

https://www.youtube.com/watch?v=1wl9AtxWdkg

Best regards,

[martinav]

@v-xida-msft ,

Thanks for the ideas.  I think doing the flow option would just take too long.  I cant afford to dump the full smartsheet into a collection either.  I do that for other things, but for this, i dont think it will be possible.  I need something quick to lookup.

I do have to ask.  If you cannot pull data from the smartsheet, for what purpose is the powerapps connector??  I see no practical use.  Is it only good for discussions??

I find it very odd that there are functions to add rows, but not the fundamental of getting data out of it.  

Is this planned??  It is preview, so i suppose that can mean that everything isnt there yet.  However, getting data from the sheet seems like the minimum fundamental thing.

[martinav]

@v-xida-msft ,

I decided to go ahead and give this a try.  I have found a way that I can use the data provided in this manner.  I am familiar with this method to convert array information to JSON, then pass it back to PA.  However, the schema that was generated by the output does not appear to have anything that can be parsed by PA.  I cannot even use Filter array to get anything useful.  The array dumped by the HTML action simply does not seem to have the proper format to use this method, unless I'm missing something.

To explain what I did, I did a test run of my flow, took the output form the HTML action, then duped that into the "use sample payload to generate schema" box.  I then copied that, and pasted it into the "schema" field.  It does not error, but does not really have anything that makes sense.  I have attached the schema below:

{
    "type": "object",
    "properties": {
        "statusCode": {
            "type": "integer"
        },
        "headers": {
            "type": "object",
            "properties": {
                "Pragma": {
                    "type": "string"
                },
                "Vary": {
                    "type": "string"
                },
                "Keep-Alive": {
                    "type": "string"
                },
                "Connection": {
                    "type": "string"
                },
                "Transfer-Encoding": {
                    "type": "string"
                },
                "Cache-Control": {
                    "type": "string"
                },
                "Date": {
                    "type": "string"
                },
                "Content-Type": {
                    "type": "string"
                },
                "Expires": {
                    "type": "string"
                },
                "Content-Length": {
                    "type": "string"
                }
            }
        },
        "body": {
            "type": "object",
            "properties": {
                "id": {
                    "type": "integer"
                },
                "name": {
                    "type": "string"
                },
                "version": {
                    "type": "integer"
                },
                "totalRowCount": {
                    "type": "integer"
                },
                "accessLevel": {
                    "type": "string"
                },
                "effectiveAttachmentOptions": {
                    "type": "array",
                    "items": {
                        "type": "string"
                    }
                },
                "ganttEnabled": {
                    "type": "boolean"
                },
                "dependenciesEnabled": {
                    "type": "boolean"
                },
                "resourceManagementEnabled": {
                    "type": "boolean"
                },
                "cellImageUploadEnabled": {
                    "type": "boolean"
                },
                "userSettings": {
                    "type": "object",
                    "properties": {
                        "criticalPathEnabled": {
                            "type": "boolean"
                        },
                        "displaySummaryTasks": {
                            "type": "boolean"
                        }
                    }
                },
                "permalink": {
                    "type": "string"
                },
                "createdAt": {
                    "type": "string"
                },
                "modifiedAt": {
                    "type": "string"
                },
                "isMultiPicklistEnabled": {
                    "type": "boolean"
                },
                "columns": {
                    "type": "array",
                    "items": {
                        "type": "object",
                        "properties": {
                            "id": {
                                "type": "integer"
                            },
                            "version": {
                                "type": "integer"
                            },
                            "index": {
                                "type": "integer"
                            },
                            "title": {
                                "type": "string"
                            },
                            "type": {
                                "type": "string"
                            },
                            "primary": {
                                "type": "boolean"
                            },
                            "validation": {
                                "type": "boolean"
                            },
                            "width": {
                                "type": "integer"
                            },
                            "systemColumnType": {
                                "type": "string"
                            }
                        },
                        "required": [
                            "id",
                            "version",
                            "index",
                            "title",
                            "type",
                            "validation",
                            "width"
                        ]
                    }
                },
                "rows": {
                    "type": "array",
                    "items": {
                        "type": "object",
                        "properties": {
                            "id": {
                                "type": "integer"
                            },
                            "rowNumber": {
                                "type": "integer"
                            },
                            "expanded": {
                                "type": "boolean"
                            },
                            "createdAt": {
                                "type": "string"
                            },
                            "modifiedAt": {
                                "type": "string"
                            },
                            "cells": {
                                "type": "array",
                                "items": {
                                    "type": "object",
                                    "properties": {
                                        "columnId": {
                                            "type": "integer"
                                        },
                                        "value": {
                                            "type": "string"
                                        },
                                        "displayValue": {
                                            "type": "string"
                                        }
                                    },
                                    "required": [
                                        "columnId"
                                    ]
                                }
                            },
                            "siblingId": {
                                "type": "integer"
                            }
                        },
                        "required": [
                            "id",
                            "rowNumber",
                            "expanded",
                            "createdAt",
                            "modifiedAt",
                            "cells"
                        ]
                    }
                }
            }
        }
    }
}

[v-xida-msft]

Hi @martinav ,

Yeah, this feature is planed to add within the Smartsheet connector.

Actually, the actions supported under the connector within PowerApps/MS Flow is based on the actions exposed through the corresponding REST API.

Currently, as an alternative solution, I think the solution I provided above could achieve your needs (Maybe it's every complicated). Please consider take a try with it, then check if it could help in your scenario.

If would like this feature to be improved within the Smartsheet connector, you could consider submit an idea in PowerApps/MS Flow Ideas Forum, which would help Product Team to improve this feature.

Best regards,

[martinav]

@v-xida-msft ,

I did consider your method.  That was why I posted a reply.  The data returned by the HTTP action is not in a format that can be digested into an array.  As you see, the JSON scheme (posted earlier) by the data for the RESPONSE  action is not proper to build an array table.  If I did not do what you recommended, please show me where I err.

THank you.

To help this a little:

I took the output from the HTTP action, and formatted it a bit to show the syntax:

{"statusCode":200,"headers":{"Pragma":"no-cache","Vary":"Accept-Encoding","Keep-Alive":"timeout=5, max=30","Connection":"Keep-Alive","Transfer-Encoding":"chunked","Cache-Control":"no-store, must-revalidate, no-cache","Date":"Tue, 29 Oct 2019 20:42:06 GMT","Content-Type":"application/json; charset=UTF-8","Expires":"0","Content-Length":"1249737"},"body":{"id":4549752307115908,

"name":"MASTER DRAWING LIST","version":289,"totalRowCount":2274,"accessLevel":"ADMIN","effectiveAttachmentOptions":["DROPBOX","FILE","GOOGLE_DRIVE","BOX_COM","EVERNOTE","ONEDRIVE","LINK","EGNYTE"],"ganttEnabled":false,"dependenciesEnabled":false,"resourceManagementEnabled":false,"cellImageUploadEnabled":true,

"userSettings":{"criticalPathEnabled":false,"displaySummaryTasks":true},"permalink":"https://app.smartsheet.com/sheets/VP3W8jGmP7r3j43mMqC7VFwJ5hWmRCQFC556GX51","createdAt":"2017-01-10T14:54:30Z","modifiedAt":"2019-10-28T18:38:05Z","isMultiPicklistEnabled":true,

"columns":[
{"id":3898437847541636,"version":0,"index":0,"title":"DRAWING #","type":"TEXT_NUMBER","primary":true,"validation":false,"width":171},
{"id":8402037474912132,"version":0,"index":1,"title":"DESCRIPTION","type":"TEXT_NUMBER","validation":false,"width":599},{"id":1083688080435076,"version":0,"index":2,"title":"NOTES","type":"TEXT_NUMBER","validation":false,"width":575},
{"id":3796166086289284,"version":0,"index":3,"title":"LAST UPDATED","type":"DATETIME","systemColumnType":"MODIFIED_DATE","validation":false,"width":150},{"id":8299765713659780,"version":0,"index":4,"title":"LAST UPDATED BY","type":"CONTACT_LIST","systemColumnType":"MODIFIED_BY","validation":false,"width":213}
],

"rows":[
{"id":818468139362180,"rowNumber":1,"expanded":true,"createdAt":"2017-01-10T14:54:30Z","modifiedAt":"2019-01-22T14:55:44Z",

"cells":[
{"columnId":3898437847541636},
{"columnId":8402037474912132,"value":"JS75 General Arrg.","displayValue":"JS75 General Arrg."},
{"columnId":1083688080435076},
{"columnId":3796166086289284,"value":"2019-01-22T14:55:44Z"},
{"columnId":8299765713659780,"value":"teresa@kelsotech.com","displayValue":"teresa@kelsotech.com"}
]},

{"id":3070267953047428,

"rowNumber":2,"siblingId":818468139362180,"expanded":true,"createdAt":"2017-01-10T14:54:30Z","modifiedAt":"2018-09-26T20:46:35Z",

"cells":[
{"columnId":3898437847541636,"value":201013.0,"displayValue":"201013"},
{"columnId":8402037474912132,"value":"JS75S PRV  Dwg. 72784-2-1","displayValue":"JS75S PRV  Dwg. 72784-2-1"},
{"columnId":1083688080435076},{"columnId":3796166086289284,"value":"2017-01-10T14:54:30Z"},
{"columnId":8299765713659780,"value":"neisler@kelsotech.com","displayValue":"neisler@kelsotech.com"}
]},

{"id":7573867580417924,
"rowNumber":3,"siblingId":3070267953047428,"expanded":true,"createdAt":"2017-01-10T14:54:30Z","modifiedAt":"2018-07-03T18:25:56Z",

"cells":[{"columnId":3898437847541636,"value":201015.0,"displayValue":"201015"},
{"columnId":8402037474912132,"value":"JS75S316 General Arrg.","displayValue":"JS75S316 General Arrg."},
{"columnId":1083688080435076},{"columnId":3796166086289284,"value":"2017-01-10T14:54:30Z"},
{"columnId":8299765713659780,"value":"neisler@kelsotech.com","displayValue":"neisler@kelsotech.com"}
]},

I can see the pattern there, and I see my data.  I'm not sure this is in a format for a JSON scheme.  Or, is it?  That is the question.

I did a Parse JSON action just to look at the output after JSON parsing... The data now looks like this:

{"body":{"id":4549752307115908,

"name":"MASTER DRAWING LIST","version":289,"totalRowCount":2274,"accessLevel":"ADMIN","effectiveAttachmentOptions":["DROPBOX","FILE","GOOGLE_DRIVE","BOX_COM","EVERNOTE","ONEDRIVE","LINK","EGNYTE"],"ganttEnabled":false,"dependenciesEnabled":false,"resourceManagementEnabled":false,"cellImageUploadEnabled":true,

"userSettings":{"criticalPathEnabled":false,"displaySummaryTasks":true},"permalink":"https://app.smartsheet.com/sheets/VP3W8jGmP7r3j43mMqC7VFwJ5hWmRCQFC556GX51","createdAt":"2017-01-10T14:54:30Z","modifiedAt":"2019-10-28T18:38:05Z","isMultiPicklistEnabled":true,

"columns":[
{"id":3898437847541636,"version":0,"index":0,"title":"DRAWING #","type":"TEXT_NUMBER","primary":true,"validation":false,"width":171},
{"id":8402037474912132,"version":0,"index":1,"title":"DESCRIPTION","type":"TEXT_NUMBER","validation":false,"width":599},
{"id":1083688080435076,"version":0,"index":2,"title":"NOTES","type":"TEXT_NUMBER","validation":false,"width":575},
{"id":3796166086289284,"version":0,"index":3,"title":"LAST UPDATED","type":"DATETIME","systemColumnType":"MODIFIED_DATE","validation":false,"width":150},
{"id":8299765713659780,"version":0,"index":4,"title":"LAST UPDATED BY","type":"CONTACT_LIST","systemColumnType":"MODIFIED_BY","validation":false,"width":213}
],

"rows":[{"id":818468139362180,"rowNumber":1,"expanded":true,"createdAt":"2017-01-10T14:54:30Z","modifiedAt":"2019-01-22T14:55:44Z",

"cells":[
{"columnId":3898437847541636},
{"columnId":8402037474912132,"value":"JS75 General Arrg.","displayValue":"JS75 General Arrg."},
{"columnId":1083688080435076},
{"columnId":3796166086289284,"value":"2019-01-22T14:55:44Z"},
{"columnId":8299765713659780,"value":"teresa@kelsotech.com","displayValue":"teresa@kelsotech.com"}
]},

{"id":3070267953047428,

"rowNumber":2,"siblingId":818468139362180,"expanded":true,"createdAt":"2017-01-10T14:54:30Z","modifiedAt":"2018-09-26T20:46:35Z",

"cells":[
{"columnId":3898437847541636,"value":201013.0,"displayValue":"201013"},
{"columnId":8402037474912132,"value":"JS75S PRV  Dwg. 72784-2-1","displayValue":"JS75S PRV  Dwg. 72784-2-1"},
{"columnId":1083688080435076},
{"columnId":3796166086289284,"value":"2017-01-10T14:54:30Z"},
{"columnId":8299765713659780,"value":"neisler@kelsotech.com","displayValue":"neisler@kelsotech.com"}]},

{"id":7573867580417924,

"rowNumber":3,"siblingId":3070267953047428,"expanded":true,"createdAt":"2017-01-10T14:54:30Z","modifiedAt":"2018-07-03T18:25:56Z",

"cells":[
{"columnId":3898437847541636,"value":201015.0,"displayValue":"201015"},{"columnId":8402037474912132,"value":"JS75S316 General Arrg.","displayValue":"JS75S316 General Arrg."},{"columnId":1083688080435076},
{"columnId":3796166086289284,"value":"2017-01-10T14:54:30Z"},
{"columnId":8299765713659780,"value":"neisler@kelsotech.com","displayValue":"neisler@kelsotech.com"}]},

It looks like my data is in the area "cells".  

I dont know how to handle this.  It looks liek a Select action is in order, but I cannot figure the syntax.  Seems like it would be a nested situation.  I have tried several different ways, all tell me that the data is an Object, and it is expecting an Array.

Please advise.

[martinav · Accepted solution]

I reformulated this post into a different question, now that I know more of how to ask it properly...

https://powerusers.microsoft.com/t5/Building-Flows/Getting-data-from-a-nested-JSON-file/m-p/395305/h...

This post can be ended.