How to Display a Single Cell from Google Sheets in WordPress

Last updated on

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

I've been writing about web hosting and WordPress tutorials since 2008. I also create tutorials on Linux server administration, and have a ton of experience with web hosting products. Contact me via 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

  2. Thank you for this tutorial. I was going down a rabbit hole with oAuth, trying to decipher Google’s API docs, when it occurred to me a simpler solution was likely available. Your explanation works perfectly and is far simpler. Kudos!

    Reply

  3. Michael Flores says:

    Hi Bhagwad!
    This is amazing! This is the closest thing that I want to do!
    If you don’t mind helping me out, I need to query google sheets from CF7 and return the keyword sent from the form and then post the column where the keyword was found.
    My English is really bad, I do apologize

    Reply

    • Glad you found it useful!

      Not sure how to do what you want though. We’ll need to send information from WordPress to Google Sheets and form a query. I’m sure it’s possible. I’ll just need to think about it!

      Reply

  4. Hi Bhagwad.

    Excellent article and very useful. I am trying to make a little change to the shortcode, based on the current url of the wordpress page where I am using the shortcode. For example if the dynamic url of the page is: http://mysite.com/?abcxyz=123

    I would like to use the ‘123’ in the shortcode. How can I do that?

    Thanks in advance.

    Reply

  5. Bhagwad, thanks for a superfast reply.

    Even though I’m not very adept in PHP, yet, my real problem would be integrating the parsing of the url with your shortcode script to build a shortcode that could dynamically use the url to update the cell reference in google sheets.

    What part of your shortcode function would I be modifying to get the URL parameter dynamically updated/inserted in the shortcode?

    Reply

    • First, you’d need to get the current URL
      Second, you’d need to create a variable for storing the cell reference and fill it.

      $url= (isset($_SERVER[‘HTTPS’]) && $_SERVER[‘HTTPS’] === ‘on’ ? “https” : “http”) . “://$_SERVER[HTTP_HOST]$_SERVER[REQUEST_URI]”;

      $parts = parse_url($url);
      parse_str($parts[‘query’], $query);
      $cell_reference=$query[‘abcxyz’];

      Then use $cell_reference in the shortcode PHP. Something like that, anyway!

      Reply

  6. Hi Bhagwad. Unfortunately I was unable to implement this.

    What I am trying to do is, land on a wordpress webpage with a predfined URL. For example https://mysite.com/somewebdirectory/ArbitraryNumberofGoogleSheetsRow and then have a PHP script auto-update/populate your Shortcode, thus displaying the data of the googlesheets with the help of row number that is to be gotten/captured from the URL.

    I tried but haven’t been able to do it. Let me know if you have some time and would like to assist me for a fee. You can write to me or leave your email here if you wish.

    Thanks again.

    Reply

  7. Hello,

    Thanks for the great explanation of this option! It all works.
    The only downside is that if i change the values in my google sheet, the wordpress page still keeps the old values. How can i make it possible so that the page auto updates when my sheet is changed?

    Thanks!

    Reply

    • That is due to some sort of caching. Either a plugin on your site, or caching by your host. Or Cloudflare (if you use it). Try making a change to the post and resaving it. That might clear the caches.

      Reply

      • Ye, is does work if i resave it. But it will have to change twice a day. Is there something i can add to the Custom Plugin so it will clear the cache like on 2 times a day (maybe certain time) ?

        I don’t really wanna go in the backend and re-save twice a day.

        Reply

        • Caching is something that happens in the backend. It could be your hosting provider, in which case there is nothing you can do.

          I would check your caching plugins to see if they provide a way to clear the cache regularly, or programmatically. Or see if your hosting provider gives that option…

          Reply

  8. Thanks for the great information Bhagwad.

    I am curious, how would you go about calling a dynamic cell location in your shortcode?
    If I have 10,000 rows and I want the shortcode to display the next row everytime it is loaded, beginning from row one? Once there are no more rows in the document, return null.

    Appreciate your knowledge sharing.

    Reply

    • Hmm…that would be a little more involved. We’d need to store the value of the cell and increment the row on each display. So a new key/value pair in say the “wp_options” table using these functions:

      add_option()
      get_option()
      update_option()

      Basically something like this:

      1. Create new option key/pair with initial cell location
      2. On usage of shortcode, retrieve the option and create the URL
      3. Increment the row number by one
      4. Update the option in the table

      Reply

  9. I have tried to setup you solution in wordpress but it does not show any value.
    Pases the code into functions.php with the sheet url id and the api key and wrote the value ark!A1 in the shortcode. Can you help me?

    Reply

  10. Am I missing something? Do I need to change the url of the sheet in the function info here : $cell_url ? I used the info to created this shortcode: [get_sheet_value location=”Donations!E14″]. I don’t get any errors but I also don’t see any data. Thank you for your help. This is my first attempt at this. I can send you a link to the site privately but it is ‘locked’ down while under construction. Thank you for your help!

    // $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); //

    Reply

  11. This is so useful I can’t even begin to describe it!

    Thank you!

    Quick question – how would I run multiple instances of this? For example, if I wanted 1 shortcode to display cell contents from SpreadsheetXYZ, and another shortcode to display cell contents from Spreadsheet123

    (different spreadsheets, not sheets within the same spreadsheet)

    Reply

    • Two options:

      1. Modify the shortcode a bit to include the spreadsheet ID as an additional parameter
      2. Create a new shortcode (cumbersome if you have many different spreadsheets)

      Reply

Speak Your Mind

*