Production Tracking with Google Spreadsheets

Ahh, there were permissions errors with this for a while (seems google tightened up stuff since I wrote it), only just realised now (july 2017), fixed it I think. Hooray!

Google spreadsheet with history per cell

Google spreadsheets are great, and super useful for collaborative tracking of shots when Shotgun is overkill. A big problem though is keeping descriptive notes; its a spreadsheet after all, and keeping big slabs of text in a single cell gets ugly.

After finding out google has a pretty good server-side javascript API, I had a stab at seeing if I could record history of cell edits based on this stackoverflow post . The results can be found on this demo spreadsheet:

http://goo.gl/ApuiNv

The core of it is simply that google gives you access to an 'onEdit' event, which as the name implies, triggers when any cell is edited. You then just have to grab the contents of the cell ( cell.getValue() ) and do stuff with it.

I also had a stab at some user friendly stuff, so I used the ability to create custom menus, create tables, name and sort stuff, all that. It's hardly shotgun, but for simple stuff, I could see this could be extended into something quite powerful. In fact, it already has, Frederik Averpil pointed out this site, which is basically an attempt to do shotgun purely with google docs. Quite ambitious! http://buckshot.vfx2.com

There's also ways to make one-click-installers, but I haven't got that far yet.

Copying to your own docs, easy way

The lazy way is to load the demo and go "File -> Make a copy".

Copying to your own docs, the hard way

You wanna know more? Good for you. Two steps here, copying the code, and then attaching the functions to triggers.

Copy the code

1. Load the demo: http://goo.gl/ApuiNv
2. Tools -> Script editor
3. Select all the code, copy it
4. Create a new spreadsheet (make sure you're using the newer google spreadsheets, the one with filter views and whatnot)
5. Tools -> script editor again
6. Choose 'blank project'
7. Select all, delete, then paste in the code you copied earlier
8. Save, give it a name (eg 'cell_history_scripts')
9. Once saved, you have to then bind these functions to events in your spreadsheet. Lets do that now!

Attach functions to triggers

1. Resources -> current project triggers
2. Click the 'No triggers set up. Click here to add one now.' link
3. Choose 'save_cell history', 'from spreadsheet', 'on edit'.
4. Click 'add new trigger'.
5. Choose 'add_custom_menu', 'from spreadsheet', 'on open'.
6. hit save
7. google will ask for authorisation to allow this code to run on your spreadsheet, click yes yes yes (I'm fairly sure my code won't steal your credit card details...)
8. Close and reopen the spreadsheet
9. If everything has gone ok, after a few seconds you should see a 'shot_tracker' menu appear. Success!

Usage

For this demo it's hardcoded to treat the first column as shots, second column as comments, and the first row is for headers ('shots' and' comments', not surprisingly).

Setting up the shots

1. Fill out the first column with shot numbers.
2. Run shot_tracker -> create shot tables
3. Wait a bit, a dialog box will appear saying that its gonna create tables, hit ok
4. Watch in amazement as it goes and creates a table for each shot, sorts them in ascending order, and drops you back onto your sheet

Making comments

1. Go to the comments cell for a shot, type in some text
2. After a short delay you'll see a little black triangle appear in the corner of that cell. Hover on the cell, you'll see the comments tooltip appear, where its made a duplicate of your text with '1' preceding it.
3. Edit the text to say something else, again after a short delay the tooltip will have that comment with '2:' preceding it, a dashed line, and the previous comment.
4. Look at the table for that shot. It's recorded the same information, but this time into separate cells, with date and time info, and the email address of the person who made the edit.

Future ideas

Lots!

• Use the email api to send email summaries/warnings/notifications
• Make more use of the mobile features (try editing the spreadsheet from iOS/android, I was surprised and pleased that it all works there too)
• Forms or wizards to help setup the spreadsheet with thumbnails, artist table etc
• Lock things down a little, it'd be very easy to delete all the info (but then again, easy to restore it with google doc's history features)
• Make the edits bidirectional, so users could be adding lots of notes from the per-shot tables, it'd updated the master view. There's nothing stopping this happening right now, I just run an early test that says only edits to the B column are recognised.
• Package it all up into a one-click add-on

Old demo

Here's a animated gif capture of my first attemp, and a snippet of the javascript code that's doing all the work, it's pretty simple.

function onEdit(event) { 
   var sheet = event.source.getActiveSheet();
   var cell = event.source.getActiveRange();

   var oldNote = cell.getNote();
   var noteCount = oldNote.split("------------\n").length;
   var newNote = cell.getValue() ;
 
   cell.setNote(noteCount+": "+newNote+"\n"+"------------\n"+oldNote);
}

Looks like this: