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

  12. Thanks for the tutorial, this is really going to help me

    I want to use this to populate a few tables in WordPress from GSheets … if I’m bringing in about 55 cells with 55 shortcodes will this slow down my site?

    Reply

    • You’ll have to try it and see! If the cells are continuous, you might consider importing tables instead of single cells. You’ll just need to parse the JSON differently. See the “How it works” section for more.

      Reply

  13. Got the code to work on my site. My next task is make the code update at a regular interva. In my case 5 seconds. Can you give at hint as to how I would be able to make the code refresh without page reload? Thanks again.

    Reply

  14. Here’s a correction to my previous post. Here is the How it works code:

    https://sheets.googleapis.com/v4/spreadsheets/10t70hKFOGwB4vwrSQgOwMdLbxSkPFxKMIBs03-0GkSQ/values/Sheet1!A1?&key=AIzaSyB8vHRfpWGsIL4HkO40fdXplD7wf4v4d8A

    That should return a value of 199.95 in my site. But I get a blank…

    Reply

  15. Exactly what I was looking for! Easy to follow and implement considering I am pretty new at this.

    I did run into a problem that seems to come from Google’s side. When adding restrictions to the API in Googles developer console the table on my website goes blank. I tested this a couple times to make sure.
    With restrictions – blank table.
    Without restrictions – table shows data as it should.

    Any ideas?

    Reply

  16. This is what I’ve been searching for a very long time.

    I’m trying to implement this so that each WordPress Page gets its own Google Sheet.

    Company ABC = GoogleSheetABC
    Company CDE = GoogleSheetCDE
    and so on…

    Is it possible to pass, say, [get_sheet_value location=”GoogleSheetFileName”, “SheetColumnRow”,]
    Or is there a better way to implement this?

    Sorry, I’m not really good at PHP.

    Reply

    • You’ll have to modify the shortcode to pass the spreadsheet ID as well instead of just the location. Something like:

      function sheet_value_shortcode($atts) {
      $API = ‘[Insert API Key Here]’;
      $api_key = esc_attr( $API);

      $location = $atts[‘location’];
      $google_spreadsheet_ID = $atts[‘gsid’];

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

      Then call the shortcode like this:

      [get_sheet_value location=”Cell Location” gsid=”[sheet ID]”]

      Reply

    • Have a look at the plugin called Private Content: https://lcweb.it/privatecontent

      I’m using this for clients and each client gets their own private page, kind of like what you’re trying to do with Company ABC, CDE, etc. Each company would have their own private page. You can then use this Sheets shortcode to reference data if you choose or load it all into the wordpress database.

      Reply

  17. Bhagwad, thank you for the great tutorial. I’ve been using it for a while now but I noticed I the time from HTTP API calls increasing my site load speed greatly due to the google sheets API. Now I’m not very strong at coding and have limited technical knowledge, but is it possible to cache these requests to the Google Sheets API? E.g. can I make it refer only once a day so that it does not seek to update the values every single day? That would greatly speed up my site (it is around 2 seconds now whereas its more like 0.2 seconds with the API).

    I did some google and found something about “transients” that could perhaps be used. Do you know if it is possible to somehow cache the API request so that I can speed up my loading again? Thanks a lot!

    Reply

    • A better solution might be to implement a page caching solution like WP Super Cache, W3 Total Cache, or WP Rocket. Your hosting provider might have such a functionality available as well.

      Basically it means that the page isn’t generated from scratch each time. The HTML is saved and served for subsequent requests. Try it out and see if that solves your problem!

      Reply

      • Thanks for the quick reply! I have tried W3 Total Cache and enabling all the caching, but it simply does not seem to work. It seems as if the API call is always made (I use the plugin: “Query Monitor” for this). It is as if the custom plugin ‘overrules’ the caching. Do you know if there is any way to work around this?

        Reply

        • Strange. There’s no reason that should happen…but I’ll need to do some testing of my own to figure otu why.

          In the meantime, here are some possible solutions:

          1. Modify the shortcode to check for the time, and only run it once a day. When it runs, save the result in an options key/value pair. Here’s the WP documentation: https://codex.wordpress.org/Options_API .

          Then every time a page loads, serve it from the database. The data will be pulled from Google only once a day when you can update the value in your database.

          2. See if you can use some solutions to get the shortcode to run asynchronously. I’m not sure how to do this. See if you can use a plugin like this to load it via Ajax: https://wordpress.org/plugins/shortcode-ajax/

          I’ve never tested it myself, so I don’t know if it’ll work. I suggest method (1), as it’s more reliable and will get the job done 100% .

          Reply

  18. Awesome code mate. Just what I was looking for. Just want to display single cell date in a wordpress members profile page. And yes, I see what you mean about the formatting part. But that’s ok, it can be formatted within the wordpress page using the theme settings. Thank you for this!

    Reply

Speak Your Mind

*