Example 1a: Purchase orders - load a list of clients into a Gravity Forms dropdown from a GSheet

Last Updated on June 12, 2021 by Jan Bachelor

Table of Contents

Real life scenario
Requirements
How to go about it
Errors you may encouter upon the code’s execution
Further explanation of the script
Scheduling your script
Dynamically populate the fields upon page refresh
Sources

Real life scenario

The objective is to dynamically populate a list of customers based the selected subsidiary

A finance team administers a list of clients that are available in their finance software – one sheet for each country named ‘CZ’, ‘DE’ and ‘US’ clients. They want to enable employees to request purchase orders. The finance team wants to receive each filled in form via email. The list of clients gets updated on a daily basis.

Note: In example no.2, we will expand it to an approval process where the name of approvers are in the GSheets. So let’s start with the simpler example and then expand it further.

Requirements

Part 1 – all steps completed
Gravity Forms plugin

How to go about it

Create a GSheet with the required structure – 3 sheets called ‘CZ’, ‘DE’ and ‘US’ with a header and the list of clients underneath it.

Client list – what we will need to load into Gravity Forms

Share the GSheet with the service account that was created for GSheet API account – viewer access is sufficient.

Share the GSheet with the service account that is enabled for GSheet API access

Create a Gravity form – note down the form ID. Then create a dropdown field and note down its ID. I assume you already know some basics on how to create a Gravity form in WordPress. If not, check this official Gravity tutorial.

Note down the drop down field. Items in this drop-down will be over-written by what we load from GSheets (only upon successful connection).

Create a code snippet in WordPress with the required code – modify it to fit your needs, esp. in relation to the form ID and field ID, which will vary.

Create a new snippet in WordPress within the ‘Code Snippet’ plugin

Paste the following code and remember to modify the following variables:
- form_id
- gsheet_id
- dropdown_field_id
- ranges – specify what range should be fetched (names of sheets + columns)
- PLUS if your path to Composer data and the service key are different, remember to also modify it.

/**
  * Load clients into Gravity Form
*/

function load_clients_from_gsheets_function() {

// The ID of your form in Gravity
$form_id = '1';  

//your GSheet ID
$gsheet_id = 'FILL_IN_GSHEET_ID';
	
// The field that contains clients in the Gravity form
$dropdown_field_id = '2';

// Define which sheets + which columns to retrieve data from - skipping the first row (headings)
$ranges = [ 
		'CZ!A2:A1000', 
		'DE!A2:A1000', 
		'US!A2:A1000',
];

// Set a placeholder in Gravity (if desired)
$placeholder = 'Select a Client';

// Load the form via Gravity Form API
$form = GFAPI::get_form( $form_id );
	
// Connect the Google Sheets API client - if running this from CodeSnippets plugin, then the relative path is under ../wp-contents/plugins/code_snippets/php'
require_once '/usr/share/php/composer/vendor/autoload.php';

// Provide path to you r service account access key
$googleAccountKeyFilePath = '/usr/share/php/composer/service_key.json';
putenv('GOOGLE_APPLICATION_CREDENTIALS=' . $googleAccountKeyFilePath);

// Create new Google client connection for API access
$client = new Google_Client();

// Set credentials
$client->useApplicationDefaultCredentials();

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

// Construct the service object.
$service = new Google_Service_Sheets($client);
	
// Save the ranges into an array + specify that we are after columns (rather than rows)
$params = array(
	'ranges' => $ranges,
	'majorDimension' => 'COLUMNS'
);
	
// Call the function to retrieve (batchGet) the ranges
$result = $service->spreadsheets_values->batchGet($gsheet_id, $params);

// 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) {
			// ...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 and if not, save it into an array called 'all_jobs'
						if (!empty($indiv_value)) {
							$all_jobs[] = $indiv_value;
							//$all_jobs[] = array('text' => $indiv_value, 'value' => $indiv_value);
							//printf('%d was saved.',$indiv_value);
						}
					}		
				}
  		    }	
            // Remove duplicates from the list of all jobs
			$filtered_jobs = array_unique($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 a place holder
            $field->$placeholder;
            //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>';
        }
		
    }
  
$result = GFAPI::update_form( $form );
return $result;
}

// Call the function defined above
load_customers_from_gsheets_function();

Errors you may encouter upon the code’s execution

To execute the script in Code Snippets, click on the ‘Save and Execute Once’ button. Be prepared that several trials and errors will be needed. A couple of examples can be found below:

Inccorect sheet name specified. Uncaught Google Service Exception – Unable to parse range XYZ:

Incorrect sheet name specified – instead of just ‘CZ’ I wrote ‘CZ Customers’

