How To Pass Values To An Html Page From Google Script
Solution 1:
Kos's answer gave me some ideas on how I could work it out. That, as well as some additional reading, especially https://www.w3schools.com/js/js_json_intro.asp and the follow up sections, helped me figure this one out.
New js code:
function onEdit(e) {
if(e.range.getColumn() == 9 && e.value == "Cancelled" && e.source.getActiveSheet().getName() == "Summary") {
varcancelSheet= ss.getSheetByName(e.source.getActiveSheet().getName());
varcancelRange= cancelSheet.getRange(e.range.getRow(), 1, 1, cancelSheet.getLastColumn());
varcancelRow= cancelRange.getValues();
//openCancelDialog(cancelRow);varaSheet= e.source.getActiveSheet().getName();
varcolumn= e.range.getColumn();
varrow= e.range.getRow();
Logger.log("Col: " + column + " Row: " + row + " Sheet: " + aSheet);
Logger.log(cancelRow);
}
Logger.log(e);
}
function openCancelDialog(row) {
varui= SpreadsheetApp.getUi();
// get templatevartemplate= HtmlService.createTemplateFromFile('Cancel');
varmyJSON= JSON.stringify(row);
// pass data to template
template.data = myJSON;
// get output htmlvarhtml= template.evaluate();
// show modal window
ui.showModalDialog(html, 'Cancel a Project');
}
New HTML:
<!DOCTYPE html><html><body><table><tr><td>Number: </td><tdid="number"></td></tr><tr><td>Name: </td><tdid="name"></td></tr><tr><td>Category: </td><tdid="category"></td></tr><tr><td>Business Owner: </td><tdid="owner"></td></tr><tr><td>Project : </td><tdid="manager"></td></tr></table><script>var objII = JSON.parse(<?=data?>);
document.getElementById("number").innerHTML = objII[0][0];
document.getElementById("name").innerHTML = objII[0][4];
document.getElementById("category").innerHTML = objII[0][1];
document.getElementById("owner").innerHTML = objII[0][17];
document.getElementById("manager").innerHTML = objII[0][18];
</script></body></html>I suspect there may be more elegant ways to do this, and probably even more "correct" ways. But this seems to be working for what I needed it to do, so I figured I'd post it in case someone else was looking.
Thank you
Solution 2:
Use HtmlService.createTemplateFromFile:
functionopenCancelDialog(row)
{
var ui = SpreadsheetApp.getUi();
// get templatevar template = HtmlService.createTemplateFromFile('Cancel');
// pass data to template
template.data = {
row: JSON.stringify(row)
};
// get output htmlvar html = template.evaluate();
// show modal window
ui.showModalDialog(html, 'Cancel a Project');
}
Cancel.html:
<!DOCTYPE html><html><head><basetarget="_top"></head><body><script>var row = <?!=data.row?>;
//document.write(row);</script></body></html>Detailed template documentation: https://developers.google.com/apps-script/guides/html/templates
Solution 3:
Here's another way to do it. I like to do it this way because I have a lot more control than I do with templates.
This is a script that I did when I was working on an email example script that is contained in a spreadsheet. This script is a little less complicated because it's just for giving the user the option for removing sent emails from the emailsetup page and archiving them on another page. It does it by creating html on the fly and collecting it as a string and then adding it to another page of html. I launch the html at the end of the script as a dialog that allows the users to select which emails to archive by checking checkboxes and clicking on a button called Archive Selected. I found it easier to put my javascript functions together in a standard html file and then run that through HtmlService first and append the string later.
Here's the script:
function archiveSelectedEmails()
{
var ss=SpreadsheetApp.getActiveSpreadsheet();
var sht=ss.getSheetByName('EmailSetup');
var rng=sht.getDataRange();
var rngA=rng.getValues();
var s='<html><head><scriptsrc="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script></head><body>';
var s='';
for(var i=2;i<rngA.length;i++)
{
var dataA={};
for(var j=0;j<rngA[1].length;j++)
{
dataA[rngA[1][j]]=rngA[i][j];
}
var row=Number(i+1);
s+='<divid="row' + row + '"><inputtype="checkbox"name="email"value="' + Number(i+1) + '" />' + ' <strong>Row:</strong> ' + Number(i+1) + ' <strong>Name:</strong> ' + dataA.Name + ' <strong>Email:</strong> ' + dataA.Email + ' <strong>Subject:</strong> ' + dataA.Subject + ' <strong>DateSent:</strong> ' + Utilities.formatDate(new Date(dataA.DateSent), 'GMT-6', "M/dd/yyyy HH:mm:ss") + '</div>';
}
s+='<br /><inputtype="button"value="Exit"onClick="google.script.host.close();" /><inputtype="button"value="Archive Checked"onClick="getCheckedBoxes(\'email\');" />';
var html=HtmlService.createHtmlOutputFromFile('htmlToBody').setWidth(800).setHeight(250);
html.append(s);
SpreadsheetApp.getUi().showModelessDialog(html, 'Select Emails to Archive');
}
Here's the html file 'htmlToBody':
<!DOCTYPE html><html><head><scriptsrc="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script><script>functiongetCheckedBoxes(chkboxName) {
var checkboxes = document.getElementsByName(chkboxName);
var rowsToArchive = [];
for (var i=0; i<checkboxes.length; i++)
{
if (checkboxes[i].checked)
{
rowsToArchive.push(Number(checkboxes[i].value));
}
}
google.script.run
.withSuccessHandler(setResponse)
.archiveSelectedRows(rowsToArchive);
}
functionsetResponse(a)
{
var s='<br />Rows: ';
for(var i=0;i<a.length;i++)
{
if(i>0)
{
s+=', ';
}
s+=a[i];
var id='#row' + a[i]
$(id).css('display','none');
}
s+='<br />Total: ' + a.length;
google.script.run.displayMessage(s,'Archived Rows')
}
console.log('script here');
</script></head><body>I took your project idea and ran with it a little.
These are the google scripts. You'll notice I started with the name of your function.
functionopenCancelDialog1()
{
var ss=SpreadsheetApp.getActiveSpreadsheet();
var sht=ss.getSheetByName('Projects');
var rng=sht.getDataRange();
var rngA=rng.getValues();
var s='';
for(var i=1;i<rngA.length;i++)
{
var dataA={};
for(var j=0;j<rngA[0].length;j++)
{
dataA[rngA[0][j]]=rngA[i][j];
}
var row=Number(i+1);
s+='<div id="row' + row + '"><input type="checkbox" name="project" value="' + row + '" />' + ' <strong>Row:</strong> ' + Number(i+1) + ' <strong>Name:</strong> ' + dataA.Name + ' <strong>Project:</strong> ' + dataA.Description + '</div>';
}
s+='<br /><input type="button" value="Exit" onClick="google.script.host.close();" /><input type="button" value="Cancel and Archive Checked" onClick="getCheckedBoxes(\'project\');" />';
var html=HtmlService.createHtmlOutputFromFile('htmlToBody').setWidth(800).setHeight(250);
html.append(s);
SpreadsheetApp.getUi().showModelessDialog(html, 'Select Project to Cancel');
}
functionarchiveSelectedRows(rows)
{
var ss=SpreadsheetApp.getActiveSpreadsheet();
var sht=ss.getSheetByName('Projects');
var dest=ss.getSheetByName('ArchivedProjects');
var rng=sht.getDataRange();
var rngA=rng.getValues();
var deleted=[];
for(var i=rngA.length-1;i>1;i--)
{
if(rows.indexOf(i+1)>-1)
{
deleted.push(Number(i+1));
rngA[i][4]=Utilities.formatDate(newDate(), 'GMT-7', 'M/d/yyyy')
dest.appendRow(rngA[i]);
sht.deleteRow(i+1);
}
}
var msg='Row Numbers Deleted = ' + deleted;
var title='Rows Deleted';
var timeout=10;
return deleted;
}
functiondisplayMessage(msg,title)
{
msg+='<br /><input type="button" value="Exit" onClick="google.script.host.close()"; />';
var html=HtmlService.createHtmlOutput(msg).setWidth(400).setHeight(300);
SpreadsheetApp.getUi().showModelessDialog(html, title);
}
This is the htmlTobody file. It's been modified a bit for this situation.
<!DOCTYPE html><html><head><scriptsrc="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script><script>functiongetCheckedBoxes(chkboxName) {
var checkboxes = document.getElementsByName(chkboxName);
var rowsToArchive = [];
for (var i=0; i<checkboxes.length; i++)
{
if (checkboxes[i].checked)
{
rowsToArchive.push(Number(checkboxes[i].value));
}
}
google.script.run
.withSuccessHandler(setResponse)
.archiveSelectedRows(rowsToArchive);
}
functionsetResponse(a)
{
var s='<br />Row Numberss: ';
for(var i=0;i<a.length;i++)
{
if(i>0)
{
s+=', ';
}
s+=a[i];
var id='#row' + a[i]
$(id).css('display','none');
}
s+='<br />Total: ' + a.length;
google.script.run.displayMessage(s,'Canceled Rows')
}
console.log('script here');
</script></head><body>And this is what my 'Projects' tab looks like. And I have a Projects tab and an ArchivedProjects tab. When I archive the projects they get copied into the ArchivedProjects sheet.
Post a Comment for "How To Pass Values To An Html Page From Google Script"