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?
Hi there,
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.
Thanks,
Natalie
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), visibleCol, and recalcDeps.
George
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:
fields property: colRef, parentId, parentPos, summarySourceCol, displayCol, visibleCol, rules, recalcDeps. Also remove the following from widgetOptions, if present in the JSON string: rulesOptions.id field from each record in the response.George
Thanks!
Is it possible to preserve the id somehow?
The record ids? I can’t think of a way to preserve them. Sorry!
George
Doesn’t help now, but if something like this gets implemented it could help in future:
Grist is great in general in terms of UX but there is a real gap there in the product : being limited to excel to transport a table from a document to another document is very limiting: from what I understand it cause you to loose layouts, column definitions, link between tables, etc… As grist invites you to have your own workspace, and to work on your own copy … but if there is no way to easily merge back the changes it is a bit like an unfinished journey there. Is there is a trick for this / if there is any progress on this, I think will be much appreciated. Obviously, the API can help, but it is not exactly good UX for something that is quite common in terms of workflow.