Over the past week, I shared multiple demos in Twitter where I showcased different sites powered by Google Sheets. Several people were curious as to how I had built them, and I promised writing a Medium article (my first one, please be kind! 💜), so here it is!
Before I get into the guide, I want to explain why this may be useful or what possible uses this could have. Being an alternative to a CMS or database, it obviously has the same capabilities as an equivalent, but being Google Sheets, there are several added advantages. Let me list a few:
- Easily edit the data from Google Sheets ✍️ (super straight-forward)
- Accessing the sheet from any device (multiplatform)
- Sharing the sheet with other people (who can also contribute)
- Backups & version history (some databases don’t have this!)
- Flexibility to grow & reduce the database by adding new columns on the go
In terms of possible uses, it can obviously do absolutely anything you can think of, but here’s some that could be particularly interesting for Google Sheets:
- Running a blog (Using Google Sheets as the CMS)
- Creating a product review website ✨
- Making a list of people, websites, or any sort of content that could be contributed by users / moderators directly through the sheet
- Storing information from a form (fully customizable, w/o Google Forms)
- Tracking finances or stats in sheets & outputting them in a website 📊
With everything cleared up, here’s how you can read and write from Google Sheets through your website (& use it as a database or content management system!). I will explain how to implement the script in PHP, but in Google’s documentation you can find some information to build it in any other language.
1. Create a sheet
The first step is simple — just make a new sheet in Google sheets & make sure you keep it open or save the URL. You don’t have to make the sheet public whatsoever, so your data will be safe there! 🔒
2. Set up the Google API
In order to connect with your sheet, you need to use the Google API. Go to the Google API Console and click on “Library” in the left menu. You will be able to choose from a range of APIs within Google — in this case, you need to look for “Google Sheets API” and click on “Enable”.
Now you need to create the credentials to authenticate & access the data from your sheet 🔑. Go back to the dashboard, and click on “Credentials” in the sidebar. If it’s your first time here, you will be asked to create a new project. Click on “Create”, give it a name, and proceed. Once done, you will finally be able to create the credentials. Click on “Create credentials”, and select “Service account key” from the dropdown.
You will be redirected to a menu where you can create the service key. You can give it any name, select a role (ensure that the role you select has read & write permissions: Project>Owner is a good option), and create a unique email address. Make sure that the key type is JSON, and click on “Create”. The key will be automatically downloaded to your computer — save it inside your server in an accessible place, so you can import it later to your code.
The email address is very important, and you have to share the previously created sheet with it, giving it read & write permissions. In order to do that, just go back to the sheet, click on “Share”, and under “People” just paste the email address, select “Edit”, and click on done.
3. Authentication to the sheet
In order to connect to the sheet, we’ll be using a simple PHP script, your service key and the sheets URL. To make things easier, I am using the Google API PHP Client library that you can find in GitHub 💁♀️. You can install it using composer (follow the guide in the GitHub repository), but if you don’t have it set up, or don’t know how it works, you can download the latest release from this page, save it inside your server (in my case it’s in the “google” folder), and insert the following line on top of your script:
require_once 'google/vendor/autoload.php';Once you have imported the library, you simply have to add the following snippet to your code to do the authentication:
$client = new \Google_Client();
$client->setApplicationName('YOURAPPNAME');
$client->setScopes([\Google_Service_Sheets::SPREADSHEETS]);
$client->setAccessType('offline');
$client->setAuthConfig(__DIR__ . '/servicekey.json');
$service = new Google_Service_Sheets($client);
$spreadsheetId = "YOUR_SHEET_ID";You have to tweak the following parts from the snippet:
- ‘YOURAPPNAME’: The name of your application, it can be anything (no relation with the name of your project in the Google API Console!)
- ‘/servicekey.json’: The directory where you stored the service key that was downloaded previously
- ‘YOUR_SHEET_ID’: The ID of your sheet. For example, in this example sheet, https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit, the ID is the part that goes from /d/ to /edit, so it would be “1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms”.
4. Reading from the sheet
With the authentication set up, you can now access the data from your sheet freely 🧐. In order to read from the sheet, there are several ways to go about it (& explained more in depth in the API documentation), but I will explain the basics to read a specific range or the entire sheet.
If you want to read a specific range from the sheet, you need to add the following snippet to the script (after the authentication):
$range = 'SHEET_NAME!A1:A5';
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$values = $response->getValues();
if (empty($values)) {
print 'No data found.\n';
} else {
foreach ($values as $row) {
for ($i = 0; $i < sizeof($row); $i++) {
echo $row[$i].'\n';
}
}
}Here is a breakdown of the script:
- ‘SHEET_NAME!A1:A5’: This should include the name of your sheet and the desired range. The name of the sheet tends to be Sheet1 by default, but if you are unsure, you can always see what it is in the bottom left of your sheet. As per the part after the “!” (which you have to keep, so if your sheet is called Sheet1, put “Sheet1!”), you simply have to specify a range from a cell to another, just like you would do in Google Sheets for other purposes.
- Foreach loop: This part simply iterates through the generated array and outputs each cell individually. From that point, you are free to use the data you retrieve for any purpose.
Alternatively, if you want to read the entire sheet, simply replace the range with the name of the sheet, likewise (without the exclamation mark):
$range = 'SHEET_NAME';5. Writing to the sheet
Updating or writing values in the sheet is also quite straight-forward, similar to what I previously explained for reading 📝. I will show you how to update or add values to a specific range within a sheet, and how to add new rows.
Let’s begin by working within a range. Here is the script:
$range = 'SHEET_NAME!A1';
$body = new Google_Service_Sheets_ValueRange([
'values' => 'YOUR_VALUE'
]);
$params = [
'valueInputOption' => 'USER_ENTERED'
];
$result = $service->
spreadsheets_values->update($spreadsheetId, $range,
$body, $params);As you can see, we are using some parameters that were present in the reading process.
- ‘SHEET_NAME!A1’: Works the same way as previously explained in the reading section.
- ‘YOUR_VALUE’: The value that you want your cell to have. Keep in mind that it is an array, so you may add as many values as cells you have selected within the previous range.
Now, let’s see how we can easily add a row to a sheet:
$range = 'SHEET_NAME';
$valueRange= new Google_Service_Sheets_ValueRange();
$valueRange->setValues(["values" => ['YOUR_VALUE1', 'YOUR_VALUE2']]);
$conf = ["valueInputOption" => "RAW"];
$ins = ["insertDataOption" => "INSERT_ROWS"];
$service->spreadsheets_values->append($sheet, $range, $valueRange, $conf, $ins);It is very similar to the previous script. Here’s the parameters you need:
- ‘SHEET_NAME’: The name of your sheet
- ‘YOUR_VALUE1’ (‘YOUR_VALUE2’, ‘YOUR_VALUE3’…): The values you want to append to the sheet. You may add more into the array — they will be added one next to another in the new row.
This was a simple guide to work with Google API for Sheets in PHP, hopefully it was useful! Feel free to ask any question below if you get stuck somewhere or have feedback 😊
Now, some of you might be a bit confused — didn’t I mention making GitHub Pages or any static website platform dynamic? It’s not like you can add PHP files to GitHub Pages! 😫
Well, you certainly can’t add PHP to GitHub Pages, BUT (!) you still have a couple of options:
- Using JavaScript to connect with the Google API (explained in the documentation). The problem with that is that your API keys will be exposed, as well as your sheets URL. If you are working with a public API key or a public sheet, this is not a big deal, but the examples I showcased required the privacy factor, so this is not a good idea.
- Hosting the PHP script in Heroku or another server & making calls to it using AJAX in GitHub Pages. This can be completely secure by not only keeping the API key & sheets URL in that server, but also only accepting calls from a specific domain (in this case, your own website within GitHub Pages)
But, what if you don’t have a server (if you want something proper it can be costly) or don’t know how to set it up? 🤔
No problem! These past weeks, at the same time that I’ve been working on the different demos, I’ve been developing a platform that allows you to submit your sheets ID, API keys, allowed URL (your GitHub Pages or static domain) and generates the code you can embed to your site to read or write from the sheet. It isn’t out yet (I want to ensure it’s polished!) but I also would like to write a little bit about how to make this yourself (open sourcing the code) so you don’t have to use my platform whatsoever, and can set it up in your own server.
I will also hopefully find the time to make some more articles with some examples & guides on how to create some of the demos I made. So far I have a form & blog in mind, but you are free to make suggestions as well — I really appreciate it! Thanks for reading!
📝 Read this story later in Journal.
🗞 Wake up every Sunday morning to the week’s most noteworthy Tech stories, opinions, and news waiting in your inbox: Get the noteworthy newsletter >
