Airship Guide

[Enseric]

Added log to Spreadsheet
Birch branch is actually "High-quality item"
http://i.imgur.com/JKH6Waf.png
And Quickarm materia III from sector 9

[Sigmakan]

The sea of clouds island group in the upper left is Sector 13, btw. I can take a screenshot when I get home.

I'm guessing that sector 12 is the penciled in island group on the left of the map.

[Nyalia]

OMG, I totally missed the penciled-in island xD So, I take it SC13 unlocked from SC11?



EDIT: Also, I decided to update the "HQ?" columns to instead say "Rare?" and have the options: No, Yes, and Several. I know the previously entered logs will skew the data some, but... it should be fine.

[Yarem]

Well lvl 60 for last isle don't mean this is the max level, it only mean it is the minimum to visit the sector... it feels wrong to not fully build a ship :/

Funny fact, the Map in liveletter http://forum.square-enix.com/ffxiv/a...7&d=1429781056 was only a part of the current map missing the top 20%.

[BonzaiFerroni]

OMG, I totally missed the penciled-in island xD So, I take it SC13 unlocked from SC11?

EDIT: Also, I decided to update the "HQ?" columns to instead say "Rare?" and have the options: No, Yes, and Several. I know the previously entered logs will skew the data some, but... it should be fine.

Woohoo! Yes I think it will be fine too \o/

I was thinking of running some analyses and possibly entering some script to do so, is that possible? Is there a way for me to view the scripts you've written? (if that is ok with you!)

Looks like the scripting language is Apps Script which looks an awful lot like javascript. You mentioned using something more similar to excel so I wondered if you were using a different method.

[Sen_Terrechant]

Any information on when you unlock the level 3 FC buffs?

[Nyalia]

Woohoo! Yes I think it will be fine too \o/

I was thinking of running some analyses and possibly entering some script to do so, is that possible? Is there a way for me to view the scripts you've written? (if that is ok with you!)

Looks like the scripting language is Apps Script which looks an awful lot like javascript. You mentioned using something more similar to excel so I wondered if you were using a different method.

If you want to play around, that's totally fine! I'll need a google account name though so I can add permissions for you. Can you just open the chat window next time you're in the sheet and say it's you? I usually have the sheet open somewhere, so I should get it. Click the speech bubble icon in the upper right to the left of the "comments" button. Or just make a comment while signed in - that should also get me your account name.


I just use the built-in formula functionality, which is different from the scripting you mentioned. I know I can do more if I attached scripts, but I haven't taken the time to learn how yet. And I really love functional programming (I work primarily with a proprietary functional language at my job). The formula language is a really simple functional language - you enter = in a cell to start, and put in a function call, such as "=sum(A3:A8)" or "=if($A3="","",transpose(SORT(unique(transpose({iferror(transpose(filter('Master Voyage Log'!$N$3:$N,'Master Voyage Log'!$R$3:$R=$A3)),),iferror(transpose(filter('Master Voyage Log'!$N$3:$N,'Master Voyage Log'!$W$3:$W=$A3)),),iferror(transpose(filter('Master Voyage Log'!$Z$3:$Z,'Master Voyage Log'!$AD$3:$AD=$A3)),),iferror(transpose(filter('Master Voyage Log'!$Z$3:$Z,'Master Voyage Log'!$AI$3:$AI=$A3)),),iferror(transpose(filter('Master Voyage Log'!$AL$3:$AL,'Master Voyage Log'!$AP$3:$AP=$A3)),),iferror(transpose(filter('Master Voyage Log'!$AL$3:$AL,'Master Voyage Log'!$AU$3:$AU=$A3)),),iferror(transpose(filter('Master Voyage Log'!$AX$3:$AX,'Master Voyage Log'!$BB$3:$BB=$A3)),),iferror(transpose(filter('Master Voyage Log'!$AX$3:$AX,'Master Voyage Log'!$BG$3:$BG=$A3)),)})),1,TRUE)))" (the formula that displays the list of sectors an item is found in on the items tab).

To reference a cell, enter the cell's coords (like A3 or BH12). To reference a range, use a : between two cells. To have a range go to the end, leave off the row/column as appropriate in the end cell (like A3:A will mean "all of column A starting with row 3 and going down"). When you drag a formula over to other cells, it'll add/subtact one to the row or column as appropriate. To make this not happen, use $ before the letter or number. So, if you do A$3 and drag it to the down, it'll be A$3 everywhere, but if you drag it right, the cells to the right will be B$3 and then C$3, etc. To reference another sheet, put the sheet's name in ''s followed by a ! and then the cell coords on that sheet, like 'Master Voyage Log'!$A$3:$A.

