Add a new row to a spreadsheet using Google Sheets API v4

addRowToSpreadsheet.php

<?php
/**
 * This is a proof of concept. In real life you would split up the various parts and allow for different cell value
 * types. Also read Leviscowles1986's comment below:
 * https://gist.github.com/thehelvetian/2e94d60b796735b167dfb1c7560049ae#gistcomment-1822986
 *
 * @param array $ary_values An array containing the cell values
 * @return bool Request status
 */
function addRowToSpreadsheet($ary_values = array()) {

	// Set up the API
	$client = new Google_Client();
	$client->setAuthConfigFile(KEYS_PATH.'/client_secret.json'); // Use your own client_secret JSON file
	$client->addScope(Google_Service_Sheets::SPREADSHEETS);
	$accessToken = 'ACCESS_TOKEN'; // Use your generated access token
	$client->setAccessToken($accessToken);
	$sheet_service = new Google_Service_Sheets($client);

	// Set the sheet ID
	$fileId = 'SPREADHEET_FILE_ID'; // Copy & paste from a spreadsheet URL

	// Build the CellData array
	$values = array();
	foreach( $ary_values AS $d ) {
		$cellData = new Google_Service_Sheets_CellData();
		$value = new Google_Service_Sheets_ExtendedValue();
		$value->setStringValue($d);
		$cellData->setUserEnteredValue($value);
		$values[] = $cellData;
	}

	// Build the RowData
	$rowData = new Google_Service_Sheets_RowData();
	$rowData->setValues($values);

	// Prepare the request
	$append_request = new Google_Service_Sheets_AppendCellsRequest();
	$append_request->setSheetId(0);
	$append_request->setRows($rowData);
	$append_request->setFields('userEnteredValue');

	// Set the request
	$request = new Google_Service_Sheets_Request();
	$request->setAppendCells($append_request);

	// Add the request to the requests array
	$requests = array();
	$requests[] = $request;

	// Prepare the update
	$batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest(array(
		'requests' => $requests
	));
	
	try {
		// Execute the request
		$response = $sheet_service->spreadsheets->batchUpdate($fileId, $batchUpdateRequest);
		if( $response->valid() ) {
			// Success, the row has been added
			return true;
		}
	} catch (Exception $e) {
		// Something went wrong
		error_log($e->getMessage());
	}
	
	return false;
}

@thehelvetian I have a exception running this. I've got an exception "Undefined index: expires_in", any help?
It for use in a service, no user interaction.

@pedrolopix As mentioned above, I don't have that parameter. Also see this.

On another note, here's what my client_secret.json looks like:

{
  "type": "service_account",
  "project_id": "$my-application-name$-proposal-requests",
  "private_key_id": "$my-application-key-id$",
  "private_key": "-----BEGIN PRIVATE KEY-----\n$my-application-key$\n-----END PRIVATE KEY-----\n",
  "client_email": "$my-application-name$-drive-api-client@$my-application-name$-proposal-requests.iam.gserviceaccount.com",
  "client_id": "$my-client-id$",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://accounts.google.com/o/oauth2/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/$my-application-name$-drive-api-client%40$my-application-name$-proposal-requests.iam.gserviceaccount.com"
}

Thank you for the Code!! This is a huge help.
I have a scenario where I need to add the data by specifying the sheet on which the data will be added.

How do I specify the sheet on which it will add the data, by default it always add on Sheet1 , I need the data to go on the second sheet that is Sheet2?

Solved . I am editing this so if someone also have the same situation

Change the statement :

$append_request->setSheetId(0);

to

$append_request->setSheetId(XXXX);

XXXX-- You will get this from the number in the URL after edit#gid=

As pointed out by @Lewiscowles1986, the values are forced to be strings.
Did someone manage to get the values as user entered?
I've also asked a question on stackoverflow but without much success until now...

@etuardu I assume you figured it out, but just in case somebody has the same problem:

The documentation for Google_Service_Sheets_ExtendedValue has additional methods (e.g. setBoolValues, setNumberValues, setFormulaValues). In our case we just checked the type of the data coming from the user and used a switch to use the correct method.

So the code from the sample:

$value->setStringValue($d);

would become:

$value->setNumberValue($d);

or one of the other methods outlined here: Google_Service_Sheets_ExtendedValue Documentation

This is how simply I did it, creating record on google sheet with range from A to K

public function newRow($record = []){

        $newRange = "A:K";

        $body = new Google_Service_Sheets_ValueRange([
            'range' => $newRange,
            'values' => ['values' => $record],
        ]);

$service = new Google_Service_Sheets($client);

        $newRow = $service->spreadsheets_values->append('SPREAD_SHEET_ID_HERE',$newRange,$body,['valueInputOption' => 'USER_ENTERED']);
        return response()->json($newRow->toSimpleObject());
    }

this was super helpful to me as well - thanks for being straightforward with your presentation - something that is lacking with a lot of Google API documentation

Perfect! Thank you