将电子表格中的行发送给其他人，从下拉菜单中选择 [英] Send rows from a spreadsheet to others selecting from a dropdown
问题描述
我有一个源电子表格A，其中包含从列B到列E的一些数据。
现在，我在列D中有一个下拉列表，在这里我可以选择一些已经存在的电子表格的名称在我的共享驱动器中创建（并带有现有ID）。
此下拉列表从源电子表格A的其他标签中的范围中获取值。
我将发送某些行（例如，当我单击这些行中的复选框之后）
我该如何进行？
I have a source spreadsheet A with some data from col B to col E.
Now, I have a dropdown in col D where I can select the names of some spreadsheets I have already created (and so with existing IDs) in my shared drive.
This dropdown takes the value from a range in other tab in the source spreadsheet A.
I would to send certain rows (for example after when I click a checkbox in those rows) to the chosen spreadsheets in the dropdown in col D.
How can I proceed?
更新
为避免手动编写ID，在其中复制行的电子表格中，有一种方法可以在驱动器中找到这些工作表，从而自动显示ID？
Update To avoid to write manually the ID of the spreadsheets where the rows are copied, there is a method to find these sheets in the drive and so the ID is showed automatically?
推荐答案
从一个电子表格复制到另一个电子表格
要在电子表格之间复制，需要注意以下几点：
Copying from one spreadsheet to another spreadsheet
To copy between spreadsheets, there are few notes:
-
简单的
onEdit（e）具有有限授权。您必须安装on edit处理功能以获得完整授权，以便可以调用SpreadsheetApp.openById。
The simple
onEdit(e)has limited authorization. You must install the on edit handling function to get full authorization so you can callSpreadsheetApp.openById.
Range.copyTo 是不允许的。我在示例中使用 Sheet.appendRow 。
Range.copyTo is not allowed. I use Sheet.appendRow in the example.
制作确保像这样安装自定义onEdit函数
Make sure to install the custom onEdit function like so
function createOnEditTrigger() {
ScriptApp.newTrigger('onEditInstalled')
.forSpreadsheet(SpreadsheetApp.getActive())
.onEdit()
.create();
}
通过编辑事件检测特定的复选框
To限制当我们运行脚本时，我们应该检查我们在正确的工作表名称和范围内。我还尝试仅检查一个单元格，并遇到了一个与复选框有关的有趣问题：如果我选择了多个复选框并使用空格键将它们全部切换，尽管切换了多个复选框，Apps Script仍将编辑大小设为1个单元格。因此，在我的示例中，如果您碰巧以此方式切换了多个复选框，则仅复制左上方复选框的行。
Detecting a particular checkbox with an edit event
To limit when we run the script, we should check that we are on the correct sheet name and range. I also tried checking for just a single cell, and ran into an interesting issue with checkboxes: if I selected multiple checkboxes and toggled them all with spacebar, Apps Script saw the size of the edit as only 1 cell, despite toggling multiple checkboxes. So in my example, if you happen to toggle multiple checkboxes this way, only the top left checkbox's row get's copied.
// see below for details of `sheets` configuration object
function onEditInstalled(e) {
if (
e.range.getSheet().getName() === sheets.source.NAME &&
e.range.getColumn() === sheets.source.columns.CHECKBOX &&
isSingleCell(e.range) &&
e.range.isChecked()
) {
onCheckCopySourceToTarget(e);
}
}
// NOTE: this does not catch the condition of
// toggling multiple checkboxes with selection + spacebar
function isSingleCell(range) {
return range.getWidth() === 1 && range.getHeight() === 1;
}
使用驱动器服务查找工作表（电子表格）ID
<对于这种特殊情况，工作表都位于一个特定的文件夹中，因此我们可以按名称获取此文件夹，然后遍历文件直到找到正确的文件夹。为了进一步预防，我们将文件的 MIME类型检查为
该示例仅假设与配置名称匹配的第一个文件夹是正确的；您可以将其扩展为处理发现多个文件夹的情况，或改用使用文件夹ID。
The example just assumes the first folder that matches the configuration name is correct; you could possibly extend this to handle the case where multiple folders are found or switch to using folder IDs instead.
根据问题询问者的问题请求，您可以重新配置
As per the question asker's request, you can reconfigure for
- 您要复制的源数据列
- 目标数据的哪一列开始于
下面是整个示例：
// configuration, MUST adjust to your needs!!
// use 1-indexed columns, rows
const sheets = {
source: {
NAME: "Foglio1", // the sheet NAME you copy FROM
columns: {
CHECKBOX: 1,
DROPDOWN: 4
},
data: {
columns: [2,3,5] // A=1, a list of columns
}
},
target: {
NAME: "Target", // the sheet NAME you copy TO
data: {
COLUMN_START: 2 // copy data starting at this column
},
folder: {
NAME: "Destinations"
}
}
};
// must install the onEdit if you want to open another spreadsheet by ID
// authMode must be FULL
// *** RUN THIS MANUALLY FIRST ***
function createOnEditTrigger() {
ScriptApp.newTrigger('onEditInstalled')
.forSpreadsheet(SpreadsheetApp.getActive())
.onEdit()
.create();
}
function onEditInstalled(e) {
if (
e.range.getSheet().getName() === sheets.source.NAME &&
e.range.getColumn() === sheets.source.columns.CHECKBOX &&
isSingleCell(e.range) &&
e.range.isChecked()
) {
onCheckCopySourceToTarget(e);
}
}
function onCheckCopySourceToTarget(e) {
const spreadsheet = SpreadsheetApp.getActive();
const source = spreadsheet.getSheetByName(sheets.source.NAME);
const row = e.range.getRow();
const targetLabel = source.getRange(row, sheets.source.columns.DROPDOWN).getValue();
const targetId = getDesinationSpreadsheetIdByName(targetLabel);
const target = SpreadsheetApp.openById(targetId)
.getSheetByName(sheets.target.NAME);
target.appendRow(getData(source, row));
//e.range.uncheck(); // uncomment if you want checkbox to automatically reset
}
function getData(sheet, row) {
const columns = sheets.source.data.columns.slice();
columns.sort(); // expected order is [lowest,...,highest]
const start = columns[0];
const end = columns[columns.length - 1];
const values = sheet
.getRange(row, start, 1, (end - start) + 1)
.getValues()[0];
return Array
.from({ length: sheets.target.data.COLUMN_START - 1 }).fill("") // left padding
.concat(values.filter((_, index) => columns.includes(index + start)));
}
// NOTE: this does not catch the condition of
// toggling multiple checkboxes with selection + spacebar
function isSingleCell(range) {
return range.getWidth() === 1 && range.getHeight() === 1;
}
function getDesinationSpreadsheetIdByName(spreadsheetName) {
const folders = DriveApp.getFoldersByName(sheets.target.folder.NAME);
const folder = folders.next(); // WARNING assumes first result is correct
const files = folder.getFiles();
const MIME_TYPE_SHEETS = "application/vnd.google-apps.spreadsheet";
do {
const file = files.next();
if (
file.getMimeType() === MIME_TYPE_SHEETS &&
file.getName() === spreadsheetName
) return file.getId();
} while (files.hasNext());
throw new Error("could not find " + spreadsheetName + " in " + sheets.target.folder.NAME);
}
这篇关于将电子表格中的行发送给其他人，从下拉菜单中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
