How to update Google Spreadsheet cell using PHP API

Question

I would like to know how I can interact with a Google spreadsheet using PHP.

I've looked through many pages of Google's documentation, however, none of that is doing what I'm looking for.

My goal is to be able to change the content of the cells using oAuth (not the email/pass).

Please, forgive me if this is a RTFM issues, but I did spend more than 2 weeks with this with no result. :/

Answer 1

You can do this with asimlqt/php-google-spreadsheet-client library.

1. Install the library via composer:

   "require": {
       "asimlqt/php-google-spreadsheet-client": "dev-master"
   }
   

2. Bootstrap composer in your PHP file:

   require('vendor/autoload.php');
   

3. Follow the steps to get a Google API client ID, client email, and P12 access key, as explained here:
   https://github.com/asimlqt/php-google-spreadsheet-client/wiki/How-to-use-%22Service-account%22-authorization-(rather-than-user-based-access-refresh-tokens)

4. Use the following code:

   $accessToken = getGoogleTokenFromKeyFile(CLIENT_ID_HERE, CLIENT_EMAIL_HERE, P12_FILE_PATH_HERE);
   use Google\Spreadsheet\DefaultServiceRequest;
   use Google\Spreadsheet\ServiceRequestFactory;
   ServiceRequestFactory::setInstance(new DefaultServiceRequest($accessToken));
   
   // Load spreadsheet and worksheet
   $worksheet = (new Google\Spreadsheet\SpreadsheetService())
       ->getSpreadsheets()
       ->getByTitle('xxxxxxxxx')       // Spreadsheet name
       ->getWorksheets()
       ->getByTitle('xxxxxxxxx');      // Worksheet name
   $listFeed = $worksheet->getListFeed();
   
   // Uncomment this to find out what Google calls your column names
   // print_r($listFeed->getEntries()[0]->getValues());
   
   // Add a new blank row to the spreadsheet, using the column headings
   $listFeed->insert(['name' => 'Simon', 'age' => 25, 'gender' => 'male']);
   
   /**
    * Retrieves a Google API access token by using a P12 key file,
    * client ID and email address
    *
    * These three things may be obtained from 
    * https://console.developers.google.com/
    * by creating a new "Service account"
    */
   function getGoogleTokenFromKeyFile($clientId, $clientEmail, $pathToP12File) {
       $client = new Google_Client();
       $client->setClientId($clientId);
   
       $cred = new Google_Auth_AssertionCredentials(
           $clientEmail,
           array('https://spreadsheets.google.com/feeds'),
           file_get_contents($pathToP12File)
       );
   
       $client->setAssertionCredentials($cred);
   
       if ($client->getAuth()->isAccessTokenExpired()) {
           $client->getAuth()->refreshTokenWithAssertion($cred);
       }
   
       $service_token = json_decode($client->getAccessToken());
       return $service_token->access_token;
   }
   

Answer 2

Drive API doesn't provide any means to edit a spreadsheet, Spreadsheets API contains low level cell modification methods. As a note, you can't use Google APIs PHP Client Library to consume the Spreadsheets API.