使用一个脚本将文本从一行中的单元格复制到另一张表中，并使用一行中的图标 [英] Using one script for copying text from cells in a row to another sheet using an icon in a row

问题描述

来自我之前的帖子(通过单击 Google 表格中的图标将单元格值从一个单元格自动复制到另一个工作表)我现在遇到了一个新问题，我希望有人能帮我解决.

From my previous post (Autocopy cell value from one cell to another sheet by clicking an icon in google sheets) I now run into a new problem which I would like someone to help me out with.

我正在尝试实现一个电子表格，其中匿名用户可以单击 A 列中的图标，并将 B 列和 C 列中的数据从 sheet1 移动到 sheet2，包括时间戳.

I'm trying to achieve a spreadsheet where an anonymous user can click the icon in column A and the data from column B and C is moved from sheet1 to sheet2 including a timestamp.

到目前为止一切顺利.它在这个伟大社区的帮助下启动并运行.我正在使用这个脚本:

So far so good. It is up and running with a help from this great community. I'm using this script:

function copyPasteValue2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName('Ark1');
  var r = s.getRange('b2');
  var v = r.getValue();
  var a = s.getRange('c2');
  var d = a.getValue();
  var s2 = ss.getSheetByName('Ark2');
  var timeZone = ss.getSpreadsheetTimeZone();
  var t = Utilities.formatDate(new Date(), timeZone, 'HH:mm:ss dd-MM-yyyy');
  s2.getRange('A2:C2').setValues([[t,v,d]]);
}

现在我正在考虑将这个工作表放大并包含超过 100 个条目，所有条目都具有此功能 - 单击 A 列中的图标并将 C 和 B 中的数据移动到工作表 2.

Now I'm looking at scaling this sheet up and contain more than 100 entries all with this ability - click on an icon in column A and move data in C and B to sheet 2.

我的问题是，如何在不创建另外 100 个脚本的情况下实现这一目标?由于我在这里慢慢取得进展，到目前为止，我的搜索还没有在解决方案中返回.

My question is, how can I achieve this without creating another 100 scripts? As I'm slowly progressing here my search hasn't returned in a solution so far.

我在这里制作了一个测试表以获得任何帮助:https://docs.google.com/spreadsheets/d/1z5TGWp3N002z0GNts4NRyspkjXcsN52rxPtx8Pu9D70/edit#gid=

I have made a test sheet for any help here: https://docs.google.com/spreadsheets/d/1z5TGWp3N002z0GNts4NRyspkjXcsN52rxPtx8Pu9D70/edit#gid=

推荐答案

• 您想通过单击电子表格上的图像来运行脚本.
• 您想为A"列放置图像.在这种情况下，您想要放置大量图像.
• 您想使用简单的脚本来实现这一点.
• 遗憾的是，在当前阶段，通过单击工作表上的图像运行脚本时，脚本无法知道单击图像的位置.通过这种方式，不幸的是，在您的情况下，当您想通过单击图像来运行脚本时，需要为每个图像分配不同的函数名称.
• 而且，当通过单击图像运行脚本时，似乎在运行全局变量之前搜索了该函数.这样就无法实现使用脚本自动安装功能.

从以上情况来看，为了实现你的目标，我想提出以下两种模式.

From above situation, in order to achieve your goal, I would like to propose the following 2 patterns.

在此模式中，使用分配了功能的图像.本例中，假设在Ark1的sheet中放置了5张图片，每张图片的函数名分别为copyPasteValue2、copyPasteValue3 等等.

In this pattern, the images assigned the functions are used. In this case, as the sample, it supposes that 5 images are put to the sheet of Ark1, and each images has the function names like copyPasteValue2, copyPasteValue3 and so on.

示例脚本如下.请将以下脚本复制并粘贴到脚本编辑器中.

The sample script is as follows. Please copy and paste the following script to the script editor.

