This is our issue as well. We are attempting to create a simple end-of-month timetracking export. Monday’s built in timetracking is woefully lacking (and the timetracking spreadsheet export is laughable) so I’m attempting to piece something together by syncing our Monday data to a Google sheet, and then connecting it (by task name) to the timetracking data from a third-party.
But because the Monday>Zapier>Google Sheets integration is so incredibly limited, this doesn’t work.
I’m nearly out-of-my-mind frustrated with this. All I need to do is export timetracking data, by technician and task—with data from several of the boards columns—into a nice clean spreadsheet that can be filtered and manipulated.
I am not an expert but I finally found a way that sort of works for us. I was equally getting frustrated that Zapier’s ability to push Monday data efficiently to Sheets was not there. More so the other way around but there was another post here about that and I was able to get Sheets to speak to Monday in a blast vs one column change at a time through Zapier.
The challenge with my solution is that it does not trigger from Monday. I have mine triggering once a day over night. What I have attached is a sample of my code that I have in my script editor in my Google sheets set on a time based trigger. I have three functions.
Function 1 is the API call. This will be the function that reaches out to Monday and gets the information back to sheets.
Function 2 gets all my boards and their ids from my Monday account and puts them into a sheet. It sets up the query details and tells function 1 what the call for then parses the data into the columns in my sheet.
Function 3 is a little more advanced. It gets all items (or pulses) from my Monday account and puts them into a sheet. It sets the query, sends to function 1 for the call and then parses the data and nested object to put the item name, item id, the board name, and board id from that item.
From these you can do a lot more. Hypothetically you could create a complete mirror of your entire Monday account by using multiple functions where you search your boards and get their ids and column names. Then for each board create a sheet with the necessary column headers. Then for each board call all the items, their ids, and their column values, and fill them into the sheets.
Hope this helps! If you need support feel free to reach out. I am not an expert and this is my first time every posting on a forum… CLICK HERE TO CHECK OUT THE CODE.
I am also working on Monday to Google Sheets integration. I was using Integromat to do this which actually did all 3 functions (Add rows, delete rows, update rows). However the delete row functionality uses a lot of operations so I was not able to use the free tier usage for this. If you don’t mind paying $50-$100 a month then Integromat may be the way to go.
However since my company is small and I am a junior developer I decided to go the route of using the API. I’m currently writing out the add/delete/update functionalities with Google App Script. If I can’t get the update to work though I might just delete and then rewrite the table every time.
Mine literally just writes over what already exists. I didn’t even bother putting in a clear data step as our items and boards always go up.
A main reason for needing this flow is so I can then ‘search’ my Monday pulses which you can’t do in Zapier yet.
I had an automation that every time a board or item was created Zapier added the details to my sheet but it was sucking a lot of my monthly tasks and put me to my limit 3 months in a row with a few days left.
Removing some of the steps between Zapier and Monday by using the API has allowed me to keep my Zapier account as is and not upgrade.
Thanks for the input! I think I’m going to go the route of rewriting over the sheet. I have a column for item ID in Sheets to verify data that already exists, but if I delete & rewrite everything then I don’t need to keep track of the ID. I was more interested in the update just to provide on my GitHub for other users that run into the same dilemma.
Y’all this is far too complicated for those of who are not programers. Monday.com sells itself as being the easy solution for workflows - this seems like a slam dunk feature they could provide and I’ll keep lobbying for it.
Try removing the limit in your query since you identify the board id you don’t need it. You may want to put it in on the items though.
So:
query{board(ids:xxxxx879){items(limit:30){name,id,column_values{value}}}}
I think this should get you want you want. You may want to add id in the column_values so you can make sure to match the right value in the right column in your spreadsheet.
We’re still analyzing the possibility of supporting this in the future. In addition to the suggestions above, check this post out: Building a Google Sheets Integration
It does not solve the ideal two way sync functionality but it may help sync info inputted in Google Sheets on to monday.com.
Not sure if you managed to pull the column values and separate them in columns but I did play with it for something I am working on today.
This is the code I used:
boards.forEach(function(elem){
var items = elem[‘items’];
for(i=0;i<items.length;i++){
sh.getRange(i+3, 1).setValue(items[i][“id”]);
sh.getRange(i+3, 2).setValue(items[i][“name”]);
for (j=0;j<11;j++){
sh.getRange(i+3, j+15).setValue(items[i][“column_values”][j][“value”]);
}
}
})
Note the i+3 refers to me starting in row 3, and j+15 has the column values starting in Column O (15). I then built nested replace formulas directly on the sheet to extract the details from each column value I wanted since it doesn’t go into the cell nice.
For example:
Status column value looked like this: {“index”:0,“post_id”:null,“changed_at”:“2020-04-20T19:24:01.777Z”}
It was in cell Q3. I put this formula in E3 to pull out the index alone:
=iferror(replace(replace(R3,1,find(":",R3)+1,""),find(",",replace(R3,1,find(":",R3)+1,""))-1,len(replace(R3,1,find(":",R3)+1,""))-find(",",replace(R3,1,find(":",R3)+1,""))+2,""))
Feel free to let me know if you need help or if you know of a better way to do this!
Has anyone had any luck here - all I need to do is export all of the fields in Monday.com to certain colums in Google sheets so that then we can import the information into deals in hubspot automatically
@Sam1 do you want this daily or based on a trigger?
The challenge is that not ALL column types are able to be read via the API in Monday. So depending on how you have your information in Monday set up what you are aiming for may not be possible automatically.
You can always export into excel then import that excel file into a Google sheet.
Except the export function always crashes!
Thanks for you help Chris, trying to get your script running now, I get this error:
Exception: Attribute provided with invalid value: Header:null
I’d love to help further. Feel free to send me the script you have in a message and I can see if I can see where the error may be. It’s hard to tell based on that message alone. Chances are it is something in the way you are sending the API call.
I’ve created a little app that does exactly this, it links a Monday Board with a Google Sheet, so you won’t have to keep updating both ends.
Just sing up here using your Monday account, and follow the instructions, if everything goes right, you’ll get a little link you need to copy and paste in your Google Sheet and that’s it, they’re now linked.
The app is free to use and you can create as many links as you want.
Here’s an example of a link I created with the app:
@Yoggie, I was having the same issue. Your solution is super-easy to use and a great tool! Thank you! Please don’t take it offline; I’m depending on it now.
Guess I’m somewhat late to the party, but hello and thank you @Yoggie, amazing that you came up with this!
However, it works for the more simple boards that we have, but for the largest and most complex boards I keep getting an error saying it can’t retrieve the URL. I’ve already tried adding more columns to the Google Sheet, but that doesn’t help. Any idea what could be causing this and/or how to solve the issue?
Hey Folks, I hope you are doing well. I was going through this thread and saw your comments and replies. I am building a tool to allow users to get data from Monday to Google Sheets automatically. The tool is called - nowstackit.com
We are still in beta and would love to get initial feedback on the product.
