Continuing on from the previous article, PowerShell Basics – Consuming a Web API, let’s move on to some slightly deeper scripting. Querying an API in isolation is seldom useful in isolation, as it requires a deal of manual work to keep feeding the script. What if we could programmatically import data and iterate through it to produce a uniform output? There’s some factors to consider here which makes this slightly less trivial than it might sound – especially with the consumption of Web APIs to popular websites.

Apologies upfront – I moved to a new machine and it lacks the source code plugin, so the code samples are in a mixed format below.

Design Considerations

Rate limiting API consumption

Some APIs, particularly busy ones, may implement some kind of consumption limitations such as returning errors after a specific threshold of queries/commands. Another common approach is to implement a charge model which limits uses per day, per hour or per minute. The Discogs API used in the previous article rate limits API calls per minute.

Error Handling

Rather than have your script error out due to an issue, or continue to execute after rate limitations have taken effect (as above), it might be worth considering how to implement try/catch functionality so that execution can be suspended and resumed after a sensible period of time.

Skip Records

If you’ve decided to execute a script in batches, and would like the flexibility to process only some of the records provided as input, a design which incorporates skipping records is worth considering.

Periodically Write Output

Lastly, it’s worth considering periodically writing out data as a script executes. This affords you an option to take a peek at the rolling output to ensure everything’s working well before you run the script for too long, or before major errors pop up.

Let’s Start

Jumping right in, first let’s define the input.

Input Data

So I have my information saved in an Excel Spreadsheet (for example purposes), and I have exported select fields into a comma separated value (CSV) format. When viewing the CSV file in Excel it looks like this:

But when I open the same file in a basic text editor, I get the raw format:

Artist,Album,Label,Catalog,Country,Year,RR,#LPs,Release,URI,Notes,Mono?,
1927,…ISH,Trafalgar,255968-1,AUS,1988,,1,3236744,http://www.discogs.com/1927-Ish/release/3236744,,,
Abba,Abba,RCA,VPL1-4013,AUS,1975,,1,1361076,http://www.discogs.com/release/1361076,,,
Abba,The Singles,RCA,VPL2-6648,AUS,1982,,2,400768,http://www.discogs.com/release/400768,,,
AC/DC,Back in Black,Albert,APLP-046,AUS,1980,,1,3017903,https://www.discogs.com/ACDC-Back-In-Black/release/3017903,,,
AC/DC,Dirty Deeds Done Dirt Cheap,Albert,APLP.020,AUS,1976,,1,400605,https://www.discogs.com/ACDC-Dirty-Deeds-Done-Dirt-Cheap/release/400605,1st press,,
AC/DC,Flick the Switch,Albert,APLP.061,AUS,1983,,1,400600,https://www.discogs.com/ACDC-Flick-Of-The-Switch/release/400600,,,

Writing the Script

What we’re going to do is to open this document from a PowerShell script and loop through each record and process it by querying the Discogs API. When it finishes, we’ll have written all the data collected out to another CSV file.

The start of our PowerShell script will focus on declaring some variables and loading the source data. It looks like this:

$records = Import-Csv c:\tools\records.csv
$results = @()

$h2 = @{}
$h2.Add(‘Authorization’, ‘Discogs token=<YOUR_PRIVATE_API_TOKEN>’)

$startFrom = 0
$count = 0
$maxItems = 500

You can see we’ve declared the authorization header as we did in the previous article, we’ve also imported data using the Import-CSV function, and declared some control variables to vary how the script is executed. I’ve also added a $maxItems variable so that you can control the max number of items handled during the execution of the script.

Error Control

I mentioned earlier that it’d be great if the script could detect issues and pause itself, mainly to respond to API rate limiting. We’ll do this by implementing a generic error handling routine. Once we start looping through the input data, the first thing we establish is a Try/Catch block. Inside the catch, we’ll optimistically assume that it’s a simple case of pausing execution for a little while (1 minute) before resuming from the record which caused the error.

foreach($entry in $records)
{ 
    try
    {
        #implementation goes here
    }
    catch
    {
       $results | export-csv -Path c:\tools\salesinfo.csv -NoTypeInformation
       Write-Host "An error occurred that could not be resolved.  Last count " $count " Sleeping"
       Start-Sleep -Milliseconds 60000
    }
}
#Post-looping logic

We’re going to insert the rest of the script inside/around the error control logic. Let’s start with some record control; we’ll be able to skip a defined number of entries by using the $startFrom variable, which was declared at the top of the script:

if($count -lt $startFrom)
{
$count = $count + 1
continue
}

This tells the script to skip the first N records, where N is $startFrom.

