Hello, I used Excel import Web API and used Nocodb Rest API, I configured a token and put the url I found on the API rest page but it did not work. Is there any page detailing this step by step how to to that ?
Thank you.
Sorry, its not clear from the above description. You mentioned excel import API (which?) and NocoDB Rest API (which one?)
Can you provide the link to the APIs that you are referring to and provide a step by step description.
Here below the full step by step descripton and the challenge I faced:
Thank you for your support.
I do not have Excel to try this out. Here is something you can try
- To talk with NocoDB using APIs, you will need an API Token. You can generate it by using steps listed here API tokens | NocoDB
- Use
xc-tokenin the header instead ofxc-auth
Note that, you do not have to do any configuration in the swagger. Swagger link that we have provided is to try out APIs to look at their response.
Ok thank you. Once I have generated a token, should I copy it in the “Value” ? or I have to keep it empty ?
This token is like your password. You need not paste it in Swagger. You need to configure it as value in excel for xc-token header
Use xc-token instead of xc-auth & in the input box next to it, paste your API Token copied from NocoDB
I managed to get connected to my Nocodb with Excel.
I replaced xc-auth by xc-token and I previsouly indicated in the value filed in NocoDB Rest API interface > “Authotize” the following code:
{
“headers”: {
“xc-token”: “Token value”
}
}
Here is my code in Excel Power Query:
let
Source = Json.Document(Web.Contents(“https://app.nocodb.com/api/v2/tables/mlxd59kzoh54d3w/records”, [Headers=[#“xc-token”=“Token value”]])),
#“Converti en table” = Record.ToTable(Source)
in
#“Converti en table”
But I get this result (see picture):
What would be the code to shall all fields and records structured in columns in Excel ?
Thank you in advance.
You can try something like below; created using GPT, you can try
let
// Get the JSON response from the web API
Source = Json.Document(Web.Contents("https://app.nocodb.com/api/v2/tables/mlxd59kzoh54d3w/records", [Headers=[#"xc-token"="Token value"]])),
// Navigate to the "list" field in the JSON response
ListField = Source[list],
// Convert the "list" field into a table
TableFromList = Table.FromList(ListField, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
TableFromList
thank you. I tried your script that below the result I get (it is better but not yet what I do expect):
Instead of having 1 unique Column, I would like to get as many columns as the number of field I have in the table “articles” and instead of having “records” that I need to select to see the content, I want to get as many lines with the content of each record per field structured in columns.
And I don’t want to be restricted to 25 records, potentially, if I have 3312 records in my table, I want to extract all of them in a structured table.
Thank you in advance for your help.
- Records will be paginated. You will have to update the script to run in a loop & rely on pageInfo to know if you have reached end of the table
- You can increase number of records that are read in one API invocation by using pageSize query parameter (defaults to 25. you can increase it upto 100). Details here NocoDB API Documentation
- And finally, to dump API response as fields - I am unsure how the field mapping works with Excel Power Query. Something that I am not familiar with.
