How to grab referenced data using Python API

Ask for Help
1

Hi there!
I am experiencing a bit of an issue, and I feel I’m just misunderstanding the correct process for obtaining this data.
I am trying to use the Python API to grab data from my Grist sheet, the problem is that the data that I am trying to grab is referenced.

I have a table called “Clients” containing the client names. I also have a table called “Client Pages” where I am adding a reference to client, and adding their pages in a list.
I am trying to pull the data from the “Client Pages” sheet to create a dictionary similar to this:
clients = {"client1": ["page11", "page12"], "client2": ["page21", "page22", "page23"], "client3": ["page31", "page32"]}

image

This is what I’m trying:

def get_grist_clients():
    DOC_ID = "xxxxx" 
    api_key = "xxxxx"
    api = GristDocAPI(DOC_ID, server="https://xxxx.xxx.xxx", api_key=api_key)
    data = api.fetch_table("xxxxx")
    for page_line in data:
        client = page_line.Client
        page = page_line.Page
        print(client)
        print(page)

With this code, the client is equal the the references cell id, rather than its value so it prints numbers, while page works just fine as it is not referenced, but direct value in the cell.
How can I pull the text value instead of the number from the referenced cell? Based on what I can tell it doesn’t seem possible this way, and I will have to pull it from the non-referenced sheet instead, overcomplicating things?

2

Add a formula like $Client.name and use that column in the API code.

3

I was hoping it was possible to actually grab the referenced cell but I guess not, this is the simplest workaround. Thank you!

4

