Excel Dynamic Forms
  This Microsoft Excel spreadsheet was converted to a dynamic web form 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 a web form.
Spreadsheets or web forms can both dynamically interact with unlimited data sources.

   -  model process solutions with Excel 365, utilize 270 Excel functions in calculations
   -  use the WEBSERVICE() function to send query parameters to PHP, PDO, SQL, cURL scripts
   -  the data returned from SQL or REST API queries can be parsed to trigger calculation updates
   -  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

Simple, reliable low-code methods for building secure 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 PHP array is parsed into spreadsheet cells.
=WEBSERVICE("https://customdataservices.net/xlpdq/bicycleshopcustomerlookup.php?token="&NOW()&"&customerNumber="&CUSTOMERID)
   
 
       
         
         
         
         
         
         
  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.
  This calculated URL queries the API for an initial base-rated JSON array that is parsed for a specific currency. 
 
 
  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     
   
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  Edit to change Quantity On Hand values, Execute to run the transaction, Refresh to view updated values 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  
 
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)
 
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.
E-mail  
Phone  
Subject  
Message  
   
   
   
  - Select a method from the green drop-down menu to send an 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.
Select "Encrypt" from the left drop-down menu to encrypt, "Decrypt" from the right menu to decrypt.
 
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    
                   
Barcodes & QR codes
   
Code 128 Barcode    
Barcode Input    
     
QR code    
QR code Input    
     
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 straight-forward way to make the world’s most popular business application work with the world’s most popular data interaction languages and server programming technologies."