Excel Live Data Connector - Partial Results Returned
Hi,
On Smartsheet the report returns all rows. When I export to excel it returns all rows. When I run the connector in Excel, it excludes some rows.
It appears that the rows being excluded have a Text/Number value in column X i.e. D21, whereas the ones being returned just contain a number in column X i.e. 1901 - so I wonder if it's something to do with Data retrieval mode??
I have changed the connector settings to "Always Return Text" and tried the "Use entirety rule". Is there anything else I can try?
Thanks,
^Dan
Comments
-
hi Dan,
I found this code for Power Query in excel that allows pull data dow from smartsheet without data connector installed on your pc/mac. Please have a look. In my case it is way easier to use this code rather than going through all computers in company and install connectors.
let Source = Web.Contents("https://api.smartsheet.com/2.0/sheets/PASTE_SHEET_ID_HERE",[Headers = [#"Authorization" = "Bearer PASTE_API_TOKEN_HERE"]]), Import = Json.Document(Source), //ROWS rows = Import[rows], #"RConverted to Table" = Table.FromList(rows, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"RExpanded Column" = Table.ExpandRecordColumn(#"RConverted to Table", "Column1", {"id", "rowNumber", "expanded", "createdAt", "modifiedAt", "cells", "siblingId"}, {"Column1.id", "Column1.rowNumber", "Column1.expanded", "Column1.createdAt", "Column1.modifiedAt", "Column1.cells", "Column1.siblingId"}), #"Expanded Column1.cells" = Table.ExpandListColumn(#"RExpanded Column", "Column1.cells"), #"Expanded Column1.cells1" = Table.ExpandRecordColumn(#"Expanded Column1.cells", "Column1.cells", {"columnId", "value", "displayValue"}, {"Column1.cells.columnId", "Column1.cells.value", "Column1.cells.displayValue"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1.cells1",{"Column1.id", "Column1.expanded", "Column1.createdAt", "Column1.modifiedAt", "Column1.cells.displayValue", "Column1.siblingId"}), #"RPivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Column1.cells.columnId", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Column1.cells.columnId", type text}}, "en-US")[Column1.cells.columnId]), "Column1.cells.columnId", "Column1.cells.value"), #"Row Data" = Table.RemoveColumns(#"RPivoted Column",{"Column1.rowNumber"}), //COLUMNS columns = Import[columns], #"CConverted to Table" = Table.FromList(columns, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"CExpanded Column" = Table.ExpandRecordColumn(#"CConverted to Table", "Column1", {"id", "title"}, {"Column1.id", "Column1.title"}), #"Column Data" = Table.Pivot(Table.TransformColumnTypes(#"CExpanded Column", {{"Column1.id", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"CExpanded Column", {{"Column1.id", type text}}, "en-US")[Column1.id]), "Column1.id", "Column1.title"), //APPEND #"Append" = Table.Combine({#"Column Data",#"Row Data"}), #"Promoted Headers" = Table.PromoteHeaders(#"Append"), #"Removed Top Rows" = Table.Skip(#"Promoted Headers",1) in #"Removed Top Rows"
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives