Google-api-php-client: PHP - Append values to spreadsheet : Request had insufficient authentication scopes

[mbougarne]

Hi!

I searched several hours looking for a solve for this issue, but without success!

Fatal error: Uncaught Google_Service_Exception:

{ "error": { "code": 403, "message": "Request had insufficient authentication scopes.", "errors": [ { "message": "Request had insufficient authentication scopes.", "domain": "global", "reason": "forbidden" } ], "status": "PERMISSION_DENIED" } }

What I want to achieve? Is sending form data to Google spreadsheet using PHP and POST method!

This is the code that I used, did I use it in the wrong way?

Thank You!

require_once __DIR__ . '/vendor/autoload.php';
define('APPLICATION_NAME', 'spreadform');
define('CREDENTIALS_PATH', './token.json');
define('CLIENT_SECRET_PATH', __DIR__ . '/client_secret.json');
define('SCOPES', implode(' ', array(
    Google_Service_Sheets::SPREADSHEETS) 
));

$client = getClient();

$service = new Google_Service_Sheets($client);
$spreadsheetId = 'xxxxxxx';
$range = 'my-sheet!A2:F'; 

$post = filter_input_array(INPUT_POST, FILTER_SANITIZE_STRING);
$firstName              = $post['firstName'];
$lastName               = $post['lastName'];
$emailAddress           = $post['emailAddress'];

$values = [
    'firstName'         => $firstName,
    'lastName'          => $lastName,
    'emailAddress'      => $emailAddress
];
$requestBody = new Google_Service_Sheets_ValueRange([
    'values' => $values
]);

$params = [
    'valueInputOption' => 'RAW'
];  
$response = $service->spreadsheets_values->append($spreadsheetId, $range, $requestBody, $params);

echo '<pre>', var_export($response, true), '</pre>', "\n";

[mattwhisenhunt]

Try it with all three scopes:

define('SCOPES', implode(' ', array(
    Google_Service_Sheets::SPREADSHEETS,
    Google_Service_Sheets::DRIVE,
    Google_Service_Sheets::DRIVE_FILE) 
));

I don't know why the docs say one of those three are required. My experience with scopes is the more the merrier and do whatever Try this API or API Explorer do.

[mbougarne]

Thank you Matt!
It is worked when I try it using Try this API But when I try with php using cURL i got :

{ 
    "error": 
    { 
        "code": 400, 
        "message": "Invalid JSON payload received. Unknown name \"\": Root element must be a message.", 
        "status": "INVALID_ARGUMENT", 
        "details": [ 
            { 
                "@type": "type.googleapis.com/google.rpc.BadRequest", 
                "fieldViolations": [ 
                    { 
                        "description": "Invalid JSON payload received. Unknown name \"\": Root element must be a message." 
                    } 
                ] 
            } 
        ] 
    } 
}

[mattwhisenhunt]

I haven't been able to reproduce your result. Is it possible you made a change that that empties out $requestBody?

The only other thing I would mention is you probably want values in another array so they insert across instead of down:

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

[mxdpeep]

1. create a Service Account and download JSON credentials
2. share the Sheet for writing with the Service Account

the working code:

settings

$range = "Sheet1!A1:Z";
$spreadsheetId = $cfg["tickets_csv"]; // actual CSV ID

client

$client = new \Google_Client();
$client->setApplicationName($cfg["applicationname"]); // some name
$client->useApplicationDefaultCredentials();
$client->setScopes([\Google_Service_Sheets::SPREADSHEETS]);
$client->setAuthConfig(ROOT."/".$cfg["credentials"]); // the credentials
JSON file
$client->setAccessType("offline");
$service = new \Google_Service_Sheets($client);
$body = new \Google_Service_Sheets_ValueRange(["values" => $values]); //
array of arrays, each line for a new row
$params = ["valueInputOption" => $cfg["valueinputoption"]]; // "
USER_ENTERED"
$result = $service->spreadsheets_values->append($spreadsheetId, $range,
$body, $params);

s pozdravem,

Filip Oščádal

On Mon, Jun 11, 2018 at 11:53 PM, Matt Whisenhunt [e-mail]
wrote:

I haven't been able to reproduce your result. Is it possible you made a
change that that empties out $requestBody?

The only other thing I would mention is you probably want values in
another array so they insert across instead of down:

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

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
https://github.com/google/google-api-php-client/issues/1396#issuecomment-396399392,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAEmXvPaeKAF0G3IdavKraeYBjXX4_Mdks5t7ubIgaJpZM4SmGqk
.

[mattwhisenhunt]

