Airtable to Vega Lite Graph

airtable_to_vega.php

function replaceSpace($string){
	return str_replace(' ','+',$string);
}

// Table and data variables
$baseid = '###';			//Required
$apikey = '###';			//Required
$tablename = '###';				//Required
$view = '';				//Optional
$lookupfieldname = '';		//Optional 1a
$lookupfieldvalue = filter_var($_GET[''],FILTER_SANITIZE_STRING);	//Optional 1b
$sortfield = '';			//Optional 2a
$sortdirection = '';				//Optional 2b
$maxrecords = '';						//Optional

// Graph variables
$xfield = "###";			//Required
$yfield = "###";				//Required
$linkfield = "";		//Optional
$fieldsneededarray = [					//Optional
	""
];

// Modifies the users table name input to be used in a URL
$encodedtablename = str_replace(' ','%20',$tablename);

// All the extra variables in the URL
// See https://codepen.io/airtable/pen/rLKkYB for Encoder
$filtervar = '&filterByFormula=%7B'.replaceSpace($lookupfieldname).'%7D%3D%22'.replaceSpace($lookupfieldvalue).'%22';
$filterview = '&view='.replaceSpace($view);
$filtersort = '&sort%5B0%5D%5Bfield%5D='.replaceSpace($sortfield).'&sort%5B0%5D%5Bdirection%5D='.$sortdirection;
$filtermaxrecords = '&maxRecords='.$maxrecords;
foreach ($fieldsneededarray as $field){
	$filterfield .= "&fields%5B%5D=".replaceSpace($field);
}

// Logic to decide if needs to add variables
$filterurl = '';
if(!empty($lookupfieldname)){$filterurl.=$filtervar;}
if(!empty($view)){$filterurl.=$filterview;}
if(!empty($sortfield)){$filterurl.=$filtersort;}
if(!empty($maxrecords)){$filterurl.=$filtermaxrecords;}
if(!empty($fieldsneededarray)){$filterurl.=$filterfield;}

//Get results from AT
$starturl = 'https://api.airtable.com/v0/'.$baseid.'/'.$encodedtablename.'?api_key='.$apikey;
$ch = curl_init($starturl.$filterurl);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_HTTPHEADER, array(
    'Content-Type: application/json'
));
$result = curl_exec($ch);
$decoderesult = json_decode($result,true)['records'];

//The initial data has a "fields" array that needs to be extracted
//Create an array to be used for the Graph data in the correct format
$newfullarray = array();
foreach($decoderesult as $eachresult){
	$fields = $eachresult['fields'];
	$newfieldsarray = array();
	foreach ($fields as $fieldname => $fieldvalue){
		// This section changes the date from AT to something more readable
		if ($fieldname == "Submission Date"){
			$fieldvalue = str_replace("T"," - ",$fieldvalue);
			$fieldvalue = str_replace(".000Z","",$fieldvalue);
		}
		// This section converts decimal to percentage
		if ($fieldname == "Percentage Off"){
			$fieldvalue = $fieldvalue * 100;
		}
		$newfieldsarray[$fieldname] = $fieldvalue;
	}
	array_push($newfullarray,$newfieldsarray);
}
// The javascript array is now ready
$reencode = json_encode($newfullarray);

?>
<script type='text/javascript' src='https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js'></script>
<script src="https://trilogylab.com/wp-content/themes/aspire-pro/js/airtable.js"></script>
<script src="https://cdn.jsdelivr.net/npm/[email protected]"></script>
<script src="https://cdn.jsdelivr.net/npm/[email protected]"></script>
<script src="https://cdn.jsdelivr.net/npm/[email protected]"></script>

<script>
	jQuery(document).ready(function($) {
		var yourVlSpec = {
        $schema: 'https://vega.github.io/schema/vega-lite/v5.json',
        description: 'A simple bar chart with embedded data.',
        data: {
          values: <? echo $reencode; ?>
        },
        layer: [
          { // dictates the gray line
			  mark: {"type":'line'},
				encoding: {
					x: {field: "<? echo $xfield; ?>", type: 'nominal',title:'Entered Date'},
					y: {field: "<? echo $yfield; ?>", type: 'quantitative'},
					"color": {"value":"gray"},
				}
          },
          { //dictates the dots and their color based on condition
			  mark: {
				  "type":'point',
				  size:15,
				  strokeWidth:5
			  },
				encoding: {
					x: {field: "<? echo $xfield; ?>", type: 'nominal',title:'Entered Date'},
					y: {field: "<? echo $yfield; ?>", type: 'quantitative'},
					"color": {
						// https://vega.github.io/vega-lite/docs/condition.html
						"condition": { 
							"test": "datum['Passed?'] == 'was outside of' ",
							"value": "red"
						},
						"value":"green"
					},
					//https://vega.github.io/vega-lite/docs/tooltip.html
					  "tooltip":{
						  "field":"Submission Date",
						  "type":"nominal"
					  } ,
					<? if(!empty($linkfield)){ ?>
					// https://vega.github.io/vega-lite/docs/encoding.html#href
					"href":{"field":"<? echo $linkfield; ?>","type":"nominal"}
					<?}?>

				}
		  },
          { //dictates the red "boundary" line of acceptance
            "data": {"values": [{"guide": 0}]},
            "mark": "rule",
            "encoding": {
              "y": {"field": "guide","type": "quantitative"},
              "color": {"value": "red"}
            }
          }
          
          
        ]
      };		vegaEmbed('#graph', yourVlSpec);

		
	});
</script>
<style>
	body .vega-embed .chart-wrapper{height: auto;}
</style>
<div id="graph"></div>