Sync Excel tables and monday.com boards

Monday.com boards store data in a tabular format, much like a spreadsheet does.

Boards make it easy for people to collaborate but editing and analyzing data in Excel is much easier and more powerful than in monday.com boards.

MondayLink lets you sync data between Excel tables and monday.com boards, and get the best of both worlds.

Link a board to an Excel table

MondayLink lets you set up a link between Excel tables and monday.com boards. Once the link is in place, changes can easily be propagated in either direction.

From monday.com into Excel

If you're starting with an existing monday.com board that you want to edit or analyze in Excel, you can pull it into Excel using the "Pull Board" command in the ribbon:

From Excel into monday.com

If, on the other hand, you're working on an Excel table that you want to publish to monday.com as a new board, you can do so using the "Push table" button in the ribbon.

Before pushing the Excel table, the table must have a "_name" column. This column will contain the row headers. For example, if the table contains a list of employees, the _name column should contain the full name of each employee. If the table doesn't have a column that is a good fit for this purpose, you can define a new _name column and populate it with artificial values like "Item1", "Item2", etc...

The "Push" dialog lets you name the new board, choose which workspace to put it in, and configure column types and board access.

The push operation will create two extra columns in the Excel table: the _id column that links an Excel row to a monday.com row, and the _group column that determines which group each row belongs to. More on these later in this post.

Synchronizing changes

Pulling or pushing a table to/from monday.com isn't just a one-off operation. It also sets up a link between the Excel table and the monday.com board. This link allows syncing changes in both directions.

Pulling changes (Refresh)

If you would like to refresh the data in an Excel table that is linked to a monday.com board, you can do so by clicking the "Refresh" button.

Pushing changes (Save)

If you've made changes to the Excel table and you want to push those changes to the board, you can do so by clicking the "Save" button.

Mapping columns

Table columns and board columns are mapped by name. Any column that does not have a counterpart on the other side is ignored during synchronization.

This applies to all columns except the three special columns: _id, _name, and _group.

The _name column

Each monday.com board has a column which controls the header of each item. This is the only mandatory column in a board. It cannot be deleted and all rows must have a value for it.

This column is mapped by MondayLink to the _name column in Excel which every table must have if it is to be linked to a board. All rows must have a value in this column.

The _id column

Each row in a monday.com board has an Id. This ID can be seen in the URL when an item in the board is selected.

MondayLink stores the ID of each item in the _id column and uses it during synchronization to match the rows and copy any changed values.

The _group column

Each row in a monday.com board belongs to a group. Excel tables do not have groups, but MondayLink uses the _group for this purpose.

You can assign any value to the _group column in Excel. During a "Save" operation, any groups that you specified that are not already in the linked board will automatically be created.

Type conversions

Monday boards have many more data types than Excel. For example, a board can define a column that stores email addresses or phone numbers. MondayLink will convert these to the most natural representation in Excel. For example, an "email" column will be treated as text, while a "rating" column will be treated as a number between 1 and 5.

Some data types, though, are not supported (yet) by MondayLink. Currently, MondayLink supports mapping the following kinds of board columns:

• Number
• Text
• Date
• Email address
• Phone number
• Rating
• Checkbox

Support for other column types can be added fairly easily, as needed.

Adding or deleting columns

If, after a table is already linked to a board, you decide that you need an additional column, simply add the column in both places. If you need to remove a column or rename it, simply do so in both places.

Adding or deleting rows

When adding a row to a linked Excel table, you might be wondering what to put in the _id column. Since the row does not yet have a corresponding entry in the monday.com board, simply leave this column empty. You should, however, populate _name column, while populating the _group column is optional.

Deleting rows is very simple, though. When you delete a row in Excel and perform a "Save" operation, the row will get deleted in the monday.com board as well.

Managing connections

The link between an Excel table and a monday.com board is saved inside the workbook itself, so if you send the workbook to another user who has MondayLink installed, they will be able to see the links.

To view the list of active links in the workbook, click the "Manage connections" button.

Avoid renaming linked tables

A link maps a table (by name) to a monday.com board (by id). This means that Excel tables should not be renamed, or they will get unlinked. Monday boards, however, can be freely renamed as they are mapped by id and not by name (Excel tables unfortunately do not have an Id).

Shortcuts and buttons

To make synchronization easy to invoke, MondayLink defines two shortcuts that you can use while the selected cell belongs to a linked Excel table.

The default shortcut for refreshing data in an Excel table (pull data from monday.com) is Ctrl+Shift+R.

The default shortcut for saving data from Excel to a corresponding monday.com board is Ctrl+Shift+S.

These can be configured by the user if needed:

Additionally, each mapped table gets additional context menu commands that allow invoking synchronization.

Performance

MondayLink's performance is determined by the throughput of the monday.com API.

Boards can be read fairly quickly (~200 rows/s) but writing rows is a bit slower (e.g. 10-30 rows/s). For this reason, when saving changes from Excel, MondayLink first reads the entire board to determine the set of changes and then only applies those changes (instead of uploading the entire table). If, for example, the table has 1k rows but only a few rows have changed, the save operation will take a second or two. However, if all rows have changed, the update could take a minute or two.

VBA API

MondayLink offers a simple VBA API which can be used to refresh or save one or more linked tables.

To refresh a list of tables, use the following VBA code:

Call CreateObject("QueryStorm.Runtime.API").SendEventWithDataTo("RefreshTables", "table1, table2, table3", "MondayLink")

To refresh all linked tables int the workbook pass in "*" as the 2nd parameter of the SendEventWithDataTo method.

To save one or more tables, send a "SaveTables" event instead, like so:

Call CreateObject("QueryStorm.Runtime.API").SendEventWithDataTo("SaveTables", "table1, table2, table3", "MondayLink")

Benefits

I've talked a lot about how to sync. Here are some of the benefits you can get by extending monday.com with Excel:

• Streamline data entry - using Excel's flash fill for bulk editing, pasting data from the clipboard, easier to enter data directly, easier to pull in data from other sources.
• Processing data - Excel has a much more extensive and powerful suite of functionality for crunching numbers, processing text, working with dates, etc...
• Data analysis and plotting - Excel has (more powerful) pivot tables and superior graphing capabilities compared to monday.com boards
• Board data backup - using an Excel spreadsheet to back up data from a monday.com board. Since Excel files have built-in version control through OneDrive, this can also be used for version-controlling monday.com board data.

Installing MondayLink

Before we can start linking tables and boards, we must first install MondayLink.

This is a two-step process:

• Step 1: Download and install the QueryStorm runtime. This is a free 4MB installer that allows installing apps built with QueryStorm into Excel.
• Step 2: Once the QueryStorm runtime is installed, use it to download and install MondayLink

Configuring MondayLink

To allow MondayLink to access your boards, you must provide it with your monday.com API key. To get the API key, open the admin section in monday.com.

Then, copy the API token from the "API" section into the clipboard.

Once you have the API token in the clipboard, paste it into MondayLink

The token grants API access to your Monday.com account. To safeguard it, MondayLink encrypts it before storing it in a file. The encryption is done using the windows data protection API which ensures that only your Windows user will be able to decrypt the stored API key.

Once you enter the API key, you can start linking Excel tables and Monday.com boards.