Not sure this helps, but when using the Grist Widget API, this are the results of the API Calls (Widget connected to Table 1. There is also Table2. Each table has columns named CA, CB, CC, etc

Grist Column Type Tester
Loading…
Chamada 1 (listTables):
Fetching list of tables…
Chamada 1 (listTables):
Tables: [
“Table1”,
“Table2”
]
Chamada 2 (fetchTable):
Fetching data for connected table: Table1
Chamada 2 (fetchTable):
Table Data: {
“id”: [
1,
2,
3
],
“manualSort”: [
1,
2,
3
],
“T1_CA”: [
“Teste 1225”,
“Teste1”,
“Teste 3”
],
“T1_CB”: [
“A”,
“B”,
“C”
],
“T1_CC”: [
“Op3”,
“Op2”,
“Op1”
],
“T1_CE”: [
1,
2,
1
],
“T1_CD”: [
“1”,
“1”,
“1”
],
“gristHelper_Display”: [
15.23,
1000,
15.23
]
}
Chamada 3 (getTable):
grist.docApi.getTable is not available.
Chamada 4 (fetchTable _grist_Tables_column):
Fetching internal metadata table (_grist_Tables_column)…
Chamada 4 (fetchTable _grist_Tables_column):
Metadata Table: {
“id”: [
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12
],
“parentId”: [
1,
1,
1,
1,
2,
2,
2,
2,
1,
2,
1,
1
],
“parentPos”: [
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12
],
“colId”: [
“manualSort”,
“T1_CA”,
“T1_CB”,
“T1_CC”,
“manualSort”,
“T2_CA”,
“T2_CB”,
“T2_CC”,
“T1_CD”,
“gristHelper_Display”,
“T1_CE”,
“gristHelper_Display”
],
“type”: [
“ManualSortPos”,
“Text”,
“Text”,
“Choice”,
“ManualSortPos”,
“Ref:Table1”,
“Bool”,
“Numeric”,
“Text”,
“Any”,
“Ref:Table2”,
“Any”
],
“widgetOptions”: [
“”,
“”,
“”,
“{"widget":"TextBox","choices":["Op1","Op2","Op3"],"alignment":"left","choiceOptions":{"Op1":{"fillColor":"#126E0E","textColor":"#FFFFFF"},"Op2":{"fillColor":"#084794","textColor":"#FFFFFF"},"Op3":{"textColor":"#E00A17","fontBold":true}}}”,
“”,
“”,
“”,
“”,
“”,
“”,
“”,
“”
],
“isFormula”: [
false,
false,
false,
false,
false,
false,
false,
false,
true,
true,
false,
true
],
“formula”: [
“”,
“”,
“”,
“”,
“”,
“”,
“”,
“”,
“1”,
“$T2_CA.T1_CA”,
“”,
“$T1_CE.T2_CC”
],
“label”: [
“manualSort”,
“T1 CA”,
“T1 CB”,
“T1 CC”,
“manualSort”,
“T2 CA”,
“T2 CB”,
“T2 CC”,
“T1 CD”,
“gristHelper_Display”,
“T1 CE”,
“gristHelper_Display”
],
“description”: [
“”,
“”,
“”,
“”,
“”,
“”,
“”,
“”,
“”,
“”,
“”,
“”
],
“untieColIdFromLabel”: [
false,
false,
false,
false,
false,
false,
false,
false,
false,
false,
false,
false
],
“summarySourceCol”: [
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
],
“displayCol”: [
0,
0,
0,
0,
0,
10,
0,
0,
0,
0,
12,
0
],
“visibleCol”: [
0,
0,
0,
0,
0,
2,
0,
0,
0,
0,
8,
0
],
“rules”: [
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null
],
“reverseCol”: [
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
],
“recalcWhen”: [
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
],
“recalcDeps”: [
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null
]
}
Chamada 4 (Extração de metadados):
Extracted Meta Columns: [
{
“tableId”: 1,
“colName”: “manualSort”,
“colType”: “ManualSortPos”
},
{
“tableId”: 1,
“colName”: “T1_CA”,
“colType”: “Text”
},
{
“tableId”: 1,
“colName”: “T1_CB”,
“colType”: “Text”
},
{
“tableId”: 1,
“colName”: “T1_CC”,
“colType”: “Choice”
},
{
“tableId”: 2,
“colName”: “manualSort”,
“colType”: “ManualSortPos”
},
{
“tableId”: 2,
“colName”: “T2_CA”,
“colType”: “Ref:Table1”
},
{
“tableId”: 2,
“colName”: “T2_CB”,
“colType”: “Bool”
},
{
“tableId”: 2,
“colName”: “T2_CC”,
“colType”: “Numeric”
},
{
“tableId”: 1,
“colName”: “T1_CD”,
“colType”: “Text”
},
{
“tableId”: 2,
“colName”: “gristHelper_Display”,
“colType”: “Any”
},
{
“tableId”: 1,
“colName”: “T1_CE”,
“colType”: “Ref:Table2”
},
{
“tableId”: 1,
“colName”: “gristHelper_Display”,
“colType”: “Any”
}
]
Chamada 5 (fetchTable + mapColumnNames):
Fetching sample record from table: Table1
Chamada 5 (Sample Record):
Sample record: {
“id”: 1,
“manualSort”: 1,
“T1_CA”: “Teste 1225”,
“T1_CB”: “A”,
“T1_CC”: “Op3”,
“T1_CE”: 1,
“T1_CD”: “1”,
“gristHelper_Display”: 15.23
}
Chamada 5 (mapColumnNames):
Mapped record (grist.mapColumnNames): {
“id”: 1,
“manualSort”: 1,
“T1_CA”: “Teste 1225”,
“T1_CB”: “A”,
“T1_CC”: “Op3”,
“T1_CE”: 1,
“T1_CD”: “1”,
“gristHelper_Display”: 15.23
}
Chamada 6 (mapColumnNamesBack):
Mapped back record (grist.mapColumnNamesBack): {
“id”: 1,
“manualSort”: 1,
“T1_CA”: “Teste 1225”,
“T1_CB”: “A”,
“T1_CC”: “Op3”,
“T1_CE”: 1,
“T1_CD”: “1”,
“gristHelper_Display”: 15.23
}