Python API - results on Reports do not match manual Excel extracts
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
Answers
-
@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)
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives