Synchronizing Monday.com and Google Sheets

A very common use case we’ve run into is having the ability to synchronize data between Google Sheets and another platform. Whether it’s CRM software or project management software, our users want to be able to keep records up to date via Google Sheets.

You get a few benefits from doing so:

• You isolate your core environment (CRM or project management software, for example) from the users that will be operating on the Google Sheet.
• The Google Sheets users can focus on working in an environment they’re familiar in, meaning you don’t need to do any retraining on a new platform
• Less clutter, less access needed to the core platform, and less chance for error
• Google Sheets is great for dealing with creating bulk data very quickly, versus other platforms

In this article we’re going to take a look at how you would perform synchronization between the popular Monday.com project management software and a Google Sheet. Specifically, we’ll look at how you can create new items under a predefined Board on Monday.com via Google Sheets.

How It Works

SheetDream will look for new records in a Google Sheet that have not yet been assigned an item ID and have completed fields: meaning we haven’t inserted them into Monday.com yet, and the user has successfully filled out all the fields in the row before we submit it to Monday.com.

Our example is going to be customer service: someone logging some necessary maintenance on the Google Sheets side, which will trigger the creation of a customer service ticket on the Monday.com side.

A good example of how this could potentially be useful: working with lots of data is simpler with spreadsheets than it is with manually inputting one record at a time. Especially when you are creating said data.

Imagine a scenario where the work day has just begun: and there is a backlog of customer requests that have come in from numerous different sources (email, phone, etc.). It may be more efficient to have an extremely simple worksheet where the user can move from one request to the next extremely quickly, a scenario where spreadsheets are extremely beneficial compared to interfaces where you’re working on a record by record basis.

Where Do We Start?

First thing’s first, we need to get our Monday.com API stuff in order before we can continue. We need to find our API key so that we can access the API, and we need to actually call the API to see exactly what it returns before we can store anything into Google Sheets.

On the bottom left hand side of the screen of Monday.com is your user profile: click it, and then there will be a menu option for “Admin”:

From there, the next page that opens up will have a left hand menu option aptly called “API”. You’ll be able to grab your API key from there:

With the API key in hand, put it somewhere safe where no one else can see it.

Then in SheetDream, we’re going to configure our API task that will be sending new data off to Monday.com.

There’s obviously a lot going on here, so we’ll take a closer look at what is happening. We’ll start with the Query field, which is where we tell SheetDream what endpoint we want to call and set any environment variables:

$.MondayStories[($not($exists(`Monday ID`)) or `Monday ID`="") and Request and Requester].{
    "__url": "https://api.monday.com/v2",
    "__id": _id
}

We’re calling Monday.com’s GraphQL endpoint, and we’ll only do so if the record we’re dealing with does not yet have a “Monday ID” field set, and both Request and Requester are not blank.

We set the _id value of the record as an environment variable so we can access it later in the API call.

We’re passing our API token directly in the header.

And for the GraphQL request body, our JSONata looks like this:

$.{
  "query": "mutation($itemName:String) { create_item (board_id:, group_id: \"topics\", item_name: $itemName) { id } }",
  "variables": {
      "itemName": $.MondayStories[_id=%.__id].Request & " - " & $.MondayStories[_id=%.__id].Requester
  }
}

This GraphQL will create a new customer service request under “Open Requests” on this screen, from the Monday.com example customer service request template:

We make sure in the Data & Importing section of the API configuration that we set the Monday ID field after receiving an ID back from the Monday API call:

And that’s it! We’re integrated.

What we have here is a small tool in Google Sheets for quickly and efficiently creating new customer service requests to be worked in Monday.com.

Obviously, we skipped over a few of the details:

• The Monday.com GraphQL syntax their API uses
• JSONata syntax, as we cover that fairly well in our tutorial.

But! That is why we’re here to help you. We’re API experts, and can get you going in the right direction very quickly and easily.