<

How to Display a Single Cell from Google Sheets in WordPress

Sometimes you need to publish dynamic data in WordPress. Whether it’s changing prices, the score for a game, or stock prices. Google Sheets is a great way to achieve this. In this tutorial, I’ll show you how to display the value of a single cell in Google spreadsheets using a simple shortcode. Here what I’ll cover step by step.

  1. Create or Select a Google API Project
  2. Enable the Sheets API
  3. Create a new API key
  4. Make the spreadsheet shareable via link
  5. Get the spreadsheet ID
  6. Plug in your details into code
  7. Specify a cell location in Google Sheets
  8. Alternatively, create a “named range”
  9. Use the shortcode

This may sound like a long list, but you only need to do this stuff once. And I’ll hold your hand all through the way. So let’s get started!

Step 1: Create or Select a Google API Project

Google allows access to its services via strings called “API Keys”. You’ll need to generate one to use it for this. The purpose is basically to keep track of who’s doing what. You can delete a key if it’s being misused. But first, we need to enter an existing Google API project. To do this, visit the Google developers console here: https://console.developers.google.com.

On the top left, you will see either “My Project”, or you’ll need to create one yourself.

Create a new Google API Project

Step 2: Enable the Sheets API

Once your project is selected, click the blue “Enable APIs and Services” box next to the dashboard, as shown in the previous screenshot. This will bring up a list of all the Google services available to you. Scroll down till you find the “Google Sheets API”. Select it.

Select the Google Sheets API

Now just click the big blue “Enable” button here:

Enable Google Sheets API

This will take a few seconds to activate. Now you’ll be able to access Google Drive spreadsheet data using the API. When it’s done, we move to the next step.

Step 3: Create a New API Key

This key allows us to identify ourselves each time we use a Google service via the API. To generate it, first move your mouse pointer to the left of the screen so that the sidebar flies out. Now select the “Credentials” menu item with the image of a key next to it:

Select Credentials in Google Developer Console

Here under the “Credentials” tab, click the “Create Credentials” blue button with the arrow next to it. Now select “API Key”:

Create a new API Key

This will automatically create your API key in a new dialogue box. You can click the icon to the right to copy it to your clipboard:

Copy the API Key to Clipboard

Note that you shouldn’t really share this key with anyone. If you do, and they happen to misuse it, your account will be blamed. Which is why I’ve blacked it out myself. An optional step is to place restrictions on the key for how it will be used. In this example, I’ve chosen not to place any restrictions, because that’s not the purpose of this tutorial. But you should look into it.

Step 4: Make your Spreadsheet Shareable by Link

For this to work, your Google sheet needs to be shareable via link. You can do this by going to your spreadsheet and clicking the blue “Sharing” button on the top right. This will bring up the sharing options where you can turn link sharing on so that it looks like this:

Enable Link Sharing for the Google Spreadsheet

This isn’t exactly the same as making your sheet public. It’s impossible for anyone to guess the URL, so they can’t access it unless you share it with them explicitly.

Step 5: Get the Spreadsheet ID

Every Google Drive spreadsheet is identified with a long string that’s present in the URL shown in your browser bar when you’re editing it. For example, here’s the URL of a sheet on my site:

Get the Spreadsheet ID

The string is the portion before the “edit” part of the URL between two slashes “/” and “/”. It’s the only long alphanumeric sequence of characters in the URL, so you can’t miss it! Take a note of this spreadsheet ID. We’ll be using it in the next step.

Step 6: Insert this Code in functions.php or your Custom PHP Plugin

WordPress allows us to insert custom code into it. If you don’t know how to do this, you can read my earlier tutorial on inserting code snippets. In that, you have two options – functions.php, or a custom PHP plugin (I prefer the latter since it’s very useful).

Here’s the code:

function sheet_value_shortcode($atts) {
    $API = '[Insert API Key Here]';
    $google_spreadsheet_ID = '[Insert Google Spreadsheet ID Here]';
    $api_key = esc_attr( $API);

    $location = $atts['location'];

    $get_cell = new WP_Http();
    $cell_url = "https://sheets.googleapis.com/v4/spreadsheets/$google_spreadsheet_ID/values/$location?&key=$api_key";	
    $cell_response = $get_cell -> get( $cell_url);
    $json_body = json_decode($cell_response['body'],true);	
    $cell_value = $json_body['values'][0][0];
    return $cell_value;
}
add_shortcode('get_sheet_value', 'sheet_value_shortcode');

