Why

If you want to collect user data from your site, like promotional signups, for example, a full-blown MySQL database or something similar might be an overkill. Besides, it will probably be harder to read and manipulate that data in a user-friendly manner.

Luckily, Google spreadsheets are a very powerful tool for this type of use case. You can easily manage data, and more importantly, collaborate with others. The missing piece? Your server. If you hook it up, customers could submit data through your site and you’d have the full power of Sheets to do whatever you want with that data. Here’s how.

Developer Console Setup

Log in the Google Developer Console and create a new project. In our case, the project is called “Sheets Append Test.” To enable the Sheets API for this project, click on the blue ENABLE APPS AND SERVICES button:

…and enable the Sheets API:

Click on Credentials in the sidebar and then CREATE CREDENTIALS. Since we want to append data via our back-end, we must create a new service account:

Service accounts function just like regular Google users, except they’re meant to be controlled by software. In this case, that software is our server.

Make sure you create a private key for this service account so we can hook it up with the server. Save the resulting JSON file in your project:

Now, we need a spreadsheet to work with. Go ahead and create one, and make sure to share it with the service account we just made:

We’re all set up now and all that’s left is to make our back-end use the private key we generated, authenticate, and write to the spreadsheet.

Implementation

To work with the Google APIs, it’s a good idea to use the official libraries maintained by Google. We’re going to use google/apiclient for PHP:

composer require google/apiclient ^2.0

Copy the spreadsheet ID (found in its URL):

https://docs.google.com/spreadsheets/d/1IMwiyCa1gCfh1lacyzZqFrYFsqCe-hOSRlaHIc2gFDw/edit#gid=0

…and paste it in the following script. Also, make sure to point the script to the credentials file we previously generated. Like this:

$client = new Google_Client();
$client->setAuthConfigFile('credentials.json');
$client->useApplicationDefaultCredentials();
$client->addScope(Google_Service_Sheets::SPREADSHEETS);
$service = new Google_Service_Sheets($client);

$values = new Google_Service_Sheets_ValueRange([
	'values' => [array_values($_GET)]
]);

$sheetId = '1SrwjTAL8qonb_4zRkn8msYNllID107R6fcKwVN1BljY';
$sheetRange = 'Sheet1';
$params = ['valueInputOption' => 'USER_ENTERED'];

$result = $service->spreadsheets_values->append(
	$sheetId, $sheetRange, $values, $params
);

$count = $result->getUpdates()->getUpdatedCells();
echo sprintf("%d cells appended.", $count);

…then we create a simple HTML form where we can submit the data:

<form action="store.php" method="get">
    <input type="email" name="val1">
    <input type="text" name="val2">
    <input type="number" name="val3">
    <button>Submit!</button>
</form>

We open that form in the browser, put some values, hit Submit, and:

For more details, check it out on GitHub:

OblikStudio/google-sheets-database

Useful Links

• Google Sheets API Quickstart Guide (PHP)
• Reading & Writing Cell Values
• Reference for spreadsheets.values.append