Impromptu APIs and Excel WEBSERVICE

Posted by Parajuli Sushil 5 months Ago 2 Comments 8 Min Read

Impromptu APIs and Excel WEBSERVICE

PHP – the programming language that at one point in history (and still) fueled the dreams of tech billionaires worldwide ! PHP quietly powers websites and applications like WordPress, Facebook, Wikipedia, Slack and the likes. We will do some php down below but it’s not going to rake in the billions like Facebook and WordPress for us unless you count saving countless hours as a fortune !

In today’s tech landscape, APIs (Application Programming Interfaces) are essential for connecting different systems and services. But what if you don’t have access to a well-documented API in the form of services? Enter the world of impromptu APIs created using PHP and Excel’s WEBSERVICE function – that will help us make makeshift APIs to complete our automation or querying tasks. Let’s explore how these tools can work together to extract valuable data from the web and the benefits they offer.

What is an Impromptu API?

An impromptu API is a makeshift interface created to interact with web services, often when a formal API isn’t available. It leverages web scraping and HTTP requests to fetch and manipulate data programmatically. PHP, a versatile scripting language, is particularly effective in this realm due to its strong support for HTTP requests and data manipulation.

Before starting here is what you need: php enabled hosting service and a domain service. 

How It Works?

Here’s a look at two PHP scripts that demonstrate this concept:

Taxpayer Information Retrieval (Only an Example)

            <?php
function get_and_parse_taxpayer_info($pan) {
    // Define the URL and payload
    $url = "https://taxpayerportal.ird.gov.np/Handlers/Registration/Taxpayer/TaxpayerHandler.ashx";
    
    // Initialize cURL session
    $ch = curl_init($url);
    
    // Define the POST fields
    $postFields = http_build_query([
        "method" => "GetTaxPayerShort",
        "pan" => $pan,
        "acctType" => "",
        "formToken" => "a"
    ]);
    
    // Set cURL options
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_POST, true);
    curl_setopt($ch, CURLOPT_POSTFIELDS, $postFields);
    
    // Execute the POST request
    $response = curl_exec($ch);
    
    // Check for cURL errors
    if ($response === false) {
        echo "cURL Error: " . curl_error($ch);
        curl_close($ch);
        return;
    }
    
    curl_close($ch);
    
    // Slice the response to remove unwanted characters
    $cleaned_response = substr($response, 6, -37);
    
    // Decode the JSON content
    $data = json_decode($cleaned_response, true);
    
    if (json_last_error() === JSON_ERROR_NONE) {
        // Extract the relevant fields
        $taxpayer_info = $data['Taxpayer'] ?? [];
        $business_name = $taxpayer_info['BusinessName'] ?? 'N/A';
        $name = $taxpayer_info['Name'] ?? 'N/A';
        
        // Output the result in the desired format
        echo $business_name . " / " . $name;
    } else {
        echo "JSON Decode Error: " . json_last_error_msg();
    }
}

// Example usage
if ($_SERVER['REQUEST_METHOD'] === 'GET') {
    if (isset($_GET['pan']) && !empty($_GET['pan'])) {
        $pan_number = $_GET['pan'];
        get_and_parse_taxpayer_info($pan_number);
    } else {
        // Output plain text error message
        echo "Invalid or missing PAN number. Example: https://sushilparajuli.com/api/pan.php?pan=600001799";
    }
} else {
    // Output plain text error message for invalid request method
    echo "Invalid request method. Please use GET request.";
}
?>

This script sends a POST request to a taxpayer portal and parses the returned JSON data to extract and display taxpayer information. You can use this web URL to send in the request to extract the business name for the given PAN number: 
https://sushilparajuli.com/api/pan.php?pan=600001799

Supreme Court Case Details (Only an Example)

            <?php
// Suppress warnings about insecure requests
error_reporting(E_ERROR | E_PARSE);

// Get regno from query parameters
$regno = isset($_GET['regno']) ? $_GET['regno'] : '';
if (empty($regno)) {
    echo "Registration number is required.";
    exit;
}

// Set URL and form data
$url = "https://supremecourt.gov.np/cp/";
$form_data = array(
    'court_type' => 'S',
    'court_id' => '264',
    'regno' => $regno,
    'darta_date' => '',
    'faisala_date' => '',
    'submit' => ''
);

// Initialize cURL
$ch = curl_init($url);

// Set cURL options
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_POST, true);
curl_setopt($ch, CURLOPT_POSTFIELDS, http_build_query($form_data));
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false); // Disable SSL verification for HTTPS
curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false); // Disable SSL host verification

// Execute cURL request
$response = curl_exec($ch);

// Check for cURL errors
if (curl_errno($ch)) {
    echo "cURL error: " . curl_error($ch);
    curl_close($ch);
    exit;
}

// Close cURL
curl_close($ch);

// Load HTML response into DOMDocument
$doc = new DOMDocument();
libxml_use_internal_errors(true); // Suppress errors due to malformed HTML
$doc->loadHTML($response);
libxml_clear_errors();

// Create DOMXPath object
$xpath = new DOMXPath($doc);

// Query for the last table
$tables = $xpath->query('//table');
if ($tables->length > 0) {
    $last_table = $tables->item($tables->length - 1);
    $rows = $xpath->query('.//tr', $last_table);

    if ($rows->length > 0) {
        $last_row = $rows->item($rows->length - 1);
        $cols = $xpath->query('.//td', $last_row);

        if ($cols->length > 0) {
            $last_col = $cols->item($cols->length - 1);
            $anchors = $xpath->query('.//a', $last_col);

            foreach ($anchors as $anchor) {
                if (strpos(trim($anchor->nodeValue), 'हेर्नुहोस') !== false) {
                    echo $anchor->getAttribute('href');
                    exit;
                }
            }
            echo "Full text not uploaded yet";
        } else {
            echo "No columns found in the last row";
        }
    } else {
        echo "No rows found in the table";
    }
} else {
    echo "No tables found in the response";
}
?>

This script handles a POST request to a court’s website, parses the HTML response to extract a specific link related to a case. You can use this web URL to send in the request to extract the case upload status for the given case number: 
https://sushilparajuli.com/api/fulltext.php?regno=०८०-WH-०३७१

Integrating with Excel WEBSERVICE (Some Examples)

Excel’s WEBSERVICE function can directly fetch data from a URL. By combining it with your PHP scripts, you can leverage Excel to query your impromptu APIs and display the results dynamically.

For instance, if you host your PHP scripts on a server, you can use Excel’s WEBSERVICE function to call these scripts and retrieve data. Here’s a simple example:

This function will call your PHP API and display the returned data in an Excel cell. It’s an excellent way to integrate live data into your spreadsheets without needing complex software.

Benefits

  • Cost-Effective: Using PHP and Excel WEBSERVICE, you can create powerful data retrieval tools without investing in expensive API services or software.
  • Flexibility: PHP scripts can be customized to handle a wide range of data formats and sources, giving you the flexibility to build solutions that fit your needs.
  • Ease of Use: Excel’s WEBSERVICE function is straightforward, making it easy for users with minimal coding knowledge to access and display API data.

In conclusion, the synergy between PHP and Excel WEBSERVICE functions creates a unique and strong solution for building impromptu APIs and integrating web data into your workflows. This approach offers a cost-effective, flexible, and user-friendly way to leverage the power of the web for data extraction and manipulation and analysis.