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:

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

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'); 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.

[get_sheet_value location="<strong>Cell Location</strong>"] 1 [ get_sheet_value location = "<strong>Cell Location</strong>" ]

Example:

<strong><code>[get_sheet_value location="Sheet1!A2"]</code></strong> 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..!

Subscribe For Latest Updates Sign up to best of PHP Code Solutions, informed analysis and opinions on what matters to you. Invalid email address We promise not to spam you. You can unsubscribe at any time. Thanks for subscribing! Please check your email for further instructions.

Share Pin 2 21 Shares