How to "Select-Object" on multiple multiple-valued properties?

Question

I'm trying to retrieve Google Calendar events and bulk-copy them into a SQL Server table.

$requestUri = "https://www.googleapis.com/calendar/v3/calendars/.../events"
$calEvents = Invoke-RestMethod -Headers @{Authorization = "Bearer $accessToken"} -Uri $requestUri -Method Get -Body $Parameters -ContentType "application/json; charset=utf-8"
$dt = $calEvents.items | Select-Object id, ????????? | Out-DataTable
...
$bulkCopy.WriteToServer($dt)

So my question is what to put into ????? so I would be able to save for organizer-email, creator-email, start-date, end-date into the table.

$calEvents.items looks like this:

created     : 2017-08-28T07:18:19.000Z
updated     : 2017-08-29T16:41:00.441Z
summary     : Vacation
creator     : @{[email protected]; displayName=XXX}
organizer   : @{[email protected]}
start       : @{date=2018-03-26}
end         : @{date=2018-03-31}
...

So I want to retrieve only email and date elements from @{} multivalues. Any idea how? (I know I can expand only one property).

Ansgar Wiechers · Accepted Answer · 2017-12-28 20:40:20Z

4

Those are most likely nested objects. You should be able to "flatten" your data by using calculated properties:

$dt = $calEvents.items |
      Select-Object id, @{n='creator';e={$_.creator.email}},
          @{n='organizer';e={$_.organizer.email}},
          @{n='start';e={$_.start.date}},
          @{n='end';e={$_.end.date}} |
      Out-DataTable

answered Dec 28, 2017 at 20:40

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mara Over a year ago

Thanx bro! That was it. I have another problem though, and that is the property "start" sometimes looking like this: start : @{dateTime=2018-06-27T11:00:00+02:00} and something looking like this: start : @{date=2018-03-26} So how can I distinct between dateTime and date, I mean to give them different column names, one for date, another one for datetime.

Mara Over a year ago

Actually, "start" as a column name is OK, but it should contain either date or datetime entity, depending on which one exists (it must be one of the two).

Mara Over a year ago

Solved it with: e={$_.start.datetime+$_.start.date}} . I'm not sure if it's the best practice though.

Ansgar Wiechers Over a year ago

Best way would be to fix your input data at the source. If that's not possible simply use a condition: e={if ($_.start.date) {$_.start.date} else {$_.start.datetime}}.

Esperento57 · Accepted Answer · 2017-12-29 12:15:45Z

0

other method:

$dt = $calEvents.items | %{
[pscustomobject]@{
                    id=$_.id
                    organizer=$_.organizer.email
                    start=$_.start.date
                    end=$_.end.date
                 }


} | Out-DataTable

answered Dec 29, 2017 at 12:15

Esperento57

17.6k6 gold badges42 silver badges47 bronze badges

Collectives™ on Stack Overflow

How to "Select-Object" on multiple multiple-valued properties?

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related