PHP - Need help completing Google Sheets API request

Question

What I'm aiming to do ~ Write to google sheets via .php file that receives html form data.

So far ~ Completed the PHP quick start here: https://developers.google.com/sheets/api/quickstart/php Completed successfully and was able to read/write to sheet using their example.

Next I used the sample code to append a sheet, found at: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/append

But it seems to be missing a vital piece of code. See below.

Linux Server, PHP5.

<?php
/*
 * BEFORE RUNNING:
 * ---------------
 * 1. If not already done, enable the Google Sheets API
 *    and check the quota for your project at
 *    https://console.developers.google.com/apis/api/sheets
 * 2. Install the PHP client library with Composer. Check installation
 *    instructions at https://github.com/google/google-api-php-client.
 */

// Autoload Composer.
require_once __DIR__ . '/vendor/autoload.php';

$client = getClient();

$service = new Google_Service_Sheets($client);

// The ID of the spreadsheet to update.
$spreadsheetId = 'my-spreadsheet-id';  // TODO: Update placeholder value.

// The A1 notation of a range to search for a logical table of data.
// Values will be appended after the last row of the table.
$range = 'my-range';  // TODO: Update placeholder value.

// TODO: Assign values to desired properties of `requestBody`:
$requestBody = new Google_Service_Sheets_ValueRange();

$response = $service->spreadsheets_values->append($spreadsheetId, $range, $requestBody);

// TODO: Change code below to process the `response` object:
echo '<pre>', var_export($response, true), '</pre>', "\n";

function getClient() {
  // TODO: Change placeholder below to generate authentication credentials. See
  // https://developers.google.com/sheets/quickstart/php#step_3_set_up_the_sample
  //
  // Authorize using one of the following scopes:
  //   'https://www.googleapis.com/auth/drive'
  //   'https://www.googleapis.com/auth/drive.file'
  //   'https://www.googleapis.com/auth/spreadsheets'
  return null;
}
?>

I would have expected to see the function 'getClient()' filled. What exactly do I need to add here?

I assume once this is filled i can just save to a php file and call to append, since my site already has authorization.

Thanks in advance.

Yasiru - Thanks for the suggestion. I now have the following ~

<?php
/*
 * BEFORE RUNNING:
 * ---------------
 * 1. If not already done, enable the Google Sheets API
 *    and check the quota for your project at
 *    https://console.developers.google.com/apis/api/sheets
 * 2. Install the PHP client library with Composer. Check installation
 *    instructions at https://github.com/google/google-api-php-client.
 */

// Autoload Composer.
require_once __DIR__ . '/vendor/autoload.php';

$client = getClient();

$service = new Google_Service_Sheets($client);

// The ID of the spreadsheet to update.
$spreadsheetId = 'XXXX';  // TODO: Update placeholder value.

// The A1 notation of a range to search for a logical table of data.
// Values will be appended after the last row of the table.
$range = 'Sheet1';  // TODO: Update placeholder value.

// TODO: Assign values to desired properties of `requestBody`:
//$requestBody = new Google_Service_Sheets_ValueRange();
$requestBody = {
    "majorDimension": 'ROWS',
    "values": [
        "val1","val2"
    ]
}

$response = $service->spreadsheets_values->append($spreadsheetId, $range, $requestBody);

// TODO: Change code below to process the `response` object:
echo '<pre>', var_export($response, true), '</pre>', "\n";

function getClient()
{
    $client = new Google_Client();
    $client->setApplicationName('Google Sheets API PHP Quickstart');
    $client->setScopes('https://www.googleapis.com/auth/spreadsheets');
    $client->setAuthConfig('credentials.json');
    $client->setAccessType('offline');
    $client->setPrompt('select_account consent');

    // Load previously authorized token from a file, if it exists.
    // The file token.json stores the user's access and refresh tokens, and is
    // created automatically when the authorization flow completes for the first
    // time.
    $tokenPath = 'token.json';
    if (file_exists($tokenPath)) {
        $accessToken = json_decode(file_get_contents($tokenPath), true);
        $client->setAccessToken($accessToken);
    }

    // If there is no previous token or it's expired.
    if ($client->isAccessTokenExpired()) {
        // Refresh the token if possible, else fetch a new one.
        if ($client->getRefreshToken()) {
            $client->fetchAccessTokenWithRefreshToken($client->getRefreshToken());
        } else {
            // Request authorization from the user.
            $authUrl = $client->createAuthUrl();
            printf("Open the following link in your browser:\n%s\n", $authUrl);
            print 'Enter verification code: ';
            $authCode = trim(fgets(STDIN));

            // Exchange authorization code for an access token.
            $accessToken = $client->fetchAccessTokenWithAuthCode($authCode);
            $client->setAccessToken($accessToken);

            // Check to see if there was an error.
            if (array_key_exists('error', $accessToken)) {
                throw new Exception(join(', ', $accessToken));
            }
        }
        // Save the token to a file.
        if (!file_exists(dirname($tokenPath))) {
            mkdir(dirname($tokenPath), 0700, true);
        }
        file_put_contents($tokenPath, json_encode($client->getAccessToken()));
    }
    return $client;
}

?>

However when I load the page, I get an internal server error 500. To clarify, the above PHP is saved in test.php and is called via url, and is located in the working directory.

Allan Allan · Accepted Answer · 2019-11-25T19:09:55

Im guessing your trying to display data from your sheet not the clients. For that you don't need the client to log in, he does not even have to know the data is coming from google sheets (obviously unless your website says that). Best and most secure way is to do it all server side.

Unfortunately google is not very good with their documentation. This is what worked for me:

Install the google api (best through composer).
Get "Service account keys" from https://console.developers.google.com/, and save on your server as a json file. This is the password that will allow your server to access your sheets. (Make sure the google api is enabled in your project).
In the spreadsheet that you want to access, give edit permission to the email that came with your "Service account keys".

Than use the following code:

require_once __DIR__ . '/vendor/autoload.php'; //Path to google sheets library
$client = new \Google_Client();
$client->setApplicationName('YOURAPPNAME'); //Add a name to your project. Can be any name
$client->setScopes([\Google_Service_Sheets::SPREADSHEETS]);
$client->setAccessType('offline');
$client->setAuthConfig(__DIR__ . '/****.json');// Path to the json file with the "Service account keys"
$service = new Google_Service_Sheets($client);

$spreadsheetId = "****"; // Add your spreadsheet id. Can e found in the url of your sheet.
$range = '****'; // Name of the sheet you are working with

From here you will add values to your shet Change it based on your needs.

$valueRange= new Google_Service_Sheets_ValueRange();
$valueRange->setValues(["values" => ['Value1', 'Value2']]); //The values you will bee adding
$conf = ["valueInputOption" => "RAW"];
$ins = ["insertDataOption" => "INSERT_ROWS"];
$service->spreadsheets_values->append($spreadsheetId, $range, $valueRange, $conf, $ins);

Let me know if it works or if you still need help.

PHP - Need help completing Google Sheets API request

2 Answers