Auto linking Shotgun and Google Sheets

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.

3 Likes

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)
1 Like

HI @jessicawynncole,

Welcome to the Community! :wave:

I see that the ever helpful @mmoshev has left an answer to your question, but let us know if you have any further questions.

Have a great Friday :partying_face:

Cheers,
Beth

1 Like

Thanks so much! I’ll check this out and respond if I have further questions.

1 Like