Convert a Google form response spreadsheet into a readable text file

README

I first tried to use sqlite's ability to import a file into a table, but ran
into a problem: if any line in the source file didn't have the same number of
columns as the table, the import would fail. For example, if your 10 question
survey ends with a comment field and a respondent left it blank, that line will
have 9 columns instead of 10 and the import will fail.

Next I tried to use a combination of command line tools like `cut` and `paste`,
but couldn't figure out anything that was reasonable. So I gave up and wrote a
perl script, which is fine, because this is what perl is good at.

I recommend working Google's text format (tab-separated values) since the tab
character is unlikely to appear in your data, making it easier to avoid dealing
with escaping issues.

example-input.tsv

example-output.txt

Q: Name
A: Leonardo
Q: Favorite Color
A: Blue

Q: Name
A: Donatello
Q: Favorite Color
A: Purple

Q: Name
A: Raphael
Q: Favorite Color
A: Red

Q: Name
A: Michaelangelo
Q: Favorite Color
A: Orange

google-form-response-tsv-to-text.sh

# Google Docs: select File > Download as > Text to get a tab separated text file

cat survey.tsv | perl -n -e '@x=split(/\t|\n/); if (@h==0) { @h=@x; } else { for($i=0;$i<=$#x;$i++) { printf "Q: %s\nA: %s\n", $h[$i], $x[$i]; } print "\n"; }'