Python API - results on Reports do not match manual Excel extracts

Oliver Mihatsch

Hi,

We are currently automating some processes around Smartsheet, where one of the key steps is to extract data from a Smartsheet Report (sic! Not a Sheet!). We tried the following approaches

• using the Python API
• using the Smartsheet connector for the Azure Data Factory (https://learn.microsoft.com/en-us/azure/data-factory/connector-smartsheet?tabs=data-factory)

With both approaches we get wrong results on Reports (not on Sheets, interestingly). The extracted data is just not the same as compared to a manual data export though the browser frontend. The differences appear pretty random to us: some rows seem duplicated, while others are missing.

Is this a known issue of the API or are we rather missing out on something? Any ideas?

(Side note: we don’t see those issues with the ODBC connector. However, as we cannot easily deploy ODDBC in a pure cloud environment, we would normally favor other options.)

thank you,

Oliver

ericncarr

@Oliver Mihatsch I'd be curious to know how you're trying to extract the data. I'm using the API to extract the value of every row in a given column in a report to update dropdowns nightly...

An example of what my script does is below. I've already gotten the virtual column ID I want, you'd likely need to look up the virtual column IDs based on name, put that in a variable and use that instead. Depends what you want to do with the data. You could always put it in a Pandas Dataframe or export it to CSV by iterating through each row of the report and putting the information in each cell for each column into the Dataframe.

I just need all the values in one column for my purposes, so I add it to a list and then update my dropdown that way. If you need help getting the code for the iterative read through the virtual columns let me know.

#Gets all the rows of the report and returns the values of each cell in the "Project Selection" column

full_report = ss_client.Reports.get_report(report_id, page_size = 9000)

rows = full_report.rows

proj_sel_opts = []

for row in rows:

  for cell in (cell for cell in row.cells if cell.virtual_column_id == 2605489438648196):

    proj_sel_opts.append(cell.value)