[Showcase + Feature Request] Reusable user code repository

Hi everyone,

inspired by @Travitron’s topic I thought it might be a good idea to create a repository for reusable user code here on Github. Take a look here.

What’s reusable user code?
The idea is that once you start writing more complex formulas, many of them will eventually be doing very similar things. So it makes sense to write your own user-made functions that can be reused everytime you need that particular functionality in a formula. Grist unfortunately doesn’t (yet?) offer a way of instituting some kind of central user code library within your document, but with a neat little trick, something not unlike that is quite possible to achieve. Basically, you create a table that’s just full of useful functions. See here how to do that. The code you can put in there and then use from everywhere in your formulas might be called, for lack of a better term, “RUC” or reusable user code.

How to use?

The repo readme contains a more comprehensive introduction and explains how to get set up.

Please join in and share yours!
Pull requests are very welcome - let’s throw in all the useful little functions that we’ve come up with over the years!

I’ll start with some contributions of my own. @natalie-grist and the rest of the team, perhaps we could sticky this?

PS. By way of a feature request: Grist really needs something like this built-in, pretty please! I’m not the first on to point this out, either… see here, here and here.
Ideally you could even take it above the document level - have the “RUC” sit in a central spot that’s available to, say, all documents in a workspace, just as what’s already been suggested for user/ACL management elsewhere.

3 Likes

Here goes a first contribution. Feedback welcome!

Working with tables
from grist import UserTable
from docmodel import global_docmodel as gdm

class Table:
  @classmethod
  def get(cls, name: str) -> UserTable:
    """Get a UserTable by name.
    Note: You can turn a UserTable into a Table by doing 'your_usertable_here.table'.
    """
    try:
      return gdm.get_table(name)
    except KeyError:
      raise KeyError(f"No such table '{name}'.")
  
  @classmethod
  def get_all(cls, exclude_internal: bool=True, exclude_uppercase: bool=True) -> list[UserTable]:
    """Get all UserTables in the document. By default, Grist-internal tables as well as tables named in all caps are excluded.
    Note: You can turn a UserTable into a Table by doing 'your_usertable_here.table'.
    """
    return [x.user_table for x in gdm._engine.tables.values() if not (exclude_internal and x.table_id.startswith("_")) and not (exclude_uppercase and x.table_id.isupper())]



return Table
Working with records
from grist import UserTable, Record
from docmodel import global_docmodel as gdm

class Record:
  @classmethod
  def create(cls, table: UserTable, fields: dict=None) -> Record:
    """Create a record in 'table' and optionally fill it with the values given in 'fields', then
    return the new record.
    
    Arguments:
    table      The table. Should be of type UserTable, which is what Grist gives you when you just
               type the name of a valid table in a formula.
    fields     A dict of layout {column_name: value_to_put} specifying how to fill out the newly
               created record. Defaults to None, for an empty record.
    """
    fields = fields or {}
    return next((r for r in gdm.add(table, **fields)), None)
  
  @classmethod
  def update(cls, record: Record, fields: dict) -> None:
    """Update 'record' according to 'fields'. The latter must be a dict of layout {column_name: value_to_put}."""
    gdm.update([record], **fields)
  
  @classmethod
  def get_fields(cls, record: Record) -> list[str]:
    """Get all fields/aka column names from a 'record'. This excludes Grist-internal invisible columns like "manualSort"."""
    return [field for field in dir(record) if not field.startswith(("#","_")) and not field in ("id", "manualSort")]


return Record
Working with reference/reference list columns and invalid user input
import ast, re
from types import FunctionType
from grist import UserTable, Record, RecordSet
from objtypes import AltText