The dynamic highlighting is done via the "Conditional Formatting" feature, where you can set the formatting to trigger based on a function. The function needs to evaluate to TRUE or FALSE, and if it's TRUE, the cell is highlighted as desired, and if it's FALSE, it isn't. But, Conditional Formatting formulas cannot reference other sheets, unfortunately. That's why I have hidden columns on tabs sometimes with data from other sheets, so I can highlight based on it.

Useful formulas to know:

• if(condition, result if true, result if false) - Basic conditional.
• iferror(function to execute, result if an error is returned) - Does what it says.
• filter(range to filter, filter condition 1, filter condition 2, etc) - Used to filter a range of cells based on any number of conditions. You can only filter either horizontally or vertically, and the result is a list of rows/columns that match all of the conditions you specified. If nothing is found, an error is returned, so always wrap these in "iferror" functions. You can also use "vlookup()" for this, but I find "index(filter())" is generally easier to use and serves the same purpose.
• index(range/array, row# or "" to get whole column, column# or "" to get whole row) - Extract a specified cell/row/column from a range.
• array_constrain( range/array, X limit, Y limit ) - Used to prevent a dynamically generated array from expanding more than there's room for it to expand. This removes all rows and columns past the specified limit. I use this, for instance, in the item lists on the sector tab so if paste in a million typoed items it won't try to expand the array and overwrite the stuff after it.
• column(cell reference), row(cell reference) - Returns the column or row number of the specified cell. For column, it does return the column number, not letter. To get it back to a letter, you have to add it to code("A")-1 and then use char() to turn it back to a letter. This only works on columns 1-26, after that, it gets more complicated since column 27 is AA and so on.
• code(letter/symbol) - Get ASCII value.
• char(ASCII value) - Convert an ASCII value back to a letter/symbol.
• value(number as text) - Cast a text string into a number.
• left(string, #chars), right(string, #chars), mid(string, start point, #chars) - Extract a substring starting from the left, right, or in the middle of a string. So, you do something like =value(right("SC05",2)) to get the number 5.
• arrayformula( function ) - This is an extremely useful and extremely finicky function. It takes in any function normally designed to work on a single cell, except you give range references instead of cell references and it performs the function on each item in the range individually, returning the results as a range. So, =or(arrayformula($B$3:$B=C3)) would return true if any of the items in the range matched the specified cell.
• concatenate(list of strings) and concat(string1, string2) - Concatenate strings together. Concatenate() takes in any number of strings, but it does not work in an arrayformula(). In an arrayformula(), you have to use concat() (or a nested series of concat()s) instead.
• min(range), max(range) - Get the lowest or highest value in a range.
• unique(range/array) strips out all duplicates from an array/range.
• sort(range/array, primary sort column, ascending?, optional secondary sort column, ascending?, etc) - Sorts a list of rows as specified.
• { item1, item2, item3 } - These create an array (aka Range) from a discrete list of items. When playing with this sort of thing, another good one to know is transpose(array/range), which transposes rows and columns of the range. Since the created arrays are horizontal, if you want to concatenate items extracted from columns together into a long list of items, you have to transpose your rows into columns first so they merge correctly. You can then transpose them back into rows.

(If the result of a function is an array, it will expand to nearby cells (unless array_constrain() is used to constrain it). This can be great, but I find google sheets gets really upset when the array tries to expand past the end of the edge of the sheet. It is supposed to automatically adds rows/columns as needed, but I find it tends to crash the sheet pretty badly instead. array_constrain() is your friend.)

And so on. There's a list you can get to from going "help -> function list" and when you start typing in a function, it should show you the documentation for that function automatically. You can use SQL queries, but I have never succeeded in getting them to work, they don't seem to play well with the two-line headers I'm fond of, and though they can be powerful, I can do plenty of relational database functionality with just creative applications of filter(), transpose(), arrayformula(), and {}. It's only really a problem on my desynth database because Google Sheets doesn't really approve of my convoluted and numerous references to my 3,000 row, 30 column database (it takes literal minutes to fully update that sheet whenever I change anything).

[BonzaiFerroni]

Wooo thanks for the tutorial! That is really appreciated. I can't wait to start XD

[Aeyis]

Finally discovered SC09 from visiting SC08. Still no luck with SC10 tho. Sending it out to 7-8-9-11 now. (11 before 10 xD)

[Kokila]

I assembled a google doc spreadsheet to track my FC's materials as we assembled our airships. I've tossed a copy of it out here for anyone who wants it for their own tracking. The last two ships I don't have access to yet in our FC but I'll update the sheet as I do!

Airship Build Spreadsheet