用于将Google电子表格行的部分内容复制到另一电子表格并全部基于单元格添加值的脚本 [英] Script for copying parts of a google spreadsheet row to another spreadsheet and adding a value all based on a cell

问题描述

我对编码非常陌生，并且真的很难编写此代码.我希望外面有人可以帮助我.这是我要完成的工作:

I am very new to coding and really struggling writing this code. I am hoping someone out there can help me. Here is what I am trying to accomplish:

当在"Jim的2018年2周快照回顾"的第19列("S")中放置"y"或"Y"时，我要复制B:G&那排的J:R.

When a "y" or "Y" is placed in column 19 ("S") on "Reviewed 2018 2 Week Snapshot for Jim" I want to copy columns B:G & J:R of that row.

将复制的行放置在标题为"Accounting"的其他电子表格上，并在C:Q列上使用标题为"Unprocessed Acct Data"的选项卡.

Place that copied row on a different spreadsheet titled "Accounting" with a tab titled "Unprocessed Acct Data" on Columns C:Q.

然后，我需要在粘贴的行"JT"的B列中写内容.

I then need to write in column B of that pasted row "JT".

最后，将原始的"y"更新为已复制".

Finally, update the original "y" to Copied".

如果有帮助，这里是两个测试文档的链接.

Here is a link to both test documents incase it helps.

2周快照

会计

function myFunction(e) {
  var sourceSheetName = "Reviewed 2018 2 Week Snapshot for Jim";
  var destinationSpreadsheetId = "1l1f6SrN7d6hYDhfv8W_ne15vAd7xSJlC6138jpoXpC4";
  var destinationSheetName = "Unprocessed Acct Data";

  if (e.source.getSheetName() == sourceSheetName && e.range.getcolumn() == 19 && e.value.toUpperCase() == "Y") {
    var sourceValues = e.source.getRange("B:G" && "J:R").getValues().filter(function(e){return e.filter(String).length > 0});
    var tss = SpreadsheetApp.openById(destinationSpreadsheetId);
    var ts = tss.getSheetByName(destinationSheetName);
    ts.getRange(ts.getLastRow() + 1, 1, sourceValues.length, sourceValues[0].length).setValues(sourceValues);
  }
}

推荐答案

如果我对您的问题的理解是正确的，那么此修改如何?我认为您的情况有几个答案.因此，请将此视为其中之一.

If my understanding for your question is correct, how about this modification? I think that there are several answers for your situation. So please think of this as one of them.

• 从Reviewed 2018 2 Week Snapshot for Jim检索值"B:R"作为源值.
  • 检索放置Y的行中从"B"列到"R"列的值.
  • Retrieve values of "B:R" as a source values from Reviewed 2018 2 Week Snapshot for Jim.
    • Values from column "B" to column "R" of a row that Y was put is retrieved.

    请进行如下修改.

    if (e.source.getSheetName() == sourceSheetName && e.range.getcolumn() == 19 && e.value.toUpperCase() == "Y") {
      var sourceValues = e.source.getRange("B:G" && "J:R").getValues().filter(function(e){return e.filter(String).length > 0});
      var tss = SpreadsheetApp.openById(destinationSpreadsheetId);
      var ts = tss.getSheetByName(destinationSheetName);
      ts.getRange(ts.getLastRow() + 1, 1, sourceValues.length, sourceValues[0].length).setValues(sourceValues);
    }
    

    到:

    if (e.source.getSheetName() == sourceSheetName && e.range.getColumn() == 19 && e.value.toUpperCase() == "Y") {
      var row = e.range.getRow();
      var sourceValues = e.source.getRange("B" + row + ":R" + row).getValues()[0];
      var values = [sourceValues.slice(0, 6).concat(sourceValues.slice(8, 17))];
      values[0].unshift("JT");
      var tss = SpreadsheetApp.openById(destinationSpreadsheetId);
      var ts = tss.getSheetByName(destinationSheetName);
      ts.getRange(ts.getLastRow() + 1, 2, values.length, values[0].length).setValues(values);
      e.range.setValue("Copied");
    }
    

    注意:

    • 在共享的电子表格中，有多个项目和功能.因此，当您使用此修改后的脚本时，请提供一个新的函数名称(例如，它是myFunction2(e))，并为其安装触发器.如果函数名称重复，则脚本无法正常工作.

    Note:

    • In your shared Spreadsheet, there are several projects and functions. So when you use this modified script, please give a new function name (for example, it's myFunction2(e)) and please install the trigger to it. If the function name is duplicated, the script doesn't work correctly.

    如果我误解了您的问题，请告诉我.我想修改它.

    If I misunderstand your question, please tell me. I would like to modify it.

    这篇关于用于将Google电子表格行的部分内容复制到另一电子表格并全部基于单元格添加值的脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！