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;
}

consider re-structuring...

• Dependencies should be passed in, probably an interface, with an implementation to a service specific adaptor
• Also break up the steps. I get this is probably PoC (proof of concept), but it was linked to over on reddit's /r/php so someone will probably copy->paste

It seems to force the spreadsheet to use stringData. Probably a good idea not to do that. It's like planning a house by building it with bricks.

Thanks for your input @Lewiscowles1986. It is indeed just a proof of concept and I guess (hope) most visitors will get that. I'll add add a DocBloc to the top to make it a bit more clear.

This was super helpful to me. Thank you! Here is what I ended up using:

/**
 * Inspired by https://gist.github.com/thehelvetian/2e94d60b796735b167dfb1c7560049ae
 *
 * @param Google_Service_Sheets $sheetsService
 * @param string $spreadsheetId
 * @param int $sheetId
 * @param array $newValues An array containing the cell values
 * @return bool Request status
 */
function addRowToSpreadsheet($sheetsService, $spreadsheetId, $sheetId, $newValues = []) {
    // Build the CellData array
    $values = [];
    foreach ($newValues 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($sheetId);
    $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 = $sheetsService->spreadsheets->batchUpdate($spreadsheetId, $batchUpdateRequest);
        if ($response->valid()) {            
            return true;// Success, the row has been added
        }
    } catch (Exception $e) {        
        error_log($e->getMessage());// Something went wrong
    }

    return false;
}

@ryancwalsh Glad it helped & thank you for your code example!

@thehelvetian / @ryancwalsh - I am new to Google Sheets API. Is there a php helper function available to generate access_token for a particular spreadsheet? I saw the official quick start script, but the json generated had a "expires_in" parameter.

I want to use the save the contact form data to the spreadsheet where the access_token should not expire in my case. Any inputs?

@harishannam It seems like we need to create an oauth2callback.php file which can set the access_token on the session: https://developers.google.com/api-client-library/php/auth/web-app

thanks a lot!

This code was a huge help. I literally did 12 hours of scratching my head with trial and error until I found your code. This was the only code on the web that helped me realize how to append a new sheet to an existing spreadsheet. Evidently, as of Feb 19, 2017, the Google API v4 docs make you think you need to use $service->spreadsheets_values->batchUpdate when you are supposed to use $service->spreadsheets->batchUpdate. Big difference. Here's the code I figured out for creating a new tab, and ignoring if one already exists, and which doesn't blow away data in the existing tab:

$sSpreadsheetRange = gmdate('M Y');
try {
	$body = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest(array(
		'requests' => array(
			'addSheet' => array(
				'properties' => array(
					'title' => $sSpreadsheetRange
				)
			)
		)
	));
	$result1 = $service->spreadsheets->batchUpdate($sSpreadsheetID,$body);
} catch(Exception $ignore) {}

At that point, I can append rows like so:

$sSpreadsheetID = '1UTk34V2VJ8uXXXX-w2OkG9LHC9Eq9LXXXXHQuEEk0'; // change me
$asSpreadsheetRows = array(
    array(
        "Mickey","Mouse " . rand(11111,99999)
    ),
    array(
    	"Donald","Duck"
    )
);
$body = new Google_Service_Sheets_ValueRange(array(
  'values' => $asSpreadsheetRows
));
$params = array(
  'valueInputOption' => 'USER_ENTERED'
);
$result = $service->spreadsheets_values->append($sSpreadsheetID, $sSpreadsheetRange, $body, $params);

header('Content-Type: text/plain');
print_r($result);
die();

Thanks to all commenters & contributors. Happy to see this Gist helps some of you fellow developers!

Anyone having issues getting the auth bit together (Line 14:$client->setAuthConfigFile(KEYS_PATH.'/client_secret.json'); // Use your own client_secret JSON file), here's how to get that client_secret.json file:

Open the PHP Quickstart and simply follow the steps described in 'Step 1: Turn on the Drive API'. Very simple.

@harishannam my file does not have the mentioned expires_in parameter ¯_(ツ)_/¯

HTH!

@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