Loop Through REST Connector (GRIST orgs >> workspaces >> docs )

[dncontin]

Hello, I need to connect QlikSense Enterprise to Grist via REST API. So far, I've managed to do this with a couple of specific files, but now I need to iterate through all its contents to retrieve records from each document, whose structure varies each time. Does anyone have information about this? Thanks a lot.

+------------+--------------------------------------------------------------------+
| orgs | Team sites and personal spaces are called 'orgs' in the API. |
+------------+--------------------------------------------------------------------+
| workspaces | Sites can be organized into groups of documents called workspaces. |
+------------+--------------------------------------------------------------------+
| docs | Workspaces contain collections of Grist documents. |
+------------+--------------------------------------------------------------------+

[marksouzacosta]

Hi @dncontin,

Not sure if this will help you but I did a YouTube video about REST APIs, WITH CONNECTION and Loops:  https://youtu.be/lFwar30BNkQ?si=T8VcNYWE1WafzJdN

I hope you may find this helpful.

Regards,

Mark Costa

[dncontin]

Thank you for the quick response. I’ll review it to see if I can adapt it to my problem. Currently, it only processes the first table, but I need to define the JSON specifically, which means the second table isn’t being processed correctly.

[dncontin · Accepted solution]

Thank you for the help provided! Below, I've shared the code to retrieve all tables from a GRIST document. The key focus is on fetching the column names, which are then used in the JSON select statement. There are still many areas for improvement, but I hope this helps someone.

LIB CONNECT TO 'REST_GRIST2QLIK_Tables';

// Query headers

// +----------------+----------------+

// | Name           | Value          |

// +----------------+----------------+

// | Authorization  | Bearer {token} |

// +----------------+----------------+

RestConnectorMasterTable2:

SQL SELECT 

"id",

"__KEY_tables",

(SELECT 

"primaryViewId",

"summarySourceTable",

"onDemand",

"rawViewSectionRef",

"recordCardViewSectionRef",

"tableRef",

"__FK_fields"

FROM "fields" FK "__FK_fields")

FROM JSON (wrap off) "tables" PK "__KEY_tables";

[fields2]:

LOAD [primaryViewId],

[summarySourceTable],

[onDemand],

[rawViewSectionRef],

[recordCardViewSectionRef],

[tableRef],

[__FK_fields] AS [__KEY_tables]

RESIDENT RestConnectorMasterTable2

WHERE NOT IsNull([__FK_fields]);

[tables2]:

LOAD [id],

[__KEY_tables]

RESIDENT RestConnectorMasterTable2

WHERE NOT IsNull([__KEY_tables]);

DROP TABLE RestConnectorMasterTable2;

// Primero, cargamos los TableId en una lista temporal

TablesList:

LOAD [id] AS TableId

,[__KEY_tables] AS TableKey

RESIDENT [tables2];

// Recorremos cada TableId para extraer sus registros

FOR EACH vTableId IN FieldValueList('TableId')

  LIB CONNECT TO 'REST_GRIST2QLIK_COLUMNS';

  LET vURL_COL = 'https://grist.{tu sitio}/api/docs/{tu id de doc}/tables/$(vTableId)/columns';

  RestConnectorMasterTable:

  SQL SELECT 

      "__KEY_columns",

      (SELECT 

          "colRef",

          "parentId",

          "parentPos",

          "type",

          "widgetOptions",

          "isFormula",

          "formula",

          "label",

          "description",

          "untieColIdFromLabel",

          "summarySourceCol",

          "displayCol",

          "visibleCol",

          "rules",

          "reverseCol",

          "recalcWhen",

          "recalcDeps",

          "__FK_fields"

      FROM "fields" FK "__FK_fields")

  FROM JSON (wrap off) "columns" PK "__KEY_columns"

      WITH CONNECTION (

          URL "$(vURL_COL)"

      );

  [fields]:

  LOAD [colRef],

      [parentId],

      [parentPos],

      [type],

      [widgetOptions],

      [isFormula],

      [formula],

      [label],

      [description],

      [untieColIdFromLabel],

      [summarySourceCol],

      [displayCol],

      [visibleCol],

      [rules],

      [reverseCol],

      [recalcWhen],

      [recalcDeps],

      [__FK_fields] AS [__KEY_columns]

  RESIDENT RestConnectorMasterTable

  WHERE NOT IsNull([__FK_fields]);

  [columns]:

  LOAD [__KEY_columns]

  RESIDENT RestConnectorMasterTable

  WHERE NOT IsNull([__KEY_columns]);

  // Crear una tabla para los labels concatenados por parentId

  LabelsByParent:

  LOAD parentId, '"' & ConcatenatedLabels & '"' AS ConcatenatedLabelsFinal;

  LOAD  distinct [parentId],

        Concat([label], '","') AS ConcatenatedLabels

  RESIDENT [fields]

  GROUP BY [parentId];

  DROP TABLEs RestConnectorMasterTable ;

  LET vConcatenatedLabels =NULL();

  LET vConcatenatedLabels = Peek('ConcatenatedLabelsFinal', 0, 'LabelsByParent');

  DROP TABLES columns, fields; //AGREGADO PARA LA SEGUNDA VUELTA

  //<<<termina columnas

  //

LET vURL = 'https://grist.{tu sitio}/api/docs/{tu id de doc}/tables/$(vTableId)/records';

     LIB CONNECT TO 'REST_GRIST2QLIK_Records';  // Ajusta la conexión al conector REST que apunta a los records de una tabla específica

      // Utilizamos la variable vTableId para construir la URL dinámica

  // Utilizamos la variable vConcatenatedLabels para pasar las columnas de la tabla 

      RestConnectorMasterTable_Records:

      SQL SELECT 

      "id",

      "__KEY_records",

      (SELECT 

      $(vConcatenatedLabels),

          "__FK_fields"

      FROM "fields" FK "__FK_fields")

  FROM JSON (wrap off) "records" PK "__KEY_records"

      WITH CONNECTION (

          URL "$(vURL)"

      );

      // Guardamos los datos de la tabla actual en una tabla Qlik permanente

  // pendiente los store en QVDs

      qualify *;

      [Table_$(vTableId)]: 

      LOAD

          *

       RESIDENT RestConnectorMasterTable_Records

      WHERE NOT IsNull([__FK_fields]);

      Unqualify *;

      DROP TABLE RestConnectorMasterTable_Records;

drop table LabelsByParent;

NEXT

// Finalmente, limpiamos la lista temporal de TableIds

DROP TABLE TablesList;

exit script;