I am currently working with the following tutorial to display google sheet data in the sidebar: https://yagisanatode.com/2018/02/12/how-to-get-something-from-google-sheets-and-display-it-in-the-sidebar-in-google-apps-script/#more-219
However, I would like to add a "for" loop in my html file to display a number of checkboxes (see code below). Ultimately, I want to change the number "16" to the "rangeResult" value from my spread sheet. Can you offer suggestions on how to do this?
Code.gs - function
function numEarlyRelease() {
var doc = SpreadsheetApp.openById("1OF6Y1CTU9dkIgd1P-nw-5f2lqHSS5cGZytndwzJhw-o");
var ss = SpreadsheetApp.getActiveSpreadsheet();
var numValues = ss.getRange('R19').getValue();
return numValues;
}
Page.html file
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
</head>
<body>
<h3><script> document.write(new Date().toLocaleDateString()); </script></h3>
<div id="rangeResult"></div>
<script>
function addRange(rangeStartEnd){
$('#rangeResult').text(rangeStartEnd);
};
for (var i = 1; i <=16; i++) {
document.write('<br><input type="checkbox" name="scores" id="i" value="i">'+ i);
}
google.script.run.withSuccessHandler(addRange).numEarlyRelease();
</script>
<br><input type="button" value="Submit Check-In" onclick="google.script.host.close()" />
<input type="button" value="Close" onclick="google.script.host.close()" />
</body>
</html>
Thank you for your help!
The function call -
google.script.run.withSuccessHandler(addRange).numEarlyRelease();
Means if function numEarlyRelease() returns a value, in this case numValues, it is available as argument to function addRange(). addRange() takes a parameter rangeStartEnd, so it'll have the value of numValues. You can use that however you like. As an example, if it is a number -
for (var i = 1; i <=rangeStartEnd; i++) {
document.append('<br><input type="checkbox" name="scores" id="i" value="i">'+ i);
}
Note: Do not use document.write() unless you want to overwrite everything that is in page already.
Thank you for your suggestions! Below is my solution. (I've changed the names of some of the variables to fit my project.)
Code.gs
function earlyRelease(e) {
var doc = SpreadsheetApp.openById("1OF6Y1CTU9dkIgd1P-nw-5f2lqHSS5cGZytndwzJhw-o");
var ss = SpreadsheetApp.getActiveSpreadsheet();
var numValues = ss.getRange('R19').getValue();
var studentName = ss.getSheetValues(20, 18, numValues, 1);
var html = HtmlService.createHtmlOutputFromFile('Page')
.setTitle('Early Release')
.setWidth(300);
SpreadsheetApp.getUi().showSidebar(html); }
function earlyReleaseList() {
var doc = SpreadsheetApp.openById("1OF6Y1CTU9dkIgd1P-nw-5f2lqHSS5cGZytndwzJhw-o");
var ss = SpreadsheetApp.getActiveSpreadsheet();
var numValues = ss.getRange('R19').getValue();
var studentNames = ss.getSheetValues(20, 18, numValues, 1);
return studentNames; }
Page.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
</head>
<body>
<script>
function addStudents(studentList){
$('#rangeResult').text(studentList);
document.write(new Date().toLocaleDateString());
var students = [];
//The following loop was added to the function
for (var i = 0; i < studentList.length; i++) {
document.write('<br><input type="checkbox" name="students" id="i" value="i">'+ studentList[i]);
}
//Also added the buttons to the function with document.write rather than html tags outside of the function
document.write('<br><input type="button" value="Submit Early Release" onclick="google.script.host.close()" />');
document.write('<input type="button" value="Close" onclick="google.script.host.close()" />');
};
google.script.run.withSuccessHandler(addStudents).earlyReleaseList();
</script>
</body>
</html>
