How do I create a Retool table to read/write a Notion database

[Jake29]

Hi all, new to the forum so apologies if this is not the correct place for this post.

I'm attempting to create a table in Retool that I can use to get/update a database in Notion. Now, I've spent some time looking through the Notion API documentation, and have a POST written to query the database and get the details of the pages within the database (each item within the database is a page, and each page has a list of properties, which is what I'm after).

The results of the query is a data object, which has a results array of page objects. Each page object has a properties object (properties are consistent from page to page, so each page object has the same properties object), which contains objects for each property. Each property object has subobjects, which eventually lead to strings, which is what I want to display in the table for each page.

For example, every page in my Notion database has a Name property (which is the name of the page). If I wanted to retrieve the name of the first page I could write a sql query to:
SELECT {{queryName.data.results['0']. properties.Name.title['0'].plain_text}}.

The problem is that this only gives me the name for one page, and I'd like to have a column which the name of each page. If I write a query to select * from data.results I get a column with the page ID, some other unimportant information about each page, and then a properties column, which has all of the properties nested within it. I'd like to select the nested string from each of these properties objects and have them each as separate columns, where the column name is the property object name.

I can do this easily enough with custom columns in the table which grab that information for the currentRow, but can't figure out how to do this with the query itself. The reason I want this in the query is twofold:
A. I'd like to be able to query the data in that table, and can't query calculated Column data
B. I'd like this to be a read/write table, which updates the Notion database, and can't think of how to do this without making sure the query returns the correct values, so that the update takes place for the correct object when sending the data (the terminology I used here may be wrong, I am still learning)

I hope that this was enough detail to explain my goal here, still learning how to do all of this, so please bear with me.

[Kabirdas]

Hi @Jake29!

If you're comfortable using some Javascript you could use a transformer on your query data to have it be returned in the appropriate format. If you'd be willing to post a sample of your data here (or write in through intercom) we can help you grab the correct syntax!

[Jake29]

Hi @Kabirdas, thanks for the reply! I have a foundational background in JS, but have no experience with transformers, so I wouldn't really know where to begin there.

The data I get from querying the Notion DB looks something like this:
"object": "list",
"results": [
{
"object": "page",
"id": "ec1bc7e9-2190-4aa9-a088-93134d07ba37",
"created_time": "2021-12-21T20:25:00.000Z",
"last_edited_time": "2021-12-21T20:25:00.000Z",
"cover": null,
"icon": null,
"parent": {
"type": "database_id",
"database_id": "9297abae-57ea-4e7f-9f57-04db7b3d9854"
},
"archived": false,
"properties": {
"Time": {
"id": "%3BJgc",
"type": "number",
"number": null
},
"Added": {
"id": "%3FnWu",
"type": "rich_text",
"rich_text": []
},
"Completed?": {
"id": "%40v%7D%3C",
"type": "checkbox",
"checkbox": false
},
"Created": {
"id": "CiYy",
"type": "created_time",
"created_time": "2021-12-21T20:25:00.000Z"
},
"Type": {
"id": "Dhrm",
"type": "multi_select",
"multi_select": [
{
"id": "82d1a0ee-01da-4791-aeb9-3a4b29918ca0",
"name": "initial",
"color": "purple"
}
]
},
},
"url": "https://www.notion.so/ -ec1bc7e921904aa9a08893134d07ba37"
},
.
.
.
This would be a list of pages, each with the information seen above, I've removed all but one entry as there are hundreds.

You'll see that that the data object has a list object of results. Each result within the result list is a page object, which has a properties object. The properties object has as children an object for each property a page can have. I've removed many of ours, but left enough to give a good example of what the data looks like. In this example, each page object has property objects of Time, Added, Completed?, Created, and Type. They each have different types, and within those objects are the information that I'm looking for. For example, the page object here has a Type object which is of type: multi-select, which has as a child a list of selected types. In this example there is only one option in the list, which has the name: initial. My goal, when rendering this DB data as a table within Retool, is to have a column titled: ID, which would be the ID of each page object, and columns for each of these properties, where the value a given property for a given page is displayed in the same row.

Ie.

ID Type
ID 1 initial
ID 2 old

Where ID1 and ID2 are page IDs, and initial and old are the name values of the multi-select object, for that page object.

There are many different types of objects, but if you could help me to format the JS syntax for a transformer to create one of these columns for a given property, I should be able extrapolate to the rest.

Hope this is clear enough.

[Kabirdas]

I see, it looks like you'll want to enable a transformer on your query that looks something like

return data.results.map(row => {
  /* build and return your row object here */
})

Does this look about right?

[Jake29]

@Kabirdas This worked! Thank you so much!! I did have to add in:

return data.results.map(row => {
const id = row.id;
const Type = row.properties.Type.multi_select[0] && row.properties.Type.multi_select[0].name;
return {id, Type}
})

To catch pages where the multi-select had no value. But after adding this, it worked for all pages.

Thank you so much for your help!