Creating row in Google Sheets with PHP

README.md

Creating row in Google Sheets with PHP

Installation and getting started

init composer

Enter this command in a terminal:
composer require google/apiclient:^2.0

init Google Console Developers Project

• Go to Console developers
• Create a new project
• Enter a name
• Go to the library
• Search for "Google sheets API"
• Click on enable
• Go to Console developers
• Select the new project
• Go to the identifiers/login
• Click on "Create identifiers" → "Service account key"
• Enter "New service account" and for role select "Project : Owner" and for "Key type" select "JSON"
• Retrieve the JSON file, rename it to credentials.json and place it at the root of the project

Configuration and execute

• Replace the variable $spreadsheetId and $newRow and $applicationName
• And start it with php sheets.php

Author

• Avi Mimoun

credentials.json

{
  "type": "",
  "project_id": "",
  "private_key_id": "",
  "private_key": "",
  "client_email": "",
  "client_id": "",
  "auth_uri": "",
  "token_uri": "",
  "auth_provider_x509_cert_url": "",
  "client_x509_cert_url": ""
}

sheets.php

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

$spreadsheetId = "8dsdfR0ucPAE_SEWQ4G_1KpzhnfSCqn6mdfdfqdfQGirs";
$applicationName = "gsheets";
$newRow = ["Hello", "World", "it's", "a", "new", "row"];

/**
 * Retrieves a client for Google Service Sheets
 *
 * @param string appName
 * @return Google_Service_Sheets
 */
function getClient($appName){
    $client = new Google_Client();
    $client->setApplicationName($appName);
    $client->setScopes(Google_Service_Sheets::SPREADSHEETS);
    $client->setAccessType('offline');
    $client->setAuthConfig(__DIR__.'/credentials.json');
    return new Google_Service_Sheets($client);
}

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

// START APPLICATION
addRowToSpreadsheet(
    getClient($applicationName), $spreadsheetId, 0, $newRow
);

The credentials.json file must be replaced with the generated file at Console Developers