Skip to content

Instantly share code, notes, and snippets.

@ConnorGriffin
Created July 14, 2018 00:00
Show Gist options
  • Save ConnorGriffin/fdeb81767f2cf977cdba2c610d6a8dbc to your computer and use it in GitHub Desktop.
Save ConnorGriffin/fdeb81767f2cf977cdba2c610d6a8dbc to your computer and use it in GitHub Desktop.
Google Sheets Export to PowerShell Object by GID (Sheet Tab ID)
# Set our options
$RefreshToken = ''
$ClientID = ''
$ClientSecret = ''
$spreadsheetId = '' # Get this from the URL: https://docs.google.com/spreadsheets/d/{spreadsheetId}
$sheetId = '' # Get this from gid= in the URL
# Set the Google Auth parameters
$params = @{
Uri = 'https://accounts.google.com/o/oauth2/token'
Body = @(
"refresh_token=$RefreshToken", # Replace $RefreshToken with your refresh token
"client_id=$ClientID", # Replace $ClientID with your client ID
"client_secret=$ClientSecret", # Replace $ClientSecret with your client secret
"grant_type=refresh_token"
) -join '&'
Method = 'Post'
ContentType = 'application/x-www-form-urlencoded'
}
$accessToken = (Invoke-RestMethod @params).access_token
# Set our auth headers
$headers = @{
"Authorization" = "Bearer $accessToken"
"Content-type" = "application/json"
}
# Get the sheet details, then find our tab and get the row data
$spreadsheet = Invoke-RestMethod -Uri "https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}?includeGridData=true" -Headers $headers
$sheet = $spreadsheet.sheets.Where{$_.properties.sheetId -eq $sheetId}
$rowData = $sheet.data.rowData
# Use the sheet headers to initialize a PSObject
$objectTemplate = [PSCustomObject]@{}
$headers = $rowData[0].values.formattedValue.Where{$_}
$headers.ForEach{
$objectTemplate | Add-Member -MemberType NoteProperty -Name $_ -Value $null
}
# Init an empty array to add our object values to and add the row data to the array
$values = @()
foreach ($row in $rowData[1..($rowData.Count-1)]) {
$tempObj = $objectTemplate.PSObject.Copy()
foreach ($header in $headers) {
$index = $headers.IndexOf($header)
$tempObj.$header = $row.values[$index].formattedValue
}
$values += $tempObj
}
$values | ft
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment