David Stockdale's Scrapcode

Getting Data From Google Sheet

Shown here is my Shortcode which aquires the value of a single cell of Google Sheet Data: 

/**
 * Aquiring google sheet data (David Stockdale)
 * Shortcode should be something like this: [get_sheet_value location="'Summary Page'!C3"]
 */
function sheet_value_shortcode($atts) {
    $API = 'XXX_XXX';
    $google_spreadsheet_ID = 'XXX-XXX-XXX';
    $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');

To get the API Key goto the “Google Developers Console” and create a key in the “Credentials” section.

To get the SpreadSheet ID simply look in the “Sharable Link” (broken down here):

https://docs.google.com/spreadsheets/d/spreadsheetId/edit#gid=sheetId

Shortcode:

[get_sheet_value location="'Summary Page'!C3"]

Leave a Reply