Send HTML Form to Google Sheets using PHP… without Google Forms
Posted at 2019-02-18

Send HTML Form to Google Sheets using PHP… without Google Forms

  1. Download google-api-php-client (via compose or direct download from https://github.com/googleapis/google-api-php-client/releases)
  2. Create a project on https://console.developers.google.com/apis/dashboard.
  3. Enable APIs, enable the Google Sheets API
  4. Go to Credentials/Create credentials, select "Service account key"

  5. Select New service account. Give the account a name, choose JSON.

  6. For Role choose "Service Account Actor For Key type"
  7. Download the file with credentials, save it on your server
  8. Go to your spreadsheet you want to access and share "Edit" privileges access to the "client_email" address from JSON file from the previous step.


Let's create "apply for a job" web form with 4 fields: email, name, phone, and position.

index.html

<form action="/post2sheet.php" method="post">
  First name: <input type="text" name="name"><br>
  Email: <input type="text" name="email"><br>
  Phone: <input type="text" name="phone"><br>
  Position: <input type="text" name="position"><br>
  <input type="submit" value="Submit">
</form>


post2sheet.php

<?php
// TODO: validate input params, ignore bots...

require __DIR__ . '/vendor/autoload.php'; // google-api-php-client path

function getClient()
{
    $client = new Google_Client();
    $client->setApplicationName('Project');
    $client->setScopes(Google_Service_Sheets::SPREADSHEETS);
    //PATH TO JSON FILE DOWNLOADED FROM GOOGLE CONSOLE FROM STEP 7
    $client->setAuthConfig('PROJECT-NAME-e2262b350d03.json'); 
    $client->setAccessType('offline');
    return $client;
}

// Get the API client and construct the service object.
$client = getClient();
$service = new Google_Service_Sheets($client);
$spreadsheetId = '1rPWDc6E0Hreg5mmnM0tIKkEnzN_n1ZMDavYhZM1MhfE'; // spreadsheet Id
$range = 'Sheet1'; // Sheet name

$valueRange= new Google_Service_Sheets_ValueRange();
$valueRange->setValues(["values" => ["a", "b"]]); // values for each cell
$valueRange->setValues(["values" => [
    $_POST["name"]
  , $_POST["email"]
  , $_POST["position"]
  , $_POST["phone"]
  , date("F j, Y, g:i a", time()) 
]]);

$conf = ["valueInputOption" => "RAW"];
$response = $service->spreadsheets_values->append($spreadsheetId, $range, $valueRange, $conf);




Comments 3

Asha

I get this error: PHP Notice: Undefined index: name in /home/doug/asha/bonehook/GPStracking/post2sheet.php on line 27 PHP Notice: Undefined index: email in /home/doug/asha/bonehook/GPStracking/post2sheet.php on line 28 PHP Notice: Undefined index: position in /home/doug/asha/bonehook/GPStracking/post2sheet.php on line 29 PHP Notice: Undefined index: phone in /home/doug/asha/bonehook/GPStracking/post2sheet.php on line 30 PHP Fatal error: Uncaught Google\Service\Exception: { "error": { "code": 400, "message": "Invalid values[0][0]: struct_value {\n fields {\n key: \"4\"\n value {\n string_value: \"December 9, 2020, 12:14 pm\"\n }\n }\n}\n", "errors": [ { "message": "Invalid values[0][0]: struct_value {\n fields {\n key: \"4\"\n value {\n string_value: \"December 9, 2020, 12:14 pm\"\n }\n }\n}\n", "domain": "global", "reason": "badRequest" } ], "status": "INVALID_ARGUMENT" } } in /home/doug/asha/bonehook/GPStracking/vendor/google/apiclient/src/Http/REST.php:128 Stack trace: #0 /home/doug/asha/bonehook/GPStracking/vendor/google/apiclient/src/Http/REST.php(103): Google\Http\REST::decodeHttpResponse(Object(GuzzleHttp\Psr7\Response), Object(GuzzleHttp\Psr7\Request), 'Google_Service_...') #1 [internal function]: Google\Http\REST::doExecute(Object(GuzzleHttp\Client), Object(GuzzleHttp\Psr7\Request), 'Google_Service_...') #2 /home/doug/asha/bonehook/GPStrackin in /home/doug/asha/bonehook/GPStracking/vendor/google/apiclient/src/Http/REST.php on line 128

Miki

I am getting an error : PHP Fatal error: Uncaught Error: Call to undefined function openssl_sign() in C:\inetpub\wwwroot\google-api-php-client-2.4.1\vendor\firebase\php-jwt\src\JWT.php:209 Stack trace: #0 C:\inetpub\wwwroot\google-api-php-client-2.4.1\vendor\firebase\php-jwt\src\JWT.php(180): Firebase\JWT\JWT::sign() #1 C:\inetpub\wwwroot\google-api-php-client-2.4.1\vendor\google\auth\src\OAuth2.php(1321): Firebase\JWT\JWT::encode() #2 C:\inetpub\wwwroot\google-api-php-client-2.4.1\vendor\google\auth\src\OAuth2.php(428): Google\Auth\OAuth2->jwtEncode() #3 C:\inetpub\wwwroot\google-api-php-client-2.4.1\vendor\google\auth\src\OAuth2.php(461): Google\Auth\OAuth2->toJwt() #4 C:\inetpub\wwwroot\google-api-php-client-2.4.1\vendor\google\auth\src\OAuth2.php(502): Google\Auth\OAuth2->generateCredentialsRequest() #5 C:\inetpub\wwwroot\google-api-php-client-2.4.1\vendor\google\auth\src\Credentials\ServiceAccountCredentials.php(135): Google\Auth\OAuth2->fetchAuthToken() #6 C:\inetpub\wwwroot\google-api-php-client-2.4.1\vendor\google\auth\src\FetchAuthTokenCach in C:\inetpub\wwwroot\google-api-php-client-2.4.1\vendor\firebase\php-jwt\src\JWT.php on line 209

Igor

You are missing the openssl extension. Uncomment (remove the ; on) the line in your php.ini which says ;extension=php_openssl.dll and restart your server.

Post a Comment