Hello Coders, today we will learn about how we get data from Google Sheet to WordPress pages using the custom code on function file.
We write a function on Function.php file to make a shortcode and use that for getting the data from google sheet. For creating this we start step by step, here are all steps which we do for getting the data from google sheet, steps are given below,
1. Creating or select a Google API project
First, we create a sheet API on google console, Google provides a key for accessing the data and also use a key for tracking who use the data and also you can delete anytime for being a misuse of that key. To generate or for further process go to this link:
https://console.developers.google.com
Check Out: Stripe Payment API integration and Card save and delete operation in Codeigniter
Go to this link and click on this highlighted
After clicking on this Enable API and Services, Search for this
2. Enable the Sheets API
Next, Click on this API box, you get this for enabling Sheet API
After clicking enable you can now make an API key for google sheet, follow below steps for that.
3. Create a new API key
From this, you can create API key which we used for getting the data,
4. Make sheet Shareable
For making sheet first go to Google sheet and create a blank sheet and that any name,
After making shareable we get sheet ID from sheet link,
5. Get the spreadsheet ID
6. Creating a function for getting Data from Sheet
Now we create a function for getting the data from the sheet using Shortcode, for this, we create a function on theme file named function.php
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
function get_sheet_value_shortcode($atts) { $API = '<strong>[Insert API Key Here]</strong>'; $google_spreadsheet_ID = '<strong>[Insert Google Spreadsheet ID Here]</strong>'; $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', 'get_sheet_value_shortcode'); |
From the above code, you can get the data from the sheet, data or particular column value or column name.
On bold text on a code, replace that places with an API key and Google sheet ID.
7. Specified Cell Location on Google Sheet
If you confused how you get the particular cell location on Google sheet, you can check below for that,
From here you can get the exact cell location for getting data using the shortcode. Now we check how we use the shortcode on WP pages.
8. Use the shortcode on WP pages
Check below code and screenshot which shows how we use and what output we get from this code.
|
1 |
[get_sheet_value location="<strong>Cell Location</strong>"] |
Example:
|
1 |
<strong><code>[get_sheet_value location="Sheet1!A2"]</code></strong> |
OutPut:
You get this name from this location.
I think you all have got my points if you have any query please comment below.
Happy Coding..!
I love what you guys are usually up too. This kind of clever work and reporting!
Keep up the fantastic works guys I’ve added you guys to my blogroll.
Hello, I log on to your blog regularly. Your humoristic style
is awesome, keep up the good work! Ahaa, its good discussion on the topic of this
post at this place at this website, I have read all that, so
at this time me also commenting here.
Google sheet integration idea is nice
[…] Also check: Google Sheet Integration with WordPress without Plugin […]
How to access Private google spreadsheet using this function
WordPress crushing !
Awesome!
Help! How about getting a range value from a spreadsheet and not displaying a table? Thanks for the help!
[…] Google Sheet Integration with WordPress without Plugin […]
Hi all, tried following th9s but just getting a blank on my WordPress site. Any help would be gratefully received.
Please enable debug mode from wp-config.php file (define( ‘WP_DEBUG’, true )), then check what errors are showing on the page.
Just tried that and again get nothing showing up on the webpage that I’m trying to publish the info. Just the text before it and then nothing where the cell data should be.
Sorry I’ve never used debug mode though so don’t know where the error would show up if it wasn’t on the site where the data would land.
Go to your root directory of wordpress, then you can see the
wp-config.php file then do the same.
Yep I did get it into debug mode but there was no errors showing up on the test page where I posted the [get_sheet_value location=“Sheet1!D3”]
okfc.info/test1
Hi thanks for the code , instead of displaying google sheet data on page is it possible to save google sheet data to ACF fields in wordpress.
Yes, you can. Just fetch data on those fields and put it on. On submit it will take as it is and save into the DB.
Thank you for your reply , is it possible to give solution to ACF fields as well here. It would be helpful. As I am new wordpress development.
Hi Bikash. This looks great and I am almost there but with the debug on I am getting “Undefined Index: values” for the line $cell_value = $json_body[‘values’][0][0];
I have got the URL to show and this looks ok ( Is the “&” needed? )
URL: https://sheets.googleapis.com/v4/spreadsheets/****/values/Sheet1!B3?&key=****
Any pointers you can give ?
Thanks
Check the permission of the Google sheet URL, permission set to anyone with link.
If permission correct then please check cell location
Thanks for that Bikash. I have already done that.
Just one more thing. I have print_r the response and get this if it helps.
[body] => {
“error”: {
“code”: 403,
“message”: “Requests from referer https://sheets.googleapis.com/v4/spreadsheets/****/values/Sheet1!A2?key=**** are blocked.”,
“status”: “PERMISSION_DENIED”,
“details”: [
{
“@type”: “type.googleapis.com/google.rpc.ErrorInfo”,
“reason”: “API_KEY_HTTP_REFERRER_BLOCKED”,
“domain”: “googleapis.com”,
“metadata”: {
“service”: “sheets.googleapis.com”,
“consumer”: “projects/****”
}
}
I have starred out any personal details.
Thanks again for the help.
Ian
In status it says PERMISSION_DENIED
Please check the sheet PERMISSION
Hi! I want to query ALL cells from column A? it is possible?
Yes, you can do it by loop through this Sheet1!A2, First you have to check the maximum length of that column then use for loop or any other loop to increase value of A1 A2 A3… like this and got all the values.
Hi! It is possible to query all cells from one column?
Hello Bikash,
At the step 3 i belive the google sheet API options were updated by google, they are asking for 3 more queries while creating API Key, The following are the questions
1.Which API are you using?
Different APIs use different auth platforms and some credentials can be restricted to only call certain APIs.
(From the drop down i am selecting the Google Sheet API.)
2.Where will you be calling the API from?
Credentials can be restricted using details of the context from which they’re called. Some credentials are unsafe to use in certain contexts. (The drop down has many options this is where i believe i am going wrong, i am selecting the “Web server option”)
3.What data will you be accessing?
Different credentials are required to authorise access depending on the type of data that you request.
–> User data (I have selected this option)
Access data belonging to a Google user, with their permission
–>Application data
Access data belonging to your own application
I have done the remaining process as you have explained but getting blank o/p at the short code location.
Do help me if i m going wrong anywhere. I am a noob at php coding so pls excuse if i am asking any dumb questions.
Thanks in advance!!
Hi, why not make a plugin that does this? import a list of google sheet urls, you call the sheet and cell by “USE SHEET “TABLE-ABC CELL=C3” to insert in editor or elementor etc.
Hi,
The article’s main part is do that task without using plugin.
You can create plugin also.
no i cant, but maybe you can.. 🙂
Hi!
Thanks for the great solution, but after some new wp updates now instead of content it’s showing:
” Notice: Undefined index: values in /home/…/web/…/public_html/wp-content/themes/…/functions.php on line 45 ”
WP_DEBUG_DISPLAY is true
Permission and cell location are correct, few months before all worked fine.
Would be appreciative for any working solutions!
Please check code is available or not. Sometimes after updates code remove from the file.
If possible please give the error and code.
Thank’s for the reply!
Found the solution by setting “Application restrictions” to “None”
Before was “HTTP referrers (web sites)” – “https://mysite.com/*”
I don’t really understand why this prevented the correct work…
HI!
Thank you so much, this is such a wonderful piece of code and it works flawlessly.
I just have one question, is it possible to replace a cell value with another value?
If so, how would the function look, or what would need to be changed in order for it to work properly?
Thank you in advance!
Really nice solution!
I have added to my website and works correctly.
The only problem is the load that produce on the web server, I was obligated to increase some server value in the .htaccess but the page remain slow:
php_value max_input_vars 6000
max_execution 400
How solve the slow load?
Thank you!!!!!!
Hi, thanks a lot for this tutorial.
I want to use get_sheet_value_shortcode() in a template file outside functions.php but in the same directory. What do I need to do to call your function from that file?
I’ve followed this but I’m getting this error:
Notice: Trying to access array offset on value of type null
on this line:
$cell_value = $json_body[‘values’][0][0];
Hi, The code was of great help. However my sheet has hyperlinks but the above code gives me only the text without the link. Will you please be able to help?