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

问题描述

从我以前的帖子中(

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列中的数据从工作表1移动到工作表2，包括时间戳.

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.

在此模式下，使用分配了功能的图像.在这种情况下，作为示例，假设将5张图像放置在Ark1的页面上，并且每张图像的功能名称都类似于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 = () => main(2); const copyPasteValue3 = () => main(3); ,,,，格式是恒定的.因此，例如，我认为您也可以将这些脚本创建为文本文件，然后将其复制并粘贴到脚本编辑器中.

• 如果要使用脚本将图像插入到"A"列，则可以使用以下功能.使用此功能时，请在Google云端硬盘上设置图片的文件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类
• 简单触发器
• Class OverGridImage
• Simple Triggers

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

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

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