Calculate monday.com column value based on other items in the board

Update the number column values in a Monday board when the number columns of other items change. Get started to learn more.

What is ScriptRunner Connect?

ScriptRunner Connect is an AI assisted code-first (JavaScript/TypeScript) integration platform (iPaaS) for building complex integrations and automations.

Can I try it out for free?

Yes. ScriptRunner Connect comes with a forever free tier.

Can I customize the integration logic?

Absolutely. The main value proposition of ScriptRunner Connect is that you'll get full access to the code that is powering the integration, which means you can make any changes to the the integration logic yourself.

Can I change the integration to communicate with additional apps?

Yes. Since ScriptRunner Connect specializes in enabling complex integrations, you can easily change the integration logic to connect to as many additional apps as you need, no limitations.

What if I don't feel comfortable making changes to the code?

First you can try out our AI assistant which can help you understand what the code does, and also help you make changes to the code. Alternatively you can hire our professionals to make the changes you need or build new integrations from scratch.

Do I have to host it myself?

No. ScriptRunner Connect is a fully managed SaaS (Software-as-a-Service) product.

What about security?

ScriptRunner Connect is ISO 27001 and SOC 2 certified. Learn more about our security.

📋 Overview

This template calculate and set cost percentage for each item in the same group when cost column is updated in an item.

🖊️ Setup

• Configure the API Connection and Event Listener by creating a connector for monday.com, or use a existing one.
• Navigate to Parameters and edit the following parameters:
  • SOURCE_COLUMN_LABEL - Enter the source column name (column type Number).
  • TARGET_COLUMN_LABEL - Enter the target column name (column type Number).

🚀 Usage

Update the 'Source' column for an item in a monday.com board. After successfully processing the event, the cost percentage of this item relative to the total cost of the group in the 'Target' column should be set to the group within the board.

import { UpdateColumnValueEvent } from '@sr-connect/monday/events';
import Monday from "./api/monday";

/**
 * Entry point to when "any column changes" event is triggered
 *
 * @param event Object that holds When any column changes event data
 * @param context Object that holds function invocation context data
 */
export default async function (event: UpdateColumnValueEvent, context: Context): Promise<void> {
    if (context.triggerType === 'MANUAL') {
        console.error('This script is designed to be triggered externally or manually from the Event Listener. Please consider using Event Listener Test Event Payload if you need to trigger this script manually.');
        return;
    }

    // Extract necessary parameters from the event
    const { boardId, groupId, columnId, columnTitle } = event.event;

    // Check if the necessary parameters are present in the event
    if (!boardId) {
        throw Error("Board ID not present in the event");
    }
    if (!groupId) {
        throw Error("Group ID not present in the event");
    }
    if (!columnId) {
        throw Error("Column ID not present in the event");
    }
    if (!columnTitle) {
        throw Error("Column Title not present in the event");
    }

    const { SOURCE_COLUMN_LABEL, TARGET_COLUMN_LABEL } = context.environment.vars;

    // Check if column changes is related to Subitem column
    if (columnTitle !== SOURCE_COLUMN_LABEL) {
        // If not, then halt the script execution
        return;
    }

    // Get all the columns in the board, and items in the group related to item event dispatched from a board in monday.com
    const dataResponse = await Monday.Board.getBoards({
        args: {
            ids: [boardId],
        },
        fields: {
            columns: {
                fields: {
                    id: true,
                    title: true,
                },
            },
            groups: {
                args: {
                    ids: [groupId],
                },
                fields: {
                    id: true,
                    items_page: {
                        fields: {
                            items: {
                                fields: {
                                    id: true,
                                    column_values: {
                                        args: {
                                            ids: [columnId],
                                        },
                                        fields: {
                                            text: true,
                                        },
                                    },
                                },
                            },
                        },
                    },
                },
            },
        },
    });

    // Find the target item columns in the board
    const targetItemColumnId = dataResponse.data.boards[0].columns.find(c => c.title === TARGET_COLUMN_LABEL)?.id;

    // Check if the target item column is present in the board
    if (!targetItemColumnId) {
        throw Error("Item column not found");
    }

    // Calculate the total cost for all the items
    var items = dataResponse.data.boards[0].groups[0].items_page.items.map(item => Number(item.column_values[0].text ?? '0'));
    const totalCost = items.reduce((acc, item) => acc + item, 0);

    // For each item in group
    for (const item of dataResponse.data.boards[0].groups[0].items_page.items) {
        // Get item cost
        const itemCost = Number(item.column_values[0].text ?? '0');
        // Calculate percentage cost for this item
        const percentageCost = calculatePercentageCost(itemCost, totalCost);
        // Update the item using the percentage cost for each item
        await Monday.Column.changeMultipleColumnValues({
            args: {
                item_id: Number(item.id),
                board_id: boardId,
                column_values: { [targetItemColumnId]: percentageCost },
            },
            fields: {
                id: true,
            },
        });
    }

}

// Calculate percentage of total cost
function calculatePercentageCost(itemCost: number, totalCost: number): number {
    return (itemCost / totalCost) * 100;
}