Google API/how-to/php/tutorial

Store data to Google Sheets using PHP

Posted on by Anis Ahmad / 46 Comments

Google SheetsIn some cases, when you need to dump some data to somewhere easily accessible, pushing to Google Sheets can be a good option. You can store data to Google Spreadsheet from cron jobs, background workers or even simple form submission. For example, in my case, I am logging some information from mailgun hooks.

While I were doing that, it seems like this easy task took more time then expected only because of lack of documentation. So, trying to write down an easy to follow tutorial that can make you set and pushing data to goggle Spreadsheet within 20 minutes.

Check Prerequisites

We’ll be using PHP and Google Sheets API v4. So, let’s check if the ground is fit for the mission.

Setting up Google Sheets API

If you don’t already have prepared a project and authentication, follow the steps to prepare one –

  1. Go through this wizard to create or select a project in the Google Developers Console and automatically turn on the API. Then continue to Go to credentials.
  2. On the Add credentials to your project page, set the following values –
    Which API are you using? Google Sheets API
    Where will you be calling the API from? Other non-UI
    What data will you be accessing? Application Data

    Note: We are selecting the above choices because our intention is to push data from some background script that runs in a server without user interaction. Your choices may vary if your plan is different.

  3. Create a service account with owner privilege
  4. In final step, you should get 2 things, a JSON file (API Client Secret) and an API Key ID for created service account. Download the json file (if not automatically downloaded) and rename it to sheets_api_secret.json. Then move it to project directory.

Now we are ready to access and manipulate Google sheets programatically. Let’s go .

Ping – check if Sheets API Authentication is working

Create a Spreadsheet on Google for our test, name it “Access Log” or something and take it’s sheet ID. If you’re not sure about the sheet ID, copy the marked segment from sheet url –
Google Sheet ID

Then, run the following command in you project directory to install the Google Client Library for PHP:

php composer.phar require google/apiclient:^2.0

At last, create a file app.php and put the following in code in it. Assuming that the composer vendor directory and the sheets_api_secret.json file are in the same directory of this file.

setApplicationName(APPLICATION_NAME);
$client->setScopes(SCOPES);
$client->setAuthConfig(CLIENT_SECRET_PATH);
$client->setAccessToken(ACCESS_TOKEN);

$service = new Google_Service_Sheets($client);

$sheetInfo = $service->spreadsheets->get(SHEET_ID)->getProperties();

print($sheetInfo['title']. PHP_EOL);

Before running, ensure the following points to make it work –

  • You have replaced the ACCESS_TOKEN value with the Key ID of service account. Find it here if not sure what it is.
  • Replaced SHEET_ID value with your sheet ID.
  • CLIENT_SECRET_PATH is set correctly to the client secret file.
  • At last, the Sheet is shared with the email of service account given Edit permission.

Now, run this script from command line.

php app.php

If it’s printing the name of sheet (e,g, Access Log), Congratulations! Your authentication is working and you are now ready to manipulate this spreadsheet!

Add data rows to Google Spreadsheet

Now we’ll write a few rows to the spreadsheet. Remove all the lines after getting the $service (line 23) and add the following lines.

$options = array('valueInputOption' => 'RAW');
$values = [
    ["Name", "Roll No.", "Contact"],
    ["Anis", "001", "+88017300112233"],
    ["Ashik", "002", "+88017300445566"]
];
$body   = new Google_Service_Sheets_ValueRange(['values' => $values]);

$result = $service->spreadsheets_values->update(SHEET_ID, 'A1:C3', $body, $options);
print($result->updatedRange. PHP_EOL);

Here, in the $option array, we are setting that the values should be treated as raw text, no formula or reference. In $values, each array of 1st level is a row and each value in 2nd level is a column value. And the last important thing is the cell range to update, the 2nd param of $service->spreadsheets_values->update method. If the range don’t cover the required space for input values, you’ll get an error.

