Shotgun + Google Sheets Case Study 1

8 min readMar 9, 2021

Your artists are fully booked and you need to outsource some tasks, so you want to create a spreadsheet with the budget for those Tasks and send it to your vendor. Quite easy and straightforward, isn’t it?

Just export the data as a CSV from Shotgun, import it into Google Sheets (or any other spreadsheets software), use some basic math formulas like multiplication and sum, give it some format and your done. Something like the following.

But what about the Budget column?, most likely it is a field you don’t have in Shotgun, because it is something you usually want to calculate based on the bid of each task, and depending on the vendor you may want to use a special price or even an exchange rate if do business with them in a different currency.

The following table is the data (Tasks) exported from Shotgun

As you can see, the first thing you’ll be doing is getting rid of the first and last columns if you don’t want the ID of the tasks and the name of the project in your budget document as I did.

Then, the Title of some columns is different (Link instead of Shot, for instance) and also the order. With the order you may argue that you just need to set up that order in Shotgun before exporting, but frankly wouldn’t be fantastic not to bother about the order of the fields before exporting?

And in this particular case, I’m getting the VFX Description and the Frames from linked fields, namely:

Shot > Client Request
Shot > Working Duration

For the Budget you will have to create a new column and a formula per Pipeline Step using the Bid in order to calculate it

Well, if you choose this path you’ll find yourself doing all those same steps over and over again every time. “Isn’t that part of my job? it is not much work, and I’ve become very fast at it” you may say (I’ve heard this many times). Besides the extra clicks, time, possible mistakes, and format inconsistencies, what if you must delegate it to someone else?

So what do we do instead?

Create an spreadsheet where you only have to import the CSV in one tab and the report is automatically generated on another, and a third tab to define the rates and control the rates depending on the currency and a percentage field. Let’s see it in action.

As you could see on the video, you don’t even have to click and drag in order to extend the formulas over all the rows of the imported data (as I think you usually do, don’t you?), yes, it doesn’t matter how many Tasks you exported from Shotgun!

Let’s start with the Rates tab, although there is nothing fancy here, just basic math formulas to calculate a Rate from a previously defined Base Rate, a percentage field to increase or decrease it for whatever reason you may need it, and an Exchange Rate to change currency value.

Exchange Rate times the Base Rate times a Percentage to increase or reduce the final Rate

I’m sharing with you the Google Sheet so you can play with it, you must Make a copy first in order to edit your own document.

Then import the sample data on the Import tab, here it is very important to check the Replace current sheet option

Now let’s build the interesting formulas on the Bid tab from scratch. For all the columns all you want is to get the cells from the corresponding column in the Import tab.

For this let me share with you another version of the Google Sheet here, with each step of the building formula progression for you to follow,

Step 1

So that’s easy, for instance in order to get the first cell for the Shot column the formula is

=Import!D2

Which is just referencing the D2 cell from the Import tab, but this would mean you have to drag the cell from its lower right corner all the way down to the same number of rows of data you’ve imported in order to extend the formula, and repeat this for the other columns.

Step 2

Allow me me to introduce you to the ARRAYFORMULA, with it you can write the formula on 1 cell and it will operate across the range (array) specified. For our sample data the first row is the name of the exported fields, so we have to go from row 2 to 21.

=ARRAYFORMULA(Import!D2:D21)

Which is something great, you no longer need to click and drag the formula all the way to cell 21, but still not ideal because the data won’t have the same number of rows every time, and you would be editing the formula for every column to include all the rows of data.

Step 3

The idea is to automatically build what is between the parenthesis in the ARRAYFORMULA

So, how do we get the number of the last row which has data? by asking for the number of values in the A column with COUNTA

=COUNTA(Import!A1:A)

By leaving the second part of the array without a number we are telling the formula to look for values from row 1 until the document ran out of rows: A1:A

Step 4

With & you can concatenate several strings, so we can use the formula on Step 3 the following way.

="Import!D2:D"&COUNTA(Import!A1:A)

To get: Import!D2:D21

Step 5

Now you may feel temped to jump ahead and write the following formula

=ARRAYFORMULA("Import!D2:D"&COUNTA(Import!A1:A))

But it won’t work, as the ARRAYFORMULA expects the actual values of the array Import!D2:D21 not a string as it is now, perhaps quite confusing, but you must use the INDIRECT function to get those values before passing them to the ARRAYFORMULA

=ARRAYFORMULA(INDIRECT("Import!D2:D"&COUNTA(Import!A1:A)))

Up to here we have a pretty descent solution, but for this to work the Shot field data must always be in the fourth column (D) when exporting/importing.

So let’s take this to the next level and make the formula smarter to look for the column Title to get what we need regardless of the order of the columns (as long as we exported all the fields we need)

Step 6

The idea is the same, to build on top of our latest working formula, at the end what we want is to get the following string: Import!D2:D21

