I organized my tables into documents, not realizing that Reference columns can’t work across documents and that I would later need to reference a table in one document from a table in the other document.
What are my options for getting a table into another document fully intact?
You can download your table from one Grist document then import it into another. Note that when you import the table, it will import the headers and the data but you will need to reset the column types and add in any formula columns.
To download your existing table, I’d recommend navigating to Raw Data and downloading from there. This way, you can ensure you are downloading the full table (including any columns that may be hidden in views). From Raw Data, click on the data table to open the view then click the three-dot icon at the upper right of the table. Select one of the download options.
Once downloaded, navigate to the Grist document where you would like to add this table.
Under the green ‘Add New’ button, select ‘Import from file’.
Select your file to import then import it into a new table.
You will need to set up column types again but it should be fairly quick to do. For example, in the screenshot below, ‘Room’ and ‘Category’ were choice columns. The values imported as text. When I change the column type to Choice, the Choice options populate based on the values imported. If you had colors assigned to choices in your other Grist document, you can copy/paste those Choice values (with formatting).
Grist is able to guess the type for Numeric columns but you may still need to set additional formatting if the column should be $.
For any formula columns, simply copy/paste the formula from the other Grist document into this one after you’ve imported your data.
That’s not good enough (in my question I wrote “fully intact”), I need to be know that I’m preserving the column types and all other metadata (e.g. formulas, trigger formulas, and anything else).
Maybe there’s a straightforward way using the rest API? Or maybe I could download the documents as SQLite files, combine them with some SQL or something, and then upload? Or anything?
I’d rather do a little more work and know that everything is preserved than rely on myself to check everything and copy paste various little things.
It is possible to GET all the columns of a table from one document, and POST them to a table in another document with the REST API. However, some of the fields of the metadata hold values which are numeric identifiers for other columns/fields in the same document, and these are not guaranteed to be the same across documents. Basic things like column alignment settings, labels, formula code, etc. should be able to be copied over without a problem, but things like trigger formula dependencies, row/column conditional styles, and reference column configuration will be problematic because their configuration depends on such numeric references. Editing the SQLite file directly has the same problem.
In the schema for the body of a POST /columns (linked below), the main ones to watch out for would be
widgetOptions (some of the options store numeric references),
Can you please give me more concrete instructions.
Which endpoints do I need to call to get all the data and metadata?
Which endpoints do I call to recreate them?
Can I send the data from the first endpoints verbatim to those in the second category, or will I have to pull it apart and reassemble it? If so in what way?
For each table you want to copy over, the general steps should be:
- Get all the columns of the source table with the GET /columns endpoint.
- In the response, remove the following from each column’s
recalcDeps. Also remove the following from
widgetOptions, if present in the JSON string:
- Send the modified response to the POST /columns endpoint.
- Get all the records of the source table with the GET /records endpoint.
- Remove the
id field from each record in the response.
- Send the modified response to the POST /records endpoint.
Is it possible to preserve the id somehow?
The record ids? I can’t think of a way to preserve them. Sorry!
Doesn’t help now, but if something like this gets implemented it could help in future: