Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Smartsheet ID data connection using REST API

HI Everyone,

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?

Thanks in advance!

Labels (4)
2 Replies
Highlighted
Partner
Partner

Hi @polisetti 

Were you perhaps able to get this working? Both the returned response and accessing all of the sheets.

Regards,

Mauritz

Highlighted
Partner
Partner

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;

Next i;

Drop Table Sheets;

Not the cleanest code, but should get you started.