在表格中选择下一行时，更新电子表格侧栏中的值 [英] Update value in a spreadsheet sidebar when next row is selected in table
问题描述
为了便于在Google电子表格中注释音频文件，我想在侧栏中实现一个音频播放器，该播放器会自动播放表行中提到的URL音频文件.在听完并在此行中输入一些日期之后，我想移至下一行并执行相同的操作.因此，每当我选择一个新行时，音频文件的URL就应该更新，并且整个过程也应该很快，以便快速地听一个声音文件.
To facilitate the annotation of audio files in a Google spreadsheet, I'd like to implement an audio player in the sidebar which automatically plays the audio file mentioned as URL in the row of a table. After listening and entering some date in this row, I'd like to move to the next row and do the same. Thus, the URL to the audio file should be updated whenever I select a new row and the whole process should be fast, too, in order to listen quickly to one sound file after the other.
我已经尝试了在中提到的解决方案这样的帖子，，但是此解决方案依赖于具有时间间隔的轮询功能，这对我来说是不切实际的，因为它会定期更新边栏.对我而言，至关重要的是仅一次更新侧边栏的内容.
I've experimented with the solution mentioned in this SO post, but this solution is relying on a poll function with a time interval, which is impractical for me as it periodically is updating the sidebar. Crucial for me would be to update the content of the sidebar only once.
Code.gs
var SIDEBAR_TITLE = 'Opnam lauschteren';
/**
* 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('Opname lauschteren', '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);
}
function getValues() {
var app = SpreadsheetApp;
var value = app.getActiveSpreadsheet().getActiveSheet().getActiveCell().getValue();
Logger.log(value);
return value;
}
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 });
}
Logger.log(record);
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">
<!-- 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>
<!-- Use a templated HTML printing scriptlet to import JavaScript. -->
<?!= HtmlService.createHtmlOutputFromFile('SidebarJavaScript').getContent(); ?>
</div>
<!-- Enter sidebar bottom-branding below. -->
<div class="sidebar bottom">
<span class="gray branding-text">PG</span>
</div>
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 || 3000;
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 = 2; i <= 2; 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('<audio id="player" controls > <source src=' + record[i].cellval + ' type=audio/wav > Your browser does not support the audio element. </audio>');
}
}
// 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>
在这里；附件> 播放音频"(需要Google帐户).
A reproducible example is accessible here; Add-ons > 'play audio' (Google account necessary).
我正在努力寻找一种仅在选择新行时才触发一次边栏更新的方法.边栏的使用不是强制性的，而是另一种解决方案，例如带有自动更新的播放"按钮，也会很有帮助.
I am struggling finding a method to trigger the update of the sidebar only once and only when a new row is selected. The use of a sidebar is not mandatory, rather another solution, e.g. with a automatically updated 'Play' button, would be helpful, too.
推荐答案
我对您提供的示例代码做了一些小的更改，以使边栏不会随时间间隔而定期更新.
I made some small changes to the example code you provided so that the sidebar does not update periodically following the time interval.
基本上，我已经使用 PropertiesService 进行存储所选的行.这个想法是，脚本检查当前选定的行和先前选定的行(上次调用getRecord时选定的那个，即在上一个间隔期间)是否相同.如果它们相同，则没有行选择更改，这意味着侧栏中的音频不需要更新.
Basically, I've used PropertiesService to store the row that is selected. The idea is that the script checks whether the currently selected row and the previously selected row (the one selected last time getRecord was called, that is, during last interval) are the same. If they are the same, there hasn't been a row selection change, which means the audio in the sidebar doesn't need updating.
因此，它仅在所选行发生更改时才更新，我认为这是您遇到的主要问题.
So it only updates if the selected row changes, which is, I think, the main issue you are having.
要实现此目的，您必须按以下方式修改您的代码(有关更改的详细信息，请查看内联注释):
To achieve this, your code would have to be modified in the following way (look at inline comments for details on the changes):
getRecord()
getRecord()
function getRecord() {
var scriptProperties = PropertiesService.getScriptProperties();
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
var headers = data[0];
var rowNum = sheet.getActiveCell().getRow(); // Get currently selected row
var oldRowNum = scriptProperties.getProperty("selectedRow"); // Get previously selected row
if(rowNum == oldRowNum) { // Check if the was a row selection change
// Function returns the string "unchanged"
return "unchanged";
}
scriptProperties.setProperty("selectedRow", rowNum); // Update row index
if (rowNum > data.length) return [];
var record = [];
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");
}
record.push({ heading: headers[col],cellval:cellval });
}
return record;
}
根据是否进行选择更改，getRecord返回:
Depending on whether there was a selection change, getRecord returns:
-
record数组，如果选定的行不同. - 字符串
"unchanged"(如果所选行相同).也许这不是解决这个问题的最优雅的方法，但是您明白了.
- a
recordarray, if the selected row is different. - the string
"unchanged", if the selected row is the same. Probably this is not the most elegant way to handle this, but you get the idea.
然后，showRecord(record)获取此返回值.如果此值为字符串"unchanged"，则不会更新侧边栏:
Then, showRecord(record) gets this returned value. If this value is the string "unchanged", it won't update the sidebar:
showRecord(记录)
showRecord(record)
function showRecord(record) {
// Checks whether returned value is `"unchanged"` (this means the row selected is the same one as before)
if (record != "unchanged" && record.length) {
for (var i = 2; i <= 2; 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('<audio id="player" controls autoplay> <source src=' + record[i].cellval + ' type=audio/wav > Your browser does not support the audio element. </audio>');
}
}
// TODO: hide any existing fields that are beyond the current record length
//Setup the next poll
poll();
}
我还在这一行中添加了autoplay属性:
I also added the autoplay attribute in this line:
.append('<audio id="player" controls> <source src=' + record[i].cellval + ' type=audio/wav > Your browser does not support the audio element. </audio>')
因此，当您选择新行时，音频将自动播放，而无需单击play按钮.
So that the audio plays automatically when you select a new row, without having to click the play button.
最后，我将poll间隔更改为500，这样您就不必等待太多时间来播放新音频.无论如何，您都可以对其进行编辑以使其最适合您:
Finally, I changed the poll interval to 500, so that you don't have to wait so much for the new audio to play. Anyway you can edit this to whatever suits you best:
interval = interval || 500;
我没有修改脚本的其余部分，尽管由于它主要是针对另一个问题而编写的，所以可能对其进行了改进.
I didn't modify the rest of the script, even though it can probably be improved owing to the fact that it was mainly written for a different issue.
我希望这会有所帮助.
这篇关于在表格中选择下一行时，更新电子表格侧栏中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
