Powershell: Limit API iterations in a single call

Problem:

Many APIs limit the number of iterations that you can make in a single API call. For example, Brightpearl limits you to getting information for a maximum of 200 orders in a single API call. If you place a call with more than 200 orders, it will simply return an error message. SmartyStreets also places a limit of 100 addresses that you can validate with a single API call.

Solution:

Dave Wyatt at PowerShell.org provides the following solution.

Lets assume there is an array of 1000 addresses which are returned by convertfrom-csv. Here are the first couple of records from the original .csv file.

PS>cat lapsed.csv
Addressee,first,spouse,Organization,Street,City,State,ZIP
Joe Dokes,Joe, Mary,,,601 W 57TH St Apt 361,New York,NY,10019
Mary Smith ,Mary,Howard,,347 Poor Farm Rd,Colchester,VT,05446
Lu-Anne Jorden,Lu-Anne,Jess,,9603 North Kiowa Rd.,Parker,CO,80138

Here is the command that we use to read in the list into the variable $bigLlist

$bigList =(cat lapsed.csv | convertfrom-csv | 
Select-Object Addressee, Organization, Street, City, State, Zip )

$bigList is a custom object with the following layout:

PS>$biglist | get-member
TypeName: Selected.System.Management.Automation.PSCustomObject
Name         MemberType    Definition 
----         ----------    ---------- 
Equals       Method        bool Equals(System.Object obj) 
GetHashCode  Method        int GetHashCode() 
GetType      Method        type GetType() 
ToString     Method        string ToString() 
Addressee    NoteProperty  System.String Addressee=Kamal Aboul-Hosn 
City         NoteProperty  System.String City=New York 
Organization NoteProperty  System.String Organization= 
State        NoteProperty  System.String State=NY 
Street       NoteProperty  System.String Street=601 W 57TH St Apt 361
ZIP          NoteProperty  System.String ZIP=10019

If you look at this in Powershell, it prints out the contents of each record.

Addressee    : Joe Dokes
Organization :
Street       : 109 Fern Ct.
City         : Delray Beach
State        : FL
ZIP          : 33444

Addressee    : Mary Smith
Organization :
Street       : 205 Dorado Dr
City         : Cherry Hill
State        : NJ
ZIP          : 08034

Addressee    : Lu-Anne Jorden
Organization :
Street       : PO Box 81666
City         : Fairbanks
State        : AK
ZIP          : 99708

Ok, so now we have the full list as an object. The list now needs to be subdivided into groups of 100.

$counter = @{ Value = 0 }
$groupSize = 100
$groups = $bigList | Group-Object -Property { [math]::Floor($counter.Value++ / $groupSize) }

The $counter variable is a hash table, initialized to zero.
The $groupsize variable is the size of the individual group that can be sent. In our example it is set to 100, for a maximum of 100 addresses to be sent at a time.
The $groups variable creates a custom object, with the following members:

PS>$groups | gm
   TypeName: Microsoft.PowerShell.Commands.GroupInfo

Name        MemberType Definition                                                      
----        ---------- ----------                                                      
Equals      Method     bool Equals(System.Object obj)                                  
GetHashCode Method     int GetHashCode()                                               
GetType     Method     type GetType()                                                  
ToString    Method     string ToString()                                               
Count       Property   int Count {get;}                                                
Group       Property   System.Collections.ObjectModel.Collection[psobject] Group {get;}
Name        Property   string Name {get;}                                              
Values      Property   System.Collections.ArrayList Values {get;}                      

If you print out the contents of $groups, you see the following list. (I’ve truncated for readability…)

PS>$groups
Count Name     Group                                                                                                              
----- ----     -----                                                                                                              
  100 0        {@{Addressee=Kamal Aboul-Hosn; Organization=; ...
  100 1        {@{Addressee=Chandler Dawson; Organization=; ...   
  100 2        {@{Addressee=Sidsel Heney; Organization=; ...
  100 3        {@{Addressee=John Marchetti; Organization=; ...
  100 4        {@{Addressee=Jane Ramsey; Organization=; ...
   59 5        {@{Addressee=James Tulloh; Organization=; ... 

This shows that I have 559 names in the original file which has been divided up into 5 groups of 100 and one of 59 names.

The next and final step is to iterate through each group and make the API call.

 
foreach ($group in $groups)
{
    $littleList = $group.Group | ConvertTo-Json
  
$Output = Invoke-RestMethod -Uri $Uri -Body $littlelist -ContentType application/json -Method Post 
}

The steps are:
For each group
Convert the addresses in one group to JSON
Assign it to the variable $littlelist
Send the contents of $littlelist as the body of the API call.
End Loop.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s