Seatble structure using java-script

[freezle]

hey everyone, is there a way using java-script to get a complete structure off all bases (name / columns / rows etc.)? I would like to get this all in a new base. Thanks

[AkDk7]

Hi @freezle.

This is quite few information to be honest.
One option would be to simply duplicate a base for example.

Could you be more specific?
What should happen with existing data?
Are you looking for a synchronizing mechanism?

Greetings
AkDk7

[freezle]

Hey @AkDk7 thanks for the quick reply.

I run seatbale on my own test server and have 24 different bases in 3 groups.
I would like to have 1 base with all the informations of the existing bases on my server. I would like to have a sort of overview. It should include base name , columns and rows (numbers).

Was thinking about something like this. https://forum2.seatable.io/t/tutorial-get-a-complete-folder-structure-from-a-seafile-server-with-a-python-script/1654

Hope this makes sense.

Thanks

[AkDk7]

Hi @freezle.

I think you can’t get over this kind of limitation of the javascript implementation. Javascript in SeaTable, as far as I figured out, is bound to the base in which you execute it.

It would be much easier to write a python script since you can connect to any base by the url of the instance and a per base api key. Then you can fetch all tables and related columns of the bases. Finally you can insert this data in your overwatch base For this you have to extend your installation with FAAS and a runner for python.

[AkDk7]

Would this suit you?

[freezle]

Thank you so much! This would be absolutely perfect.

[AkDk7]

Here you go.
This is a very simple script. It will not empty the target table nor update any row. It only inserts!

from seatable_api import Base, context

##### variables

# Add as many bases as you want. But you have to create an api key for each base you want to read from!
bases = [
    {
        'url': 'https://???',
        'baseName': '???',
        'apiKey': '???',
    },
    {
        'url': 'https://???',
        'baseName': '???',
        'apiKey': '???',
    },
]

targetTableName = 'Overview'

# If you don't run this script from SeaTable change ??? to the target SeaTable url and api key
targetUrl = '???'
targetApiKey = '???'

#####


# you don't need to change anything below this line
targetServerUrl = context.server_url or targetUrl
targetServerToken = context.api_token or targetApiKey
targetBase = Base(targetServerToken, targetServerUrl)
targetBase.auth()

batchRows = []

# run through all bases
for base in bases:
    baseName = base['baseName']
    
    tmpUrl = base['url']
    tmpToken = base['apiKey']
    
    # connect to the source base
    sourceBase = Base(tmpToken, tmpUrl)
    sourceBase.auth()
    
    # get all table information
    metaData = sourceBase.get_metadata()
    
    for table in metaData['tables']:
        tableName = table['name']
        
        # all columns
        columns = [column['name'] for column in table['columns']]
        columns.sort(key = lambda x: x.lower())
        columnText = ', '.join(columns)
        
        # row count
        rowCountResult = sourceBase.query(f'SELECT count(*) AS nbr FROM `{tableName}`')
        rowCount = rowCountResult[0]['nbr']
        
        row = {
            'Base': baseName,
            'Table': tableName,
            'Columns': columnText,
            'Row count': rowCount,
        }        
        batchRows.append(row)

# insert into the target table
targetBase.batch_append_rows(targetTableName, rows_data=batchRows)

[freezle]

Thank you so much! I really appreciate your help!!

[AkDk7]

Awesome, you’re welcome!