I’ve been working with some clients recently who wanted to come up with custom Google Sheets integrations. To give them a taste of our API, I came up with a quick and dirty script to show them how they would send data from Google Sheets to text columns in monday.com.
This example is for developers who have some experience with Google’s scripting platform and may be too technical (or bare bones) for non-developers.
Description
The example below will connect a Google Sheet to a monday.com board, such that every time an item is changed in Google Sheets, the data will update in monday.com.
This is a super quick example, so treat this as a jumping-off point for more complicated integrations!
Setting up the example
To start, you’ll want a Google Sheet with a header row:
Finally, you’ll need to change some variables to correspond to your environment. Add your API key to line 2, change the board ID on line 5, and update the array on line 8.
Hey Johnathon! I created this as an add-on for simplicity, but I think you could do it with an onEdit trigger too. I had to create the script and then add it to the Google Sheet as an installable trigger.
Hey @dipro. I was able to do some really interesting stuff with your code snippet as a starting point, Google apps scripts, and Monday.com Automations.
This is great - are there any scripts where Sheets can simply fetch the values in a Monday board, almost like an IMPORTRANGE in Sheets? Would love to be able to have a mirrored version in sheets, but want all updates/editing to be done in Monday. Sheets would just be a “public view”.
Hi Dipro, just checking i’ve unsertood what this should do correctly as i can’t get it to work; if i create a spreadsheet with the headers and data filled in, then create a blank monday board with the same column headers, then the script will copy that data onto the monday board?
I’m not a developer and haven’t used scripts before (or google sheets much tbh) so i dont know what you mean by ‘add it to the google sheet as an installable trigger’, I had added it to my sheet with Tools>Script Editor , pasted the script into that, altered the relevant lines, saved it, then I’ve run it from the sheet by going to Tools>Macros>myFunction ,the first time it prompted me to allow Monday access to my gmail account but I’ve run it several times and my Monday board stays blank - i can’t see it flagging up any errors.
I am using a personal gmail account - should it work from this or will it only work with the enterprise ones? Sorry if i am missing something really basic but i am new to this and any pointers you can give would be much appreciated
Andrew
I tried your code.gs(from github) to my google spreadsheet.
However, this will not work, and the following warning statements will be displayed:
makeAPICall : Exception: Attribute with invalid value (Header:null) provided (‘code’ file, line 23)
getPulseID : Exception: Attribute with invalid value (Header:null) provided (‘code’ file, line 23)
updateCell : TypeError: Cannot read property ‘range’ of undefined(‘code’ file, line 43)
I’m not a professional developer. Can you help me?
My guide was intended for developers who are interested in use cases for the monday.com API. I realize that this was not clear in the original post, so I’ve edited it for clarity.
If you’re looking to build a connection between Google Sheets and monday.com, I recommend connecting with our Partners team. They can put you in touch with a developer who can build a custom solution based on your needs! Check out the “Hire a Specialist” category to connect with someone!
I have this working wonderfully, going as far as having a very complex branching Google Form submitting to different boards based on the selections made in the form, very awesome!
However, I can’t figure out why the DateColumn is not working. I’ve tried more formats than I can count both in the active Sheet and converting it using JavaScript before using JSON.stringify(cellValue).
Going from the example, I was hoping sending the date as a string in the format of YYYY-MM-DD would work, but I get:
When you get values from your sheet you have to format the cell value. There are two ways to do it. Either in your script or in your sheet then call the display values not the values from the sheet. Here is an example of what I mean. Happy to help further if needed.
var ss = SpreadsheetApp.getActive();
var sh = ss.getSheetByName(‘enterSheetName’);
var values = sh.getDataRange().getValues();
for(i=1;i<values.length;i++){
if(values[i][7]==""){ //where column H has pulseIDs
var date = Utilities.formatDate(values[i][6], “GMT-4”, “YYYY-MM-dd”); //where column G has the unformatted date
var query = “mutation($board:Int!, $name:String!,$colvals:JSON!){create_item(board_id:$board, item_name:$name, column_values:$colvals){id}}”;
var variables = {
“board” : boardID,
“name” : values[i][0], //where column A has the name I want for the item
“colvals”: JSON.stringify({“text”:values[i][1],“date”:{“date”:date}}) //calling the now formatted date variable
};
var pulseID = JSON.parse(makeAPICall(mondayAPIkey, query, variables)).data.create_item.id;
sh.getRange(i+1, 8).setValue(pulseID)
}
}
Alternatively, if you use the .getDisplayValues() and you have the date column on your sheet formatted as “YYYY-MM-dd” it will work too. Like this:
var ss = SpreadsheetApp.getActive();
var sh = ss.getSheetByName(‘enterSheetName’);
var values = sh.getDataRange().getDisplayValues(); //note this is different than .getValues()
for(i=1;i<values.length;i++){
if(values[i][7]==""){ //where column H has pulseIDs
var query = “mutation($board:Int!, $name:String!,$colvals:JSON!){create_item(board_id:$board, item_name:$name, column_values:$colvals){id}}”;
var variables = {
“board” : boardID,
“name” : values[i][0], //where column A has the name I want for the item
“colvals”: JSON.stringify({“text”:values[i][1],“date”:{“date”:values[i][6]}}) //where column B has the text desired and column G has the date in the proper display format.
};
var pulseID = JSON.parse(makeAPICall(mondayAPIkey, query, variables)).data.create_item.id;
sh.getRange(i+1, 8).setValue(pulseID)
}
}
I was definitely feeding in the wrong formatting for the DateColumn type.
These were the pieces I was missing:
var date = Utilities.formatDate(values[i][6], “GMT-4”, “YYYY-MM-dd”);
“colvals”: JSON.stringify({“text”:values[i][1],“date”:{“date”:date}})
With that in place, everything is submitting properly!
Hello. My boards integrate with my marketing CRM HubSpot. I read in another post that you recommend importing information into Google 1st then Monday.com. Is this a best practice we should adopt when data will need to be used in other places?
I ask this in this forum because my initial thought was to pull the information into Monday and then share the info with external partners on a google sheet by reverse-engineering the integration above.
I would need to understand more of what you want to do. You can use the API to pull data off of your boards into Google sheets. Just keep in mind that the API does not read/pull data from certain columns like formula or mirror columns.
Alternately, why not use dashboards and viewer only free Monday users so they can see those dashboards?
That’s an interesting approach! I would do it in a bit different way - install Google Sheets add-on, then fill out the required fields, set up an automatic data schedule and run it for the first time (to make sure it fetches all data correctly). And import data from JSON to Google Sheets! Here is the screenshot with required parameters for you to see.
