Trying to append a row to a Google Spreadsheet in PHP

Question

I'm using Google's Client API via Composer (https://packagist.org/packages/google/apiclient) and I have successfully authenticated and received an access token.

I am trying to add a row to a Google sheet in my drive, but I can't find any relevant documentation that specifically addresses PHP.

Here's what I've got so far:

$service = new Google_Service_Sheets($a4e->google); // my authenticated Google client object
$spreadsheetId = "11I1xNv8cHzBGE7uuZtB9fQzbgrz4z7lIaEADfta60nc";
$range = "Sheet1!A1:E";
$valueRange= new Google_Service_Sheets_ValueRange();
$service->spreadsheets_values->update($spreadsheetId,$range,$valueRange);

This returns the following error:

Fatal error: Uncaught exception 'Google_Service_Exception' with message '{ "error": { "code": 400, "message": "Invalid valueInputOption: INPUT_VALUE_OPTION_UNSPECIFIED", "errors": [ { "message": "Invalid valueInputOption: INPUT_VALUE_OPTION_UNSPECIFIED", "domain": "global", "reason": "badRequest" } ], "status": "INVALID_ARGUMENT" } } ' in /usr/share/nginx/vendor/google/apiclient/src/Google/Http/REST.php

I'm stuck as to the format of the "Google_Service_Sheets_ValueRange()" object, and also how to append a row to the end of the sheet, rather than having to specify a particular range.

I would greatly appreciate any help with this issue.

score 23 · Accepted Answer · answered Jul 07 '16 at 13:44

23

I had the same problem, there is a lack of documentation about this. But I found a solution. Here is a working example:

// ...

// Create the value range Object
$valueRange= new Google_Service_Sheets_ValueRange();

// You need to specify the values you insert
$valueRange->setValues(["values" => ["a", "b"]]); // Add two values

// Then you need to add some configuration
$conf = ["valueInputOption" => "RAW"];

// Update the spreadsheet
$service->spreadsheets_values->update($spreadsheetId, $range, $valueRange, $conf);

I think it's weird syntax, and I did not found clear documentation about it, I just tried some combination and now it works! Not sure it's the right way, hope it could help.

answered Jul 07 '16 at 13:44

rap-2-h

30,204
37
167
263

5

FYI, we just added a small sample for this in the documentation: https://developers.google.com/sheets/guides/values#writing_to_a_single_range – Eric Koleda Oct 03 '16 at 20:46
@EricKoleda Hey Eric, what happened to the documentation? It is compeletely nonexistent. There are no explanations or examples. Constructors, methods and properties are not listed either? What happened? – Walter M Feb 12 '20 at 02:20
What do you mean? PHP-specific reference docs are here: https://developers.google.com/resources/api-libraries/documentation/sheets/v4/php/latest/ – Eric Koleda Feb 12 '20 at 14:48
Might i ask, doesn't the update method overwrite the range rather than inserting the rows for it? I can't seem to have the rows insert without using the "append" method, which has its own set of issues. – Bigbob556677 May 04 '20 at 14:56
2

@EricKoleda Hey Eric, those are **API references**, not **code examples**. In the past Google provided copious PHP code examples for the API, but lately, PHP is obviously an afterthought. – cloudxix Jun 13 '20 at 18:08

score 13 · Answer 2 · answered Jan 31 '20 at 10:18

There is a new append method: (I am using a service account.)

$client = new Google_Client();
$client->useApplicationDefaultCredentials();
$client->setSubject($delegated_user);
$client->addScope(Google_Service_Sheets::SPREADSHEETS);
$service = new Google_Service_Sheets($client);
$range = 'Sheet1!A:E';
$values = [
    ["a", "b", "C", "D", "E"]
];
$body = new Google_Service_Sheets_ValueRange([
    'values' => $values
]);
$params = [
    'valueInputOption' => "RAW"
];
$result = $service->spreadsheets_values->append($spreadsheet_id, $range, $body, $params);

You don't need to add the range ```A:E``` that it will fill the columns with the data. I think you answer is the correct one. — Guilherme Sampaio, May 06 '22 at 23:50

score 0 · Answer 3 · edited Jan 29 '19 at 23:54

$client = $this->getClient();

$service = new Google_Service_Sheets($client);

$spreadsheetId = '1bFh8FYeez6c1snPCHZF_olsHOLRqEENJbChLKbE9xg0';

$range = 'A1:S1';

$values = [
    ['col 1', 'col 2', 'col 3'],
    ['col 1', 'col 2', 'col 3'],
    ['col 1', 'col 2', 'col 3']
];

$requestBody = new Google_Service_Sheets_ValueRange(array(
    'values' => $values
));

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

print_r($requestBody);

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

Trying to append a row to a Google Spreadsheet in PHP

3 Answers3

Linked