class Input:
  @classmethod
  def create_ref_from_input(cls, input: AltText|str|Record, table: UserTable, field_name: str="", fields: dict=None, input_processing_cb: FunctionType=None) -> Record:
    """Transform raw string input into a value acceptable for putting into reference-type columns by creating a linked record according to the input if no such record exists.
    
    Background information: In a reference-type column, selecting a valid option from the dropdown menu produces input of type Record. Typing something invalid into the cell
    will produce an AltText containing the input as a string instead. This is used here to create missing linked records as necessary.
    
    Arguments:
    input                   The user input.
    table                   The table that we should create any missing linked records in.
    field_name              When a linked record is created, this specifies the name of the column in 'table' that should get filled with 'input'.
    fields                  A dict of layout {name: value} specifying which other columns to fill out, and with what values, on newly created records.
                            Specifying a string with just an asterisk inside like "*" for a value will cause that value to be replace by 'input'.
    input_processing_cb     A callback function to operate on user input before it gets written to any newly created record. The function takes one parameter, the user input
                            as a string. It should return the processed/cleaned up value.
    """
    if not input or not isinstance(input, AltText|str):
      return input
    input = str(input)
    input = input_processing_cb(input) if input_processing_cb else input
    fields = fields or {}
    if field_name:
      fields[field_name] = "*"
    fields = cls._prepare_fields_dict(input, fields)
    return $Record.create(table, fields) or None

  @classmethod
  def create_reflist_from_input(cls, input: AltText|str|RecordSet, table: UserTable, field_name: str="", fields: dict=None, input_processing_cb: FunctionType=None, list_sep_regex: str=";", prevent_doublets: bool=False) -> list[Record]:
    """Transform raw string input into a value acceptable for putting into reference list-type columns by creating linked records according to the input if no such records exist.
    
    Background information: In a reference list-type column, selecting a valid option from the dropdown menu produces input of type RecordSet. Typing something invalid into the cell
    will produce an AltText instead, containing the input as a string representation of a list of record IDs plus the recently added user input values.
    This function attempts to parse the latter as an actual list, then filter out the items that are new and create the missing linked records for them. Finally, a list of valid
    Records will be returned (which a reference list-type column will accept just like a proper RecordSet).
    
    Arguments:
    input                   The user input.
    table                   The table that we should create any missing linked records in.
    field_name              When a linked record is created, this specifies the name of the column in 'table' that should get filled with 'input'.
    fields                  A dict of layout {name: value} specifying which other columns to fill out, and with what values, on newly created records.
                            Specifying a string with just an asterisk inside like "*" for a value will cause that value to be replace by 'input'.
    input_processing_cb     A callback function to operate on user input before it gets written to any newly created record. The function takes one parameter, the user input
                            as a string. It should return the processed/cleaned up value.
    list_sep_regex          Character or search pattern to split user input up by. This allows users to enter things like "one; two" into the cell and have that create two
                            records "one" and "two" rather than just one "one; two" on the 'table'.
    """
    if not input or not isinstance(input, AltText|str):
      return input
    fields = fields or {}
    if field_name:
      fields[field_name] = "*"
    inputlist = cls.sanitize_reflist_input(input, input_processing_cb=input_processing_cb, list_sep_regex=list_sep_regex)
    reflist = []
    records_to_create = []
    for item in inputlist:
      if isinstance(item, int):
        reflist.append(item)
        continue
      fields_for_this_item = cls._prepare_fields_dict(item, fields)
      if prevent_doublets and (existing_record := table.lookupOne(**fields_for_this_item)):
        reflist.append(existing_record)
        continue
      records_to_create.append((table, fields_for_this_item))
    for info in records_to_create:
      reflist.append($Record.create(info[0], info[1]))
    return reflist or None
  
  @classmethod
  def sanitize_reflist_input(cls, input: AltText|str|RecordSet, input_processing_cb: FunctionType=None, list_sep_regex: str=";") -> list[str|int]:
    """Transform garbage data in a reference list-type column, as caused by invalid user input, into a clean list of valid record IDs
    (representing those items in the reference list that were properly selected using the dropdown menu) and strings of invalid data
    (representing that which the user added by just typing into the cell).
    
    Arguments:
    input                   The user input.
    input_processing_cb     A callback function to operate on the user input before it gets added to the results list.
                            The function takes one parameter, the user input as a string. It should return the processed/cleaned up value.
    list_sep_regex          Character or search pattern to split user input up by. This allows users to enter things like "one; two" into the cell
                            and have that appear as two items "one" and "two", rather than just "one; two", on the results list.
    """
    if not input or not isinstance(input, AltText|str):
      return list(input) if input else []
    input = str(input)
    try:
      input_list = cls.parse_listrepr(input)
    except:
      input_list = [input]
    result = []
    for entry in input_list:
      if isinstance(entry, int):
        result.append(entry)
        continue
      items = re.split(re.escape(list_sep_regex), entry)
      result += [input_processing_cb(item) for item in items] if input_processing_cb else items
    return result

  @classmethod
  def _prepare_fields_dict(cls, input: object, fields: dict) -> dict:
    """Helper function to replace the special "*" value in a column-to-value specification.
    See create_ref_from_input() or create_reflist_from_input() for details."""
    return {key: input if val == "*" else val for key, val in fields.items()}

  @classmethod
  def parse_listrepr(cls, input: str) -> list:
    """Parse a string that looks like a Python list into an actual Python list."""
    input = str(input)
    try:
      return ast.literal_eval(input)
    except:
      raise ValueError(f"Input '{input}' can't be parsed as a list.")



return Input

This is fantastic, thanks for sharing!

Questions/Thoughts:

  1. Can you explain the reason why you’d want to use Table.get? I.e., what is the purpose of turning a UserTable into a Table?
  2. After this thread has matured a bit, it might be a good idea to create a Git repo to store these various classes, and evolve them over time. Plus then they’d be ‘versioned’ and you could always get the latest and add it to your document (even automatically via a script and the Grist API).

T

Hi, you’re welcome, glad if it’s useful to anyone.
I think a git repo is a good idea, too. If I get some time to do it, I’ll set one up.
Regarding your other question: Table.get() takes the name of a table as a str and returns the correct UserTable. This is useful whenever you can’t just type the name of the table into your formula field. For example, you might have a formula that produces table names programmatically, and you need to get the actual tables from that.

1 Like

I’ve updated the example document to put in your Table, Record, and Input classes, and then created a page to show working examples of the functions (just Table and Record for now).

Nice work on the example document. I’ve followed up on your suggestion, too: Here’s a github repo.