function main(row) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName('Ark1');
  var [v, d] = s.getRange(`B${row}:C${row}`).getValues()[0];
  var s2 = ss.getSheetByName('Ark2');
  var timeZone = ss.getSpreadsheetTimeZone();
  var t = Utilities.formatDate(new Date(), timeZone, 'HH:mm:ss dd-MM-yyyy');
  s2.getRange(`A${row}:C${row}`).setValues([[t,v,d]]);
}

const copyPasteValue2 = () => main(2);
const copyPasteValue3 = () => main(3);
const copyPasteValue4 = () => main(4);
const copyPasteValue5 = () => main(5);
const copyPasteValue6 = () => main(6);

• 在这个脚本中，当点击函数名称为copyPasteValue2的图像时，2即图像的行被赋予main().这样，copyPasteValue2 可以作为第 2 行运行.
• In this script, when the image which has the function name of copyPasteValue2 is clicked, 2 which is the row of the image is given to the function of main(). By this, copyPasteValue2 can be run as the row 2.
• 关于const copyPasteValue2 = () =>主要(2)；const copyPasteValue3 = () =>主要(3)；,,,，格式不变.例如，我认为您也可以将这些脚本创建为文本文件，然后您可以将它们复制并粘贴到脚本编辑器中.

• 如果要使用脚本将图像插入A"列，可以使用以下函数.当您使用它时，请在 Google Drive 上设置图像的文件 ID.并请设置sheetName 和max.

• About const copyPasteValue2 = () => main(2); const copyPasteValue3 = () => main(3); ,,,, the format is constant. So for example, I think that you can also create these script as a text file, then you can copy and paste them to the script editor.

• If you want to insert the images to the column "A" using a script, you can use the following function. When you use this, please set the file ID of the image on Google Drive. And please set sheetName and max.

function insertImages() {
  var id = "###";  // Please set the file ID of the image.
  var sheetName = "Sheet1"; // Please set the sheet name.
  var max = 5;  // Please set the number of images you want to put.

  var blob = DriveApp.getFileById(id).getBlob();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName);
  sheet.setRowHeights(2, max, 64).setColumnWidth(1, 64);
  for (var i = 0; i < max; i++) {
    sheet.insertImage(blob, 1, i + 2).setHeight(64).setWidth(64).assignScript("copyPasteValue" + (i + 2));
  }
}

在这种模式中，我想建议使用复选框而不是图像.在这种情况下，使用 OnEdit 事件触发器，当复选框被点击时，它可以知道被点击的复选框的坐标.这样，脚本可以变得比模式 1 的脚本更简单.

In this pattern, I would like to propose to use the checkboxes instead of the image. In this case, using the OnEdit event trigger, when the checkbox is clicked, it can know the coordinate of the checkbox which was clicked. By this, the script can become simpler than that of pattern 1.

function main(row) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName('Ark1');
  var [v, d] = s.getRange(`B${row}:C${row}`).getValues()[0];
  var s2 = ss.getSheetByName('Ark2');
  var timeZone = ss.getSpreadsheetTimeZone();
  var t = Utilities.formatDate(new Date(), timeZone, 'HH:mm:ss dd-MM-yyyy');
  s2.getRange(`A${row}:C${row}`).setValues([[t,v,d]]);
}

function onEdit(e) {
  const range = e.range;
  if (range.getSheet().getSheetName() == "Ark1" && e.value == "TRUE") {
    main(range.rowStart);
    range.uncheck();
  }
}

• 在上面的脚本中，它假设Ark1表上的A"列有复选框.
• 在这种情况下，您可以使用简单触发器运行脚本.
• At above script, it supposes that the column "A" on the sheet of Ark1 has the checkboxes.
• In this case, you can run the script using the simple trigger.
• 在这种情况下，请在脚本编辑器中启用 V8.
• OverGridImage 类
• 简单触发器

如果我误解了您的问题并且这不是您想要的方向，我深表歉意.

If I misunderstood your question and this was not the direction you want, I apologize.

这篇关于使用一个脚本将文本从一行中的单元格复制到另一张表中，并使用一行中的图标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！