Hello everyone! I have long been a fan of using .csv exports from Shotgun to link with GSuite, allowing me to create custom formatted reports within Google sheets and excel. These reports are never live as they require me to export a .csv of the SG page, copy from this document, and then update my Google sheet.
I was wondering if anyone has had success automatically linking these two platforms? I looked into using Javascript tools for GSuite but because Shotgun pages are coded elements, not text tables, I can’t source the information automatically from the Shotgun URL. I also tried using the web link to the .csv file but no luck on that either.
We have been exporting time logs and uploading them to Google sheets.
However we go the long route of querying Shotgun from Python, and using the gspread library to insert rows into the table.
Generating and inserting the tabular data is actually not that much work, once you get the connection and authentication to google working.
Here is some code for inspiration:
sh = spread.get_worksheet(0)
tl = get_timelog(proj_name)
sh.clear()
sh.append_row(generate_header_row())
rows = map(generate_row_from_record, tl)
num_rows = len(rows)
num_cols = len(rows[0])
# skip first row, which is header
cells = sh.range(2, 1, num_rows + 1, num_cols)
flat_data = sum(rows, [])
for data, cell in zip(flat_data, cells):
cell.value = data
# batch update
sh.update_cells(cells)
I am just thinking of creating a similar tool. The problem is that I am not even sure how to actually programmatically export the data from Shotgun apart from manually clicking the Export All Tasks To Excel (CSV). Do you have anything in mind?
So I haven’t tested this but now we have a very good REST API in SG you could always just pull the data you need from the gsheets side using Google Appscript (essentially just JS)
I’ve tried using our Shotgrid Event Daemon with a modified logArgs example script from their github to time log status changes to google sheets, but i’m pretty blocked on that.
I basically got all the logs i need going into a file already, but i don’t know where to go from there on to make it write into the google sheet. Checked out some online guides with pygsheets but no luck there. Any more pointers how to set it up with gspread?
Would Appscript be easier to setup? Have never tried it before
What blocks you from using gspread? Just the authentication is a bit of a pain to set up (which is for any solution), but the api is pretty straightforward?
Hi there @mmoshev - what’s the best way to host and trigger the python script once I’ve set it up? I want to be able to trigger my python script that syncs data from SG to my Google sheet via a UI menu item in the actual sheet itself. Let me know your thoughts - It’s been a nightmare trying to get this set up.
I don’t have much experience with this API, so not sure how to trigger an action. It must be possible, though. check out Simple Triggers | Apps Script | Google Developers
Form submission seems a suitable event.
Also I guess you could use webhooks for the ShotGrid part. You need an external facing endpoint for this, however.
That looks awesome! I’d like to do a very similar thing with a spreadsheet listening to status changes on SG… if you don’t mind, would you able to share what you did to make that happen?
So initially I set up a flask web app hosted on a server that received http requests from google’s app script. Which worked great, but then I decided to pivot and just use google cloud functions and deploy my app script as an add-on. It made the whole thing easier to deploy and distribute internally since we use Google Workspace. The script I use is pretty straightforward, it pulls from SG using the SG API and appends the spreadsheet I want using gspread. Here’s the code: GitHub
As far as listening goes, you’ll need to use Webhooks or the SG Events Daemon. Both are well documented on SG’s developer documentation site. I use the SG Events Daemon for a Tidbyt new versions tracker. Some combination of the code I link there, and the code I’ve linked above will get you where you need to go.
If you’re experienced in the world of SG and coding, Glad I could help. If you’re new to it and don’t know a git from a hub, I highly suggest creative uses of chat GPT, reading as much documentation as you can and or taking a intro python class on Codecademy. That all got me where I needed to go.
