How to automatically Sync your Google Sheet with Monday.com

I bumped into this helpful discussion in the Monday.com forum when researching how to do a Google Sheet to Monday.com integration for a client.

However, the instructions were too complicated for someone who isn't a developer.

This tutorial provides a straightforward, easy-to-follow guide on integrating the two platforms, which anyone can understand and use.

Let's assume we are doing lead generation, and we want to ensure the leads added to a google sheet sync automatically to a Monday.com board.

Create a Google Sheet and a Monday.com Board

The first step is to create our lead generation sheet with the columns we intend to collect.

Next, create a board with similar columns on Monday.com.

Adding AppScript for automation

We will use AppScript to send data back and forth between the two platforms.

Apps Script is a rapid application development platform that makes creating business applications that integrate with Google Workspace easy.

Click on Extensions and select AppScript from the dropdown.

To update the board, we will need mondayAPIToken and the Board id.

Replace your code with the code below:

const scriptProperties = PropertiesService.getScriptProperties();
const mondayAPIKey = scriptProperties.getProperty('mondayAPIKey');

// ID of destination board
var boardID = 1162408938; // change this :)

// list that stores the column IDs of the monday.com columns to be updated (ordered left to right)
var colMappings = ["name","text0", "status", "date4","email"]; // change this :)

function makeAPICall(key, query, variables) {
  var url = "https://api.monday.com/v2";
  var options = {
    "method" : "post",
    "headers" : {
      "Authorization" : key,
    },
    "payload" : JSON.stringify({
      "query" : query,
      "variables" : variables
    }),
    "contentType" : "application/json"
  };
  var response = UrlFetchApp.fetch(url, options);
  Logger.log("API results: " + response.getContentText());
  return response;
}

function getPulseID(key, pulseName) {
  var query = "query($board:Int!, $name:String!){items_by_column_values(board_id:$board, column_id:\"name\", column_value:$name){id}}";
  var variables = {
    "board" : boardID,
    "name" : pulseName
  };
  var data = makeAPICall(key, query, variables);
  Logger.log(data.getContentText());
  return data;
}


function updateCell(e) {
  
  // declare pulse ID based on column 6
  var rowNumber = e.range.getRow();
  var name = e.range.getSheet().getSheetValues(rowNumber,1,1,1)[0][0];
  var pulseID = e.range.getSheet().getSheetValues(rowNumber,6,1,1);
  // get pulse ID if column 6 is empty
  if (pulseID == 0) {
    Logger.log("No pulse ID given.");
    pulseID = JSON.parse(getPulseID(mondayAPIKey, name)).data.items_by_column_values[0];
    // if item doesn't exist, create it
    if (typeof pulseID == "undefined") {
      var query = "mutation($board:Int!, $name:String!){create_item(board_id:$board, item_name:$name){id}}";
      var variables = {
        "board" : boardID,
        "name" : name
      };
      pulseID = JSON.parse(makeAPICall(mondayAPIKey, query, variables)).data.create_item.id;
    }
    else {
      pulseID = pulseID.id;
    }
    Logger.log("Pulse ID returned was " + pulseID);
    e.range.getSheet().getRange(rowNumber, 6).setValue(pulseID);
  }
  
  // update columns
  
  for (var i = 0; i < colMappings.length; i++) {
    var cellValue = String(e.range.getSheet().getSheetValues(rowNumber,(i+1),1,1)[0][0]);
    // properly format the status
    if(i ==2){
      cellValue = {"label" : cellValue};
    }
    //properly format date
    if(i == 3){
      cellValue = {"date" : Utilities.formatDate(e.range.getSheet().getSheetValues(rowNumber,(i+1),1,1)[0][0],"GMT+3","YYYY-MM-dd")};
    }
    //properly format the email
    if(i ==4){
      cellValue = {"email" : cellValue,"text" : cellValue};
    }
    query = "mutation($item:Int!, $board:Int!, $val:JSON!, $col:String!){change_column_value(item_id:$item, board_id:$board, column_id:$col, value:$val){id}}";
    variables = {
      "item" : parseInt(pulseID),
      "board" : boardID,
      "col" : colMappings[i],
      "val" : JSON.stringify(cellValue)
    };
    var res = JSON.parse(makeAPICall(mondayAPIKey, query, variables));
  }
}

First thing first, let's add a Script Property mondayAPIKey to AppScript.

1. Click on the Project Settings icon ⚙️ from the AppScript window.
2. Scroll down to the Script Properties section and click Add Script Property.
3. Enter "mondayAPIKey" as Property, paste the API Token you obtained from Monday.com in the value input, and save.

How do you obtain the Monday.com column ids?

You need to activate "developer mode".

1. Click your profile picture on the bottom left, and select "monday.labs".
2. In the new window, scroll down to "Developer mode" and click on the "Activate" button:

When you go back to your board, you should be able to see the column id as shown below:

Depending on the column types on your board, you need to properly format and add appropriate properties before sending the data.

For example, in this tutorial, we have the label property for the status column, the date property for the date column and the email property for the email column.

Check the column type documentation for a comprehensive list of all the column types supported on Monday.com.

Create a OnEdit Trigger

Finally, create a trigger for your Apps Script function that should be called anytime the Google Sheet is edited.

From the Script Editor window, click "Triggers" from the side menu, then select "Add Trigger" in the next window.

Update your form as shown in the image below and click on save.

Testing our automation

Let's add a lead to our sheet and see if an item would be automatically added to our board.

Here we go:

We have successfully created a basic automation that syncs a Google Sheet with a Monday.com board.

Do you need help creating such integrations? Book a call with me today.