如何从单元格制作侧边栏显示值?
[英] How do I make a Sidebar display values from cells?
本文介绍了如何从单元格制作侧边栏显示值?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个 Google 表格，用作客户数据库.我需要为每个条目存储很多变量(列)，所以我认为如果我可以让侧边栏显示活动行的所有值，那么查看每个条目会更容易.
<块引用>
示例:如果我单击单元格 C1，它会在侧边栏中显示来自 C1、C2、C3 和 C4 的值.
这可能吗?我已经让侧边栏显示了，但我不知道如何从单元格中获取值并将其打印到侧边栏.
提前谢谢你，我的(非常)有限的智力已经走到了尽头！
解决方案
类似的事情?
这个附加组件使用了 如何从附加组件轮询 Google 文档 以调用服务器函数 getRecord().该函数获取当前选择的数据行，并将其返回给客户端 (JavaScript) 端的 showRecord() 回调，该回调处理侧栏中的演示文稿.
它不完整 - 注意 TODO 评论.我想知道这是否值得进一步开发和发布?
代码.gs
/*** @OnlyCurrentDoc 将脚本限制为仅访问当前电子表格.*/var SIDEBAR_TITLE = '记录查看器';/*** 添加带有项目的自定义菜单以显示侧边栏和对话框.** @param {Object} e 一个简单的 onOpen 触发器的事件参数.*/函数 onOpen(e) {电子表格App.getUi().createAddonMenu().addItem('查看记录', 'showSidebar').addToUi();}/*** 安装插件时运行；调用 onOpen() 以确保菜单创建和* 任何其他初始化工作都会立即完成.** @param {Object} e 一个简单的 onInstall 触发器的事件参数.*/功能 onInstall(e) {onOpen(e);}/*** 打开侧边栏.Sidebar.html 中描述了侧边栏结构* 项目文件.*/功能显示侧边栏(){var ui = HtmlService.createTemplateFromFile('Sidebar').评价().setSandboxMode(HtmlService.SandboxMode.IFRAME).setTitle(SIDEBAR_TITLE);SpreadsheetApp.getUi().showSidebar(ui);}/*** 返回活动行.** @return {Object[]} 标题 &行中所有单元格的值.*/函数 getRecord() {//检索并返回侧边栏请求的信息.var sheet = SpreadsheetApp.getActiveSheet();var data = sheet.getDataRange().getValues();var 标头 = 数据 [0];var rowNum = sheet.getActiveCell().getRow();if (rowNum > data.length) return [];var 记录 = [];for (var col=0;col<headers.length;col++) {var cellval = data[rowNum-1][col];//日期必须作为字符串传递 - 现在使用固定格式if (typeof cellval == "object") {cellval = Utilities.formatDate(cellval, Session.getScriptTimeZone(), "M/d/yyyy");}//TODO: 使用 SheetConverter 库格式化所有单元格值record.push({ 标题: headers[col],cellval:cellval });}退货记录；}
侧边栏.html
<!-- 使用模板化的 HTML 打印脚本导入通用样式表.--><?!= HtmlService.createHtmlOutputFromFile('Stylesheet').getContent();?><!-- 下面是定义侧边栏元素结构的 HTML 代码.--><div class="sidebarbranding-below"><p>此侧边栏显示一行中的所有单元格，作为记录".</p><!-- div-table 类用于使一组 div 表现得像一个表格.--><div class="block div-table" id="sidebar-record-block">
<div class="block" id="sidebar-button-bar">
<div id="sidebar-status"></div>
<!-- 在下面输入侧边栏底部品牌.--><div class="侧边栏底部"><img alt="附加标志" class="标志" width="25"src="https://googledrive.com/host/0B0G1UdyJGrY6XzdjQWF4a1JYY1k/apps-script_2x.png"><span class="gray branding-text">Mogsdad 的记录查看器</span>
<!-- 使用模板化的 HTML 打印脚本来导入 JavaScript.--><?!= HtmlService.createHtmlOutputFromFile('SidebarJavaScript').getContent();?>
SidebarJavascript.html
<script src="//ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></脚本><脚本>/*** 在侧边栏加载时运行初始化.*/$(函数(){//如果需要，在此处为侧边栏元素分配处理函数.//在此处调用服务器以检索构建所需的任何信息//对话框，如有必要.//开始轮询更新轮询();});/*** 在给定的时间间隔轮询服务器端函数，以获得* 结果传递给一个 successHandler 回调.** https://stackoverflow.com/a/24773178/1677912** @param {Number} 间隔(可选)轮询之间的时间(以毫秒为单位).* 默认为 2 秒(2000 毫秒)*/函数轮询(间隔){间隔 = 间隔 ||1000；设置超时(功能(){google.script.run.withSuccessHandler(showRecord).withFailureHandler(功能(味精，元素){showStatus(msg, $('#button-bar'));element.disabled = false;}).getRecord();}， 间隔);};/*** 显示记录"或电子表格行的回调函数.** @param {object[]} 字段标题数组 &单元格值*/功能显示记录(记录){如果(记录长度){for (var i = 0; i < record.length; i++) {//动态构建字段名称，格式为 field-1234var str = '' + i;var fieldId = 'field-' + ('0000' + str).substring(str.length)//如果页面上不存在此字段#，则创建它如果 (!$('#'+fieldId).length) {var newField = $($.parseHTML('<div id="'+fieldId+'"></div>'));$('#sidebar-record-block').append(newField);}//用新记录替换字段 div 的内容$('#'+fieldId).replaceWith('<div id="'+fieldId+'" class="div-table-row"></div>');$('#'+fieldId).append($('<div class="div-table-th">' + record[i].heading + '</div>')).append('<div class="div-table-td">' + record[i].cellval + '</div>');}}//TODO: 隐藏任何超出当前记录长度的现有字段//设置下一次轮询轮询();}/*** 在侧边栏中显示给定的状态消息.** @param {String} msg 要显示的状态消息.* @param {String} classId 消息的消息类型(class id)* 应显示为.*/函数 showStatus(msg, classId) {$('#sidebar-status').removeClass().html(msg);如果(classId){$('#sidebar-status').addClass(classId);}}
样式表.html
<!-- 这个 CSS 包应用了 Google 样式；它应该始终包括在内.--><link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons.css"><风格>标签 {字体粗细:粗体；}.branding-below {底部:54px；顶部:0；}.branding-text {左:7px；位置:相对；顶部:3px；}.标识 {垂直对齐:中间；}.width-100 {宽度:100%；box-sizing: 边框框；-webkit-box-sizing : 边框框；-moz-box-sizing :边框框；}#sidebar-value-block,#对话框元素{背景色:#eee;边框颜色:#eee;边框宽度:5px；边框样式:实心；}#sidebar-button-bar,#dialog-button-bar {底边距:10px；}.div 表{显示:表；宽度:自动；/* 背景颜色:#eee;边框:1px 实心 #666666;*/边框间距:5px；}.div 表行{显示:表格行；宽度:自动；清除:两者；}.div-table-td, .div-table-th {显示:表格单元格；宽度:200px；背景色:rgb(230, 230, 230);}.div-table-th {/*向左飘浮;*/字体粗细:粗体；}.div-表-td {/*浮动:右；*/}</风格>
I have a Google Sheet that I'm using as a database of clients. There are quite a lot of variables (columns) that I need to store per entry, so I thought that it would be easier to view each entry if I could have a Sidebar display all of the values of the active row.
Example: If I click cell C1, it will display the values from C1, C2, C3 and C4 in the sidebar.
Is this possible? I've gotten as far as getting the Sidebar to display but I cannot figure out how to get a value from a cell and print it to the Sidebar.
Thank you in advance, I've reached a dead end with my (very) limited intellect!
解决方案
Something like this?
This add-on uses the poller idea from How to poll a Google Doc from an add-on to call a server function getRecord(). That function grabs the row of data that is currently selected, and returns it to the showRecord() callback on the client (JavaScript) side, which handles the presentation in the sidebar.
It's not complete - watch for TODO comments. I wonder if this would be worth further developing and publishing?
Code.gs
/**
* @OnlyCurrentDoc Limits the script to only accessing the current spreadsheet.
*/
var SIDEBAR_TITLE = 'Record Viewer';
/**
* Adds a custom menu with items to show the sidebar and dialog.
*
* @param {Object} e The event parameter for a simple onOpen trigger.
*/
function onOpen(e) {
SpreadsheetApp.getUi()
.createAddonMenu()
.addItem('View records', 'showSidebar')
.addToUi();
}
/**
* Runs when the add-on is installed; calls onOpen() to ensure menu creation and
* any other initializion work is done immediately.
*
* @param {Object} e The event parameter for a simple onInstall trigger.
*/
function onInstall(e) {
onOpen(e);
}
/**
* Opens a sidebar. The sidebar structure is described in the Sidebar.html
* project file.
*/
function showSidebar() {
var ui = HtmlService.createTemplateFromFile('Sidebar')
.evaluate()
.setSandboxMode(HtmlService.SandboxMode.IFRAME)
.setTitle(SIDEBAR_TITLE);
SpreadsheetApp.getUi().showSidebar(ui);
}
/**
* Returns the active row.
*
* @return {Object[]} The headers & values of all cells in row.
*/
function getRecord() {
// Retrieve and return the information requested by the sidebar.
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
var headers = data[0];
var rowNum = sheet.getActiveCell().getRow();
if (rowNum > data.length) return [];
var record = [];
for (var col=0;col<headers.length;col++) {
var cellval = data[rowNum-1][col];
// Dates must be passed as strings - use a fixed format for now
if (typeof cellval == "object") {
cellval = Utilities.formatDate(cellval, Session.getScriptTimeZone() , "M/d/yyyy");
}
// TODO: Format all cell values using SheetConverter library
record.push({ heading: headers[col],cellval:cellval });
}
return record;
}
Sidebar.html
<!-- Use a templated HTML printing scriptlet to import common stylesheet. -->
<?!= HtmlService.createHtmlOutputFromFile('Stylesheet').getContent(); ?>
<!-- Below is the HTML code that defines the sidebar element structure. -->
<div class="sidebar branding-below">
<p>
This sidebar displays all cells in a row, as a "record".
</p>
<!-- The div-table class is used to make a group of divs behave like a table. -->
<div class="block div-table" id="sidebar-record-block">
</div>
<div class="block" id="sidebar-button-bar">
</div>
<div id="sidebar-status"></div>
</div>
<!-- Enter sidebar bottom-branding below. -->
<div class="sidebar bottom">
<img alt="Add-on logo" class="logo" width="25"
src="https://googledrive.com/host/0B0G1UdyJGrY6XzdjQWF4a1JYY1k/apps-script_2x.png">
<span class="gray branding-text">Record Viewer by Mogsdad</span>
</div>
<!-- Use a templated HTML printing scriptlet to import JavaScript. -->
<?!= HtmlService.createHtmlOutputFromFile('SidebarJavaScript').getContent(); ?>
SidebarJavascript.html
<script src="//ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script>
<script>
/**
* Run initializations on sidebar load.
*/
$(function() {
// Assign handler functions to sidebar elements here, if needed.
// Call the server here to retrieve any information needed to build
// the dialog, if necessary.
// Start polling for updates
poll();
});
/**
* Poll a server-side function at the given interval, to have
* results passed to a successHandler callback.
*
* https://stackoverflow.com/a/24773178/1677912
*
* @param {Number} interval (optional) Time in ms between polls.
* Default is 2s (2000ms)
*/
function poll(interval) {
interval = interval || 1000;
setTimeout(function() {
google.script.run
.withSuccessHandler(showRecord)
.withFailureHandler(
function(msg, element) {
showStatus(msg, $('#button-bar'));
element.disabled = false;
})
.getRecord();
}, interval);
};
/**
* Callback function to display a "record", or row of the spreadsheet.
*
* @param {object[]} Array of field headings & cell values
*/
function showRecord(record) {
if (record.length) {
for (var i = 0; i < record.length; i++) {
// build field name on the fly, formatted field-1234
var str = '' + i;
var fieldId = 'field-' + ('0000' + str).substring(str.length)
// If this field # doesn't already exist on the page, create it
if (!$('#'+fieldId).length) {
var newField = $($.parseHTML('<div id="'+fieldId+'"></div>'));
$('#sidebar-record-block').append(newField);
}
// Replace content of the field div with new record
$('#'+fieldId).replaceWith('<div id="'+fieldId+'" class="div-table-row"></div>');
$('#'+fieldId).append($('<div class="div-table-th">' + record[i].heading + '</div>'))
.append('<div class="div-table-td">' + record[i].cellval + '</div>');
}
}
// TODO: hide any existing fields that are beyond the current record length
//Setup the next poll
poll();
}
/**
* Displays the given status message in the sidebar.
*
* @param {String} msg The status message to display.
* @param {String} classId The message type (class id) that the message
* should be displayed as.
*/
function showStatus(msg, classId) {
$('#sidebar-status').removeClass().html(msg);
if (classId) {
$('#sidebar-status').addClass(classId);
}
}
</script>
Stylesheet.html
<!-- This CSS package applies Google styling; it should always be included. -->
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons.css">
<style>
label {
font-weight: bold;
}
.branding-below {
bottom: 54px;
top: 0;
}
.branding-text {
left: 7px;
position: relative;
top: 3px;
}
.logo {
vertical-align: middle;
}
.width-100 {
width: 100%;
box-sizing: border-box;
-webkit-box-sizing : border-box;
-moz-box-sizing : border-box;
}
#sidebar-value-block,
#dialog-elements {
background-color: #eee;
border-color: #eee;
border-width: 5px;
border-style: solid;
}
#sidebar-button-bar,
#dialog-button-bar {
margin-bottom: 10px;
}
.div-table{
display:table;
width:auto;
/* background-color:#eee;
border:1px solid #666666;*/
border-spacing:5px;
}
.div-table-row{
display:table-row;
width:auto;
clear:both;
}
.div-table-td, .div-table-th {
display:table-cell;
width:200px;
background-color:rgb(230, 230, 230);
}
.div-table-th {
/*float:left;*/
font-weight: bold;
}
.div-table-td {
/*float:right;*/
}
</style>
这篇关于如何从单元格制作侧边栏显示值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
