SheetShow

Recently I got a question from Stack Overflow. A teacher wanted their spreadsheet to toggle between 2 sheets. Evidently this teach used their Smart Board to put up information during tests and quizzes and she would display that information on the Smart Board using Google Sheets. I did the project and delivered the script but got to thinking that I could have just as well made a SheetShow as opposed to a SlideShow with any number of Sheets involved and so that's what this project is all about. The Google Apps Script code to develop a SheetShow is presented here.

The Code (Code.gs)

function onOpen()

{

SpreadsheetApp.getUi().createMenu('G+ Script Problems')

.addItem('showSheets', 'showSheets')

.addItem('Sheet Show Sidebar', 'SheetShowSidebar')

.addToUi();

}

The Code(SheetShow.gs)

function showSheet(idx)

{

var idx = (typeof(idx)!='undefined')?idx:1;

var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

if(idx>0 && idx<=sheets.length)

{

var sht=sheets[idx-1];

sht.activate();

var r = [idx,sht.getName()];

return r;

}

else

{

var r = [-1,'Index out of range.'];

return r;

}

function SheetShowSidebar()

{

var ui = HtmlService.createHtmlOutputFromFile('SheetShowDialog').setTitle('Sheet Show Info:');

SpreadsheetApp.getUi().showSidebar(ui);

}

function displayToast(msg,title,timeoutSeconds)

{

SpreadsheetApp.getActiveSpreadsheet().toast(msg, title, timeoutSeconds)

}

function getTotalSheets()

{

return SpreadsheetApp.getActiveSpreadsheet().getSheets().length;

}

The HTML(SheetShowDialog.html)

<!DOCTYPE html>

<html>

<head>

<style>

#my_block{border:2px solid black;background-color:rgba(0,150,255,0.2);padding:10px 10px 10px 10px;}

#conv_block{border: 1px solid black;padding:10px 10px 10px 10px;}

.bttn_block{padding:5px 5px 0px 0px;}

.sndr_block {border:1px solid rgba(0,150,0,0.5);background-color:rgba(150,150,0,0.2);margin-bottom:2px;}

</style>

</head>

<body>

<form>

<br />Sheets in Show:

<br />Sheet Name:

<br />Sheet Number:

<br />Sheet Delay(seconds):

</select>

<br /><strong>Timer Controls</strong>

</div>

</div>

</form>

</script>

var idx=1;

var myInterval='';

$(function() {

$('#startTimer').click(startmytimer);

$('#stopTimer').click(stopTimer);

$('#txt4').val('');

$('#txt3').val('');

$('#txt2').val(10);

$('#txt1').val(5);

google.script.run

.withSuccessHandler(setTotal)

.getTotalSheets();

});

function showSheet()

{

var maxidx = $('#txt1').val();

if(idx>maxidx)idx=1;

if(idx<=maxidx && idx>0)

{

google.script.run

.withSuccessHandler(showStatus)

.showSheet(idx);

idx += 1;

}

function showStatus(r)

{

if(r[0]>-1)

{

$('#txt3').val(r[0]);

$('#txt4').val(r[1]);

}

else

{

$('#txt3').val('');

$('#txt4').val(r[1]);

}

function startmytimer()

{

showSheet();

myInterval=setInterval(showSheet, Number($('#txt2').val())*1000);

google.script.run.displayToast('Timer Started','Script Status', 5);

}

function stopTimer()

{

if(myInterval)

{

clearInterval(myInterval);

google.script.run.displayToast('Timer Stopped','Script Status', 5);

}

else

{

google.script.run.displayToast('myInterval not set.','Script Status', 5);

}

function loadTxt(from,to)

{

document.getElementById(to).value = document.getElementById(from).value;

}

function mystep(d)

{

idx+=d;

showSheet();

}

function setTotal(n)

{

var s = '<select id="sel2" onChange="loadTxt(\'sel2\',\'txt1\');">';

var n = (typeof(n)!='undefined')?n:0;

var sel = $('#txt1').val();

if(n>0)

{

for(var i=1;i<=n;i++)

{

var sltd = (i!=sel)?'':' selected';

s+='<option value="' + i + '"' + sltd + '>' + i + '</option>';

}

s+='</select>';

var el = document.getElementById('shtsinfo1').innerHTML += s;

$('#txt1').val(sel);

}

console.log('My Code');

</script>

</body>

</html>

I've found that volunteering at StackOverFlow.com has been a real asset to helping me to learn google apps scripting.