Building a Google Sheets Integration

Hey y’all! :crystal_ball:

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:

Then, create a board that has a text column that corresponds to each column in the Google Sheet:

Next, you will need to install this Google Script as an add-on to your Google Sheet. You may need to consult the Google Scripts API reference for more information on adding the script to your Google Sheet.

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.

Enjoy!

7 Likes

Hi Dipro,

How do you get this to trigger and for clarity did you create this as an addon or Marco with an onedit trigger ?

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.

@dipro-monday.com is there a way to get this to work with other column types other than text?

Hey @dipro-monday.com. I was able to do some really interesting stuff with your code snippet as a starting point, Google apps scripts, and Monday.com Automations.

thanks !

2 Likes

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”.

1 Like

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?

It doesn’t work to me. Could you rewrite it including the detailed guide?

Hey @kangingoo!

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!

Cheers,
Dipro