Thank you for filing this issue. We asked some clarifying questions or suggested a course of action a week or more ago and never heard back from you. We are unable to proceed with this issue until then, so we are closing it. Please feel free to comment with more information and we will re-open this issue.

[mcannon354]

I have a similar issue with the Google Slides API (PHP). I took Matt's advice and added all three (I think I did it correctly). I tried adding $client->useApplicationDefaultCredentials(), like Filip had in his code, and I got a different error, so I commented it out.

I started with the PHP Quickstart example from the Google Slides API using CLI, which only reads from an existing slides file to determine information and output it to the shell. That worked for me and it was PRESENTATIONS_READONLY.

I modified it so that it will create a slide, so I had to make it PRESENTATIONS. Below is my code, and then the response (after taking @mattwhisenhunt 's advice above and using all three). I get the same error codes, but I have provided the additional responses from REST.php also. I could not figure out which line in my code actually was causing the problem.

Below are the CODE (with Id values replaced with "...not shared here..."), COMMAND, and RESPONSE. Please help as you are able. Thanks! (@mattwhisenhunt do I need to create a new item for this?)

CODE: (with comments all removed)

<?php
require 'C:\xampp\vendor\autoload.php';

function getClient()
{
    $client = new Google_Client();
    $client->setApplicationName('Google Slides API TEST1');
    $client->setScopes(array(
        Google_Service_Slides::PRESENTATIONS,
        Google_Service_Slides::DRIVE,
        Google_Service_Slides::DRIVE_FILE)
    );
    $client->setAuthConfig('client_secret.json');
    $client->setAccessType('offline');

    $credentialsPath = expandHomeDirectory('credentials.json');
    if (file_exists($credentialsPath)) {
        $accessToken = json_decode(file_get_contents($credentialsPath), true);
    } else {
        $authUrl = $client->createAuthUrl();
        printf("Open the following link in your browser:\n%s\n", $authUrl);
        print 'Enter verification code: ';
        $authCode = trim(fgets(STDIN));

        $accessToken = $client->fetchAccessTokenWithAuthCode($authCode);

        if (!file_exists(dirname($credentialsPath))) {
            mkdir(dirname($credentialsPath), 0700, true);
        }
        file_put_contents($credentialsPath, json_encode($accessToken));
        printf("Credentials saved to %s\n", $credentialsPath);
    }
    $client->setAccessToken($accessToken);

    if ($client->isAccessTokenExpired()) {
        $client->fetchAccessTokenWithRefreshToken($client->getRefreshToken());
        file_put_contents($credentialsPath, json_encode($client->getAccessToken()));
    }
    return $client;
}

function expandHomeDirectory($path)
{
    $homeDirectory = getenv('HOME');
    if (empty($homeDirectory)) {
        $homeDirectory = getenv('HOMEDRIVE') . getenv('HOMEPATH');
    }
    return str_replace('~', realpath($homeDirectory), $path);
}

$client = getClient();
$service = new Google_Service_Slides($client);

$title = "Google Slides API TEST1";
$presentation = new Google_Service_Slides_Presentation(array(
    'title' => $title
));
$presentation = $service->presentations->create($presentation);
printf("Created presentation with ID: %s\n", $presentation->presentationId);
echo $presentationId;

COMMAND:
# php GoogleSlidesAPI-TEST1.php
RESPONSE:

PHP Fatal error:  Uncaught Google_Service_Exception: {
  "error": {
    "code": 403,
    "message": "Request had insufficient authentication scopes.",
    "errors": [
      {
        "message": "Request had insufficient authentication scopes.",
        "domain": "global",
        "reason": "forbidden"
      }
    ],
    "status": "PERMISSION_DENIED"
  }
}
 in C:\xampp\vendor\google\apiclient\src\Google\Http\REST.php:118
Stack trace:
#0 C:\xampp\vendor\google\apiclient\src\Google\Http\REST.php(94): Google_Http_REST::decodeHttpResponse(Object(GuzzleHttp\Psr7\Response), Object(GuzzleHttp\Psr7\Request), 'Google_Service_...')
#1 C:\xampp\vendor\google\apiclient\src\Google\Task\Runner.php(176): Google_Http_REST::doExecute(Object(GuzzleHttp\Client), Object(GuzzleHttp\Psr7\Request), 'Google_Service_...')
#2 C:\xampp\vendor\google\apiclient\src\Google\Http\REST.php(58): Google_Task_Runner->run()
#3 C:\xampp\vendor\google\apiclient\src\Google\Client.php(788): Google_Http_REST::execute(Object(GuzzleHttp\Client), Object(GuzzleHttp\Psr7\Re in C:\xampp\vendor\google\apiclient\src\Google\Http\REST.php on line 118

Fatal error: Uncaught Google_Service_Exception: {
  "error": {
    "code": 403,
    "message": "Request had insufficient authentication scopes.",
    "errors": [
      {
        "message": "Request had insufficient authentication scopes.",
        "domain": "global",
        "reason": "forbidden"
      }
    ],
    "status": "PERMISSION_DENIED"
  }
}
 in C:\xampp\vendor\google\apiclient\src\Google\Http\REST.php:118
Stack trace:
#0 C:\xampp\vendor\google\apiclient\src\Google\Http\REST.php(94): Google_Http_REST::decodeHttpResponse(Object(GuzzleHttp\Psr7\Response), Object(GuzzleHttp\Psr7\Request), 'Google_Service_...')
#1 C:\xampp\vendor\google\apiclient\src\Google\Task\Runner.php(176): Google_Http_REST::doExecute(Object(GuzzleHttp\Client), Object(GuzzleHttp\Psr7\Request), 'Google_Service_...')
#2 C:\xampp\vendor\google\apiclient\src\Google\Http\REST.php(58): Google_Task_Runner->run()
#3 C:\xampp\vendor\google\apiclient\src\Google\Client.php(788): Google_Http_REST::execute(Object(GuzzleHttp\Client), Object(GuzzleHttp\Psr7\Re in C:\xampp\vendor\google\apiclient\src\Google\Http\REST.php on line 118

[mattwhisenhunt]

@mcannon354 It looks like you need to implode(' ', ) your scopes array.

[rodrigomojeda]

I've solved this problem by deleting token.json content in order to re-trigger the validation process.

[gvictorazzi]

perfect !!!!

[mamunur105]

Fatal error: Uncaught Google_Service_Exception: { "error": { "code": 400, "message": "Invalid JSON payload received. Unknown name \"name\" at 'data.values[0]': Cannot find field.\nInvalid JSON payload received. Unknown name \"roll\" at 'data.values[0]': Cannot find field.\nInvalid JSON payload received. Unknown name \"class\" at 'data.values[0]': Cannot find field.\nInvalid JSON payload received. Unknown name \"dev\" at 'data.values[0]': Cannot find field.", "errors": [ { "message": "Invalid JSON payload received. Unknown name \"name\" at 'data.values[0]': Cannot find field.\nInvalid JSON payload received. Unknown name \"roll\" at 'data.values[0]': Cannot find field.\nInvalid JSON payload received. Unknown name \"class\" at 'data.values[0]': Cannot find field.\nInvalid JSON payload received. Unknown name \"dev\" at 'data.values[0]': Cannot find field.", "domain": "global", "reason": "badRequest" } ], "status": "INVALID_ARGUMENT" } } in /opt/lampp/htdocs/php/save in /opt/lampp/htdocs/php/savetogooglesheet/vendor/google/apiclient/src/Google/Http/REST.php on line 118

` require __DIR__ . '/vendor/autoload.php';

define('SCOPES', implode(' ', array(
Google_Service_Sheets::SPREADSHEETS,
Google_Service_Sheets::DRIVE,
Google_Service_Sheets::DRIVE_FILE)
));
/**

• Returns an authorized API client.
• @return Google_Client the authorized client object
  */

function getClient() {
$client = new Google_Client();
$client->setAuthConfig('credentials.json');
$client->setApplicationName('theName');
$client->setScopes(SCOPES);
return $client;
}

// Get the API client and construct the service object.
$client = getClient();
$service = new Google_Service_Sheets($client);

// Prints the names and majors of students in a sample spreadsheet:
// https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
$spreadsheetId = '1wAxarp4q9yo7kUUMaA3RudgmSTab53Dc9fReSbyreeM';
$range = 'Sheet1!A1:Z';

// $post = filter_input_array(INPUT_POST, FILTER_SANITIZE_STRING);

$firstName = "Mamunur";
$roll = "5200";
$class = "class";

$values = [
'Name' => $firstName,
'Roll' => $roll,
'Class' => $class,
'Dev' =>"dev"
];
$requestBody = new Google_Service_Sheets_ValueRange([
'values' => [$values]
]);

$params = [
'valueInputOption' => 'RAW'
];

$result = $service->spreadsheets_values->append($spreadsheetId, $range, $requestBody, $params);
printf("%d cells appended.", $result->getUpdates()->getUpdatedCells());`

[devLeopar]

I've solved this problem by deleting token.json content in order to re-trigger the validation process.

This made my day ! After all, you need to activate google drive sheet api for that project id if you hadn't done before.