在自定义HTML侧栏中使用.gs文件中的变量值 [英] Use variable value from .gs file in custom HTML sidebar
问题描述
我有一个google工作表,我在其中编写了自定义侧边栏。
在侧边栏中,我想在活动行中显示第I列的值。 / p>
我设法制作脚本以在侧边栏打开时存储变量'I':
var hotelName =
function openSidebar(){
var html = HtmlService.createHtmlOutputFromFile('index');
SpreadsheetApp.getUi()。showSidebar(html);
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName(合同登记文件);
var row = s.getActiveCell()。getRow();
var column = 9;
hotelName = s.getRange(row,column).getValue();
Logger.log(hotelName);
}
但是我现在需要它出现在我的侧边栏中的空DIV元素中。如何导出此变量?
更新:
效果很好。我现在还添加了侧边栏按钮以刷新,我想要做的是更新脚本,这样如果用户更改了行,则会显示新的酒店名称。目前我添加了这个HTML:
< div id =footer>
< div><?= hotel?>< / div>
< p>< i class =fa fa-refresharia-hidden =trueid =testing>< / i>< / p>
< / div>
这个JQuery到索引文件:
$(#testing)。on('click',function buttonClick(){
google.script.run.buttonClick();
}) ;
对于我刚刚复制原始函数的函数:
function buttonClick(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName(合同登记文件);
var row = s.getActiveCell()。getRow();
var column = 9;
var hotelName = s.getRange(row,column).getValue();
//在检索值后获取html模板
var html_template = HtmlService.createTemplateFromFile('index');
//设置index.html`hotel`占位符/ scriptlet的值
html_template.hotel = hotelName;
//评估模板
var sidebar_html = html_template.evaluate();
SpreadsheetApp.getUi()。showSidebar(sidebar_html);
Logger.log(hotelName);
}
但是,我真的希望单元格的值异步更新,以便侧边栏没有重新加载,只是酒店名称...
这可能吗?
在加载侧边栏时从电子表格中获取数据
在显示侧边栏之前,不要忘记先选择行。
code.gs
function openThisSideBar()
{
var html = HtmlService.createHtmlOutputFromFile('hotel')。setTitle('MySideBar');
SpreadsheetApp.getUi()。showSidebar(html);
}
函数getHotel()
{
var s = SpreadsheetApp.getActive()。getSheetByName(Contract Registry Document);
var row = s.getActiveCell()。getRow();
var n = Utilities.formatString('您的酒店名称为:%s',s.getRange(row,9).getValue());
返回n;
}
函数onOpen()
{
SpreadsheetApp.getUi()。createMenu('MyTools')
.addItem('Open Sidebar', 'openThisSideBar')
.addToUi();
}
hotel.html:
<!DOCTYPE html>
< html>
< head>
< script src =// ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js\"></script>
< script>
$(function()
{
google.script.run
.withSuccessHandler(updateHotel)
.getHotel();
});
函数updateHotel(name)
{
document.getElementById('hotel')。innerHTML = name;
}
console.log('MyCode');
< / script>
< / head>
< body>
< div id =hotel>< / div>
< / body>
< / html>
您可以像这样添加刷新:
<脚本>
函数refresh()
{
google.script.run
.withSuccessHandler(updateHotel)
.getHotel();
}
< / script>
新的html:
< input type =buttonvalue =刷新onClick =refresh(); />
这样你不需要刷新整个页面而只需刷新div 。
I have a google sheet where I have scripted a custom sidebar.
Within the sidebar, I want to display the value of column I within the active row.
I've managed to work out the script to store the variable 'I' when the sidebar is opened:
var hotelName = ""
function openSidebar() {
var html = HtmlService.createHtmlOutputFromFile('index');
SpreadsheetApp.getUi().showSidebar(html);
var ss=SpreadsheetApp.getActiveSpreadsheet();
var s=ss.getSheetByName("Contract Registry Document");
var row=s.getActiveCell().getRow();
var column=9;
hotelName = s.getRange(row, column).getValue();
Logger.log(hotelName);
}
However I now need this to appear in an empty DIV element in my sidebar. How do I export this variable?
UPDATE:
That works great. I've now added as well a button to the sidebar to refresh and what I want that to do is to update the script so that if the user has changed rows, the new hotel name will show. At the moment I've added this HTML:
<div id="footer">
<div><?= hotel ?></div>
<p><i class="fa fa-refresh" aria-hidden="true" id="testing"></i></p>
</div>
And this JQuery to the index file:
$("#testing").on('click', function buttonClick() {
google.script.run.buttonClick();
});
And for the function I just copied the original function:
function buttonClick() {
var ss=SpreadsheetApp.getActiveSpreadsheet();
var s=ss.getSheetByName("Contract Registry Document");
var row=s.getActiveCell().getRow();
var column=9;
var hotelName = s.getRange(row, column).getValue();
// get the html template after retrieving the values
var html_template = HtmlService.createTemplateFromFile('index');
// set the value for the index.html `hotel` placeholder/scriptlet
html_template.hotel = hotelName;
// evaluate the template
var sidebar_html = html_template.evaluate();
SpreadsheetApp.getUi().showSidebar(sidebar_html);
Logger.log(hotelName);
}
However, really I want the value of the cell to update asynchronously so that the sidebar doesn't re-load, just the hotel name...
Would this be possible??
Getting data from a spreadsheet on loading a sidebar
Don't forget to select the row first before displaying the sidebar.
code.gs
function openThisSideBar()
{
var html = HtmlService.createHtmlOutputFromFile('hotel').setTitle('MySideBar');
SpreadsheetApp.getUi().showSidebar(html);
}
function getHotel()
{
var s=SpreadsheetApp.getActive().getSheetByName("Contract Registry Document");
var row=s.getActiveCell().getRow();
var n=Utilities.formatString('Your Hotel is named: %s',s.getRange(row, 9).getValue());
return n;
}
function onOpen()
{
SpreadsheetApp.getUi().createMenu('MyTools')
.addItem('Open Sidebar', 'openThisSideBar')
.addToUi();
}
hotel.html:
<!DOCTYPE html>
<html>
<head>
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<script>
$(function()
{
google.script.run
.withSuccessHandler(updateHotel)
.getHotel();
});
function updateHotel(name)
{
document.getElementById('hotel').innerHTML=name;
}
console.log('MyCode');
</script>
</head>
<body>
<div id="hotel"></div>
</body>
</html>
You can add your refresh like this:
<script>
function refresh()
{
google.script.run
.withSuccessHandler(updateHotel)
.getHotel();
}
</script>
And the new html:
<input type="button" value="Refresh" onClick="refresh();" />
That way you needn't refresh the entire page but just the div.
这篇关于在自定义HTML侧栏中使用.gs文件中的变量值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!