Last Updated on October 9, 2021 by Jan Bachelor

What is the goal?

• A user selects a client (customer) from a list of drop-down list that we dynamically populated from GSheets previously in step 1a.
• Another drop-down list called ‘Project’ is dynamically populated based on what client was selected in the previous drop-down.

How are we going to achieve it?

1. We will need to create a drop-down field for projects but we do not need to populate it with any meaningful data – just write down the ID of the field.

2. We need to add a ‘page break’ feature so that we can fetch what was selected in the first step to send a request via GSheets API to fetch more details and dynamically populate a drop-down field. This will cause a bit of a delay when loading the webpage, but should be tolerable for the users.

3. We will need to expand our snippet to [a] identify what client was selected in the first step & [b] to fetch more sheets / columns related to projects and sort them out in the snippet to only display the relevant ones for the client.
   • Note the ‘rgpost()’ function that captures what was selected in field no.250, which is the ID of the client drop-down list.
   • We then fetch data from a GSheet document that has three sheets called (Sheet1-3), namely columns A and B (first 1000 rows).
   • We loop through all of them until we find one that matches the client we are looking for.
   • One the match is found, the next() function is called to save the next item from the array, which is the name of the project (as the array contains data from column A and then B).
   • The resulting array is then sorted alphabetically and saved in the drop-down field for projects.

/**
  * Load projects based on customer selection in the PO request form 
*/

// The ID of your form in Gravity
$form_id = '18';  
 
add_filter( 'gform_pre_render_'.$form_id, 'load_projects_from_customers_PO' );
add_filter( 'gform_pre_validation_'.$form_id, 'load_projects_from_customers_PO' );
add_filter( 'gform_pre_submission_filter_'.$form_id, 'load_projects_from_customers_PO' );
add_filter( 'gform_admin_pre_render_'.$form_id, 'load_projects_from_customers_PO' );

function load_projects_from_customers_PO( $form ) {
	
	// Enable report reporting
	ini_set('display_startup_errors', 1);
	ini_set('display_errors', 1);
	error_reporting(-1);
	
	// Get value from $_POST as the new page loads in the browser
	$selected_project = rgpost( 'input_250', true );
	
	//echo ('An the winner is...');
	//echo ($selected_project);
	
	// If a customer was selected, then proceed with loading the corresponding project names
	if (!empty($selected_project)){
		
	// Connect the Google Sheets API client. Ensure the folder is included in your /etc/php.ini file under the open_basedir='' directive.
	require_once '/usr/share/php/vendor/autoload.php';

	// Our service account access key. Ensure the folder is included in your /etc/php.ini file under the open_basedir='' directive.
	$googleAccountKeyFilePath = '/usr/share/php/service_key.json';
	putenv('GOOGLE_APPLICATION_CREDENTIALS=' . $googleAccountKeyFilePath);

		// Create new client
		$client = new Google_Client();
		// Set credentials
		$client->useApplicationDefaultCredentials();

		// Adding access area for reading, editing, creating and deleting tables - add read.only
		$client->addScope('https://www.googleapis.com/auth/spreadsheets');

		$service = new Google_Service_Sheets($client);
	
		//your g sheet ID
    	$spreadsheetId = 'GSHEET-ID';
	
		//the select field id in your gravity form where you want the names to load
    	$dropdown_field_ID = '253';
		
		// which column to scan - what is the heading name
    	$placeholder = 'Select a Project';
	
		// Go through each list of active (ACT) jobs for each subsidiary
		$ranges = [ 
			'Sheet1!A2:B1000', 
			'Sheet2!A2:B1000', 
			'Sheet3!A2:B1000',
		];
	
		// Save the ranges into an array + specify that we are after rows
		$params = array(
    		'ranges' => $ranges,
			'majorDimension' => 'ROWS'
		);
	
		// Call the function to retrieve (batchGet) the ranges
		$result = $service->spreadsheets_values->batchGet($spreadsheetId, $params);
	
		// Count the number of ranges retrieved (can be compared with how many ranges were supplied)
		//printf("%d ranges retrieved.", count($result->getValueRanges()));
		//echo '<pre>'; print_r($result); echo '</pre>';

		// strip the JSON output of other stuff apart from the multi-dimensional array called 'Values'
		$values = $result->getValueRanges();
		//echo '<pre>'; print_r($values); echo '</pre>';
		
		//Go through all the fields in the form
    	foreach ( $form['fields'] as $field ) { 	
			//Check if field type is a select dropdown and if its ID is correct, then...
         	if ( $field->type == 'select' && $field->id == $dropdown_field_ID) {
			//echo 'looping through each field..';
			// ...go through the multi-dimensional array to fileter out duplicates and empty values
				foreach($values as $value){
					foreach($value as $minivalue){
						foreach($minivalue as $indiv_value){
							//print($indiv_value);
							//Verify that the cell is not empty, then
							if (!empty($indiv_value)) {
								// If it matches the previously selected project, then
								if ($indiv_value == $selected_project) {								
									// Add to the array the next cell from the row (the project name)
									$all_jobs[] = next($minivalue);
									//echo($minivalue[1]." was added.");	
								}								
							}
						}		
					}
  			    }
				
				// Process the filtered out jobs if any were loaded.
				if (!empty($all_jobs)) {
					$filtered_jobs = $all_jobs;
			
					//Sort the filtered jobs alphabetically regardless of case (upper or lower)
					sort($filtered_jobs, SORT_NATURAL | SORT_FLAG_CASE);

					//Create a multi-dimensional array compatible with the drop-down menu in Gravity
					foreach($filtered_jobs as $filtered_job){
						//printf($filtered_job);
						$jobs_to_display[] = array('text' => $filtered_job, 'value' => $filtered_job);
					}
			 
				}

            	             //Add the new names to the form choices
           		     $field->choices = $jobs_to_display;
            	             // Print out the contents of the array (troubleshooting only)
           		    //echo '<pre>'; print_r($choices_LLC); echo '</pre>';
			}
		}

	}
	
	return $form; //return data to the form
}

Sources

• MightyMiNow
• Gravity’s official docs
• Whiteleydesigns.com
• GFAPI official documentation – examples

Back to: Dynamically Populate Gravity Forms from Google Sheets (GSheets APIv4) > Part 2 - Examples for loading GSheets into Gravity Forms