In 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.
We’ll be using PHP and Google Sheets API v4. So, let’s check if the ground is fit for the mission.
- You are logged in to browser with a Google Account. Create one if required.
- PHP 5.4 or greater with the command-line interface (CLI) and JSON extension installed.
- Composer is installed.
Setting up Google Sheets API
If you don’t already have prepared a project and authentication, follow the steps to prepare one –
- 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.
- 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.
- Create a service account with owner privilege
- 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 –
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_TOKENvalue with the Key ID of service account. Find it here if not sure what it is.
SHEET_IDvalue with your sheet ID.
CLIENT_SECRET_PATHis 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.
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?