Export a table using external API with unlimited columns and rows

Community Show & Tell
1

Hello guys,

I would like to give feedback on a feature that could be used by other people.
I wanted to be able to export my tables to Retool in PDF format with my column names and rows. It was a bit tricky because Retool doesn't allow for the moment to do it in a simple way.
I first found a well documented page to export a table using an external API. Here is the link to this page: Creating PDFs in Retool
I encountered a major problem with this API, it can't loop over columns and rows at the same time.
So I used PDFGeneratorAPI which is a great tool to achieve it. Here is how I did it:

First here is a part of the table I wanted to export :

Capture
Capture990×130 8.96 KB

Next step is to create your account on PDFgeneratorAPI (https://pdfgeneratorapi.com/). When your account is created, go to the template page, create a new one. You will be directed to the editing page. Rename your template if you want. You can orient the page in portrait mode by clicking on the orange arrow.
Now create a table by drag and drop a table like on Retool (purple arrow) and extend the cell like I did.
The most important part here is to click on automatic table (green arrow). Save the page, publish it an go back to dashboard.

template
template1918×938 48.5 KB

Click on Account Setting, you will find here your API Key and your secret.
Go back on Retool, create a new query and select the RESTQuery (restapi) in Resource drop down. Action type is POST and URL is the next one : "https://us1.pdfgeneratorapi.com/api/v3/templates/YourTemplateID/output?output=url"

For URL parameters, do as I did. Now fill the headers part with your values matching with the keys : key, secret, workspace (your email), content type, and accept. All the documentation refering to this part is here : PDF Generator API | API documentation

For the body, select Raw and create the JSON structure.

{
  "id" : "TemplateID",
  "name" : "TemplateName",
  "item" : {{[_.mapKeys(table3.columns)].concat(table3.displayedData)}}
}

Let me explain the item part:
table3.columns are the headers of my table.
table3.displayedData are the rows.
This is the part that is a bit complex because displayedData is an object array and .columns is an object.
You have to encapsulate your .columns between [] to declare it as an array and then insert displayedData in the new array with .contat
The API only works if all your keys/values are similar, which is not the case. _.mapKeys(table3.columns) allows me to switch from this

{
  "0": "ac_tail",
  "1": "category",
  "2": "ac_type"
}

to this

    {
      "ac_tail": "ac_tail",
      "category": "category",
      "ac_type": "ac_type"
    }

retoolpdf
retoolpdf1246×693 41.3 KB

Now everything is supposed to work normally if you have followed the steps. Run your query and if you have a valid response, paste the link into a new tab and your pdf will appear. Now you just to create a button to trigger the query. You can use the pdf component on Retool and give a FileURL to it with the response of your query. Something like this :

{{APIcallPDF.data.response}}

I hope this tip will be useful for you.

2

@AntoineOA
Thank you for this! It seems to be just what I have been looking for.
I am a novice at this and I am having some trouble. I seem to have everything connected and working. I feel that I may be missing something on the PDFgeneratorAPI side of things when setting up my template.

Where did you use your code?:

    {
      "ac_tail": "ac_tail",
      "category": "category",
      "ac_type": "ac_type"
    }

It seems this would be in the template table data. I inserted the data there with my named columns as is returned in my retool RESTQuery.

In the end no matter what I try I keep getting a blank PDF. :frowning:

Any words of advice would be super helpfull!

Thanks!

3

Hello, PDFGenarator only works if your values are similar to the key in your JSON. The only way to achieve it with Retool is to use the _.mapKeys function.
In the PDFGenerator editor, you have to feed the table component with your JSON and set your table as an automated table.

1 Like
4

Thank you so much for taking the time to write this and share your feedback, @AntoineOA. This is super helpful :raised_hands: