sisaacrussell · August 14, 2024 07:07
diff --git a/bento_validate_email.ps1 b/bento_validate_email.ps1
 <#
 .SYNOPSIS
 This script processes email subscriber data in an Excel file, sends each entry to the Bento API for validation, and writes the boolean response to Column D.

 .DESCRIPTION
 - The script requires the System.Web assembly to be loaded for URL encoding.
 - The script handles rate limiting (100 requests per minute) and includes progress tracking.
 - A progress log is displayed in the terminal, showing current progress, out of total rows, and estimated time remaining.

 .INSTRUCTIONS
 1. Ensure the PowerShell execution policy allows script execution:
   Run the following command to temporarily allow the script to run:
   Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass

 2. Load the System.Web assembly (needed for URL encoding):
   Add-Type -AssemblyName System.Web

 3. After running the script, it is recommended to revert the execution policy to its default state:
   Set-ExecutionPolicy -Scope Process -ExecutionPolicy Default

 .PARAMETERS
 - $excelFilePath: Path to the Excel file containing the email subscriber data.
 - $headers: API request headers, including the authorization key.
 - $requestsPerMinute: The rate limit for API requests (default is 100 requests per minute).

 .NOTES
 - Ensure that Excel is installed on the machine where you are running this script.
 - Modify the $excelFilePath and $headers as needed for your environment.
 - Modify the column number in line 68 to correspond with your desired location.
 - See https://docs.bentonow.com/reference-api/validate-email for API documentation.
 #>

 # Define the headers for the API request
 $headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
 $headers.Add("Accept", "application/json")
 $headers.Add("Authorization", "") # Replace with your actual API key

 # Load the Excel file
 $excelFilePath = "test.xlsx"  # Replace with the actual path to your Excel file
 $excel = New-Object -ComObject Excel.Application
 $workbook = $excel.Workbooks.Open($excelFilePath)
 $worksheet = $workbook.Sheets.Item(1)  # Assuming the data is in the first sheet

 # Find the last row with data in Column A
 $lastRow = $worksheet.Cells($worksheet.Rows.Count, 1).End(-4162).Row
 $totalRows = $lastRow - 1  # Exclude the header row

 # Time and progress tracking
 $requestsPerMinute = 100 # Expirimental API limit is 100 requests per minute.
 $delayInSeconds = 60 / $requestsPerMinute
 $startTime = Get-Date

 for ($row = 2; $row -le $lastRow; $row++) {
    $firstName = $worksheet.Cells.Item($row, 1).Value2
    $lastName = $worksheet.Cells.Item($row, 2).Value2
    $email = $worksheet.Cells.Item($row, 3).Value2

    # Combine first and last name, URL encode the space between them
    $fullName = [System.Web.HttpUtility]::UrlEncode("$firstName $lastName")

    # Make the API request
    $apiUrl = "https://app.bentonow.com/api/v1/experimental/validation?name=$fullName&email=$email"
    $response = Invoke-RestMethod -Uri $apiUrl -Method 'POST' -Headers $headers

    # Extract the boolean value from the response
    $isValid = $response.valid

    # Write the boolean value to Column D (4th column) modify accordingly
    $worksheet.Cells.Item($row, 4.Value2 = $isValid

    # Calculate and display progress
    $currentProgress = $row - 1  # Exclude the header row
    $elapsedTime = (Get-Date) - $startTime
    $estimatedTotalTime = $elapsedTime.TotalSeconds / $currentProgress * $totalRows
    $estimatedTimeRemaining = $estimatedTotalTime - $elapsedTime.TotalSeconds

    Write-Host "Processed $currentProgress of $totalRows. Estimated time remaining: $([timespan]::FromSeconds($estimatedTimeRemaining).ToString("hh\:mm\:ss"))"

    # Wait for the specified delay before making the next request
    Start-Sleep -Seconds $delayInSeconds
 }

 # Save and close the Excel file
 $workbook.Save()
 $workbook.Close()
 $excel.Quit()

 # Release COM objects
 [System.Runtime.InteropServices.Marshal]::ReleaseComObject($worksheet) | Out-Null
 [System.Runtime.InteropServices.Marshal]::ReleaseComObject($workbook) | Out-Null
 [System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel) | Out-Null
 [GC]::Collect()
 [GC]::WaitForPendingFinalizers()

 # Final completion message
 $endTime = Get-Date
 $totalElapsedTime = $endTime - $startTime
 Write-Host "Processing complete. Total time: $($totalElapsedTime.ToString("hh\:mm\:ss"))"
	<#
	.SYNOPSIS
	This script processes email subscriber data in an Excel file, sends each entry to the Bento API for validation, and writes the boolean response to Column D.

	.DESCRIPTION
	- The script requires the System.Web assembly to be loaded for URL encoding.
	- The script handles rate limiting (100 requests per minute) and includes progress tracking.
	- A progress log is displayed in the terminal, showing current progress, out of total rows, and estimated time remaining.

	.INSTRUCTIONS
	1. Ensure the PowerShell execution policy allows script execution:
	Run the following command to temporarily allow the script to run:
	Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass

	2. Load the System.Web assembly (needed for URL encoding):
	Add-Type -AssemblyName System.Web

	3. After running the script, it is recommended to revert the execution policy to its default state:
	Set-ExecutionPolicy -Scope Process -ExecutionPolicy Default

	.PARAMETERS
	- $excelFilePath: Path to the Excel file containing the email subscriber data.
	- $headers: API request headers, including the authorization key.
	- $requestsPerMinute: The rate limit for API requests (default is 100 requests per minute).

	.NOTES
	- Ensure that Excel is installed on the machine where you are running this script.
	- Modify the $excelFilePath and $headers as needed for your environment.
	- Modify the column number in line 68 to correspond with your desired location.
	- See https://docs.bentonow.com/reference-api/validate-email for API documentation.
	#>

	# Define the headers for the API request
	$headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
	$headers.Add("Accept", "application/json")
	$headers.Add("Authorization", "") # Replace with your actual API key

	# Load the Excel file
	$excelFilePath = "test.xlsx" # Replace with the actual path to your Excel file
	$excel = New-Object -ComObject Excel.Application
	$workbook = $excel.Workbooks.Open($excelFilePath)
	$worksheet = $workbook.Sheets.Item(1) # Assuming the data is in the first sheet

	# Find the last row with data in Column A
	$lastRow = $worksheet.Cells($worksheet.Rows.Count, 1).End(-4162).Row
	$totalRows = $lastRow - 1 # Exclude the header row

	# Time and progress tracking
	$requestsPerMinute = 100 # Expirimental API limit is 100 requests per minute.
	$delayInSeconds = 60 / $requestsPerMinute
	$startTime = Get-Date

	for ($row = 2; $row -le $lastRow; $row++) {
	$firstName = $worksheet.Cells.Item($row, 1).Value2
	$lastName = $worksheet.Cells.Item($row, 2).Value2
	$email = $worksheet.Cells.Item($row, 3).Value2

	# Combine first and last name, URL encode the space between them
	$fullName = [System.Web.HttpUtility]::UrlEncode("$firstName $lastName")

	# Make the API request
	$apiUrl = "https://app.bentonow.com/api/v1/experimental/validation?name=$fullName&email=$email"
	$response = Invoke-RestMethod -Uri $apiUrl -Method 'POST' -Headers $headers

	# Extract the boolean value from the response
	$isValid = $response.valid

	# Write the boolean value to Column D (4th column) modify accordingly
	$worksheet.Cells.Item($row, 4.Value2 = $isValid

	# Calculate and display progress
	$currentProgress = $row - 1 # Exclude the header row
	$elapsedTime = (Get-Date) - $startTime
	$estimatedTotalTime = $elapsedTime.TotalSeconds / $currentProgress * $totalRows
	$estimatedTimeRemaining = $estimatedTotalTime - $elapsedTime.TotalSeconds

	Write-Host "Processed $currentProgress of $totalRows. Estimated time remaining: $([timespan]::FromSeconds($estimatedTimeRemaining).ToString("hh\:mm\:ss"))"

	# Wait for the specified delay before making the next request
	Start-Sleep -Seconds $delayInSeconds
	}

	# Save and close the Excel file
	$workbook.Save()
	$workbook.Close()
	$excel.Quit()

	# Release COM objects
	[System.Runtime.InteropServices.Marshal]::ReleaseComObject($worksheet) \| Out-Null
	[System.Runtime.InteropServices.Marshal]::ReleaseComObject($workbook) \| Out-Null
	[System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel) \| Out-Null
	[GC]::Collect()
	[GC]::WaitForPendingFinalizers()

	# Final completion message
	$endTime = Get-Date
	$totalElapsedTime = $endTime - $startTime
	Write-Host "Processing complete. Total time: $($totalElapsedTime.ToString("hh\:mm\:ss"))"