Path to the service account .JSON file is not reachable (e.g. due to permissions for the web server user or incorrect path) – Uncaught DomainException: Unable to read the credential file specified by GOOGLE APPLICATION CREDENTIALS.

The service account credentials file is inacessible to the script called from Code Snippets

Path to the .JSON file is reachable but the file is not readable – likely due to rows being splitted up, esp. row no.4 (private_key) – all needs to be on one row. Uncaught DomainException: Could not load the default credentials.

The .JSON file is not readable, likely due to additional spaces (esp. row no.4 gets splitted up if copy pasting the text from Windows).

Composer files (vendor folder) is not reachable by the script executed within the Code Snippet’s plugin. require_once( – /vendor/autoload.php): failed to open stream: No such file or directory:

The vendor autoload.php file is not reachable to the script called from Code Snippets

Further explanation of the script

With Google Sheets API, we need to specify whether we are after columns or rows – see GSheets API documentation that shows an example for each case and how does the output differ.
Since the raw output of the data from GSheets wraps the data we actually need in a three-dimensional array of arrays, we have to loop through them in three layers before we can get to the data we care about. The best way to see it is to uncomment line 59 (echo '<pre>'; print_r($values); echo '</pre>';) which will print out the output as soon as the data is filltered out to just the values. I recommend you to play with it and uncomment these echo() and printf() functions throughout the process to understand how do the data look like at the time of being processed.
In case duplicates are found, the php function called array_unique() will take care of it.
Because we are getting data from three different sheets, even if within each sheet, the list of customers is sorted alphabetically using the sort() function, once we fetch values from all three, we need to re-sort it again.

Scheduling your script

The last bit for our code to be complete is to have it scheduled regularly. In this scenario, the finance team updates the list of clients daily. So just in case, let us update it every 12 hours by scheduling a cron job from within WordPress that will do it for us.

Firstly, remove the last bit of code to not trigger the function on every action:

Then add this code before the beginning of your function:

// Call this code to create a cron job - once executed once, comment out
add_action( 'init', function () {
	$hook = 'load_clients_from_gsheets';
	$args = array();

	if ( ! wp_next_scheduled( $hook, $args ) ) {
		wp_schedule_event( time(), 'fifteen_minutes', $hook, $args );
	}
} );

add_action( 'load_customers_from_gsheets', 'load_customers_from_gsheets_function' );

Run the above code once and then check that a cron job was created. In the left panel in WordPress’ dashboard, go to ‘Settings’ -> ‘Cron Schedules’.

Go to Cron Schedules to check the newly created job

Check if the scheduled job was added to the list inc. the ‘Action’.

Dynamically populate the fields upon page refresh

In case you need the fields to be populated upon a page refresh (e.g. every time someone loads the page), then disable the scheduled job and use pre-hooks.

A word of caution: With more traffic on your site (roughly 10 refreshes per minute), you will end up utilizing all your allowance for API usage. This would result in the API call being rejected, causing an error message and a pause of about 120 seconds before another API call is allowed. Consequently, I strongly suggest to set up periodic (e.g. every minute, if you need it often).

To enable dynamic pull of data from GSheets API to your form field, move the definition of the form ID to the top and add the following pre-hooks before the function starts:

// The ID of your form in Gravity
$form_id = '18';  

// Prehooks
add_filter( 'gform_pre_render_'.$form_id, 'load_clients_from_gsheets_function' );
add_filter( 'gform_pre_validation_'.$form_id, 'load_clients_from_gsheets_function' );
add_filter( 'gform_pre_submission_filter_'.$form_id, 'load_clients_from_gsheets_function' );
add_filter( 'gform_admin_pre_render_'.$form_id, 'load_clients_from_gsheets_function' );

function load_clients_from_gsheets_function() {

   //your GSheet ID
   $gsheet_id = 'FILL_IN_GSHEET_ID';

  // The rest of the script

}

The result is that whenever the particular form page is refreshed, the snippet will kick in.

Note: If you use GravityFlow, it would apply to any page load during that, too, which may not be desirable. To counter that, you can check from which URL is the user accessing the form and if gravityflow is in the URL path, then simply do not execute the rest of the script. For example:

function load_clients_from_gsheets_function() {

  $url = "$_SERVER[HTTP_HOST]$_SERVER[REQUEST_URI]";

   // Execute the code only if this page load is NOT part of an existing gravityflow page (e.g. during approvals, etc.) to save loading time / API usage resources.
  if (substr_count($url, 'gravityflow') < 1) {
      // Your GSheet ID
      $gsheet_id = 'FILL_IN_GSHEET_ID';

     // Rest of your code

  }
}

Example 1a: Purchase orders – load a list of clients into a Gravity Forms dropdown from a GSheet