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.