But if the Shot column it is now on the sixth column what we want to get would be: Import!F2:F21

With the HLOOKUP function we can search for an specific value (Link which corresponds to the name of the Shot) on the first row of a range, i.e. the Title of the columns and get any cell from that column, for this case the one bellow the Title. And we already know how to use COUNTA and INDIRECT

HLOOKUP(search_key, range, index, [is_sorted])

=HLOOKUP("Link", INDIRECT("Import!$A$1:$Z$"&COUNTA(Import!B1:B)), 2, FALSE)

Have you already spot the limitation it has?

It only considers 26 columns on the imported data, from A to Z, because I wasn’t really thinking about exporting more fields, but if you do you can go to whatever you think may need, ZZZ perhaps

The number 2 is the row from we want to get the cell value of the column found, in this case:

021_nid_0270

Step 7

But actually the value of that cell we got on the previous step does not serve to our purposes, what we need is to get the letter of the column along with its row D2, F2 (i.e. the cell reference as a string) or wherever the Link string was found.

But this has to be done in several steps, first let’s get the index number of the column with the COLUMN function and the formula from Step 6

=COLUMN(HLOOKUP("Link", INDIRECT("Import!$A$1:$Z$"&COUNTA(Import!B1:B)), 2, false))

And then with the ADDRESS function we can do the rest to get D2 for this specific case

=ADDRESS(2, COLUMN(HLOOKUP("Link", INDIRECT("Import!$A$1:$Z$"&COUNTA(Import!B1:B)), 2, false)), 4)

Step 8

We use the same as in steps 6 and 7 in order to get the cell reference of the last row, for this specific case we want to get D21

=ADDRESS(COUNTA(Import!A1:A) ,COLUMN(HLOOKUP("Link", INDIRECT("Import!$A$1:$Z$"&COUNTA(Import!A1:A)), 2, false)),4)

Stay with me, I hope I haven’t lost you yet, is not that hard, I just exchanged the number 2 with our COUNTA trick to get the cell reference of the last row of the found column.

Step 9

Let’s concatenate the pieces together to get: Import!D2:D21

="Import!"&ADDRESS(2, COLUMN(HLOOKUP("Link", INDIRECT("Import!$A$1:$Z$"&COUNTA(Import!B1:B)), 2, false)), 4)&":"&ADDRESS(COUNTA(Import!A1:A) ,COLUMN(HLOOKUP("Link", INDIRECT("Import!$A$1:$Z$"&COUNTA(Import!A1:A)), 2, false)),4)

Step 10

Just use the previous formula with the almighty ARRAYFORMULA as we did on Step 2 and we’re done, just remember that it is expecting cell values, not just a string so we have to use INDIRECT

=ARRAYFORMULA(INDIRECT("Import!"&ADDRESS(2, COLUMN(HLOOKUP("Link", INDIRECT("Import!$A$1:$Z$"&COUNTA(Import!B1:B)), 2, false)), 4)&":"&ADDRESS(COUNTA(Import!A1:A) ,COLUMN(HLOOKUP("Link", INDIRECT("Import!$A$1:$Z$"&COUNTA(Import!A1:A)), 2, false)),4)))}

Now the formula looks quite intimidating, but as you saw I build it step by step, this is the way it was done, I didn’t sat and write the whole thing at once!

Budget

The formula for the Budget column is a bit longer as we want to multiply each Bid value imported times the Rate, but we have different rates for each Pipeline Step.

So long story short, what we want is the following

=ARRAYFORMULA(Import!E2:E21 * VLOOKUP(INDIRECT("B3:B"&TO_TEXT(COUNTA(Import!A1:A) + 1)), Rates!$A$2:$B$10, 2, false))

But getting Import!E2:E21 dynamically as as we did with the other columns

The only new function introduced here is the VLOOKUP which is something similar to HLOOKUP but to find a value (or a string) on the rows, this way we can get the rate depending on the Pipeline Step of each Task

And the complete formula is:

=ARRAYFORMULA(INDIRECT("Import!"&ADDRESS(2, COLUMN(HLOOKUP("Bid", INDIRECT("Import!$A$1:$Z$"&COUNTA(Import!A1:A)), 2, false)),4)&":"&ADDRESS(COUNTA(Import!A1:A), COLUMN(HLOOKUP("Bid", INDIRECT("Import!$A$1:$Z$"&COUNTA(Import!A1:A)), 2, false)), 4)) * VLOOKUP(INDIRECT("B3:B"&TO_TEXT(COUNTA(Import!A1:A) + 1)), Rates!$A$2:$B$10, 2, false))

Once again, don’t get overwhelmed and intimidated by a long formula, now you know how to build one step by step.

Remember, I shared the documents so you can follow each step and play with them

Takeaway

Work Smarter, Not Harder