Moving data from MySQL to Google Sheets with PHP

I recently needed to pull data from a database and add it to a google spreadsheet. Google provides an API for working with Sheets, but like many of their APIs, the documentation isn’t great. I’ve got my program working and figured I’d document my steps for future me and anyone else who needs it.

Step 1 – Permissions

The first step is to setup your app in the google developer console. Create a project and enable the Google Sheets API for that project. Under the API Manager, select Credentials. This is where you need to setup a way to authenticate your application when it uses the API.

Other ways to authenticate

For an application that runs in the background without a user interface, the best approach is to use the “service account key”. Create a service account key. Give the account a name and pick a role (I don’t think it matters). Select JSON as the key type. The “Service account ID” that looks like an email address can be used to give this fake account access to a private spreadsheet. Just share the sheet with this email address. If the spreadsheet is public or shared by link, you wont need this address.

Hit done and you will be prompted to download a JSON encoded private key. We will use this key to authenticate all our API calls.

Step 2 – PHP Library

Next we need to get the Sheet API PHP client library. There are good instructions for doing that at the github. Basically, you can either use Composer or download the whole API library.

Then you add the line

require_once '/path/to/your-project/vendor/autoload.php';
-OR-
require_once '/path/to/google-api-php-client/vendor/autoload.php';

Copy the JSON encoded private key to the directory containing the PHP file. Keep this file secure as it identifies your app and anyone with that file can act as your application. Google also can’t replace it for you, so don’t lose it.

To use the PHP library, you have to setup the environment and then create a Google_Client:

putenv('GOOGLE_APPLICATION_CREDENTIALS=<Your Service Account Key File>.json');
define('SCOPES', implode(' ', array(Google_Service_Sheets::SPREADSHEETS)));

$client = new Google_Client();
$client->useApplicationDefaultCredentials();
$client->setScopes(SCOPES);

$service = new Google_Service_Sheets($client);

The first line tells the client where to find the JSON file with your application’s private key and the second line tells the client which APIs you are going to use. I just needed the spreadsheet API for this application. (Remember that you have to add each API you need to the APIs on the developer console)

Step 3 – Read data from the database and write to a sheet

Use MySQL to load some data:

$dbLink = mysqli_connect("localhost", "<dbUsername>", "<dbPassword>", "<databaseName>");
if ( !$dbLink )
  die("Couldn't connect to database");

$stmt = $dbLink->prepare("SELECT Name, Address FROM People WHERE City = ?");
$stmt->bind_param("s", <Some variable>);
$stmt->execute();

$data = array();
$rowCount = 0;
$stmt->bind_result($name, $address);
while ( $stmt->fetch() )
{
  ++$rowCount;
  $row = array(is_null($name) ? "" : $name, 
               is_null($address) ? "" : $address);
  array_push($data, $row);
}

This code assumes that there isn’t so much data that you need to break up the API calls. It is also going to put all the data onto a single range in the google sheet. I’m basically making an array of rows where each element is an array of column values. Google doesn’t like NULL values, so you’ll want to handle that case. I just inserted blank strings.

Now that we have the data loaded, we can write it to the spreadsheet with this code:

$spreadsheetId = <SpreadsheetID>;
$optParams = ['valueInputOption' => 'RAW'];
$requestBody = new Google_Service_Sheets_ValueRange();
$requestBody->setMajorDimension("ROWS");
$requestBody->setValues($data);
$range = "SheetName!A2:B";
$response = $service->spreadsheets_values->update($spreadsheetId, $range, $requestBody, $optParams);

// Clear out old data
$range = sprintf("SheetName!A%d:B", $rowCount+2);
$service->spreadsheets_values->clear($spreadsheetId, $range, new Google_Service_Sheets_ClearValuesRequest());

The $spreadsheetId is the unique identifier that google gives to any google doc. It is the long string of characters in the URL of the spreadsheet.

The $range is a standard spreadsheet address like you would use in any Google sheet or excel formula. Google is fairly generous here and allows you to specify a range that is too big for the data.

The $optParams of RAW tells Google to treat the input as raw data. The other option is to pretend that it was typed in by a user so that any triggers are applied.

This application fills in data that may be shorter or longer than the previously written data. The last few lines clear out any old data past the end of the new range.

That is all there is to it. The API has lots of other capabilities for manipulating the spreadsheet. Here is some sample code for reading data from a sheet:

$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$values = $response->getValues();

if (count($values) == 0) {
  print "No data found.\n";
} else {
  foreach ($values as $row) {
    printf("%s\n", $row[0]);
  }
}