Submit HTML Form to Google Sheets with PHP

We are familiar with google forms that is uses to collect data and saved in google steets. It is very easy way to collect customer feedback and responses. But Google forms may not fit in all circumstances, specially in case of corporate identity or other factors. The customer facing entities must be uniform and consistent regardless of the inhouse/backend operations. It enhances customer experience. But also, custom application development may cost more than expected return of inventment. In such case, we resort to low cost, opensource or even free of cost solutions to minimize cost. And Google Sheets is free tool that we can use to collect and store data and use it later. HTML form submission to Google Sheets can be an optimal low cost solution for such requirements. Submit HTML form to Google sheets is convenient when we have requirement to just collect data form user or customers, while maintaining corporate consistency, without costly development. We can create a form in our website and save submitted form data in google sheets.

Here we are going to use PHP to handle form submission and saving data in Google Sheets.

Implementation

First we need google api client. Since we are working with php, we will use google-api-php-client. Download it via composer or directly from https://github.com/googleapis/google-api-php-client/releases.

Then we will need to create a project in Google Developer console. Then in that project, we need to enable Google Sheets API. For that, go to project we just created > Enable APIs and Services > Find “Google Sheets API” > Enable.

Now we need to create credentials to access Google Sheets API. Go to Credentials > Create Credentials >Service Account Key. Provide with required details to create service account key (Service account name,Service account description). Then click Create and Continue. Following steps are optional, so we can leave it as it is and click “Done”.

Now, service account is created, click on new service account we just created , go to Keys > Add key > Create new key > Choose JSON > Create. Then a json file be downloaded containing the credentials.

Create Spreadsheet

Now, go to your spreadsheet in Google sheets you want to access and grant “Edit” privilege access to the “client_email” address from JSON file we just downloaded.

Form

Let’s create a simple contact form with name, email, phone and message fields in our website or web application.

<form action="postToGooggleSheet.php" method="post">
<lable>Full Name:</label>
<input name="name" type="text" />
<br/>
<lable>Email:</label>
<input name="email" type="email" />
<br/>
<lable>Phone:</label>
<input name="phone" type="tel" />
<br/>
<lable>Message:</label>
<textarea name="message"></textarea>

<button type="submit" name="submit">Submit</button>

</form>

Submit Form and insert into Google Sheets

To submit HTML form to Google Sheets, we create a file handleFormSubmit.php to handle form submission.

<?php
require_once('./postToGooggleSheet.php');

if (isset($_POST['submit'])) {
$name = filter_input(INPUT_POST, 'name', FILTER_UNSAFE_RAW);
$email = filter_input(INPUT_POST, 'email', FILTER_VALIDATE_EMAIL);
$phone = filter_input(INPUT_POST, 'phone', FILTER_UNSAFE_RAW);
$message =  filter_input(INPUT_POST, 'message', FILTER_UNSAFE_RAW);

insertData('Sheet1', [$name, $email, $phone, $message, date("F j, Y, g:i a", time())]);
}

Now create postToGooggleSheet.php file to insert data into spreadsheet with following code.

<?php
require_once('./google-api-php-client/vendor/autoload.php');
define('SHREAD_SHEET_ID', <GOOGLE_SPREAD_SHEET_ID>);
define('SPREAD_SHEET_CREDENTIALS_FILE', <PATH_TO_JSON_FILE>);   //PATH TO JSON FILE DOWNLOADED FROM GOOGLE CONSOLE

function getClient()
{
  $client = new Google_Client();
  $client->setApplicationName('Project');
  $client->setScopes(Google_Service_Sheets::SPREADSHEETS);
  $client->setAuthConfig(SPREAD_SHEET_CREDENTIALS_FILE);
  $client->setAccessType('offline');
  return $client;
}

function insertData($range = 'Sheet1', array $data = [])
{
  // Get the API client and construct the service object.
  $client = getClient();
  $service = new Google_Service_Sheets($client);
  $valueRange = new Google_Service_Sheets_ValueRange();
  $valueRange->setValues(
    [
      'values' => $data
    ]
  );
  $conf = ["valueInputOption" => "RAW"];
  $response = $service->spreadsheets_values->append($spreadsheetId, $range, $valueRange, $conf);
  return $response;
}

GOOGLE_SPREAD_SHEET_ID is ID of Google spreadsheet in which we want to insert form data.

And now we are ready to submit HTML form and insert data into google sheets.

If you are working with WordPress, read Custom form in WordPress without plugin to create custom form in WordPress and insert into Google Sheets.