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]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;
}