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   
         
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"}]]  
             
           
           
           
           
           
           
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)  
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.
 
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  
         
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  
   
Aggregate Query
 
  This example executes an aggregate query and returns the sorted result. Select "Summarize" above to run.
 
  Product Name   Total Sales  
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
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  
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
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  
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    
                   
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."