The WEBSERVICE() Function
  This Microsoft Excel spreadsheet was converted to HTML by SpreadSheetConverter www.spreadsheetconverter.com

Microsoft Excel spreadsheets can run parameterized queries on SQL databases and REST APIs.
Excel's WEBSERVICE() function can use PHP scripts to execute calculated queries and process results.
Queries can run in the Excel application or the spreadsheet can be converted to an HTML form.
Spreadsheets or HTML forms can both dynamically interact with unlimited data sources.

   -  extend Excel with open-source PHP, PDO, CURL, and SQL technologies
   -  model process solutions with Excel 365, utilize 270 Excel functions in calculations
   -  any cell can interact with data entered into a field or returned from a SQL or API query
   -  the data returned from SQL or REST API queries can be parsed back into cells and calculations
   -  PHP Data Objects database access layer drastically simplifies database communications
   -  PDO uses typed parameters and prepared statements to avoid SQL injection
   -  prepared statements can safely create, update, delete in transactions
   -  calculated output can POST to other spreadsheets, forms, APIs
   -  MySQL, SQLServer, Oracle, PostgreSQL, SQLlite PDO drivers
   -  no VBA, no ODBC or JDBC, no subscriptions, ultra-simple deployment

The examples below demonstrate the web form version's mechanics and show the scripts used.
Simple, reliable low-code methods for building precision SQL and REST API-integrated user interfaces.
 
Customer Look-up
  Selecting a Customer Name from the green drop-down menu performs a VLOOKUP that returns the customer's number. The number is passed to a PHP script that executes a SQL query that returns the customer's information using the formula below. The returned data is parsed into spreadsheet cells.
=IFERROR(WEBSERVICE("https://customdataservices.net/classicmodelscustomerlookup.php?customerNumber="&CUSTOMERNUMBER),"")
 
 
       
     
     
     
     
     
     
  Account Credit Limit   
  Current Accounts Payable   
  Available Credit   
         
  This script looks-up a customer's billing, shipping, and accounts payable information.    
  <?php
//comment the next line if scripts are on same server as converted spreadsheet, un-comment to test from workstation
//header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Cache-Control: no-cache, must-revalidate");
//uncomment next 2 lines if scripts are on same server as converted spreadsheet, comment to test from workstation
$domain = 'yourdomain.com';
$pos = strpos($_SERVER['HTTP_REFERER'], $domain); if (($pos === false) or ($pos == '')) {die("Have a nice day!");}
$host = '127.0.0.1';
$db   = 'MODELSdb';
$user = 'USER01name';
$pass = 'USER01password';
$charset = 'UTF8';
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => FALSE,];
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$pdo = new PDO($dsn, $user, $pass, $options);
$customerNumber = $_GET["customerNumber"];
$sql = "select customerNumber, customerName, contactLastName, contactFirstName, phone, addressLine1, AddressLine2, city, state, postalCode, country, creditLimit, accountsPayable from customers WHERE customerNumber = :customerNumber";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':customerNumber',(int) $customerNumber, PDO::PARAM_INT);
$stmt->execute();
$records = $stmt->fetchAll();
$json = json_encode($records);
$filter1 = str_replace ( ":null", ":\"null\"", $json);
echo $filter1;
?>
         
         
REST API Queries
  This first example uses the calculated URL below to request currency exchange rates from the fixer.io API.
  "https://data.fixer.io/api/latest?access_key=FIXERIOAPIKEY&base="&BASERATECODE  
 
  It uses the VLOOKUP formula below to match the selected currency to it's unique 3-character currency code.  
  VLOOKUP(BASESELECTOR,B22:C191,2,FALSE)  
  Select a Base Rate Currency below to trigger the request and load the JSON result.  
 
   
  Select the currency to use for this order form or invoice's calculations…  
 
   
   
   
  Enter an amount in the base currency  below to calculate the converted amount.  
     
     
  The selected currency will be used to calculate prices for the rest of the examples.  
 
  The second example (not live) used the PHP script below and the SmartyStreets API to verify a postal address  
  Street Address Unit City State  
   
     
   
  [[{"Line1":"1 Microsoft Way"}],[{"LastLine:":"Redmond WA 98052-8300"}],[{"County":"King"}],[{"Latitude":"47.64352"}],[{"Longitude":"-122.11922"}],[{"TimeZone":"Pacific"}]]  
             
           
           
           
           
           
           
     
  <?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Cache-Control: no-cache, must-revalidate");
header("Expires: Sat, 26 Jul 1997 05:00:00 GMT");
require_once('src/ClientBuilder.php');
require_once('src/US_Street/Lookup.php');
require_once('src/StaticCredentials.php');
use SmartyStreets\PhpSdk\Exceptions\SmartyException;
use SmartyStreets\PhpSdk\StaticCredentials;
use SmartyStreets\PhpSdk\ClientBuilder;
use SmartyStreets\PhpSdk\US_Street\Lookup;
$street = $_GET['street'];
$secondary = $_GET['secondary'];
$city = $_GET['city'];
$state = $_GET['state'];
$zipcode = $_GET['zipcode'];
$lookupExample = new UsStreetSingleAddressExample();
$lookupExample->run();
class UsStreetSingleAddressExample {
    public function run() {
        $authId = 'SMARTYSTREETSAUTHORIZATIONID';
        $authToken = 'SMARTYSTREETSAUTHORIZATIONTOKEN';
        $staticCredentials = new StaticCredentials($authId, $authToken);
        $client = (new ClientBuilder($staticCredentials))
                        ->buildUsStreetApiClient();
        $lookup = new Lookup();
        $lookup->setStreet($GLOBALS['street']);
        $lookup->setSecondary($GLOBALS['secondary']);
        $lookup->setCity($GLOBALS['city']);
        $lookup->setState($GLOBALS['state']);
        $lookup->setZipcode($GLOBALS['zipcode']);
        $lookup->setMaxCandidates(3);
        $lookup->setMatchStrategy("strict");
        try {
            $client->sendLookup($lookup);
            $this->displayResults($lookup);
        }
        catch (SmartyException $ex) {
            echo($ex->getMessage());
        }
        catch (\Exception $ex) {
            echo($ex->getMessage());
        }
    }
    public function displayResults(Lookup $lookup) {
        $results = $lookup->getResult();
        if (empty($results)) {
            echo("\"[[{This address is not valid}]]\"");
            return;
        }
        $firstCandidate = $results[0];
        $Line1 = "[[{\"Line1\":\"" . $firstCandidate->getDeliveryLine1() . "\"}],";
        $Line2 = "[{\"LastLine:\":\"" . $firstCandidate->getLastLine() . "\"}],";
        $Line3 = "[{\"County\":\"" . $firstCandidate->getMetadata()->getCountyName() . "\"}],";
        $Line4 = "[{\"Latitude\":\"" . $firstCandidate->getMetadata()->getLatitude() . "\"}],";
        $Line5 = "[{\"Longitude\":\"" . $firstCandidate->getMetadata()->getLongitude() . "\"}],";
        $Line6 = "[{\"TimeZone\":\"" . $firstCandidate->getMetadata()->getTimeZone() . "\"}]]";
        $data = ($Line1.$Line2.$Line3.$Line4.$Line5.$Line6);
        echo $data;
    }
}
?>
 
Dynamic Invoice
  International Bicycle Equipment Warehouse, LLC    
  12345 Airport Loop Road     Tonokset Township   Alaska, USA   98765   800-888-7777  
         
       
         
       
         
         
                                 
                                 
       
  Product #   Product Name         Price On Hand   Ordered Extension    
 
     
 
     
 
     
 
     
 
     
 
     
 
     
 
     
 
     
 
     
  Instructions or Notes   Subtotal     
    Discount %     
    Discount Amount     
    Adjusted     
    Sales Tax %     
    Sales Tax Amount    
    Subtotal     
    Shipping     
  Credit Limit   Balance Available     Order Total     
 
       
  This PHP script looks up a product's name, current price, and units in stock by it's product number. Each line uses this formula.  =WEBSERVICE("https://customdataservices.net/bicycleshopproductlookup.php?productnumber="&PRODUCTNUMBER01)  
       
  <?php
//comment the next line if scripts are on same server as converted spreadsheet, un-comment to test from workstation
//header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Cache-Control: no-cache, must-revalidate");
//uncomment next 2 lines if scripts are on same server as converted spreadsheet, comment to test from workstation
$domain = 'yourdomain.com';
$pos = strpos($_SERVER['HTTP_REFERER'], $domain); if (($pos === false) or ($pos == '')) {die("Have a nice day!");}
$host = 'localhost';
$db   = 'BIKESHOPdb';
$user = 'USER01name';
$pass = 'USER01password';
$charset = 'UTF8';
$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,];
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$pdo = new PDO($dsn, $user, $pass, $options);
date_default_timezone_set('America/Los_Angeles');
$StatMsg = date('d-m-y h:i:s');
$productnumber = $_GET["productnumber"];
$sql = "select ProductNumber, ProductName, RetailPrice, QuantityOnHand from Products WHERE productnumber = :productnumber";
$stmt = $pdo->prepare($sql);
$stmt->execute(['productnumber' => $productnumber]);
$records = $stmt->fetchAll(PDO::FETCH_ASSOC);
$json = json_encode($records);
echo $json;
?>
 
SQL Transaction
  This example updates 3 database records using 1 transaction. If an individual record's update fails the transaction rolls back. 
  The first 3 products from the Order Form Example appear below. Enter new Quantity On Hand values to test transactions.
  Product Number Product Data  
 
 
 
     
  Product Number New Quantity On Hand  
    Enter values in the New Quantity On Hand fields to build the URL.
    All 3 fields need to have a numeric value between 0 and 32767.
    Non-numeric, empty or larger numbers cause the transaction to fail.
 
 
  Select Execute to run the transaction, Refresh to see updates above.
  The calculated URL
 
  The SQL error message if there is an error or transaction completed message if  successful.
 
  The URL that is sent to the service API when Execute is selected.
 
  This is the PHP script that performs the SQL transaction and returns the message in JSON format.
  //comment the next line if scripts are on same server as converted spreadsheet, un-comment to test from workstation
//header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Cache-Control: no-cache, must-revalidate");
//uncomment next 2 lines if scripts are on same server as converted spreadsheet, comment to test from workstation
$domain = 'yourdomain.com';
$pos = strpos($_SERVER['HTTP_REFERER'], $domain); if (($pos === false) or ($pos == '')) {die("Have a nice day!");}
$pdo = null;
try {
$host = 'localhost';
$db   = 'BIKESHOPdb';
$user = 'USER01name';
$pass = 'USER01password';
$charset = 'UTF8';
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION];
date_default_timezone_set('America/Los_Angeles');
$status = date('m/d/Y h:i:s a', time());
$productnumber1 = $_GET["productnumber1"];
$quantityonhand1 = $_GET["quantityonhand1"];
if (!is_numeric($quantityonhand1) || $quantityonhand1 > 32767 || $quantityonhand1 < 0) {
throw new Exception("Invalid quantity, must be a numeric value between 1 and 32,767.");
}
$productnumber2 = $_GET["productnumber2"];
$quantityonhand2 = $_GET["quantityonhand2"];
if (!is_numeric($quantityonhand2) || $quantityonhand2 > 32767 || $quantityonhand2 < 0) {
throw new Exception("Invalid quantity, must be a numeric value between 1 and 32,767.");
}
$productnumber3 = $_GET["productnumber3"];
$quantityonhand3 = $_GET["quantityonhand3"];
if (!is_numeric($quantityonhand3) || $quantityonhand3 > 32767 || $quantityonhand3 < 0) {
throw new Exception("Invalid quantity, must be a numeric value between 1 and 32,767.");
}
    $pdo = new PDO($dsn, $user, $pass, $options);
    $pdo->beginTransaction();
    $sqlUpdate = "UPDATE Products SET QuantityOnHand = :quantity, Status = :status WHERE ProductNumber = :productnumber";
    $stmtUpdate = $pdo->prepare($sqlUpdate);
    $stmtUpdate->execute([
        'quantity' => $quantityonhand1,
'status' => $status,
        'productnumber' => $productnumber1
    ]);
$sqlUpdate = "UPDATE Products SET QuantityOnHand = :quantity, Status = :status WHERE ProductNumber = :productnumber";
    $stmtUpdate = $pdo->prepare($sqlUpdate);
    $stmtUpdate->execute([
        'quantity' => $quantityonhand2,
'status' => $status,
        'productnumber' => $productnumber2
    ]);
$sqlUpdate = "UPDATE Products SET QuantityOnHand = :quantity, Status = :status WHERE ProductNumber = :productnumber";
    $stmtUpdate = $pdo->prepare($sqlUpdate);
    $stmtUpdate->execute([
        'quantity' => $quantityonhand3,
'status' => $status,
        'productnumber' => $productnumber3
    ]);  

    $pdo->commit();
$result = json_encode("Transaction completed successfully");
    echo $result;

} catch (Exception $e) {
    if ($pdo !== null) {
        $pdo->rollBack();
    }
$error = json_encode("Transaction Failed: " . $e->getMessage());
    echo $error;
}
?>
Multiple KPI Queries
                     
  Selecting "Refresh" from the green drop-down menu executes 4 queries in sequence. Returns a string result which is parsed into 4 cells.
 
The WEBSERVICE() URL   https://customdataservices.net/kpiquery.php  
 
The query result  
 
Total retail value     Units in stock  
 
Average retail price     Distinct products  
         
The PHP script   <?php
//comment the next line if scripts are on same server as converted spreadsheet, un-comment to test from workstation
//header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Cache-Control: no-cache, must-revalidate");
//uncomment next 2 lines if scripts are on same server as converted spreadsheet, comment to test from workstation
$domain = 'yourdomain.com';
$pos = strpos($_SERVER['HTTP_REFERER'], $domain); if (($pos === false) or ($pos == '')) {die("Have a nice day!");}$host = 'localhost';
$db   = 'BIKESHOPdb';
$user = 'USERO1name';
$pass = 'USER01password';
$charset = 'UTF8';
$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,];
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$pdo = new PDO($dsn, $user, $pass, $options);
$sql = "SELECT SUM(RetailPrice * QuantityOnHand) AS TotalRetailValue FROM Products";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$totalretailvalue = $stmt->fetch();
$sql = "SELECT SUM(QuantityOnHand) AS TotalOnHand FROM Products";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$totalininventory  = $stmt->fetch();
$sql = "SELECT COUNT(ProductNumber) AS DistinctProducts FROM Products";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$distinctproducts = $stmt->fetch();
$sql = "SELECT AVG(RetailPrice) AS AveragePrice FROM Products";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$averageretailprice = $stmt->fetch();
$package = array("retailvalue" => $totalretailvalue, "instock" => $totalininventory, "distinct" => $distinctproducts, "average" => $averageretailprice);
//print_r($package);
$retailvalue = $package['retailvalue']['TotalRetailValue'];
$instock = $package['instock']['TotalOnHand'];
$distinct = $package['distinct']['DistinctProducts'];
$average = $package['average']['AveragePrice'];
$wrapper = '["'.'TOTALRETAILVALUE=('.$retailvalue.')UNITSINSTOCK=('.$instock.')DISTINCTPRODUCTS=('.$distinct.')AVERAGERETAILPRICE=('.$average.')"]';
echo $wrapper;
?>
Multi-parameter Query
  This example uses a calculated URL to connect to a PHP script and run a SQL query that returns a value.  
  Changing any of the selectors below re-calculates the URL and triggers the price per carat look-up.   
  Cut Type Clarity Color Weight Price Per Carat   Extended Price      
 
       
The calculated URL with parameters  
   
  This PHP script recieves the 4 parameters, executes the query, and returns a scalar result.  
  <?php
//comment the next line if scripts are on same server as converted spreadsheet, un-comment to test from workstation
//header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Cache-Control: no-cache, must-revalidate");
//uncomment next 2 lines if scripts are on same server as converted spreadsheet, comment to test from workstation
$domain = 'yourdomain.com';
$pos = strpos($_SERVER['HTTP_REFERER'], $domain); if (($pos === false) or ($pos == '')) {die("Have a nice day!");}
$host = '127.0.0.1';
$db   = 'PriceDB';
$user = 'USER01name';
$pass = 'USER01password';
$charset = 'UTF8';
$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,];
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$pdo = new PDO($dsn, $user, $pass, $options);
$series = "Diamonds";
$gemtype = "Diamond";
$cuttype = $_GET["CutType"];
$weight = $_GET["Weight"];
$quality = $_GET["Quality"];
$color = $_GET["Color"];
$sql = "select Price from PriceGrid WHERE Series = :series and GemType = :gemtype and CutType = :cuttype and WeightLower <= :weight and WeightUpper > :weight and Quality = :quality and Color = :color";
$stmt = $pdo->prepare($sql);
$stmt->execute(['series' => $series, 'gemtype' => $gemtype, 'cuttype' => $cuttype, 'weight' => $weight, 'quality' => $quality, 'color' => $color]);
$records = $stmt->fetchAll(PDO::FETCH_ASSOC);
$price = $records[0]['Price'];
echo $price;
?>
 