Ok, let’s run it again. If it prints something like “Sheet1!A1:C3” maybe we’ve updated the sheet successfully! Go check the sheet to get the feeling of being .

Append data after last filled row of Google Spreadsheet

Now, the biggest challange you may see is setting the range. How will you track of filled rows? How to find next empty row? How to get the…

Okay, the solution is as easy as bigger the problem seems to be. Set the header row of sheet manually and always use the range of the header rows only. And use append function instead of update. This will automatically find the next empty row and insert the columns there.

$values = [["Anis", "001", "+88017300112233"]];
...
$result = $service->spreadsheets_values->append(SHEET_ID, 'A1:C1', $body, $options);

So, now every time you insert a row, that will be appended to the existing rows.

It was easy right?

BTW, don’t forget to check the Google Sheets API Reference and Client library for PHP.

AssalamuA’laikum-Wa-Rahmatullah!

, ,
Anis Ahmad

46 Comments

  4. Hi,

    How do I remove the SSL Certificate?

    I’m receiving a “Fatal error: Uncaught exception ‘GuzzleHttp\Exception\RequestException’ with message ‘cURL error 60: SSL certificate problem: unable to get local issuer certificate” when testing on localhost

    Thank you! Great tutorial btw.

    Reply

    1. So, the error message says, “Request had invalid authentication credentials. Expected OAuth 2 access token, login cookie or other valid authentication credential. See https://developers.google.com/identity/sign-in/web/devconsole-project.”

      I guess you may have either not set the credentials properly or your application settings are not appropriate for running from command line. As you are trying to run as php app.php from the command line, check your application is registered as non UI, backend app.

      For this type of app, static credentials should be provided for Authentication and need to set them in your code before calling google’s API. For example, in app settings on google app console, the selection of “Where will you be calling the API from?” should be “Other non-UI“.

      Reply

      2. API keys ->
        Key :- AIzaSyCo3__7MgOJBeqPMaTeocYS1M314w1QClY
        Name:- formDataToGoogleSheet3
        Creation date :- Feb 9, 2017
        Restriction :- None
        Key :- AIzaSyCo3__7MgOJBeqPMaTeocYS1M314w1QClY

        OAuth 2.0 client IDs ->

        Name:- formDataToGoogleSheet3
        Creation date:- Feb 9, 2017
        Type:- Web application
        Client ID :- 1004247683933a8smr6vq966i92naeijd2fuelqmltlsp.apps.googleusercontent.com

        Service account keys ->
        ID :- df53717b85940bd7ea35893276938ec6f553cc30
        Creation date:- Feb 9, 2017
        Service account:- formDataToGoogleSheet3

        Reply

        1. Here Is My Code:

          define(‘SHEET_ID’, ‘1X_UeT6EA6HE0O4wSe-ZzAElPVJcLupEAMNM8fWMqpGA’);
          define(‘APPLICATION_NAME’, ‘Manipulating Google Sheets from PHP’);
          define(‘CLIENT_SECRET_PATH’, __DIR__ . ‘/sheets_api_secret.json’);

          //define(‘ACCESS_TOKEN’, ‘df53717b85940bd7ea35893276938ec6f553cc30’); //Key ID of service account.
          define(‘ACCESS_TOKEN’, ‘AIzaSyCo3__7MgOJBeqPMaTeocYS1M314w1QClY’); //API KEY
          define(‘SCOPES’, implode(‘ ‘, [Google_Service_Sheets::SPREADSHEETS]));
          // Create Google API Client
          $client = new Google_Client();
          $client->setApplicationName(APPLICATION_NAME);
          $client->setScopes(SCOPES);
          $client->setAuthConfig(CLIENT_SECRET_PATH);
          $client->setAccessToken(ACCESS_TOKEN);

          $service = new Google_Service_Sheets($client);

          $sheetInfo = $service->spreadsheets->get(SHEET_ID)->getProperties();

          print($sheetInfo[‘title’]. PHP_EOL);

          Reply

          1. After Firing Command :-
            aministrator@Chrysalis-22:/opt/lampp/htdocs/formDataToGoogleSheet3$ php app.php

            Gives Error:-
            PHP Fatal error: Uncaught exception ‘Google_Service_Exception’ with message ‘{
            “error”: {
            “code”: 400,
            “message”: “Client project not found. Please pass a valid project.”,
            “errors”: [
            {
            “message”: “Client project not found. Please pass a valid project.”,
            “domain”: “global”,
            “reason”: “badRequest”
            }
            ],
            “status”: “INVALID_ARGUMENT”
            }
            }
            ‘ in /opt/lampp/htdocs/formDataToGoogleSheet3/vendor/google/apiclient/src/Google/Http/REST.php:118
            Stack trace:
            #0 /opt/lampp/htdocs/formDataToGoogleSheet3/vendor/google/apiclient/src/Google/Http/REST.php(94): Google_Http_REST::decodeHttpResponse(Object(GuzzleHttp\Psr7\Response), Object(GuzzleHttp\Psr7\Request), ‘Google_Service_…’)
            #1 [internal function]: Google_Http_REST::doExecute(Object(GuzzleHttp\Client), Object(GuzzleHttp\Psr7\Request), ‘Google_Service_…’)
            #2 /opt/lampp/htdocs/formDataToGoogleSheet3/vendor/google/apiclient/src/Google/Task/Runner.php(181): call_user_func_array(Array, Array)
            #3 /opt/lampp/htdocs/formDataToGoogleSh in /opt/lampp/htdocs/formDataToGoogleSheet3/vendor/google/apiclient/src/Google/Http/REST.php on line 118

        2. Thanks for sharing more context. Before digging much, I see your type of “OAuth 2.0 client ID” is “Web application”. So it won’t work from command line.

          You should create new Authentication keys for command line app. Have you followed the “Setting up Google Sheets API” of this post?

          BTW, I recommend sharing long debug text or error messages using external snippet. You may use pastiebin (for code), justpaste.it (for text) or even gist.

          Reply

  7. yes, from this post only I have followed. I Have Created “Authentication keys for command line app” twice. anyway thanks for support.

    Reply

  10. Can I use the above mentioned method to my application which enters data from a form to a database and I also want to enter in Google sheets or I have to create a web application specific credentials. I mean is it will be non UI as you mentioned or Web Application ?

    Reply

    1. If you’re submitting your form to the server, then saving data to the database and then trying to push something to google sheet, yes, this is the way to go. Alternatively, if you want to push data directly from the web app (e,g, from js), you’ll need web application specific credentials.

      Reply

      1. Yes I will get student name and roll no then I want to save it to database with PHP and then after entering data in to database I want to send it to Google Sheets with PHP. So I will use your method ? And if so then there is no option to choose from php web application ?
        Thanks You Very Much

        Reply

  11. Hi. I am new to composer. I have a couple of doubts regarding this.

    I am creating a website form and I want the data from the form to be saved into google spread sheet. Suppose, I have installed composer and and everything is working fine in my PC (development machine). Now I want to upload this working files to a server.

    1. Will the codes work if I upload the whole project folder to a web server?
    2. Will I need to install composer in the web server? I am using a shared server.
    3. Will the person who fills in the form needs to be logged into google, in order for the form filled data to be saved in google sheet?

    Thank you for the tutorial.

    Reply

    1. AssalamuA’laikum.

      If your deployment server has no issues with using composer, you should put the only the composer.json and composer.lock files (not the vendor directory). Then run composer install to download the vendor libraries. Otherwise, (not recommended though,) you can move the whole project along with the vendor directory.

      No, “the person who fills in the form” do not need to be logged in to google. Because, after receiving the form data in the backend, you’ll push them to google sheet using your own authentication.

      Reply

  12. Hello Anis,

    Would you please provide code sample to add new sheet in spreadsheet.

    I have tried below code but it’s not working.

    $service = new Google_Service_Sheets($client);

    //pr($service);
    $spreadsheetId = ‘You spredsheet id’;
    $json_request_string = ‘{“requests”:[{“addSheet”: {“properties”: {“title”: “Project tasks”}}}]}’;
    //$requests = json_decode($json_request_string);

    // TODO: Assign values to desired properties of `requestBody`:
    $requestBody = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest();
    $requestBody->setRequests($json_request_string);

    $response = $service->spreadsheets->batchUpdate($spreadsheetId, $requestBody);

    Thanks

    Reply

  14. Nice tutorial, Anis!

    By the way, how can I append data to a specific sheet?
    Since I am having 4 sheets in a single spreadsheet, I am not sure how to select the desired sheet in PHP.

    Looking forward to hear from you soon!

    Wester

    Reply

  15. As per your description update not working. Can you check? what’s my fault?

    $values = [

    [“2017-09-9”, “262902”, “275”, “77933”, “771265”, “3610”, “2268”, “8801”, “812”, “5011”, “2413”, “27”, “120”, “3845”]
    ];
    $options = array(‘valueInputOption’ => ‘RAW’);
    $body = new Google_Service_Sheets_ValueRange([‘values’ => $values ]);

    $result = $service->spreadsheets_values->update(SHEET_ID, ‘Sheet1!A101:N101’, $body, $options);
    print($result->updatedRange. PHP_EOL);

    Reply

  16. Thank you for this! I spent several hours trying to follow Google’s documentation and got nowhere. This tutorial had me up and running in less than 30 minutes.

    Reply
  18. Pingback: Insert Data ke Google Sheet dengan Goolge API | Blog coba-coba

  19. Hi there,

    from where I can get this: “At last, the Sheet is shared with the email of service account given Edit permission.” ?

    Should be a @partnercontent.gserviceaccount.com email, right? I can’t find it!

    Reply

  21. Hi,

    This code is working on localhost properly. But when I migrated code on live server, it is showing page isn’t working..

    Regards
    Dilip

    Reply

  22. I’m trying your update example and I got error:
    Request had insufficient authentication scopes.
    “status”: “PERMISSION_DENIED”

    Reply

  23. I have a list of over 3000 rows that I would like to add into Google Sheets. I’m running into a over quota limit error: “Insufficient tokens for quota ‘WriteGroup’ and limit ‘USER-100s'”

    Foreach row I’m calling the update command. Looking at the google sheet it populates the records until I hit 100. Any suggestions?

    Code:

    foreach( $value as $csv_result ) {

    $updateRange = ‘A’.$currentRow.’:O’.$currentRow;
    $updateBody = new \Google_Service_Sheets_ValueRange([
    ‘range’ => $updateRange,
    ‘values’ => [$csv_result],
    ]);

    $sheers->spreadsheets_values->update(
    $spreadsheetId,
    $updateRange,
    $updateBody,
    [‘valueInputOption’ => ‘USER_ENTERED’]
    );

    $currentRow++;
    }

    Reply

  25. hi
    I can easily insert data into the end of the table, or update a specific area.
    How can I insert data in the beginning of the table? I need to insert one row at the top of the table. For some reason, it is always added only to the end.
    Сan someone solve such a problem?

    Reply

  26. Fatal error: Uncaught Error: Call to undefined function setApplicationName() in /Applications/XAMPP/xamppfiles/htdocs/settlSpreadSheet/app.php:2 Stack trace: #0 {main} thrown in

    this error popup.

    I follow your instruction

    Reply

  27. Hello, I’ve tried using append and specified the location to ‘B7:D7’ but when there is a value on that cell it will instead append the value to ‘A42:C42

    Reply

Leave a Comment

Your email address will not be published. Required fields are marked *