Skip to content

Instantly share code, notes, and snippets.

@midnightfreddie
Last active March 25, 2016 20:03
Show Gist options
  • Save midnightfreddie/c03946b7bb4265b4c907 to your computer and use it in GitHub Desktop.
Save midnightfreddie/c03946b7bb4265b4c907 to your computer and use it in GitHub Desktop.
# In reply to https://www.reddit.com/r/PowerShell/comments/4a08hk/importcsv_with_as_a_hashtable/
# Public Google Form at https://docs.google.com/a/jimnelson.us/forms/d/1dYmWTcw3c3q9p3HGWGvbNDvXWekUEKQfgsecZOpsV-k
# Results are at https://docs.google.com/spreadsheets/d/1G7J8qCL4gQ_NSYLiGE_3dauSaa75ppzPmcO-Rl7yV6w
[cmdletbinding()]
param(
$SheetKey = "1G7J8qCL4gQ_NSYLiGE_3dauSaa75ppzPmcO-Rl7yV6w",
$MaxRows = 5
)
# Get raw CSV data from publicly-shared URL. (The REST API requires login and is out-of-scope for this example.)
$RawDataRequest = Invoke-WebRequest -Uri "https://docs.google.com/spreadsheets/d/$SheetKey/export?format=csv"
# Put the CSV string data in object format
$FormData = $RawDataRequest.Content | ConvertFrom-Csv
# Take the first few rows and reformat the comma-separated data in the multi-choice cell becomes an array
$StructuredData = $FormData |
# Limit output of this test script in case the response data gets large
Select-Object -First $MaxRows |
ForEach-Object {
New-Object psobject -Property ([ordered]@{
Timestamp = $_.Timestamp
"Who are you?" = $_."Who are you?"
"What did you do?" = ([string]$_."What did you do?").Split(",") |
# Trim the whitespace from each choice inserted when comma-separated
ForEach-Object { $_.Trim() }
})
}
$StructuredData
# Timestamp Who are you? What did you do?
# --------- ------------ ----------------
# 3/12/2016 2:07:38 Another browser {I came, I saw, I conquered}
# 3/12/2016 2:08:52 Oops I did it again {I came, I conquered}
# 3/15/2016 10:43:54 no one {I came, I saw}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment