Storing Array Of API Keys and SpreadSheet IDs in Options Database Table

Continuing with my ongoing task of making it as simple as possible to display a number contained by a cell within a Google Spreadsheet as a scrolling number I have altered the code to store the users various API Keys and Spreadsheet IDs within the websites “Options Database Table” (because thats the best place I could find to store data like this) and altered the shortcodes so that the user can select which key and spreadsheet they want to use for each scroller.

Shortcode Example:

[david_scroller location="'Summary Page'!L6" api_key=1 spreadsheet_id=2 delay=1 increment=100]
0

NumScroller

Adding Script

/**
 * Adds script (David Stockdale).
 */
add_action( 'wp_enqueue_scripts', 'add_my_script' );
/**
 * Adds script for NumScroller (David Stockdale).
 */
function add_my_script() {
	wp_enqueue_script(
		'numscroller-1.0', // name your script so that you can attach other scripts and de-register, etc.
		get_stylesheet_directory_uri() . 
		'/lib/numscroller-1.0.js', // this is the location of your script file.
		array( 'jquery' ), // this array lists the scripts upon which your script depends
		'', //Not needed with Beans.
		true //Not needed with Beans.
	);
}

JQuery

/**
* jQuery scroroller Plugin 1.0
* (With a single small change by David Stockdale)
* http://www.tinywall.net/
* 
* Developers: Arun David, Boobalan
* Copyright (c) 2014 
*/
jQuery(document).ready((function($){
    $(window).on("load",function(){
        $(document).scrollzipInit();
        $(document).rollerInit();
    });
    $(window).on("load scroll resize", function(){
        $('.numscroller').scrollzip({
            showFunction    :   function() {
                                    numberRoller($(this).attr('data-slno'));
                                },
            wholeVisible    :     false,
        });
    });
    $.fn.scrollzipInit=function(){
        $('body').prepend("<div style='position:fixed;top:0px;left:0px;width:0;height:0;' id='scrollzipPoint'></div>" );
    };
    $.fn.rollerInit=function(){
        var i=0;
        $('.numscroller').each(function() {
            i++;
           $(this).attr('data-slno',i); 
           $(this).addClass("roller-title-number-"+i);
        });        
    };
    $.fn.scrollzip = function(options){
        var settings = $.extend({
            showFunction    : null,
            hideFunction    : null,
            showShift       : 0,
            wholeVisible    : false,
            hideShift       : 0,
        }, options);
        return this.each(function(i,obj){
            $(this).addClass('scrollzip');
            if ( $.isFunction( settings.showFunction ) ){
                if(
                    !$(this).hasClass('isShown')&&
                    ($(window).outerHeight()+$('#scrollzipPoint').offset().top-settings.showShift)>($(this).offset().top+((settings.wholeVisible)?$(this).outerHeight():0))&&
                    ($('#scrollzipPoint').offset().top+((settings.wholeVisible)?$(this).outerHeight():0))<($(this).outerHeight()+$(this).offset().top-settings.showShift)
                ){
                    $(this).addClass('isShown');
                    settings.showFunction.call( this );
                }
            }
            if ( $.isFunction( settings.hideFunction ) ){
                if(
                    $(this).hasClass('isShown')&&
                    (($(window).outerHeight()+$('#scrollzipPoint').offset().top-settings.hideShift)<($(this).offset().top+((settings.wholeVisible)?$(this).outerHeight():0))||
                    ($('#scrollzipPoint').offset().top+((settings.wholeVisible)?$(this).outerHeight():0))>($(this).outerHeight()+$(this).offset().top-settings.hideShift))
                ){
                    $(this).removeClass('isShown');
                    settings.hideFunction.call( this );
                }
            }
            return this;
        });
    };
    function numberRoller(slno){
            var min=$('.roller-title-number-'+slno).attr('data-min');
            var max=$('.roller-title-number-'+slno).attr('data-max');
            var timediff=$('.roller-title-number-'+slno).attr('data-delay');
            var increment=$('.roller-title-number-'+slno).attr('data-increment');
            var numdiff=max-min;
            var timeout=(timediff*1000)/numdiff;
            //if(numinc<10){
                //increment=Math.floor((timediff*1000)/10);
            //}//alert(increment);
            numberRoll(slno,min,max,increment,timeout);
            
    }
    function numberRoll(slno,min,max,increment,timeout){//alert(slno+"="+min+"="+max+"="+increment+"="+timeout);
        if(min<=max){
            $('.roller-title-number-'+slno).html(min);
            min=parseInt(min)+parseInt(increment);
            setTimeout(function(){numberRoll(eval(slno),eval(min),eval(max),eval(increment),eval(timeout))},timeout);
        }else{
            $('.roller-title-number-'+slno).html(max);
        }
    }
}));

Sheet Value Shortcode

/**
 * Adds shortcode (David Stockdale).
 */
add_shortcode('get_sheet_value', 'sheet_value_shortcode');
/**
 * A shortcode for aquiring google sheet data (David Stockdale).
 * 
 * Shortcode should be something like this: 
 * [get_sheet_value location="'Sheet Name'!Cell" api='API_KEY' sheet='SPREADSHEET-ID']
 * OR (if you right click a cell and name it a "named range" you only need that name for location)
 * [get_sheet_value location="Named Range" api='API_KEY' sheet='SPREADSHEET-ID']
 * 
 * EXAMPLE:
 * [get_sheet_value location="'Summary Page'!C3" api='XXX_XXX' sheet='XXX-XXX-XXX']
 * 
 * Where to find API Key and SpreadSheet Id:
 * API Key (to get this goto the "Google Developers Console" and create a key in the "Credentials" section).
 * SpreadSheet Id: https://docs.google.com/spreadsheets/d/spreadsheetId/edit#gid=sheetId
 */
function sheet_value_shortcode($atts) {
	$google_spreadsheet_ID = $atts['sheet'];
	
    $api_key = esc_attr( $atts['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;
}

Storing API Keys and SpreadSheet IDs

/**
 * Stores/updates the array of Api Keys within the options database table.
 */
update_option( 'api_key_array', array(1 => "XXX_XXX"), true );
/**
 * Stores/updates the array of SpreadSheet IDs within the options database table.
 */
update_option( 'spreadsheet_id_array', array(
1 => "XXX-XXX-XXX",
2 => "XXX-XXX-XXX"), true );

David Scroller Shortcode

/**
 * Adds shortcode (David Stockdale).
 */
add_shortcode('david_scroller', 'david_scroller_shortcode');
/**
 * Runs the "sheet_value_shortcode" and then uses the result
 * to create a "numscroller" which it returns as HTML (David Stockdale).
 * 
 * ----------Shortcode Use Examples---------- 
 * -----------------Required-----------------
 * Location, Key and ID: [david_scroller location="'Summary Page'!C3" api_key=1 spreadsheet_id=2]
 * OR
 * Location (using "Named Range"), Key and ID: [david_scroller location="NamedRange1" api_key=1 spreadsheet_id=2]
 * -----------------Optional-----------------
 * Delay: [david_scroller location="'Summary Page'!C3" api_key=1 spreadsheet_id=2 delay=10]
 * 
 * Increment: [david_scroller location="'Summary Page'!C3" api_key=1 spreadsheet_id=2 increment=1]
 * 
 * Minimum: [david_scroller location="'Summary Page'!C3" api_key=1 spreadsheet_id=2 min=4]
 * 
 * Default: [david_scroller location="'Summary Page'!C3" api_key=1 spreadsheet_id=2 default=999]
 * 
 * All: [david_scroller location="'Summary Page'!C3" api_key=1 spreadsheet_id=2 delay=10 increment=1 min=4 default=999]
 */
function david_scroller_shortcode($atts) {
	$sho = "[get_sheet_value location=";
	$sho .= '"';
	$sho .= $atts['location'];
	$sho .= '"';
	
	$sho .= " api=";
	$sho .= "'";
	/**
	 * API Key (to get this goto the "Google Developers Console"
	 * and create a key in the "Credentials" section).
	 */
	$sho .= get_option( 'api_key_array', false )[$atts['api_key']];
	$sho .= "'";
	
	$sho .= " sheet=";
	$sho .= "'";
	/**
	 * SpreadSheet Id: https://docs.google.com/spreadsheets/d/spreadsheetId/edit#gid=sheetId
	 * (REMEMBER TO MAKE THE SHPREADSHEET SHARABLE/ACCESSABLE BY LINK!)
	 */
	$sho .= get_option( 'spreadsheet_id_array', false )[$atts['spreadsheet_id']];
	$sho .= "'";
	
	$sho .= "]";
	
	ob_start(); //Start output buffer
	/**
	 * [get_sheet_value location="'Sheet Name'!Cell" api='API_KEY' sheet='SPREADSHEET-ID']
	 */
	echo do_shortcode($sho);
	$number = ob_get_contents(); //Grab output
	ob_end_clean(); //Discard output buffer
	
	if (!(isset($atts['min']->a)) && !empty($atts['min'])) {
		$min = $atts['min'];
	} else {
		$min = 1;
	}

	if (!(isset($atts['delay']->a)) && !empty($atts['delay'])) {
		$delay = $atts['delay'];
	} else {
		$delay = 5;
	}
	
	if (!(isset($atts['increment']->a)) && !empty($atts['increment'])) {
		$increment = $atts['increment'];
	} else {
		$increment = 10;
	}
	
	if (!(isset($atts['default']->a)) && !empty($atts['default'])) {
		$default = $atts['default'];
	} else {
		$default = 0;
	}
	
	return <<<HTML
    <html>
    <body>
	<span class="numscroller" data-min=$min data-max=$number data-delay=$delay data-increment=$increment>$default</span>
    </body>
    </html>
HTML;
}

Leave a Reply