flickr photo shared by Internet Archive Book Images with no copyright restriction (Flickr Commons)
Sometimes you want a Google Sidebar element to be able to pull variables from a Google Script. The key element is google.script.run.withSuccessHandler. I’m still not entirely sure I have this in my head deeply/correctly but this is functional and might help someone else (even if that someone is just me at a later point in time when I’ve forgotten this).
This one sets some default times based on spreadsheet variables stashed elsewhere and then automates some date/time entries using the form sidebar.
Sidebar Code
function onSuccess(startTime) { document.getElementById('startTime').value = startTime; //thing you want done after getting the variable } google.script.run.withSuccessHandler(onSuccess) .getDefaultStartTime(); //name of Google Script function you want the variable from
Google Script Code
function getDefaultStartTime(){ var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName('validation'); var am = sheet.getRange("C1").getValue(); h = (am.getHours()<10?'0':'') + am.getHours(), m = (am.getMinutes()<10?'0':'') + am.getMinutes(); var startHr = h + ':' + m; return startTime; }
The entire sidebar/script setup is below in case anyone cares for some reason.
//adds menu item function onOpen() { SpreadsheetApp.getUi() .createMenu('Add Time') .addItem('Do it!', 'addTime') .addToUi(); } //set validation elements function validationRighter(){ var ss = SpreadsheetApp.getActiveSpreadsheet(); var meetColumn = ss.getSheetByName('Sheet1').getRange('C2:C'); //meeting types var meetValidationValues = ss.getSheetByName('validation').getRange('A1:A40').getValues(); var meetRule = SpreadsheetApp.newDataValidation().requireValueInList(meetValidationValues).build(); meetColumn.setDataValidation(meetRule); var appColumn = ss.getSheetByName('Sheet1').getRange('D2:D'); //application options var appValidationValues = ss.getSheetByName('validation').getRange('B1:B40').getValues(); var appRule = SpreadsheetApp.newDataValidation().requireValueInList(appValidationValues).build(); appColumn.setDataValidation(appRule); var userColumn = ss.getSheetByName('Sheet1').getRange('E2:E'); //teacher signup options var userValidationValues = ss.getEditors(); Logger.log(userValidationValues); var userRule = SpreadsheetApp.newDataValidation().requireValueInList(userValidationValues).build(); userColumn.setDataValidation(userRule); } //add time sidebar creation function addTime() { var html = HtmlService.createHtmlOutputFromFile('sidebar') .setTitle('Add Time') .setWidth(300); SpreadsheetApp.getUi() // Or DocumentApp or FormApp. .showSidebar(html); } //enter days in 15 min intervals function enterDay(theDate,startTime,endTime) { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet(); var lastRow = parseInt(sheet.getLastRow())+1; var startH = startTime.split(':')[0]; var endH = endTime.split(':')[0]; var startM = startTime.split(':')[1]; var d = new Date(); d.setHours(startH); d.setMinutes(startM); var totalHours = endH-startH; for (i = 0; i < (totalHours*4)+1; i++) { sheet.getRange("A"+(lastRow+i)).setValue(theDate); var mins = (d.getMinutes()<10?'0':'') + d.getMinutes();//make sure has leading 0 sheet.getRange("B"+(lastRow+i)).setValue(d.getHours()+':'+mins); d = new Date(d.getTime() + (15*60000)); //adds 15 mins } } function hideAllRows(){ var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet(); var lastRow = sheet.getLastRow(); sheet.hideRows(2, lastRow); } function processForm(formObject) { var theDate = formObject.theDate; var startTime = formObject.startTime; var endTime = formObject.endTime; var hideRows = formObject.hider; Logger.log(hideRows); if (hideRows === 'on'){ hideAllRows(); } enterDay(theDate, startTime, endTime); validationRighter(); } function getDefaultStartTime(){ var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName('validation'); var am = sheet.getRange("C1").getValue(); h = (am.getHours()<10?'0':'') + am.getHours(), m = (am.getMinutes()<10?'0':'') + am.getMinutes(); var startHr = h + ':' + m; return startHr; } function getDefaultEndTime(){ var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName('validation'); var pm = sheet.getRange("C2").getValue(); h = (pm.getHours()<10?'0':'') + pm.getHours(), m = (pm.getMinutes()<10?'0':'') + pm.getMinutes(); var endHr = h + ':' + m;; Logger.log(endHr); return endHr; }
Comments are closed.