Calculate accumulated value¶

This script accumulates the values of the current row and the previous rows, and records the result to the current row. It does the same than the Calculate accumulated value operation from the data processing menu. If there's a grouping rule active on the view, accumulated values will be calculated for each group.Otherwise, values are accumulated for all rows. Please not that this script only supports grouping by a single column.

Here is the structure of the table named Accumulated value you need so that this script could run:

Column name	Value to add	Incremental total	Grouping column
Column type	number	number	single select

from seatable_api import Base, context
from seatable_api.date_utils import dateutils
"""
This script accumulates the values of the current row and the previous rows, 
and records the result to the current row (as the *Calculate accumulated value*
operation from the data processing menu).
"""

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

table_name = 'Accumulated value'
view_name = 'Default View'

# Name of the column that records total number at a specific time
value_column_name = 'Value to add'
# Name of the column that need to calculate incremental value
incremental_column_name = 'Incremental total'

view = base.get_view_by_name(table_name, view_name)
rows = base.list_rows(table_name, view_name)

# If current view is a grouped view
if 'groupbys' in view and len(view['groupbys']) > 0 :
#    # Get group view rows
    grouping_column = [c for c in base.list_columns(table_name) if 'column_key' in view['groupbys'][0] and c['key'] == view['groupbys'][0]['column_key']]
    if grouping_column and len(grouping_column) == 1 :
            grouping_column_name = grouping_column[0]['name']
    group_values = []
    for row in rows :
        if row[grouping_column_name] not in group_values :
            group_values.append(row[grouping_column_name])
    for value in group_values :
        group_rows = [r for r in rows if r[grouping_column_name] == value]
        incremental_total = 0
        for row_index,row in enumerate(group_rows) :
            current_number = row[value_column_name];
            if current_number :
                # Calculate increment
                # If there is no previous row, set increase_count to 0
                previous_number = 0 if row_index == 0 else incremental_total
                increase_count = current_number + previous_number
                incremental_total = increase_count
                # Set calculated increment to row
                base.update_row(table_name, row['_id'], {incremental_column_name: increase_count})
else :
    incremental_total = 0
    for row_index,row in enumerate(rows) :
        current_number = row[value_column_name];
        if current_number :
            # Calculate increment
            # If there is no previous row, set increase_count to 0
            previous_number = 0 if row_index == 0 else incremental_total
            increase_count = current_number + previous_number
            incremental_total = increase_count
            # Set calculated increment to row
            base.update_row(table_name, row['_id'], {incremental_column_name: increase_count})