CODEWITHSUNDEEP
phpgoogle-sheetsgoogle-sheets-api
user12133523
user12133523

Reputation:

issue with writing in google sheet with API

I am trying to use google sheet API to write in google sheet API.

For that, I am using below code

$spreadsheetId = 'XXXX';
$range = "Zoho Emails 2";
$values = [["This","is","a","new","row"],];
$body = new Google_Service_Sheets_ValueRange([
   "values" =>$values
]);
$params = [
   `valueInputOptions` => "RAW"
];
$insert = [
   "insertDataOption" => "INSERT_ROWS"
];
$result = $service->spreadsheets_values->append(
  $spreadsheetId,
  $range,
  $body,
  $params,
  $insert
);

But when I run This code this code gives me below error

[26-Nov-2019 22:45:36 America/Chicago] PHP Fatal error:  Uncaught exception 'Google_Exception' 
with message '(append) unknown parameter: ''' in 
/Google_sheet/vendor/google/apiclient/src/Google/Service/Resource.php:147
Stack trace:
#0 /Google_sheet/vendor/google/apiclient- 
services/src/Google/Service/Sheets/Resource/SpreadsheetsValues.php(65): 
Google_Service_Resource->call('append', Array, 'Google_Service_...')
#1 /Google_sheet/quickstart.php(99): Google_Service_Sheets_Resource_SpreadsheetsValues- 
>append('1k8sR-aV8O5W7jP...', 'Zoho Emails 2', Object(Google_Service_Sheets_ValueRange), Array, 
Array)
#2 {main}
thrown in /Google_sheet/vendor/google/apiclient/src/Google/Service/Resource.php on line 147

But I don't understand why this error occurs.

Can anybody help me with this?

Upvotes: 1

Views: 781

Answers (1)

Tanaike
Tanaike

Reputation: 201613

How about this modification?

From:

$params = [
   `valueInputOptions` => "RAW"
];
$insert = [
   "insertDataOption" => "INSERT_ROWS"
];
$result = $service->spreadsheets_values->append(
  $spreadsheetId,
  $range,
  $body,
  $params,
  $insert
);

To:

$params = [
  "valueInputOption" => "RAW",
  "insertDataOption" => "INSERT_ROWS"
];
$result = $service->spreadsheets_values->append(
  $spreadsheetId,
  $range,
  $body,
  $params
);

Note:

  • This modified script supposes that you have already been able to get and put values for the Spreadsheet using Sheets API.

Reference:

If this was not the direct solution of your issue, I apologize.

Added:

When the script in the link of your another question you provided in the discussion is used, the whole script reflected my modified script is as follows. Before you run the script, please set the variables of $spreadsheetId and $range. Before you run the script, please confirm credentials.json and delete the file of token.json. Then, run the script. At that time, please authorize again. By this, I think that the script works.

The scope is changed from Google_Service_Sheets::SPREADSHEETS_READONLY to Google_Service_Sheets::SPREADSHEETS.

Whole script:

<?php
require __DIR__ . '/vendor/autoload.php';

if (php_sapi_name() != 'cli') {
 throw new Exception('This application must be run on the command line.');
}

function getClient()
{
  $client = new Google_Client();
  $client->setApplicationName('Google Sheets API PHP Quickstart');
  $client->setScopes(Google_Service_Sheets::SPREADSHEETS);
  $client->setAuthConfig('credentials.json');
  $client->setAccessType('offline');
  $client->setPrompt('select_account consent');

  $tokenPath = 'token.json';
  if (file_exists($tokenPath)) {
    $accessToken = json_decode(file_get_contents($tokenPath), true);
    $client->setAccessToken($accessToken);
  }

  if ($client->isAccessTokenExpired()) {
    if ($client->getRefreshToken()) {
        $client->fetchAccessTokenWithRefreshToken($client->getRefreshToken());
    } 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);
        $client->setAccessToken($accessToken);

        if (array_key_exists('error', $accessToken)) {
            throw new Exception(join(', ', $accessToken));
        }
    }
    if (!file_exists(dirname($tokenPath))) {
        mkdir(dirname($tokenPath), 0700, true);
    }
    file_put_contents($tokenPath, json_encode($client->getAccessToken()));
}
  return $client;
}


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

$spreadsheetId = "###"; // Spreadsheet ID
$range = "###"; // Sheet name

$values = [["This","is","a","new","row"],];
$body = new Google_Service_Sheets_ValueRange([
   "values" =>$values
]);
$params = [
  "valueInputOption" => "RAW",
  "insertDataOption" => "INSERT_ROWS"
];
$result = $service->spreadsheets_values->append(
  $spreadsheetId,
  $range,
  $body,
  $params
);

Upvotes: 1

Related Questions