How to add a table via the API?

Developers
1

Hello,

I am hoping to use Grist as an easy-to-use database replacement for my in-house application. What I want is the ability to automatically create and populate tables with the API. For example, when a new year comes around, add a new table for 2023 along with the templated sub-tables.

This doesn’t seem to be possible at the moment though? I don’t see a way to import data via the API or even create a new table. Is that the case? Any guidance for what I want to accomplish would be greatly appreciated.

Thanks!

2

Hi @Blake, you can create tables via the API, although it isn’t documented yet - see this thread:

A friendlier, documented API for table and column creation is actually under development now.

3

Hi @paul-grist,

That is great news. I was about to abandon the effort!

I also encourage you to allow for importing of a Grist export via the API. It would be amazing to have a template setup in Grist and automatically export the latest version of the template and import it via the API, rather than typing out hundreds of columns and tables in JSON (and having to modify it each time you adjust the template.)

Thanks!

-Blake

Edit: I wonder if it is possible to query the contents of the template and then pipe that output back into the creation of new tables in an automated way. I am going to try that!

Edit2: I can’t seem to find a way to list all the tables in a doc. Is that possible?

1 Like
4

You can use the regular endpoints for reading table contents, for the special table _grist_Tables which contains a list of all tables - for example: https://templates.getgrist.com/api/docs/pwGTfus26ck8/tables/_grist_Tables/records

There are examples of other kinds of enumeration here (this code is from a Zapier integration):

Your suggestions about importing make sense. There are import endpoints of various kinds, but not documented yet. There’s an example of a request that can copy a document as a template here grist-core/app/common/UserAPI.ts at ac61e8237dfcf2f5e0e862112a237b1363dd888c · gristlabs/grist-core · GitHub

5

Thanks @paul-grist,

Would you mind helping me craft a http request to copy a doc? I can’t seem to get it working based on that code.

Once I get this, I am home free!

Thanks,
Blake

6

Just took a shot at this, and here is how it currently works. I assume just as an example that we are working with https://docs.getgrist.com

First, GET https://docs.getgrist.com/api/worker/import and extract the docWorkerUrl you get back. If self hosting on a single-server setup, this step is a bit different, you’ll currently get back a path to add to your server’s URL.

Then, POST ${docWorkerUrl}copy?doc=DOC-ID-TO-COPY-FROM&template=1. You’ll need an Authorization header set. You’ll need the full “document id” of the document you want to copy from (see Creating a document - Grist Help Center), not just the shorter id you see in document URLs. If you want to copy the document in its entirety, use template=0. If you want to omit rows of data and just use the structure, use template=1.

Not done quite yet! The POST gives back an uploadId, make a note of it. As a last step, we need to land the document in a workspace. You’ll need an id of that workspace, you can find it in the URL for the workspace, e.g. this workspace Funding and Finance template workspace has an id of 15491.

POST ${docWorkerUrl}/api/workspaces/WORKSPACE-ID/import with body of JSON {"uploadId": ....}. You’ll need an Authorization header set, and Content-Type of application/json. You should get back a title and an id for your new document copy.

Whew… sorry for how many steps there are, it is an artifact of how our web client evolved.