In this code, replace the text in bold and blue with the pieces of information you obtained before. We got the API key in Step 3, and the Spreadsheet ID in Step 5. This shortcode will allow you to display a single cell from Google sheets!

Now the boring part is done. The code is in place and it’s time to specify which cell you want!

Step 7: Specify the Cell Location in Google Sheets

Every Google spreadsheet is made up of “sub” sheets. These are tabs at the bottom, and each has a name. By default, they’re named “Sheet1”, “Sheet2” etc.

Cells, on the other hand, are uniquely identified by their column letter (A, B, C etc) and the row number (1, 2, 3). So we have multiple cell locations – A1, C32, H9 etc.

To specify the location of a cell, we just give the Sheet name and the cell number, separated by an exclamation mark (!).

So for example, the following location:

Sheet1!A1

Refers to the first sheet, and the very first cell in the top left. We can have another location like Sheet3!G5 which refers to the fifth sheet (default name), and cell number G5.

Keep in mind that we can rename sheets to make them more relevant. In that case, simply use the changed name instead of “Sheet1”, “Sheet2”, etc.

Step 8: (Optional) Use a Named Range to Refer to a Cell Instead

If you’re going to be using cells from Google Sheets a lot, it’s easy to lose track of what you’re referring to. You’re not going to remember that Sheet5!F78 refers to the age of Mike Trout for example. Instead, I find it easier to create a “Named Range” and specify the cell in that way. How do we do this?

Open your spreadsheet, and click the “Data” menu item at the top:

Data Menu for Named Ranges in Google Sheets

Now select “Name Ranges” as shown in the screenshot.

A section will pop out of the right hand side of the screen. Click “Add a range”, and give your cell a meaningful name. In my case, I’ve called it “first_cell_first_row” to refer to Sheet1!A1.

Create a Named Range

Enter the location of your cell as determined in Step 7 and click “Done”.

You can now refer to this named range by its name instead of the unintuitive sheet!cell format. You can create as many named ranges as you want:

List of all Named Ranges

Step 9: Use the Shortcode to Display a Single Cell

The code we inserted in Step 6, allows us to use a shortcode of this form:

[get_sheet_value location="Cell Location"]

Replace the Cell Location in bold and blue with the name of the cell you got either in Step 8, or Step 9 (Keep the double quotes). In my example, “first_cell_first_row” or Sheet1!A1 has the value “ID” as shown here:

Value is "ID" for First Cell

I use the shortcode in my WordPress editor like this in two ways. The first is with the named range, and the second is by using Sheet1!A1:

Using the Shortcode in the WordPress Editor

And here’s the output as expected:

How to Display a Single Cell from Google Sheets

As you can see, both shortcodes give me the value “ID” which as we say, was the value of the cell I referenced. You can use this shortcode as many times as you want anywhere in your WordPress posts or pages, and it will display the dynamic value of the single cell the Google Spreadsheet of your choice!

How it Works

We’re able to extract the data using the Google Sheets API v4, which allows us to construct a URL to get the cell or range we want. The format is like this:

https://sheets.googleapis.com/v4/spreadsheets/$google_spreadsheet_ID/values/$location?&key=$api_key

This takes the following parameters:

  1. The spreadsheet ID
  2. The range
  3. The API key

The “range” can refer to any range of cells. We specify the top left and the bottom right cell names separated by a colon (:). It’s just that for this shortcode, I just want to display a single cell value.

The annoying part is that the data returned is in json format, as you can see here:

Google Sheets API Returns Json

So we can even display a table with the returned values if we specify a range. It’s just that parsing through json and creating a table out of it with <table>, <th>, and <tr> tags is a pain the ass. And the formatting of the original cells doesn’t carry over either. So the tables you get are pretty limited, and you can’t have detailed customization. For this reason, I prefer to use my own tables solution and just extract the individual values from Google Sheets instead.

I’ve designed my shortcode with minimal options since it’s meant to be used very often – possibly multiple times throughout a single WordPress page or post. And I wanted it to be easy to write and understand. You can of course, change the shortcode to suit your needs.

Good luck with your dynamic WordPress site!

About Bhagwad Park

Bhagwad has been writing WordPress tutorials for years. He also creates tutorials on Linux server administration, and has a ton of experience writing about and using web hosting products! Send me an e-mail!

Comments

  1. Fantastic! I have been looking for such process to display my upto date data from google sheets into my wordpress site’s page. Thank you very much Mr. Bhagwad Park. I love this. Please do mail me if you have any other updates. The way you explain is also easy to understand.

    Reply

Speak Your Mind

*