Aggregate Query
 
 
  This example executes an aggregate query and returns the sorted result. Select "Summarize" above to run.  
   
  Product Name   Total Sales    
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
 
  This PHP script  executes the query and returns the sorted result.  
 
  <?php
//comment the next line if scripts are on same server as converted spreadsheet, un-comment to test from workstation
//header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Cache-Control: no-cache, must-revalidate");
//uncomment next 2 lines if scripts are on same server as converted spreadsheet, comment to test from workstation
$domain = 'yourdomain.com';
$pos = strpos($_SERVER['HTTP_REFERER'], $domain); if (($pos === false) or ($pos == '')) {die("Have a nice day!");}
$host = 'localhost';
$db   = 'BIKESHOPdb';
$user = 'USER01name';
$pass = 'USER01password';
$charset = 'UTF8';
$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,];
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$pdo = new PDO($dsn, $user, $pass, $options);
$stmt = $pdo->prepare("SELECT Products.ProductName, sum(Order_Details.QuotedPrice * Order_Details.QuantityOrdered) AS TotalSales
FROM Products INNER JOIN Order_Details ON (Products.ProductNumber = Order_Details.ProductNumber)
GROUP BY Products.ProductName ORDER BY TotalSales DESC");
$stmt->execute();
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
$json = json_encode($data);
$pdo = null;
echo $json;
?>
 
API Response Parser
Excel's text functions make it relatively easy to parse information returned from SQL or REST API queries.
Selecting a currency from the green menu queries the CoinLore API with an id and parses the result using field names as text delimiters.
  Delimiter Value
  id":"
    symbol":"
    name":"
Parse the data for use in calculations…   nameid":"
  rank":
  price_usd":"
  percent_change_24h":"
Enter a quantity to purchase…   percent_change_1h":"
  percent_change_7d":"
  market_cap_usd":"
Exchange rate from REST API Query Example…   volume24":"
  volume24_native":"
  csupply":"
  price_btc":"
This example merges data from 2 REST APIs.   tsupply":"
Easily adapted to incorporate SQL data.    msupply":"
Formulas using relative and absolute cell references make it possible to set up the first row and drag down to automatically parse. Nice!
LEFT(MID($B$4,FIND(D4,$B$4)+LEN(D4),100),FIND($G$2,MID($B$4,FIND(D4,$B$4)+LEN(D4),100))-1)
LEFT(MID($B$4,FIND(D5,$B$4)+LEN(D5),100),FIND($G$2,MID($B$4,FIND(D5,$B$4)+LEN(D5),100))-1)
LEFT(MID($B$4,FIND(D6,$B$4)+LEN(D6),100),FIND($G$2,MID($B$4,FIND(D6,$B$4)+LEN(D6),100))-1)
LEFT(MID($B$4,FIND(D7,$B$4)+LEN(D7),100),FIND($G$2,MID($B$4,FIND(D7,$B$4)+LEN(D7),100))-1)
 
Mobile API Clients
               
  SpreadSheetConverter clients run on any mobile device with a web browser. 
 
  Scan the QR Codes to test mobile versions of the previous examples .
 
 
 
   
  Currency Exchange Calculator
   
   
   
   
 
 
 
   
  Multi-parameter Price Look-up
   
   
   
   
 
   
SQL Search Form
  This example searches 1,000,000 company records and pages results. Use "%" (multiple characters) or "_" (single character) as wildcards.
 
  Enter Part of a Company Name Sort By Direction Offset  
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
  The script below returns the results above using a whitelist method that, used with prepared statements, helps protect against SQL injection.
  <?php
//comment the next line if scripts are on same server as converted spreadsheet, un-comment to test from workstation
//header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Cache-Control: no-cache, must-revalidate");
//uncomment next 2 lines if scripts are on same server as converted spreadsheet, comment to test from workstation
$domain = 'yourdomain.com';
$pos = strpos($_SERVER['HTTP_REFERER'], $domain); if (($pos === false) or ($pos == '')) {die("Have a nice day!");}
$host = '127.0.0.1';
$db   = 'CUSTOMERSdb';
$user = 'USER01name';
$pass = 'USER01password';
$charset = 'UTF8';
$options = [PDO::ATTR_ERRMODE  => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES   => FALSE];
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$pdo = new PDO($dsn, $user, $pass, $options);
$searchterm = $_GET["searchterm"];
$whitelist = ["Company","Address","City","County","ZIP","Phone1"];
$selection = array_search($_GET['orderby'], $whitelist);
$orderby = $whitelist[$selection];
$direction = $_GET['direction'] == 'DESC' ? 'DESC' : 'ASC';
$limit = 10;
$offset = $_GET["offset"];
$stmt = $pdo->prepare("SELECT Company, Address, City, County, ZIP, Phone1 FROM customers WHERE Company LIKE :searchterm ORDER BY ".$orderby." ".$direction." LIMIT :limit OFFSET :offset");
$stmt->bindValue(':searchterm', "$searchterm%");
$stmt->bindValue(':limit', (int) $limit, PDO::PARAM_INT);
$stmt->bindValue(':offset', (int) $offset, PDO::PARAM_INT);
$stmt->execute();
$data = $stmt->fetchAll();
$json = json_encode($data);
echo $json;
?>
 
E-mail & SMS
    These examples use either the PHPMailer code library or the TextMagic API to send e-mail or SMS messages.    
Recipient E-mail   Recipient Phone Number  
Subject  
Message  
   
   
   
  - Select a method from the green drop-down menu to send and e-mail or SMS message
E-mail Response  
SMS Response  
PHPMailer Script   <?php
//comment the next line if scripts are on same server as converted spreadsheet, un-comment to test from workstation
//header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Cache-Control: no-cache, must-revalidate");
//uncomment next 2 lines if scripts are on same server as converted spreadsheet, comment to test from workstation
$domain = 'yourdomain.com';
$pos = strpos($_SERVER['HTTP_REFERER'], $domain); if (($pos === false) or ($pos == '')) {die("Have a nice day!");}
use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;
require 'PHPMailer/src/Exception.php';
require 'PHPMailer/src/PHPMailer.php';
require 'PHPMailer/src/SMTP.php';
require_once 'PHPMailer/src/PHPMailer.php';
require_once 'PHPMailer/src/SMTP.php';
$status = date("Y-m-d h:i:s a");
$mail = new PHPMailer;
$smtpUsername = "youraccount@yourdomain.com";
$smtpPassword = "youraccountemailpassword";
$emailFrom = "youraccount@yourdomain.com";
$emailFromName = "youraccount";
$emailTo = $_GET["recipient"];
$emailSubject = $_GET["subject"];
$emailMessage = $_GET["message"];
$mail->isSMTP();
//$mail->SMTPDebug = 2;
$mail->Host = 'mail.yourdomain.com';
$mail->Port = 587;
$mail->SMTPSecure = 'tls';
$mail->SMTPAuth = true;
$mail->Username = $smtpUsername;
$mail->Password = $smtpPassword;
$mail->setFrom($emailFrom, $emailFromName);
$mail->addAddress($emailTo);
$mail->Subject = $emailSubject;
$mail->msgHTML($emailMessage.' - '.$status);
$mail->AltBody = 'HTML messaging not supported';
if (!$mail->send()) {
$json = json_encode("Mailer Error: " . $mail->ErrorInfo);
echo $json;
} else {
$json = json_encode("E-mail sent - $status");
echo $json;
}
?>
       
TextMagic Script   <?php
//comment the next line if scripts are on same server as converted spreadsheet, un-comment to test from workstation
//header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Cache-Control: no-cache, must-revalidate");
//uncomment next 2 lines if scripts are on same server as converted spreadsheet, comment to test from workstation
$domain = 'yourdomain.com';
$pos = strpos($_SERVER['HTTP_REFERER'], $domain); if (($pos === false) or ($pos == '')) {die("Have a nice day!");}
$recipient = $_GET["recipient"];
$subject = $_GET["subject"];
$message = $_GET["message"];
date_default_timezone_set('America/Los_Angeles');
$status = date('m-d-Y h:i:s');
$to = "$recipient@textmagic.com";
$from = "youraccount@yourdomain.com";
$headers = "From:" . $from;
mail($to,$subject,$message,$headers);
$json = json_encode("Text sent - $status");
echo $json;
Data Encryption
    This example uses the libsodium library to encrypt and decrypt data using the AES-256-GCM algorithm.
Change "the text to encrypt" or "un-encrypted reference text" to trigger the encryption/decryption scripts.
 
The text to encrypt    
Replace CHAR(10) with ---    
   
Un-encrypted reference text
The encrytion array    
Encrypted text from array    
Encryption key from array    
Encryption nonce from array    
The decrypted text    
                   
Security Methods
   
  This is a collection of different scripts that show ways to improve PHP interaction security and protect against SQL injection

<?php
//This section allows or disallows remote access to the script based on the calling web browser's CORS settings
//Comment the next line if scripts are on same server as converted spreadsheet, un-comment to allow remote access
//header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Cache-Control: no-cache, must-revalidate");
//The next section will exit the script immediately if called from outside the current domain, limiting remote access
//Uncomment next 2 lines if scripts are on same server as converted spreadsheet, comment to test from remote location
$domain = 'yourdomain.com';
$pos = strpos($_SERVER['HTTP_REFERER'], $domain); if (($pos === false) or ($pos == '')) {die("Have a nice day!");}
?>

The section above is included in every file in previous demonstrations but typically this code would go into a separate
file (settings.php for example) in a directory above your server's public html directory to improve script protection and
reduce future maintenance, using the syntax below, which means go up two directory levels from the current directory,
include the code found in the settings.php file. Good place to store database connection and authentication settings.
<?php
require '../../settings.php';
?>


<?php
//This script shows a few different methods that help to reduce the risk of SQL injection or other query tampering
header("Content-Type: application/json; charset=UTF-8");
header("Cache-Control: no-cache, must-revalidate");
$domain = 'yourdomain.com';
$pos = strpos($_SERVER['HTTP_REFERER'], $domain); if (($pos === false) or ($pos == '')) {die("Have a nice day!");}
$host = '127.0.0.1';
$db   = 'CUSTOMERSdb';
$user = 'CUSTOMERSdbUSER01';
$pass = 'CUSTOMERSdbUSER01password';
$charset = 'UTF8';
$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES   => FALSE];
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$pdo = new PDO($dsn, $user, $pass, $options);
//The next line will truncate string input up to where it finds # or & characters, remove all \ characters, and remove leading or trailing spaces
$searchterm = htmlspecialchars(stripslashes(trim($_GET["searchterm"])));
//The next 3 lines define the allowed sort parameters
$whitelist = ["Company","Address","City","County","ZIP","Phone1"];
$selection = array_search($_GET['orderby'], $whitelist);
$orderby = $whitelist[$selection];
//The next line uses the ternary operator to set the variable $direction. If the URL parameter for 'direction' is not 'DESC', it defaults to 'ASC'.
$direction = $_GET['direction'] == 'DESC' ? 'DESC' : 'ASC';
//The next line hardcodes the maximum number of rows returned
$limit = 10;
//The next line sets the starting record in the found set, making it possible to page through records
$offset = $_GET["offset"];
//The next 5 lines execute the query using a PDO prepared statement and typed variables
$stmt = $pdo->prepare("SELECT Company, Address, City, County, ZIP, Phone1 FROM customers WHERE Company LIKE :searchterm ORDER BY ".$orderby." ".$direction." LIMIT :limit OFFSET :offset");
$stmt->bindValue(':searchterm', "$searchterm%");
$stmt->bindValue(':limit', (int) $limit, PDO::PARAM_INT);
$stmt->bindValue(':offset', (int) $offset, PDO::PARAM_INT);
$stmt->execute();
$data = $stmt->fetchAll();
$json = json_encode($data);
echo $json;
?>


