How to Backup Twitter Friends with PowerShell and Excel Automatically

This script is an updated version of the
How to Backup Twitter Friends with PowerShell and Excel
post earlier today. In this version I use the
out-Excel function by The Pathological Scripter.

[System.Reflection.Assembly]::LoadWithPartialName(”System.Web") | Out-Null

$userName = "username"
$password = "password"

Function Get-TwitterFriends {
param ($username, $password, $ID)
if ($WebClient -eq $null) {$Global:WebClient=new-object System.Net.WebClient  }
$WebClient.Credentials = (New-Object System.Net.NetworkCredential -argumentList $username, $password)
$page = 1
$Friends = @()
if ($ID) {$URL="http://twitter.com/statuses/friends/$ID.xml?page="}
else     {$URL="http://twitter.com/statuses/friends.xml?page="}
do {  $Friends += (([xml]($WebClient.DownloadString($url+$Page))).users.user   )
        $Page ++
    } while ($Friends.count -eq 100)
$Friends
}

function out-excel {

param ([string[]]$property,[switch]$raw)

begin {
  # start Excel and open a new workbook 
  $Excel = New-Object -Com Excel.Application
  $Excel.visible = $True
  $Excel = $Excel.Workbooks.Add()
  $Sheet = $Excel.Worksheets.Item(1)
  # initialize our row counter and create an empty hashtable 
  # which will hold our column headers 
  $Row = 1
  $HeaderHash = @{}
}

process {
  if ($_ -eq $null) {return}
  if ($Row -eq 1) {
    # when we see the first object, we need to build our header table 
    if (-not $property) {
      # if we haven’t been provided a list of properties, 
      # we’ll build one from the object’s properties 
      $property=@()
      if ($raw) {
        $_.properties.PropertyNames | %{$property+=@($_)}
      } else {
        $_.PsObject.get_properties() | % {$property += @($_.Name.ToString())}
      }
    }
   $Column = 1
    foreach ($header in $property) {
      # iterate through the property list and load the headers into the first row 
      # also build a hash table so we can retrieve the correct column number 
      # when we process each object 
      $HeaderHash[$header] = $Column
      $Sheet.Cells.Item($Row,$Column) = $header.toupper()
      $Column ++
    }
    # set some formatting values for the first row 
    $WorkBook = $Sheet.UsedRange
    $WorkBook.Interior.ColorIndex = 19
    $WorkBook.Font.ColorIndex = 11
    $WorkBook.Font.Bold = $True
    $WorkBook.HorizontalAlignment = -4108
  }
  $Row ++
  foreach ($header in $property) {
    # now for each object we can just enumerate the headers, find the matching property 
    # and load the data into the correct cell in the current row. 
    # this way we don’t have to worry about missing properties 
    # or the “ordering” of the properties 
    if ($thisColumn = $HeaderHash[$header]) {
      if ($raw) {
        $Sheet.Cells.Item($Row,$thisColumn) = [string]$_.properties.$header
      } else {
        $Sheet.Cells.Item($Row,$thisColumn) = [string]$_.$header
      }
    }
  }
}

end {
  # now just resize the columns and we’re finished 
  if ($Row -gt 1) { [void]$WorkBook.EntireColumn.AutoFit() }
}
}

Get-TwitterFriends $userName $password | select name,screen_Name,url | out-excel

No related posts.