Now, we’ll define a block of the script which can be invoked from elsewhere in the script. The main reason for this is so that we can invoke it from our error handling routine. It’s a little bit like implementing GOTO functionality for those who remember when that was a thing. Notice that I do a check for (in bold), and exclude, any records which lack the required release number.

$GetData = {

    Write-Host $count $entry.Artist $entry.Release
    $releaseNo = $entry.Release
    if([string]::IsNullOrEmpty($releaseNo))
    {
        Write-Host $entry.Album ‘ missing Release No’
        continue
    }

$market = Invoke-WebRequest -uri https://api.discogs.com/marketplace/price_suggestions/$releaseNo -Headers $h2
$json2 = ConvertFrom-Json $market.Content

You’ll also note that I’m returning the created object; this is so that the consuming/calling code can obtain the object and store it in an array. The next thing we do is invoke the block of code, and store the results. To guard against an infinite loop, we do a check of the max number of items and terminate the loop if we reach it:

$results += &$GetData

$count = $count + 1
if($count -eq $maxItems)
{
      break;
}

Then, we just need to update the catch block:

catch
{
   $results | export-csv -Path c:\tools\salesinfo.csv -NoTypeInformation
   Write-Host “An error occurred that could not be resolved. Last count ” $count ” Sleeping”
   Start-Sleep -Milliseconds 60000
   $results += &$GetData
}

You’ll also notice that I’m piping the collected output to a results file as we go. I’m using a 1 minute (60 second) timeout which is a restriction applied by the Discogs API.

At the end of the script, outside the loop, I finish the processing:

$results | export-csv -Path c:\tools\salesinfo.csv -NoTypeInformation
Write-Host “Last count ” $count

The Complete Script

Well, that’s about it. Here’s the script in its entirety:

$records = Import-Csv c:\tools\records.csv
$results = @()

$h2 = @{}
$h2.Add(‘Authorization’, ‘Discogs token=<YOUR_PERSONAL_API_TOKEN>’)

$startFrom = 0
$count = 0
$maxItems = 500

foreach($entry in $records)
{
    try
    {
        if($count -lt $startFrom)
        {
            $count = $count + 1
            continue
        }

        $GetData = {

            Write-Host $count $entry.Artist $entry.Release
            $releaseNo = $entry.Release
            if([string]::IsNullOrEmpty($releaseNo))
            {
                Write-Host $entry.Album ‘ missing Release No’
                continue
            }

$market = Invoke-WebRequest -uri https://api.discogs.com/marketplace/price_suggestions/$releaseNo -Headers $h2
$json2 = ConvertFrom-Json $market.Content

            $details = @{
                        Artist       = $entry.Artist
                        Album        = $entry.Album
                        Release      = $entry.Release
                        CurrentDate = Get-Date
                        ‘Very Good (VG)’       = $json2.’Very Good (VG)’.value
                        ‘Very Good Plus (VG+)’ = $json2.’Very Good Plus (VG+)’.value
                        ‘Near Mint (NM/M-)’    = $json2.’Near Mint (NM or M-)’.value
                        ‘Good (G)’             = $json2.’Good (G)’.value
                        ‘Mint (M)’             = $json2.’Mint (M)’.value
                        ‘Fair (F)’             = $json2.’Fair (F)’.value
                        ‘Good Plus (G+)’       = $json2.’Good Plus (G+)’.value
                        ‘Poor (P)’             = $json2.’Poor (P)’.value
            }
            return New-Object PSObject -Property $details
        }

        $results += &$GetData

        $count = $count + 1
        if($count -eq $maxItems)
        {
            break;
        }
    }
    catch
    {
        $results | export-csv -Path c:\tools\salesinfo.csv -NoTypeInformation
        Write-Host “An error occurred that could not be resolved. Last count ” $count ” Sleeping”
        Start-Sleep -Milliseconds 60000
        $results += &$GetData
    }
    Write-Host “Added ” $results.Count
}

$results | export-csv -Path c:\tools\salesinfo.csv -NoTypeInformation
Write-Host “Last count ” $count

Summary

In hindsight, this article attacks a number of script execution and design concepts. Hopefully the approach and design goals make sense, and that you’ve seen one of the gaps in the approach. This wasn’t intended as a full proof script, partly because it is based on the behaviour of the Discogs API, and party because it can be re-run if needs be.

If you wanted to take it to the next level, we’d extract the code which invokes the Discogs API and put it into a function. Then we’d rewrite the error handling to retry processing of an entry and break/quit after a pre-defined number of attempts. At any rate, this script was good enough for my purposes, and here’s what some of the output looks like (376 rows!):

PowerShell Basics – Looping, Error Handling and Conditional Control Logic