Sep 2021 started using.

20 Jul 2022

The Community is very helpful, and (wo)manned by the Grist team too, who usually responds quickly :)

Started using it in '21, on a paid plan (hosted) since Nov '21. At $8/month I feel it's a steal considering the scope & capabilities. Hope I'll be able to lock this price when their pricing goes up in the future :)
Grist is open-source so can be self-hosted to overcome the records limitation due to assigned CPU and memory resources assigned in the hosted model (see https://community.getgrist.com/t/grist-as-database-backend-headless-cms-alternatives/586/7).

Grist has become my go-to whenever I need to use a spreadsheet, with its Python logic and API empowering me to manipulate data in new ways.

Embedding Grist spreadsheets has now also been released: https://support.getgrist.com/embedding/

Not sure how large the pool was, but I started well that month 😊

Limitation

~100k records per document, ie all tables in a spreadsheet.
Where I expect to possibly have more, I break out tables in their own document, with just 1 Master table in it.

Formulas

Created field

Updated field

Past / Future

IF($date_webinar!= None,IF($date_webinar<NOW(),"PAST","FUTURE"),"")

identify duplicate values

• create column dup (or other name)
• under Column Options, choose Set formula and use "DUP" if len(Master.lookupRecords(domain=$domain)) > 1 else "" as formula. In this example, duplication check is done on the domain column - change as needed using the relevant column header (eg email). It can also be anything other than "DUP". This basically fills the cell with the string "DUP" if there is more than one row with the same domain value.

At this stage, you can filter the dup column for rows where DUP comes up - these are the duplicates:

To prevent duplicate values being added to the table from that point on, you need to create an access rule under Access Rules, which can be accessed in the bottom left corner of the table, under "Tools"):

The formula should be newRec.dup == "DUP" which will be applied when the value of the dup column in a new record (newRec) is equal to "DUP".
Permissions can be defined by clicking on each as needed:
- R for Read
- U for Update
- C for Create
- D for Delete

Keyboard shortcuts

API

Getting started

pip3 install grist_api

API key per document can be found by clicking on the user icon in the top right corner, then Document Settings:

then copy/paste the key from here:

First need to create the Grist objects per document, as follows:

from grist_api import GristDocAPI

from dotenv import load_dotenv
load_dotenv()

SERVER = os.getenv("SERVER")
API_KEY_GRIST = os.getenv("API_KEY")

DOC_ID_BOOKS = os.getenv("DOC_ID_PE_BOOKS")
Books = GristDocAPI(DOC_ID_BOOKS, server=SERVER, api_key=API_KEY_GRIST)

DOC_ID_MOVIES = os.getenv("DOC_ID_PE_MOVIES")
Movies = GristDocAPI(DOC_ID_MOVIES, server=SERVER, api_key=API_KEY_GRIST)

DOC_ID_APPS = os.getenv("DOC_ID_PE_APPS")
Apps = GristDocAPI(DOC_ID_APPS, server=SERVER, api_key=API_KEY_GRIST)

The above enables me to call the methods listed below, by just referencing the document object Books, Movies or Apps depending on the spreadsheet I want to access.

Create / add records

grist_XX.DOC.add_records('SHEET', [{
                                    'field_name_1': python_object_1,
                                    'field_name_2': python_object_2,
                                    'field_name_3': python_object_3,
                                    'field_name_3': python_object_4,
                                    }
                            ])

where:
- grist_XX is the Grist object
- DOC is the document object
- SHEET is the sheet name within the document
- field_name_x is the column name in the sheet

Update records

Similar structure as above, with the update_records method insted of add_records BUT needs to identify what Grist record (ie row) to update, via its 'id':

grist_XX.DOC.update_records('SHEET', [{
                                    'id': id, # MANDATORY for the update method
                                    'field_name_1': python_object_1, # field to update
                                    'field_name_2': python_object_2, # field to update
                                    'field_name_3': python_object_3, # field to update
                                    'field_name_3': python_object_4, # field to update
                                    }
                    ])

Fetch last checked record

grist_XX.DOC.fetch_table('SHEET', filters={"field_name": 'filter_criteria'})[0].last_checked

Delete individual record

delete_records method:

grist_XX.DOC.delete_records('SHEET', [
                        id,
                    ])

Upload images

https://community.getgrist.com/t/how-to-upload-images-as-attachment-via-api-with-python/1216/2

import requests

response = requests.post(
    f"https://docs.getgrist.com/api/docs/{doc_id}/attachments",
    files={"upload": open("my_pic.png", "rb")},
    headers={"Authorization": f"Bearer {api_key}"},
)
attachment_id = response.json()[0]
cell_value = ["L", attachment_id]

Then use add_records or update_records and pass cell_value as the value of a single cell in an Attachments type column.

See the other endpoints in https://support.getgrist.com/api/#tag/attachments for listing/downloading attachments.

If an attachment is not referenced by any cell, it will be deleted automatically after some time. If you need to delete it immediately, there’s an undocumented endpoint: POST /api/docs/{doc_id}/attachments/removeUnused. Use it with caution.

Tricks

hidden value field for reference fields

When using a List column, or other data type referencing to a value, instead of fetching that columns' data - which returns a list of reference(s) - you can fetch straight the value itself by identifying the hidden column/field, usually with format gristHelper_Display2.

nested dictionary comprehension to select many fields you want at once

list_WN001_archive = [
  {
    key: value
    for key, value in record._asdict().items()
    if not key.startswith("gristHelper")
  }
  for record in grist_WN.fetch_table('WebReg')
  if record.discard or record.reg or record.over
]

see https://community.getgrist.com/t/api-crud-with-native-grist-object/541

using zebra stripes on large tables

https://community.getgrist.com/t/highlight-active-row-column-on-selection-change/443

downloading document data as a file

https://community.getgrist.com/t/api-call-to-download-table-as-csv/431/3

Case Study

I was asked by Grist to provide a case study.

See !apps/grist-case-study

CSV Viewer

04 Sep 2023

Grist just launched "CSV Viewer": Open source, in-browser, interactive CSV viewer installed with two lines of HTML.

Grist CSV Viewer | Grist

getgrist.com

https://www.getgrist.com/grist-csv-viewer/