如何更改用户正在编辑的单元格? (Google表格) [英] How do I change the cell a user is editing? (Google Sheets)
问题描述
我正在使用的工具会显示Google云端硬盘文件列表.提示用户在要包含在文档链接的主库中的每个文件旁边放置一个"X".添加文件后，我删除每个带有"X"的行.
The tool I'm working on displays a list of Google Drive files. Users are prompted to put an "X" next to every file they want to have included in a Master Library of document links. After the files are added I delete every row that has an "X".
今天进行测试时，我注意到，如果我在单元格中键入"X"，但是在单击将所选文件添加到库"按钮之前未按Enter，则不会添加该文件.很好，问题在于仍然存在一个选定的单元格，其中带有"X"，但它的位置只有一个.
While testing today I noticed that if I type "X" in a cell but do NOT hit ENTER before clicking the "Add Selected Files to Library" button, that file doesn't get added. That's fine, the problem is that there is still a selected cell with an "X" in it, but it's off by one.
是否可以清除正在编辑的单元格?还是将正在编辑的单元格移至已删除的行?
Is there a way to clear the cell being edited? Or move the cell being edited to account for the deleted row?
如果有帮助，这是我的代码(我显然不是专业的编码人员，所以感谢您的耐心配合):
If it helps, here's my code (I'm obviously not a professional coder, so thanks for your patience):
function addToLibrary() {
var x = 2;
var added = 0;
var dupes = 0;
lib_last = libSheet.getLastRow();
while (sheet.getRange(x,1).getValue() != "") {
if (sheet.getRange(x,5).getValue() != "") {
id = sheet.getRange(x,1).getValue();
var checkRow = checkDupes(id);
if (checkRow >= lib_last + 1) {
var my_name = DriveApp.getFileById(id).getName();
SpreadsheetApp.getActiveSpreadsheet().toast('The design file: ' + '"' + my_name + '"' + ' has been added to the Library!', "ADDED FILE...",600);
var my_link = DriveApp.getFileById(id).getUrl();
getDeets(id, my_link);
lib_last++;
added++;
}
else {
dupes++;
}
}
x++;
}
SpreadsheetApp.getActiveSpreadsheet().toast("", "",0.1);
Browser.msgBox("Finished! Documents added: " + added + ". Duplicate documents: " + dupes);
for (var i = x - 1; i>=2; i--) { // Deletes rows with non-null
if (!sheet.getRange(i,5).isBlank()) {
sheet.deleteRow(i);
}
}
}
推荐答案
正在编辑的单元格正在浏览器中而不是服务器上进行编辑.在用户停止编辑之前，更新值的请求不会发送到Google.如果用户通过单击按钮停止"编辑，则存在争用条件，在争用条件之间将首先得到处理-脚本执行或编辑状态的提交.
The cell being edited is being edited in the browser, not on the server. The request to update the value isn't sent to Google until the user stops editing. If users "stop" editing by clicking a button, then a race condition exists between which gets handled first - the script execution or the commit of the edit state.
我建议使用自定义脚本/附加菜单，这将导致在调用功能之前进行单元格编辑，因为浏览器选择从电子表格单元格更改为表格"菜单栏.
I suggest using a custom script / add-on menu, which will cause the cell edits to be committed before the function can be invoked, because the browser selection changes from the spreadsheet cell to the Sheets menu bar.
function onOpen(e) {
// add ons need to check authMode before doing this stuff
e.source.addMenu('My Custom Menu', [
{ name: 'Add Selected Files to Library', functionName: 'addToLibrary' }
]);
}
或者:
function onOpen(e) {
// add ons need to check authMode before doing this stuff
SpreadsheetApp.getUi()
.createMenu('My Custom Menu') // .createAddonMenu()
.addItem('Add Selected Files to Library', 'addToLibrary')
.addToUi();
}