<?php
//The next 4 scripts work together to encrypt and decrypt data and also show a way to increase script protection
//This script sends input variables to the encryption script stored in a password-protected directory on the server
header("Access-Control-Allow-Origin: *");
header("Content-Type: text/html; charset=UTF-8");
header("Cache-Control: no-cache, must-revalidate");
$message = $_GET["message"];
$reference = $_GET["reference"];
$url = 'https://yourdomain.com/yoursecurefolder/aes256gcmEncryptor.php';
$username = 'yoursecurefolderusername';
$password = 'yoursecurefolderusernamepassword';
$headers = array(
    'Content-Type: multipart/form-data',
    'Authorization: Basic '. base64_encode("$username:$password")
);
$postRequest = array(
    'message' => $message,
    'reference' => $reference
);
$ch = curl_init($url);
curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_POSTFIELDS, $postRequest);
$response = curl_exec($ch);
if(curl_errno($ch)){
    throw new Exception(curl_error($ch));
}
echo $response;
curl_close($ch);
?>

<?php
//this is the aes256gcmEncryptor.php, called from the script above, which is stored in yoursecurefolder
header("Content-Type: text/html; charset=UTF-8");
header("Cache-Control: no-cache, must-revalidate");
if (! sodium_crypto_aead_aes256gcm_is_available()) {throw new \Exception("AES-GCM is not supported on this platform");}
$message = $_POST["message"];
$reference = $_POST["reference"];
$encryptionkey = random_bytes(SODIUM_CRYPTO_AEAD_AES256GCM_KEYBYTES);
$nonce = random_bytes(SODIUM_CRYPTO_AEAD_AES256GCM_NPUBBYTES);
$encryptedtext = sodium_crypto_aead_aes256gcm_encrypt($message,$reference,$nonce,$encryptionkey);
echo "<br>EncryptedText<br>".sodium_bin2hex($encryptedtext)."<br>";
echo "<br>EncryptionKey<br>".sodium_bin2hex($encryptionkey)."<br>";
echo "<br>Nonce<br>".sodium_bin2hex($nonce)."<br>";
echo "<br>Reference<br>".$reference."<br>";
?>



