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 is what I’ll cover step by step.
- Create or Select a Google API Project
- Enable the Sheets API
- Create a new API key
- Make the spreadsheet shareable via a link
- Get the spreadsheet ID
- Plug your details into the code
- Specify a cell location in Google Sheets
- Alternatively, create a “named range”
- 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.
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.
Now just click the big blue “Enable” button here:
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:
Here under the “Credentials” tab, click the “Create Credentials” blue button with the arrow next to it. Now select “API Key”:
Update: Google recently changed their interface these are the new steps with screenshots.
We want to force Google to create an API key, so click “Help me choose” as shown here:
And now in the first step, click “API key” as shown here:
Now just choose your usage rights.
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:
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 a 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:
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:
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 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:
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.
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:
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:
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:
And here’s the output as expected:
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 in 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:
- The spreadsheet ID
- The range
- 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:
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 in 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!
Update: Lauren in the comments notes that there’s a limit of 100 API calls per user per second. So if you have more than that, it might slow everything down! Thanks, @Laura!
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.
You’re welcome. I’m glad you found it useful!
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!
You’re welcome 🙂 . I’m glad you found it useful!
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
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!
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.
Try and use the parse_url() function in PHP. Here’s the documentation: http://php.net/manual/en/function.parse-url.php
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?
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!
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.
I can try and help you out. I won’t charge anything 🙂 . If you want, I’d appreciate a linkback for your site instead! Send me a message using the contact form and I’ll get back to you: https://www.wp-tweaks.com/contact/
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!
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.
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.
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…
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.
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
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?
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); //
Are you able to get the cell value if you craft the URL manually? Look at the “How it Works” section to check…
Thank you. I started over and didn’t change the Name Range and it works. I must have muddled something there so I will stay simple.
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)
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)
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?
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.
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.
This sounds like something that needs to be done via Ajax. Try this: https://wordpress.org/plugins/shortcode-ajax/
I tried the plugin but it does not seem to load the the shortcode. Is it possible that i might buy some time from you to look at this
Hi Rasmus,
Ajax isn’t something I’m very familiar with. I’m sorry I cant help you more 🙁
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…
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?
Strange. Does the same thing happen if you construct the URL manually?
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.
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]”]
Hello Bhagwad,
The code doesn’t generate any result, it’s blank. Also, in Google Sheet’s API dashboard says “requests “xx” error 100%”.
Any idea what’s the problem? Thank you
I tried this:
function sheet_value_shortcode($atts) {
$API = ‘[INSERT API KEY HERE]’;
$google_spreadsheet_ID = $atts[‘gsid’];
$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’);
And proceed with this:
[get_sheet_value location=”Sheet1!B2” gsid=”1vTyCttwqLo33jtuVbHu_45lGYJrbpjdgPnlmo02aIVE”]
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.
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!
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!
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?
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% .
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!
Thanks for the walkthrough. Is it possible that inserting data from many cells into one WordPress page would slow it down or prevent it from loading? I’m using this shortcode with another plugin to create a clickable map that displays data. Example: click a place on the map and it displays data from cells in a spreadsheet. It’s set up to get information from about 20 cells for each location clicked and I’ve set up about 20 locations so far. Could that be slowing it down?
It’s possible, but I’m not sure. You’ll have to test it out and see. Sorry for not being more helpful. But if you do find out, leave a comment here so we can all share the results!
It seems that google has a quota limit for Google Sheets API of 100 requests per user per 100 seconds and the quota can’t be increased. Each cell must equal one request and if that’s true, I was essentially trying to make 400 requests at once.
I see! That’s very interesting. Thanks for explaining. I’ve updated the article with a shoutout. Thanks!
Hi
Thanks for your nice work.
Are you able to add a refresh button into the code, using ajax, to refresh the value without refreshing the entire page?
Thanks again
I wish I knew how to do this, but I don’t!
Thanks anyway.
I’ll appreciate if anyone else can help. It is very needed..
Really well and easy-to-understand way of writing, well done!
Will you write a post if wanting the data flow to go in the other direction, i.e. from WP to Sheets?
In my case, I am having a membership organisation where a member should be able to enter their ownership % and have his personal version of the annual report, ie his/her’s share.
So I want visitor enter a % that should be sent to a specific cell in Sheets to then do the magic in Sheets and to present the full calculation back to the user.
You can maybe try using something like Gravity Forms? https://www.gravityforms.com/form-entries-google-sheets/
Hi there!
First of all, thank you for this very awesome tutorial! Could you help me with an error that I am getting?
I followed all your steps, and it seems to work fine, but sometimes it throws me the following error:
Notice: Undefined index: values in *******/htdocs/wp-content/themes/oxigeno-child/functions.php on line 28
Being line 28 the following:
$cell_value = $json_body[‘values’][0][0];
I don’t know how to solve it, because it does not always throw this error when opening the page where I call the cell data, sometimes it does, sometimes it does not.
Thank you in advance!
Hi there, Thanks for the awesome solution.
I would like to know is there anyway i can embedded the complete table from spreadsheet to wordpress?
Thanks
WOW, I was really searching hard for this, thank you very much, cannot be more easily explained.
I am searching for something very similar but I cannot find, maybe you can help me here. There we go:
I need to show data depending on the date, so I have a spreadsheet like this:
Date Value
6-8-19 Hello
6-9-19 Bye
Then I dream for a shortcode like this: [get_sheet_value date=”6-8-19″] with result: Hello
Thank you very much even if you cannot reply, this post is awesome anyway.
Thanks David 🙂 .
What you’re asking for should be possible, but I’m not sure how to do it. Sorry!
At the end I solved it going around, I named the cells (as per your step 8 up) with the date (and one parameter before and after because the date alone cannot be the name of a cell). Then y get the date using php and put them into the shortcode… It was complicated but it works 😀
Thank you anyway!
Very clever. Well done!
Hi Bhagwad,
thanks for this great tutorial!
Unfortunately google changed the api-backend. Now you have to chose between OAuth 2.0 Client or Service-Accounts for creating the credentials. Also there is a consent screen to fill out. Lots of information Google wants from me.
The workflow from your article won’t work anymore I guess 🙁
Cheers
Philipp
Thanks for the update. I’ve just made the changes to step 3 to show the new workflow!
Great!
Thanks so much 🙂
I’ve tried this but still doesn’t work. i suspect it’s in the step 3. So, what is the choice I have to chose? Please advise. Thanks
Hi,
I cannot get this to work for some reason. I try the link (https://sheets.googleapis.com/v4/spreadsheets/$google_spreadsheet_ID/values/$location?&key=$api_key) manually with the correct parameters and I get the JSON data returned as it should. But for some reason the data does not show with the shortcode on the website. It doesn’t show any errors or anything.
Any troublehsooting ideas?
Actually, I figured out that issue. In the PHP code I had inserted my value in the variable for $location AND had it in the shortcode. Thank you for this snippet, Very useful!
I’m glad you were able to get it to work! And thanks for coming back with the solution 🙂
Hi guys. This was very very helpful. Thank you!
I have one question: would it be possible to use this to dynamically show images in my WordPress site by taking the image url from a specific cell in google sheets?
So for example cell A1 has the url of an image, the API would show the image of that URL.
Thanks again!
While I haven’t tried it, there’s no reason why this shouldn’t work. I suspect you’ll need to type the HTML code in such a way that the “<a hrefs" tag contains the shortcode instead of the URL.
Let me know if you try it, as well as the outcome!
You were right! It worked. Thank you!
I was also trying to do the same thing. How exactly did you get it to work?
Hi can you provide the html you used.
Much appreciated
How to access private google spreadsheet using this code?
Hello Sir,
I’m not really a programmer but trying to help build my friend’s site. I tried your tutorial, but when inserted the shortcode in a Page, it just shows the shortcode – not the content of the data. Could you please kindly help? Many thanks in advance.
sorry,
i was wrong. the custom php plugin I used require me to turn it ON. I did, the shortcode doesn’t show up anymore, but then the it shows blank content box. 🙂
I am having the same problem, I have successfully applied the shortcode to the functions.php and verified my domain but it shows up as blank.
HELLO
I need your help.
I will add a table Sheet1!A1:K7
I readed your post HOW IT WORKS but i don´t understand where to change it for the “table” and not only for 1 columm.
Thank you
Peter
Hi there, Thanks for the awesome solution.
But, I´m asking myself if it´s possible to set some data in spreadsheet from html input? what do you think about?
Hi!!! I want to use this to extract the total hours of an activity I contabilize in the google spreadsheet. I have a cell with the total number of hours.
In my WP page, I want to insert that number but not directly as text, but to replace a field value I haver in an Elementor widget:
Through the web inspector I found out which is the component I’d need to change its value to the cell’s value in question:
#content > div > div > div > section.elementor-element.elementor-element-bfdcc62.elementor-section-boxed.elementor-section-height-default.elementor-section-height-default.elementor-section.elementor-top-section > div > div > div > div > div > div.elementor-element.elementor-element-654dd80.elementor-widget.elementor-widget-counter.animated.bounceIn > div > div > div.elementor-counter-number-wrapper > span.elementor-counter-number {
field {
}
However, I do not know how to replace the value number for the shortcode’s result. Any workaround anyone?
PS: If you checkmy webpage, there’s an animated text that tallies from 0 to the final value of hours. That’s the number I’d like to change for the result of the shortcode.
Sorry my bad, the component is
#content > div > div > div > section.elementor-element.elementor-element-bfdcc62.elementor-section-boxed.elementor-section-height-default.elementor-section-height-default.elementor-section.elementor-top-section > div > div > div > div > div > div.elementor-element.elementor-element-654dd80.elementor-widget.elementor-widget-counter.animated.bounceIn > div > div > div.elementor-counter-number-wrapper > span.elementor-counter-number {}
I guess I could just put the PHP code inside? but I’d need to return the value to the component’s value field (which I do not know how to access through CSS anyway).
Hi Alejandro! I don’t know anything about elemontor unfortunately 🙁
Have you just tried putting the shortcode in the place where you want the number to show up?
Yeah I did,
Firstly it shows as empty text.
Secondly, elementor is just an editor for WP. So It has a widget called “counter” which ramps a number from 0 – your value.
What I’m trying to do is change that destination value through addtional CSS to pick up the number from the shortcode.
The short code alone returns a number without format so I’m not sure how I can make it look pretty if the counter widget wouldn’t be possible.
Any clues?
Ok, got the value alright from the spreadsheet.
Now I need to assign it through CSS somehow. I tried giving the widget a class, and using
content: classname;
but nothing happens… guess its too far fetched..
So, again, I’m getting closer to my objective.
In CSS, how do I pass the result of the above code (the content of a gspreedsheet cell) to an existitng screen element?
I’ve tried stuff like content: value=class and that doesn’t work.
Hello ! Have you found a solution to this?
Really interesting, many thanks. I have one question though – can you somehow reference data in a more specific way? In your screenshot you use a column “ID” but you don’t use it to reliably reference a certain row and dataset. Using =SheetX!Yn is going to produce wrong results the moment someone deletes or moves a row/column, adds a new one in between existing ones or res-sorts the spreadsheet data, right?
It would probably be more reliable to lookup specifically named rows (ID) and columns (TITLE in row 1) to find a specific cell.
I have small data example here https://docs.google.com/spreadsheets/d/1tf1nOFaSwqEpj_oMx2Zg85MANVW-erupFBPIaT6DoU0/edit?usp=sharing
Is there a way to do that at all?
It’s a good question! To be honest, I’m not sure if it’s possible. I’ll need to check out some Google documentation first…
Thank you so much for this. I’ve been testing this out on my website as a way to implement weekly updates to a racing series. I’ve only tested a small portion with a single google sheet, but the goal is to eventually have multiple sheets, each with sometimes 100+ racers, their weekly race results, point totals, and more, all of which will feed into several pages on my wordpress website.
I’m currently using a basic google account and the fields randomly won’t populate, leaving lots of empty spaces in my results charts. I believe this is caused by the limited amount of read requests that google allows for my basic account. When I refresh, some of the fields will fill in but others will come up empty instead. I also can see errors in the Google API overview and metrics sections (which I’m still learning to use) though I don’t see any info on what types of errors they are so that I might be able fix them. I tried to request a higher quota, but it says my “project needs to be linked to a billing account.” which I was planning on doing anyways, but I don’t want to do that and put together all this code and find out later it still doesn’t populate everything I need it to.
So my question is, in your experience, do you think this problem could be fixed by upgrading to a billing account with higher read request quotas? Do you think your solution here will be sustainable for pulling data from thousands of cells or should I try something else?
Thank you so much in advance for any help you can give me.
Hi Dave, thanks for taking the time to explain your problem! Unfortunately, I have really have no experience in this matter 🙁 . I guess if I were you, I would try it out for a short while and see if it works…
This is an incredible resource, thank you. Does anyone know if there is a way to have a URL that is stored in the Google Sheet appear on my website as clickable/hyperlinked once it is pulled in through the shortcode?
I reached out too soon. Here’s my solution:
Cell A1: click here.
Cell A4: =A1&A2&A3
Then aim the shortcode at cell A4.
Working for me so far! Other potential solutions are welcome.
Thanks again to Bhagwad for this awesome page and site. It’s been a huge help.
Thanks Andrew! I’m glad you found it useful 🙂
Hey, I’m also trying to this but without success. Are A2 and A3 empty?
Also is there a way to return a clickable url to a divi button?
Thanks for this!
OK my post above turned into an actual link. It should’ve shown:
Cell A1: click here.
then
Cell A4: =A1&A2&A3
Never mind.
Please try again. I just cant get the links to work
Thanks
I already tried this method a lot and I couldn’t get it to work.
I can get the result manually.
https://sheets.googleapis.com/v4/spreadsheets/1RFHoUTO_tPE3dYch8AG0x9uFzXieGpB9DmvbBqlOeJk/values/IntervaloNomeado1?&key=AIzaSyCF7kCVgYJqPMSWawGyxgxi
But it doesn’t appear in wordpress, I already switched hosts
can you check if i’m doing something wrong?
Thank you very much for this tuto.
I was looking for a solution to display the number of subscribers on my blog automatically with my new email marketing manager but it does not provide a script.
So, thanks to your clear explanations I was able to create a simple google sheet that I can update periodically with the value.
My pleasure. I’m glad you found the tutorial useful!
Thank you very much for this information. I was looking to fetch information from a Google sheet and output it into a custom html structure, so your code was the starting point I needed to accomplish my goal.
Two years later, your information is still very useful. Thanks!
Hi Hugo,
I am struggling to do something similar. Could you please explain how you achieved this?
Thank you so much for this man! I really appreciate it. I now have an updated covid data displayed in my website from WHO database. Commeded!
Hi Bhagwad,
Are you able to upload images to the spreadsheet, and it gets pulled through the shortcode to the frontend? Also, can you customize the css of each cell using this method?
Thank you for the awesome resource!
Hi Nicholas, that’s a good question! I honestly have no idea. Perhaps you can test it out and let us know!
Hi Bhagwad,
I follwed all the instructions and this is what i am getting back
function sheet_value_shortcode($atts) { is not a valid setting.
$API is not a valid setting.
$google_spreadsheet_ID is not a valid setting.
$api_key is not a valid setting.
$location is not a valid setting.
$get_cell is not a valid setting.
$cell_url is not a valid setting.
$cell_response is not a valid setting.
$json_body is not a valid setting.
$cell_value is not a valid setting.
return $cell_value; is not a valid setting.
} is not a valid setting.
Am i doing something wrong?
Hi, Bhagwad,
Thank you for this simple to setup solution. 2 years later it still works.
You did mention ‘For this reason, I prefer to use my own tables solution and just extract the individual values from Google Sheets instead.’ Do you have an example of how this is done in a table.
If you take a look at my website, I pull those data from custom fields and I thought, maybe I could get the same from Google spreadsheet. Just couldn’t get wrap around my head on how to achieve the same.
Any pointers is very much appreciated.
Thanks and have a good day ahead.
Hi! I meant you can continue to use the table solution you already have on your site. Just use the appropriate shortcode in each cell.
I’m not sure how much this can scale though. The free version of Google sheets might have some restriction on the number of calls, but I think it should still be enough for your needs. Then use a caching solution to serve a cached page instead of regenerating it for each visitor.
Hi, this is fantastic, thank you so much for this info.
Is there any way to pass a value to the cell?
I want to execute a formula from the spreadsheet and just return the value to my wp page.
I appreciate your help!
Hi Piero, you’re welcome! Not sure about passing values, but why don’t you just perform the calculate locally? I wrote a WordPress plugin for exactly this purpose: https://www.wp-tweaks.com/how-to-calculate-values-in-wordpress-posts-and-pages/
I allows you to use formulas in the WordPress editor. Hope you find it useful!
Hi, Thank you for your tutorial, it’s very helpful. Is it possible to use this with Google Sheets’ QUERY function, eg like this =QUERY(countries,”SELECT B, C, D WHERE C = ‘Europe’ “,1) ?
I was able to get it working when I made the URL myself. Is it possible to use the same piece of code if I wanted to pull another cell location from the same sheet? For example I want to pull Sheet1A1 and Sheet1B1 from the same Google workbook but when in the code snippet I have to specify the value. Therefore when I use the short code [get_sheet_value location=”Cell Location”] it returns the specified cell I put in the code.
to run this program, do i need to plugin anything in my wordpress.
because i write the entire program in notpad++ which is there in step 6and wrote shortcode in wp pages but its displaying the code itself..
Hi, thanks for such a beautiful tuts. i did the same as explained above but my WordPress website is behaving ulter. Everything this working fine but after two days my site got blank. then i again disable the snippet and the website works fine. after again i enable the code and it works for another 2 days and after that it got blank. Please help me. I am using astra and elementor pro.
This is a game changer for my website and what I wanted to accomplish. Thank you so much!
This didn’t work for me – breaks my site when adding the code to Functions.php and just doesn’t work (as in nothing happens) when using a snippet plugin. I followed the instructions but to no avail…which sucks because this would be awesome to get working!
I have a Sheet that automatically sort data in Column B (descending). The cells in column B will be filled via an online form. These cells contain Client Account numbers generated through a QR-code scan function in the online form.
I wish to display the form data associated with the specific account number on the client dashboard (Private Page).
I assume that there should be a “search/find” call or function?
Hope you can help?
Not sure why, but I followed EACH of the step you mentioned and after using https://sheets.googleapis.com/v4/spreadsheets/$google_spreadsheet_ID/values/$location?&key=$api_key
it’s showing me the data as it should. But on my website it’s not showing any data.
Shortcode I used: [get_sheet_value location=”Sheet1!A1″]
Not sure why
but I did everything
I typed
Sheet1!e5 and nothing happened ??
I tried [get_sheet_value location=Sheet1!e5]
[get sheet_value_shortcode=sheet1!e5]
did I miss a step ?
Hi Bhagwat,
In function sheet_value_shortcode($atts) {..
What do I need to pass as parameters, and and where do I need to call this function in my wordpress page?
Regards,
Pramod
I can’t seem to get this working. I’m trying to take a list of names from a spreadsheet and automically populate them into blocks on my website. I am using elementor pro to create the page and I’ve been adding the “shortcode” option and inputting the [get_sheet_value location=”Cell Location”] after following all of the previous steps.
Nothing happens, if I view the page after updating it remains blank. I’d love for some help if anyone can spare some time!
Update: When I try to enter my complete address I recieve this error:
{
“error”: {
“code”: 403,
“message”: “The request is missing a valid API key.”,
“status”: “PERMISSION_DENIED”
}
}
I’m guessing i’ve messed something up! My API key has no restrictions and my spreadsheet is available to anyone with the link. I’d really love to some help!
I also have this issue. Any solutions?
Guys Make sure you remove the square brackets in the code near API Key and Google Sheet ID
Thanks 😳
Thank you so much for instructions clear enough for a knucklehead like me to get it working on the first try. I’ve been tearing my hair out for 3 days trying to figure out how to do this and you saved me from the verge of buying an expensive plugin.
Hi Lannyl, I’m glad my article was useful to you 🙂 . Thanks for the comment!
Hello i have this error can you say me what ?
Parse error: syntax error, unexpected ‘sheet_value_shortcode’ (T_STRING) in C:\Users\jpfau\Local Sites\alfcoin\app\public\wp-content\plugins\google-sheet-to-wordpress\google-sheet-to-wordpress.php on line 5
Thk.
I can’t do it, do you know a plugin that does this?
Hi Bahgwad
After insert the code into functions wordpress, in found some eror say :
“Something went wrong. Your change may not have been saved. Please try again. There is also a chance that you may need to manually fix and upload the file over FTP.”
I hope you can help me. Thanks
hello i have this error can u sy me what ?
Parse error: syntax error, unexpected ‘sheet_value_shortcode’ (T_STRING) in C:\Users\jpfau\Local Sites\alfcoin\app\public\wp-content\plugins\google-sheet-to-wordpress\google-sheet-to-wordpress.php on line 5
Hello i have this error can you say me what ?
Parse error: syntax error, unexpected ‘sheet_value_shortcode’ (T_STRING) in C:\Users\jpfau\Local Sites\alfcoin\app\public\wp-content\plugins\google-sheet-to-wordpress\google-sheet-to-wordpress.php on line 5
Thk.
Hi, Thanks for the nice information. I tried and got success.
But I want to know also for post.
Actually I want to post some data to sheet.
What is method for that?
Thanks
Hello I’m trying to use this method but with URLs and its not working, I know I need to convert some characters somehow but I’m kind of stumped
Hi Bhagwad Thank you!
this is my code:
function sheet_value_shortcode($atts) {
$API = ‘AIzaSyD-cxmjc7lHWkp7daeWf510vcm8hZMln_A’;
$google_spreadsheet_ID = ‘1MhskllKpd2bw6qwqicm-1u0KiLRlda1A’;
$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’);
and this is the shortcode [get_sheet_value location=”‘KSY Times’!L141″]
but its turning up blank on the site. I tested on another site as well
any ideas?
Did you ever figure this out? I’m encountering the same issue?
This is exactly what I am looking for. Thank you so much for the detailed instruction and for sharing the information!!
You’re welcome, and I’m glad you found it useful!
Hi Brother,
It’s great Work n it was helped me so much.
But I want multiple cell values like below.
[sheets_html] Hi, this is #Sheet1!A1 and I”‘m from #Sheet1!A2 [/sheets_html]
Can you look at my Question on stackoverflow.
https://stackoverflow.com/questions/74362089/get-google-sheet-cell-values-into-wp-site-by-shortcode
Thank You so much : )
My sheet isn’t publicly sharable.
I’ve setup a service account in the Google Console, shared the sheet with that service account, but I don’t think the service account key is the same as the API key. I’ve shared the Google Sheet with the email address associated with the service account, and have generated a JSON Key for that service account.
However, I can’t access the file via the browser and think I need something more complex than this. Any recs?
I’ve had a version of this on a previous website and am trying to apply it to a new website but cannot get it work. Nothing has changed except the domain and hosting service. The snippet is below:
add_action( ‘init’, function () {
$hook = ‘run_snippet_daily’;
$args = array();
if ( ! wp_next_scheduled( $hook, $args ) ) {
wp_schedule_event( time(), ‘daily’, $hook, $args );
}
} );
add_action( ‘run_snippet_daily’, function () {
} );
function fixed_value_shortcode($atts) {
$API = ‘[AIzaSyAvppcy7eYhb-YxpXFCKj3e9vnGxiUbWPI]’;
$google_spreadsheet_ID = ‘[1VEUq3QW1EMQcLe-i_cYfO6LskEWsNOEE-93dq-A8BRM]’;
$api_key = esc_attr( $API);
$location = $atts[‘location’];
$get_cell = new WP_Http();
$cell_url = “https://sheets.googleapis.com/v4/spreadsheets/1VEUq3QW1EMQcLe-i_cYfO6LskEWsNOEE-93dq-A8BRM/values/Fixed?&key=AIzaSyAvppcy7eYhb-YxpXFCKj3e9vnGxiUbWPI”;
$cell_response = $get_cell -> get( $cell_url);
if ( is_wp_error( $cell_response ) ) {
return ‘An error occurred’;
}
$json_body = json_decode($cell_response[‘body’],true);
$cell_value = $json_body[‘values’][0][0];
return $cell_value;
}
add_shortcode(‘get_fixed_value’, ‘fixed_value_shortcode’);
Would it be possible to use column and row name (first column and first row names) instead of A B C and 1 2 3?
Thank you for sharing!
It works great up to 50-60 cells. Thank you for the detailed presentation.
Maybe someone knows a solution that can be used to import up to 100 cells? (I know that the google api limit is 100. But I don’t need an immediate update, once a day would be more than enough)
Thanks.
Hello, thank you very much for this tip!
However, the script doesn’t seem to work anymore, any idea where it could be coming from?
Hi
Did you get it working again?
Is this solution still working ?
Can’t manage to have the data to show up. All I have is the short code [get_sheet_value location=”name_ranged”] appearing like it was simple html text input.
Thanks for the help !