Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
WE ARE LISTENING! New Navigation for Qlik Community, Sept. 26: TELL ME MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
polisetti
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)
11 Replies
Mauritz_SA
Partner - Specialist
Partner - Specialist

Hi @polisetti 

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

Regards,

Mauritz

Mauritz_SA
Partner - Specialist
Partner - Specialist

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.

bradforrest
Partner - Contributor II
Partner - Contributor II

Hi Mauritz,

Thanks for this - its really helpful. I'm having a problem where it only loads the first 100 sheets from Smartsheet, and the one I want to load into Qlik isnt in that list. Did you find anything similar?

KingsleyJH
Partner - Contributor III
Partner - Contributor III

Hi Brad.

Here's a link for info regarding the rest connector. The part that answers your question is within the "With Connection Keyword" section.

https://help.qlik.com/en-US/connectors/Subsystems/REST_connector_help/Content/Connectors_REST/Load-R...

Your WITH CONNECTION should look like the following:


WITH CONNECTION (
URL "https://api.smartsheet.com/2.0/sheets/$(vSheetID)",
HTTPHEADER "authorization" "$(vBearer)",
QUERY "includeAll" "true"
);

jamielim
Contributor III
Contributor III

Hi @KingsleyJH ,

I'm facing similar issues as @bradforrest , except that I'm connecting directly to a smartsheet report instead of sheet.

Even when I have updated the data load  with connection portion to include the includeAll parameter as you have advised, it is still returning just the first 100 rows from the smartsheet report. Any idea?

 

Regards,

Jamie

KingsleyJH
Partner - Contributor III
Partner - Contributor III

Hi Jamie.

I don't have answer for you. Its due to pagination. I am aware of this obstacle and its in my pipeline to research and resolve but as of right now I don't have an answer for you. If you go ahead and try figure it out yourself and find an answer, please let me know haha. But if we find a solution sooner, I'll add it to this post.

 

Thanks

Kingsley

jzientek
Contributor
Contributor

Having an issue creating the connector itself. Qlik doesnt want to save the connection without credentials. Smartsheet creds are rejected as well. Any ideas?

jzientek_0-1655907504211.png

 

KingsleyJH
Partner - Contributor III
Partner - Contributor III

Hi Jzientek.

 

I have a few ideas but hard to know without seeing whats happening.

1. Could be because you don't have a licensed account (I know at some point with Smartsheet, you could only load data if you had a smartsheet license account, I'm not sure if this is still the case)

2. Have you generated an API Key and applied like below:

KingsleyJH_0-1655914786817.png

In the "Value" column, its just: "Bearer xYourAPIKeyHerex"

 

Kingsley

jzientek
Contributor
Contributor

1) Thanks for the quick response. I beleieve Im licensed as I can create/edit Smartsheets and have an enterprise license. 

2) Yes, I created the API key in smartsheet first and enter in the query header below

Are you using smartsheet creds or just using anonymous?

jzientek_0-1655915717376.png