解决方法for PowerBI Navigator record limit

Philip Robbins ✭✭✭✭

08/21/23 edited 08/21/23 inAPI & Developers

Problem statement:

When looking to utilise PowerBI via the Live Data Connector I was unable to find the sheets I wanted to connect.

Cause:

The PowerBI Navigator will return a maximum of 10,000 records from a given source.

处理:

I've managed to work around this by setting up a second DSN connection for the Live Data Connector using an alias Google account that has a Smartsheet licence assigned to it. This one I use for demos mostly and it's shared to far fewer sheets than my main account, so doesn't hit the limit. This way I've been able to access the sheet I want and connect it to PowerBI.

Phil Robbins

https://www.prodactive.co.uk/

https://www.youtube.com/%40Smartsheetexpert

All the best,

Phil

https://www.prodactive.co.uk/

https://www.youtube.com/@Smartsheetexpert

Tags:

Integrations

0 ·

Categories

Try this: =COUNTIFS({RegSci Review Projections Range 1}, [Primary Column]@row, {RegSci Review Projections Range 2}, OR(@cell = \"Planned\", @cell = \"Submitted\"<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":320,"name":"API & Developers","url":"https:\/\/community.smartsheet.com\/categories\/api-developers","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":110580,"type":"question","name":"When a new row is created in Smartsheet, Create SharePoint List Item","excerpt":"I'm trying to do my first Smartsheet Power Automate flow. I've created a SharePoint list to hold the data. When a new row is added to a Smartsheet, I would like for that row of data to be used to create a new item in a SharePoint List. The part that I need help with is the mapping of the columns from the SharePoint Create…","snippet":"I'm trying to do my first Smartsheet Power Automate flow. I've created a SharePoint list to hold the data. When a new row is added to a Smartsheet, I would like for that row of…","categoryID":320,"dateInserted":"2023-09-21T15:27:45+00:00","dateUpdated":null,"dateLastComment":"2023-09-24T13:46:47+00:00","insertUserID":167164,"insertUser":{"userID":167164,"name":"chasquad","url":"https:\/\/community.smartsheet.com\/profile\/chasquad","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-09-25T14:36:03+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":112221,"lastUser":{"userID":112221,"name":"Kelly Moore","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kelly%20Moore","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!jnPa4zng1Pw!n1lbH6Wxgbo!UbxYNkVsKJw","dateLastActive":"2023-09-28T17:42:53+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":59,"score":null,"hot":3390874472,"url":"https:\/\/community.smartsheet.com\/discussion\/110580\/when-a-new-row-is-created-in-smartsheet-create-sharepoint-list-item","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/110580\/when-a-new-row-is-created-in-smartsheet-create-sharepoint-list-item","format":"Rich","lastPost":{"discussionID":110580,"commentID":396710,"name":"Re: When a new row is created in Smartsheet, Create SharePoint List Item","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/396710#Comment_396710","dateInserted":"2023-09-24T13:46:47+00:00","insertUserID":112221,"insertUser":{"userID":112221,"name":"Kelly Moore","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kelly%20Moore","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!jnPa4zng1Pw!n1lbH6Wxgbo!UbxYNkVsKJw","dateLastActive":"2023-09-28T17:42:53+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"API & Developers","url":"https:\/\/community.smartsheet.com\/categories\/api-developers"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/423RTQAETRWC\/smartsheet-mapping-png.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"smartsheet mapping.PNG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-09-25T14:36:45+00:00","dateAnswered":"2023-09-24T13:46:47+00:00","acceptedAnswers":[{"commentID":396710,"body":"

Hey @chasquad<\/a> <\/p>

As a build on Ryan's advice, try adding The Get Sheet (schema) to your flow. Although there might be less clunky ways of doing this, this flow works for me. I have to use the same flow if I am adding a smartsheet row back to an online Excel table.<\/p>

\n \n $\"image.png\"$ <\/img><\/a>\n <\/div>\n<\/div>\n

<\/p>

Here's the flow broken down, step by step<\/p>

\n \n $\"image.png\"$ <\/img><\/a>\n <\/div>\n<\/div>\n

Expression: last(createArray(outputs('Get_a_sheet_data_(dynamic_schema)')?['body\/value']))<\/strong><\/p>

<\/p>

\n \n \n $\"image.png\"$ <\/img><\/a>\n <\/div>\n<\/div>\n
This is the array that gets the Last Row added<\/p>
Expression: last(outputs('ComposeArrayForLastRow'))<\/strong><\/p>

<\/p>
And finally, create the list item. The expressions are individually created using smartsheet column names<\/p>
\n \n \n $\"image.png\"$ <\/img><\/a>\n <\/div>\n<\/div>\n

<\/p>
Expression: outputs('ComposeLastRowOfArray')?['Business Day']<\/strong><\/p>

<\/p>
I hope this helps<\/p>
Kelly<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":320,"name":"API & Developers","url":"https:\/\/community.smartsheet.com\/categories\/api-developers","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":110440,"type":"question","name":"Smartsheet API Call","excerpt":"Hello Team, Please let me know the best way to make an API call from Smartsheet to request data from another tool and how to handle the response. I know I need to use a GET request to make the API, call but I am not sure how to handle the response in Smartsheet. Many Thanks,","snippet":"Hello Team, Please let me know the best way to make an API call from Smartsheet to request data from another tool and how to handle the response. I know I need to use a GET…","categoryID":320,"dateInserted":"2023-09-19T16:45:10+00:00","dateUpdated":null,"dateLastComment":"2023-09-21T13:57:34+00:00","insertUserID":166537,"insertUser":{"userID":166537,"name":"Emskie321","title":"Mr","url":"https:\/\/community.smartsheet.com\/profile\/Emskie321","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/ACg8ocLQ3XNhEe-vhgR7uLjCh-xFnB62fGRy-ciu3nJ2IkX6=s96-c","dateLastActive":"2023-09-28T08:59:53+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":120447,"lastUser":{"userID":120447,"name":"Ryan Kramer","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Ryan%20Kramer","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-09-27T03:56:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":73,"score":null,"hot":3390448364,"url":"https:\/\/community.smartsheet.com\/discussion\/110440\/smartsheet-api-call","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/110440\/smartsheet-api-call","format":"Rich","tagIDs":[227,319],"lastPost":{"discussionID":110440,"commentID":396450,"name":"Re: Smartsheet API Call","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/396450#Comment_396450","dateInserted":"2023-09-21T13:57:34+00:00","insertUserID":120447,"insertUser":{"userID":120447,"name":"Ryan Kramer","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Ryan%20Kramer","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-09-27T03:56:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"API & Developers","url":"https:\/\/community.smartsheet.com\/categories\/api-developers"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-09-21T16:36:09+00:00","dateAnswered":"2023-09-20T19:35:30+00:00","acceptedAnswers":[{"commentID":396313,"body":"
So the best way to do this is to have a script run locally or hosted somewhere outside of Smartsheet. <\/p>
You can then have this script make the \"GET\" requests to the various sources and pull that information locally and then push that data via a \"POST\" request to another source or potentially Smartsheet. <\/p>
We do these types of integrations all the time for various clients.<\/p>
Hope that helps!<\/p>
Ryan<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":320,"name":"API & Developers","url":"https:\/\/community.smartsheet.com\/categories\/api-developers","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":227,"urlcode":"api-and-developers","name":"API and Developers"},{"tagID":319,"urlcode":"functionality","name":"functionality"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=320&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":221,"limit":3},"title":"Trending in API & Developers","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">