I cordially invite everyone reading this to open pull requests and contribute their own reusable code!

is this code supposed to be used on cells?

Hi @TomNit – there is one thing I’m not clear on.

Within your user code examples, you are importing some libraries, such as:

from grist import UserTable, Record
from docmodel import global_docmodel as gdm

Then you’re calling functions within these, such as:

  • gdm.get_table(name) to get context to a table
  • gdm.add(table, **fields) to add a record to a table,
  • gdm.update([record], **fields) to update a record in a table
    etc

I have searched all through the grist-core Git repo and am not finding these items anywhere. Can you shed some light on where these methods are defined? I was curious what else was available to potentially be used to interact with the document via Python code.

Thanks,
T

Hi @Rogerio_Penna, yes absolutely. Here’s an example to illustrate!

Let’s say we have a table “Books”. Each book has an author, or sometimes multiple authors at once. As authors often write more than one book in their lifetime, it makes sense to set up a separate “Authors” table that contains all the authors, and have the “Books” table reference that. So, in “Books”, we set up a reference list column and point it to “Authors”. So far so good, nothing new here.

But suppose your users now want to be able to simply add a new book to “Books” and just type in the title, author(s), and so forth in sequence. Suppose they can’t be bothered with going to the “Authors” table (or a linked widget showing it) and creating the author records first so that they can be selected properly via dropdown. In that case, they’ll probably just type into the reference list column, and that will make it turn red and not be a valid reference. But could we use a formula to correct this input and turn it into a valid reference? Yes, we can, but it takes a lot of fiddling around with Grist internals and things get complicated. So, the RUC repo to the rescue! Go to gristruc/sanitize-refs.md at main · tomnitschke/gristruc · GitHub and paste that code into a separate table, let’s name it “CODE” as per the readme. Then in the reference list column pointing to “Authors”, we can just set up a trigger formula like this:

CODE.lookupOne().column_that_you_pasted_the_code_from_the_repo_in.create_reflist_from_input(value, Authors, "LastName")

Set this formula to trigger on each change to the column. Result: If users type something invalid into your reference list, a record containing what they just wrote will be automatically created in the “Authors” table and added to the reference list. No red cell, no confused users, all bliss and happiness. :smile:

Hey @Travitron, yeah it took me quite a while to figure all of this out, too. These methods are in sandbox/grist/docmodel.py. Take a look at this line in there to get you started; for example, here’s where to find get_table(). Basically, your Grist document always has exactly one docmodel.global_docmodel instance running, which for the sake of brevity we can import as just “gdm” (or any other name, really).

2 Likes

Aha! I see what my issue was finding those methods! I didn’t notice that I was signed out of my GitHub account – and it only searches code if you’re signed in! Confusingly, it doesn’t mention this on the left sidebar where it shows Code results = 0…but if you click on it, then it asks you to sign in. Lesson learned.

I am a new user to grist and I am happy to see this thread. I would suggest that Grist team considers something like what Glide Apps has done or what GitHub Actions team has done with their action - they have enabled power users to create actions/function which can be easily reused by others - there will always be cases where there are some talented programmers who can create function to solve their own needs/others’ needs and others will be using them - So, what Tom is starting as a private github repo can be converted into a model that is workable for all users without them having to set up the Table each on their own and then copying and pasting functions.

+1 on having a centralized accessible “RUC”.

I’d like to see a similar centralized “User Widget Library” which can be browsed from within Grist and then “installed” (added to the list of available Widgets) from within Grist. Separate idea/thread and I don’t want to hijack this one, but it’s along the same vein of building these sorts of things in.

1 Like

@Travitron we’re of the same mind completely. Just leaving my express “+1” here, too, and I’ve also edited my initial post to say as much. @dmitry-grist and team, perhaps you might look into this further? Thanks!

Travitron pointed me here regarding my post…

I have this 12 thousand characters code for creating a Risk Matrix.

As I don´t want to repeat the code in every single record of the Risks Table, I decided to have a table with the code… a single record with one column for each complex code… and just reference the code from the Risks Table.

Not sure it’s the same thing.

Oh yes, it’s definitely the same thing. The entire point of this thread was to demonstrate exactly that: You can have a Grist table entirely filled with code that’s intended to be utilised by formulas elsewhere. Suppose you have a 1000 gazillion lines python function called solve_meaning_of_life, then you could put it in a separate table, let’s call it QuestionBot as per the readme, and finally call it from other formulas by doing: QuestionBot.lookupOne().solve_meaning_of_life().

I just took this approach a step further by providing some actual code to go with it, you know, generally reusable things that can come in handy when working with Grist. For lack of a better term, I put all of these in a repo and called it “reusable user code repo”, or “RUC”.

Bibo’s example in my thread used the “eval” function with code in a data column (not formula column)

eval($No.Formula)

in my case, it’s not working, probably because of the formula complexity, getting values from several different places

Yes, that’s an entirely different approach. It’s something to be considered when you want to enable your users to input python code as actual column content and then have that evaluated somewhere. But for your usecase here, the “code table” approach definitely makes more sense.