Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Qlik community. I wanted to share this in the event someone is looking for a comprehensive load script that could be used for the Smartsheet REST connection.
These are the development issues I came across and built a solution around:
- How do I remove the limit of the first 100 reports/sheets I'm connected to?
- How do I connect to more than 10k records that are limited once a table is found?
- How can I get the script to work on both a Smartsheet 'Sheet' and a Smartsheet 'Report'?
- Is there a way I can pull multiple Smartsheet 'Reports', 'Sheets', or 'Reports and Sheets' all at once?
- What if multiple Smartsheet tables are named the same?
I found some guidance in the forums and then read up on Smartsheet's API documentation to get an understanding of how they structured their API. After working through this, I now have a solution worthy of sharing with everyone. Really hope this helps someone else - Feel free to let me know what you think. And of course, give it a like to show some appreciation 🙂
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I will walk through the solution below and you can download the txt file to paste into your load editor.
The developer only needs to update a few things in the first few rows of code.
- Update the 'Bearer' code
- Update your connection name
- Define the 'Sheet ID'' or 'Report ID' and state if they are a 'Sheet' or 'Report'
Note: Even though we are using the Shartsheet ID for best practice, the Name is what you will see for each table.
From this point forward, everything is set and the only changes to make are optional.
Variables are assigned so that Sheets or Reports can be pulled.
Connection is established and the "include all" query is passed to get more that the first 100 reports/sheets.
There is an optional stop point you can do here where the two [ACTION] comments are. These are so you can run the script at this point and get the list of all Sheets or Reports - This is in the event you wanted to view a list of all tables you are connected to without having to open Smartsheet to to see.
Once we have our list of Reports and Sheets we are going to pull in, a loop will be set to walk through each one and determine how many records there are. This was a necessary step if you wanted to pull in an unlimited number of records, as the row count limiter prevented us from bringing in more than 10k records at once. The "Include All" query only works on the table name table - not at the row level. Also, asking for the row count always returned the row count to the current page you are on (page one by default).
To get around this, I defined each page to be 1 record long with a max page of 9999999 (set whatever limit you want here). Then I could call and ask for how many pages in total there were - Which in essence tells me how many records there are.
It is possible you have 2 or more Smartsheet reports with the same name. There is also a section of code that checks to see if a table name has already been used. If it has, then it renames one of the tables to add the row number of the duplicate from the list of reports you want to bring in. This way it is easy to find and correct if it was a mistake.
After knowing how many records there are, I set variables up to define limits in the query, by calculating how many pages there are, and created a stopping point for a loop to pull in the exact number of pages available for the Report/Sheet.
Define variables:
Loop through each page and pull it in:
The data that comes in will be in an indexed view of the data table. This script will do an index matching process to bring it back to a standard table view. I also left an optional point for you to decide if you want to exclude fields that are fully null and save space, or include them if you need them for later when as there may not be data there yet.
A space is left here if you want to add the table name to each of the fields.
For example: Table.Field1, Table.Field2
After we have all of the pages for the Report/Sheet, we stitch them back together so they become one table again.
Then we close the loop and drop the table after we iterate through the full list of Reports/Sheets we set to include.
At this point the API load is complete and resident loads can be called for any ETL needed.
Accepted Solutions
