Excel Live Data Connector - Partial Results Returned

Dan C

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

Marcin P

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"