I connected to Smartsheet using the REST API and successfully generated the connection between smartsheet and Qlik sense. However, while retrieving the data form the smartsheet - I not able to see the data in proper table format in "Select data to load" option. In the tables menu- I can see root folder and sub-tabs of usersettings, columns, rows. I am unable to pull the exact sheets of columns and rows from this menu. Attached is the screenshot of tables example.
Could you please help me to get the proper table format from the smartsheet REST API?
Also, can I create a connection for smartsheet where I can see different sheets available in my smartsheet workspace instead of creating a connection for single smartsheet based on sheet ID?
In case someone else struggles with this, here is something that I did to get all the data from all the sheets. Obviously you can adapt it to only look at certain sheets by putting a filter on the name field in the Sheets table (in the script).
1. Create a REST connector to https://api.smartsheet.com/2.0/sheets/ , put in a query header with Name authorization and Value Bearer <your token> (for example Bearer vgx2lnzo8oq7uaohu8wwrjvu8f).
2. Call the connector REST_Smartsheet.
3. Use the following code (I just added the vBearer variable so that I could test on multiple accounts. Just make its value your token).
Let vBearer = 'Bearer vgx2lnzo8oq7uaohu8wwrjvu8f'; //Account 1
LIB CONNECT TO 'REST_Smartsheet';
RestConnectorMasterTable: SQL SELECT "__KEY_root", (SELECT "id", "name" FROM "data" FK "__FK_data") FROM JSON (wrap on) "root" PK "__KEY_root" WITH CONNECTION ( URL "https://api.smartsheet.com/2.0/sheets", HTTPHEADER "authorization" "$(vBearer)" ) ;
Sheets: LOAD id AS SheetId, name AS Sheetname RESIDENT RestConnectorMasterTable WHERE NOT IsNull(name);
DROP TABLE RestConnectorMasterTable;;
For i = 0 to NoOfRows('Sheets') - 1
let vSheetName = peek('Sheetname',$(i),'Sheets'); Let vSheetId = Peek('SheetId',$(i),'Sheets');
RestConnectorMasterTable: SQL SELECT "__KEY_root", (SELECT "id", "title", "index", "__FK_columns" FROM "columns" FK "__FK_columns"), (SELECT "id" AS "id_u0", "rowNumber", "expanded", "createdAt", "modifiedAt", "siblingId", "__KEY_rows", "__FK_rows", (SELECT "columnId", "value", "displayValue", "__FK_cells" FROM "cells" FK "__FK_cells") FROM "rows" PK "__KEY_rows" FK "__FK_rows") FROM JSON (wrap on) "root" PK "__KEY_root" WITH CONNECTION ( URL "https://api.smartsheet.com/2.0/sheets/$(vSheetId)", HTTPHEADER "authorization" "$(vBearer)" ) ;
[RawData]: LOAD [id] AS [columnId], [title] AS Attribute, index AS ColumnPosition RESIDENT RestConnectorMasterTable WHERE NOT IsNull([__FK_columns]);
INNER JOIN ([RawData])
LOAD [columnId], [value] AS Value, [__FK_cells] AS [__KEY_rows] RESIDENT RestConnectorMasterTable WHERE NOT IsNull([__FK_cells]) AND NOT IsNull([value]);
LEFT JOIN ([RawData])
LOAD [rowNumber] AS Field1, [__KEY_rows] RESIDENT RestConnectorMasterTable WHERE NOT IsNull([__FK_rows]);
DROP TABLE RestConnectorMasterTable;
DROP FIELDS [__KEY_rows],columnId;
//This piece of code was stolen from a different thread and adapted a bit, but I couldn't find the user's name to tag him/her. //import field list and count number of fields masterfields: LOAD distinct Attribute resident RawData ORDER BY ColumnPosition ASC; //count no of distinct attributes let vfieldnos# = fieldvaluecount('Attribute'); //create pk list '$(vSheetName)': load distinct Field1 resident RawData; //set loop to zero let counter#=0; //loop through every attribute for counter#=0 to ($(vfieldnos#)-1) //createfield names let vfield = peek('Attribute',$(counter#),'masterfields'); //join data to pk list join('$(vSheetName)') load Field1, Value as [$(vSheetName).$(vfield)] resident RawData where Attribute='$(vfield)'; next counter# //cleanup stage drop table masterfields,RawData; drop field Field1;
Drop Table Sheets;
Not the cleanest code, but should get you started.