<?php
//This script sends input variables to the decryption script stored in a password-protected directory on the server
header("Access-Control-Allow-Origin: *");
header("Content-Type: text/html; charset=UTF-8");
header("Cache-Control: no-cache, must-revalidate");
$encryptedtext = $_GET["encryptedtext"];
$encryptionkey = $_GET["encryptionkey"];
$nonce = $_GET["nonce"];
$reference = $_GET["reference"];
$url = 'https://yourdomain.com/yoursecurefolder/aes256gcmDecryptor.php';
$username = 'yoursecurefolderusername';
$password = 'yoursecurefolderusernamepassword';
$headers = array(
    'Content-Type: multipart/form-data',
    'Authorization: Basic '. base64_encode("$username:$password")
);
$postRequest = array(
    'encryptedtext' => $encryptedtext,
'encryptionkey' => $encryptionkey,
'nonce' => $nonce,
    'reference' => $reference
);
$ch = curl_init($url);
curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_POSTFIELDS, $postRequest);
$response = curl_exec($ch);
if(curl_errno($ch)){
    throw new Exception(curl_error($ch));
}
echo $response;
curl_close($ch);
?>
<?php
//this is aes256gcmDecryptor.php, called from the script above, which is also stored in yoursecurefolder
header("Content-Type: text/html; charset=UTF-8");
header("Cache-Control: no-cache, must-revalidate");
$reference = $_POST["reference"];
$encryptedtext = sodium_hex2bin($_POST["encryptedtext"]);
$nonce = sodium_hex2bin($_POST["nonce"]);
$encryptionkey = sodium_hex2bin($_POST["encryptionkey"]);
$decrypted = sodium_crypto_aead_aes256gcm_decrypt($encryptedtext,$reference,$nonce,$encryptionkey);
echo $decrypted;
?>
   
Summary
  Microsoft Excel spreadsheets can use the WEBSERVICE() function to send calculated parameters to PHP scripts that utilize PDO (PHP Data Objects) to execute SQL queries or cURL to execute REST API queries based on the parameters and then format and return the results back to the spreadsheet cells or calculations.

Excel spreadsheets can execute queries from the desktop application or can optionally be converted to HTML with SpreadSheetConverter, resulting in calculating web forms (like this document) that integrate Excel's calculation engine, formulas, and native functions with PHP's connection and data interaction methods.

Using the PDO database access layer drastically simplifies SQL database communications, making it much easier to design and build securable web applications that can include simple or very complex calculations, custom math or business logic, with unlimited real-time SQL database or REST API connections.

Whether creating new applications or enhancing legacy systems, dynamic SpreadSheetConverter forms can provide an extremely cost-effective and adaptive programmable canvas, running on desktops or servers.

"A relatively simple way to integrate the world's most popular business application with the world's most popular database language and the world's most popular server programming language."