Utilize Composer to talk to Google Sheets API

1. Note down the spreadsheet ID of the document In GSheets that you want to pull data from we will need to know two values when using Composer to query Google Sheets API. For starters, I recommend using a simple two-column GSheet.

2. Copy paste your service account key into the Composer’s directory. Open the JSON file on your computer and copy its contents. Then via SSH, paste it.

# Go into the directory with Composer (as per previous step)
cd /tmp/composer

# Create a file into which you can paste the contents of the JSON file
nano service_key.json

# Press SHIFT + Insert (or CTRL+V / Command+V) to paste the text, followed by CTRL+X (Command+X) and confirm that you want to save the doc by pressing 'Y' and enter.

3. Let us now create a simple a .php document that test that we can connect and pull the data.

# Go into the directory with Composer (as per previous step)
cd /tmp/composer
nano gsheet.php

# As before Press SHIFT + Insert (or CTRL+V / Command+V) to paste the text
<?php
// Your spreadsheet ID
$spreadsheetID = 'ENTER_THE_GSHEET_ID_HERE';
$range = 'NAME_OF_THE_INDIVIDUAL_SHEET';

// Connect the Google Sheets API client
require_once __DIR__ . '/vendor/autoload.php';

// Our service account access key
$googleAccountKeyFilePath = __DIR__ . '/service_key.json';
putenv('GOOGLE_APPLICATION_CREDENTIALS=' . $googleAccountKeyFilePath);

// Create new client
$client = new Google_Client();

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

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

$service = new Google_Service_Sheets($client);

// Get the contents of an entire sheet by its name
$response = $service->spreadsheets_values->get($spreadsheetID, $range);

// output the result
var_dump($response);
?>

# To save + exit, press CTRL+X (Command+X) and confirm that you want to save the doc by pressing 'Y' and enter.

4. If we were to launch the code now, it would fail because the service account does not have access to the file. You will need to open the spreadsheet and share it with the email address that you created in the first step.

Note for organizations: if you get an error message that you cannot share the doc outside of your organization, you will need to have your GSuite admin move your GSuite account into an OU that is allowed to share externally.

5. Execute the code!

php gsheet.php

If you receive error messages related to ‘Could not load default credentials’, then there is formatting issue with the JSON file. Make sure that there are no extra spaces on the line that starts with ‘—–BEGIN PRIVATE KEY—–‘ and the ‘— END PRIVATE KEY’. See below:

6. Check the output. I have highlighted three things that are of particular interest for us:
   • string(4) “ROWS” –> indicates that the sheet is being read as rows – one array per row. In the next step, we will look into how to communicate that we want to just load one column.
   • string(15) “Sheet1!A1:Z1000” –> How the range that we specified was interpreted by GSheet API. In the fuiture step, we will look into specifying a smaller scope to only fetch data that we need. You may wish to consider the structure of your GSheets in relation to the range that you will then need to fetch.
   • [“values”] –> values lead us to an array of arrays – the values that we want to fetch into our Gravity form (or another destination) are coated in another array that we need to go through to extract the actual values we want to get out.

jan@entelechia:/tmp/composer$ php gsheet.php
object(Google_Service_Sheets_ValueRange)#73 (7) {
  ["collection_key":protected]=>
  string(6) "values"
  ["majorDimension"]=>
  string(4) "ROWS"
  ["range"]=>
  string(15) "Sheet1!A1:Z1000"
  ["values"]=>
  array(3) {
    [0]=>
    array(1) {
      [0]=>
      string(5) "Steve"
    }
    [1]=>
    array(1) {
      [0]=>
      string(6) "George"
    }
    [2]=>
    array(1) {
      [0]=>
      string(5) "Lucia"
    }
  }
  ["internal_gapi_mappings":protected]=>
  array(0) {
  }
  ["modelData":protected]=>
  array(0) {
  }
  ["processed":protected]=>
  array(0) {
  }
}

So how do we fetch a specific column or multiple sheets? And how do we get the values into Gravity Forms? Follow to the next step to see 🙂

Sources:

• Pocket Admin’s blog
• Artisan’s web blog

Go back to course overview: Dynamically Populate Gravity Forms from Google Sheets (GSheets APIv4) > Part 1 - Get GSheets API working with your WordPress instance