Tutorial: get email address from Creator/Collaborator/Last Modifier column

[cdb]

Hey everybody,

this is a short tutorial about how to use python to solve a concrete problem in SeaTable.

The challenge

Some days ago, one of our users asked me if it is possible to get the email address from a creator/collaborator or last modifier column. He wanted to send emails to these users via automation, but these columns are not yet supported in email sending via automations.

To be precise: SeaTable can send a notification to the user Ernie or Krümelmonster, but it cannot send E-Mails to these users. (probably in the future but right now, this is not possible)

I told him that he should save the contact_email of the users to a separate column. But this column should be filled either …

1. via a button
2. or via an automation as soon as the column user changes

Here is the base I just created to develop such a solution and how the solution looks and feels.

The solution

I solved this problem with a Python script that is executed via an automation every time the column user changes. Because automations are only available in the Enterprise plan, I also make it possible to use this script with a button.

The Python script does the following:

• SeaTable only delivers the auth.local address of the users from the user-columns
• to get the email of a user we have to get a list of all collaborators of this base with their auth.local and their contact_email.
• if we have this list we search this list for our auth.local address
• important: to execute this API-call we need to determine the base_uuid and the api-access-token. The api_token is not enough.

Here is the result:

from seatable_api import Base, context

server_url         = context.server_url or 'https://cloud.seatable.io'
api_token          = context.api_token or '5351f1ed416227c582de8818f50233a73734bfc4'
table_name         = 'Table1'
user_column_name   = 'user'
target_column_name = 'email'

##########################################
## no changes necessary below this line ##
##########################################

import json
import requests

base = Base(api_token, server_url)
base.auth()

##
## get access_token and base_uuid (https://api.seatable.io/#3b782fd2-6091-4871-acc7-2725bfc7e067)
##
webhook_url = server_url + '/api/v2.1/dtable/app-access-token/'
headers = {
    'Content-Type': 'application/json',
    'authorization': 'Token ' + api_token
}
response = requests.get(webhook_url, headers=headers)
access_token = response.json()['access_token']
dtable_uuid = response.json()['dtable_uuid']

##                                             
## get list with collaborators for this base (https://api.seatable.io/#36595ea6-21ef-49f3-9251-0336e675438a)
##                                                                                   
webhook_url = server_url + '/dtable-server/api/v1/dtables/' + dtable_uuid + '/related-users/'
headers = {
    'Content-Type': 'application/json',
    'authorization': 'Token ' + access_token
}
response = requests.get(webhook_url, headers=headers)
user_list = response.json()['user_list']
# returns something like: [{'email': 'a7e6b3cd6322451fb6bb6647481f289d@auth.local', 'name': 'kleines Monster', 'avatar_url': 'https://stage.seatable.io/media/avatars/3/a/be9e6925dd3c2bdfaa9b35cf53efb6/resized/80/d1130ccee33b283fe8c772a7e03799bb.png', 'contact_email': 'kleinesmonster@seatable.de', 'id_in_org': '', 'name_pinyin': 'kleines Monster'}, {'email': '824802fe00d048a18ef8c02893ba6ca3@auth.local', 'name': 'Ernie', 'avatar_url': 'https://stage.seatable.io/media/avatars/d/8/94b64eb7bfbb88fa1789f8da49d878/resized/80/1b6f49825bb84bc971c7ea89155937e1.png', 'contact_email': 'ernie@seatable.io', 'id_in_org': '', 'name_pinyin': 'Ernie'}]

##
## get user from current row (buttom-column or automation)
##
current_row = context.current_row or base.get_row('Table1', 'c8km8hqLRYy_-n1hxQZFjx')
current_row_id = current_row['_id']
user_we_search_for = current_row[user_column_name][0]

for user in user_list:
    if user['email'] == user_we_search_for:
        user_email = user['contact_email']

##
## write result to email column
##
row_data = {
    target_column_name: user_email
}
base.update_row(table_name, current_row_id, row_data)

Just save this script as a python-Script and change the input parameters at the beginning of the script. Then execute this script either via automation or via a button.

Here is the button:

Here is the automation:

I am excited for your feedback.
Christoph

---

Important update from 29.8.2024

If you use an automation to trigger this script, please make sure that you only ran this automation once. Otherwise you will create an automation loop.

[Ben]

That’s exactly what I need, but could it be possible to have the javascript code that would be triggered by the addition of a collaborator ?

[cdb]

An automation runs on the SeaTable server. JavaScript is executed on the browser of the user.
Therefore, it is not possible to trigger JavaScript from an automation in SeaTable.