mornelithe_falconsbane: (pic#11705029)
[personal profile] mornelithe_falconsbane
Link to Google docs Spreadsheet Tutorial 1

The first thing you need to know about Google sheets--and Microsoft Excel, for that matter--is that they are so much more than just a calculator or a spreadsheet. They are made to keep, store, organize, record, manipulate, locate and analyze data, and they are made to automate those functions. With sufficient effort and research, you can make a spreadsheet do almost anything for you.

So, if at any point you become frustrated and confused at why you're doing repetitive work to individual cells like some kind of loser who actually types things? You are probably not using your spreadsheet to its fullest capacities. Try Googling for a formula that does what you want. ;)

The second thing you need to know about Google Sheets is that their official documentation is fucking atrocious, but their help forums are fucking amazing. It's almost hilarious how unbelievably bad their official help is, and I say that as a warning: do not trust Google's help documentation to describe the full capacity of any formula. The stuff they do write up is probably accurate, but it's like Episode I of Star Wars--only the tip of the iceburg, and fairly confusing if you don't know a hell of a lot more context than you probably do.

Your Data

If you're reading this, I'm just going to assume that you're doing this for fandom reasons, and your data is 99% text. And not just text, but lumped together masses of text from an AO3 exchange .csv that you would like to organize in neat, tidy, and useful data.

CSVs = comma separated value. This is essentially a text file of spreadsheet data in which the columns are designated by commas and the rows are designated by paragraph breaks. Both Excel and Google Sheets should allow you to open a .csv file as a spreadsheet.

AO3 formats their .csv files like this:

Pseud,Email,Sign-up URL,Request 1 Tags,Request 1 Optional Tags,Request 1 Description,Request 1 URL,Request 2 Tags,Request 2 Optional Tags,Request 2 Description,Request 2 URL,Request 3 Tags,Request 3 Optional Tags,Request 3 Description,Request 3 URL,(repeats for all possible requests),Offer 1 Tags,Offer 2 Tags,Offer 3 Tags,(repeats for all possible offers)
Anonymo,, link,Fandom, Ships, Warnings, Character Tag, Freeform Tag 1, Freeform Tag 2, Freeform Tag 3,Optional Tag 1, Optional Tag 2, Optional Tag 3,The request from their signup in html,the link to their letter,Fandom, Ships, Warnings, Character Tag, Freeform Tag 1, Freeform Tag 2, Freeform Tag 3,Optional Tag 1, Optional Tag 2, Optional Tag 3,The request from their signup in html,the link to their letter,Fandom, Ships, Warnings, Character Tag, Freeform Tag 1, Freeform Tag 2, Freeform Tag 3,Optional Tag 1, Optional Tag 2, Optional Tag 3,The request from their signup in html,the link to their letter,~,Fandom, whatever else matchable criteria,Fandom, whatever else matchable criteria,Fandom, whatever else matchable criteria

When you spreadsheet-itize that, it looks like this:

PseudEmailSign-up URLRequest 1 TagsRequest 1 Optional TagsRequest 1 DescriptionRequest 1 URL(repeats for all possible requests)Offer 1 Tags(repeats for all possible offers) linkFandom, Ships, Warnings, Character tags, Freeform Tags 1,Freeform Tag 2,Freeform Tag 3Optional Tag 1, Optional Tag 2, Optional Tag 3, (and so on)The request from their signup in htmlthe link to their letter(previous three/four columns repeated)All matchable criteria for this offer 

As you can see, the csv conversion SHOULD make the distinction between a comma without a space after it (your column designation) and a comma with a space after it (text values inside the column.)

If it does not distinguish between them, your data will not line up with your headers (this makes it useless). If you don't have access to Excel (Excel makes this almost pitifully easy), then the fastest way to fix it is to open the .csv file in notepad, wordpad, or similar, and do a find/replace of all commas with spaces after them. Replace them with a weird piece of punctuation that is unlikely to be used in anyone's signup--# is good, as is % or ^. After you've saved the file, try opening it again in Google Sheets and it should work.

If you intend to make this spreadsheet public, your first step should always be to delete the columns with the participant's email addresses and their offers (highlighted in orange).That stuff is private, and you cannot keep it secure in a publicly available spreadsheet.

Now, to why AO3's exchange download is a load of fucking horseshit: those two cells with red text are in chronological order, as near as I can tell, and they cover every single matchable criteria a participant can enter. This means that it is impossible to split them into fandom, characters, media, ship, freeforms, and warnings based on their location in the list. That also means that the data (in that format) is worthless without a lot of manual effort on your part to parse, sort, and organize it.

The second reason is that it treats a requester's entire signup as a single row. This is hard to parse and even harder to search, especially considering how difficult to read the cells in red are.

That's where this tutorial comes in. I am a firm believer in clear, legible data that you can use (meaning it is searchable, countable, sortable, readable), and that's what this tutorial should show you how to do.

Today's Useful Google Sheets Formulas:



The first step in organizing your request (and offers, if you want to do the offers for your own purposes) tags is to split them into separate cells. The good news is that there is a formula that does exactly that--split.

Split separates all items in a list into separate columns based on a delimiter (a dividing character or set of characters). This is the same principle that the .csv file works under.

=split(text,delimiter,[split by each])

Text: what you want to split. Hypothetically, you could do this with actual text--something like "A, B, C", but you'll be using a cell reference 99% of the time.

Delimiter: The character (or characters) that separate items on your list. In this case, it will be ","

[split by each]: An optional TRUE FALSE command. If you set it to true and you have multiple characters in your delimiter category, it'll treat all of the characters as delimiters instead of all of the characters in that sequence as delimiters. If you put it in, use false. You are highly unlikely to need true.

Your formula will look something like this:


It will turn A2:

Fandom, Ships, Warnings, Character tags, Freeform Tags 1,Freeform Tag 2,Freeform Tag 3

Into this:

FandomShipsWarningsCharacter tagsFreeform Tags 1Freeform Tag 2Freeform Tag 3

How useful! However, there's one tiny issue with that output--leading spaces. Not an issue at this second, but it will be in the future. We could add a space to our delimiter in split, but I've never had very consistent results with that. So instead, let's use trim!


=trim(whatever you want to trim)

Trim is super easy. You stick it around anything where you want the leading and ending spaces removed from a cell. In this case, we want to trim the spaces off of the output of our split formula, so we put the split formula inside the trim formula.

=trim(= split(A2,",",FALSE))

But wait! This only gives us the first item from the list! How do we fix this?


You can use this formula with almost any other formula. It is the best formula I will teach you, and it is the most important formula. This is your "fuck typing that shit" formula. Your "impress your boss" formula. Your ace in the hole, your best friend, the finest formula known to man--you get the picture, right?

Anyway, Arrayformula. To nest whatever formula you just wrote inside arrayformula, hit ctrl+shift+enter. Or you can type it in. It's up to you.

Arrayformula calculates almost any formula and outputs a range. So our poor trim formula up there will not output a range, but if we stick it inside an arrayformula, it will!


Outputs our split array, trimmed, and it's all very convenient. But--is it convenient enough? (Spoiler, no)

The true glory of Arrayformula comes from the fact that Arrayformula will also calculate ranges. Say you want to split 300 rows of a column (which you probably do!). You could use our arrayformula with split and trim up there and drag it to fill 300 rows--or you could change your cell reference from D2 to D2:D300.


With that one, small formula, you just split and trimmed every single cell between D2 and D300. And you can use it for almost anything you would usually use individual formulas on! How fun!

Identity URL: 
Account name:
If you don't have an account you can create one now.
HTML doesn't work in the subject.


Notice: This account is set to log the IP addresses of everyone who comments.
Links will be displayed as unclickable URLs to help prevent spam.


mornelithe_falconsbane: (Default)

September 2017

34 56789

Custom Text

Style Credit

Expand Cut Tags

No cut tags
Page generated Sep. 21st, 2017 03:07 am
Powered by Dreamwidth Studios