问题

I am trying to move the station/unit identifiers using Google App Script from column B to Column D. This step is a small part of this whole process I have so I can eliminate excess data on this sheet that is emailed to me every day. I have a script that finds and gets the station/unit identifiers, but then it fills up the data and writes everyone it has found. So instead of just moving the one, it will initially do it correctly, but then it will write two of them, then three, then four, and so on deleting all the names. How do I get it to do this just one at a time?

function copyrange() {
  var ss = SpreadsheetApp.openById("1Smd4fQ1nG_O7wXlcTzEqeZw9ghdcB_keqEy0d-CBVFM");
  var sheet = ss.getSheetByName('Roster Report'); 
  var testrange = sheet.getRange('B:B');
  var testvalue = (testrange.getValues());
  var data = [];
  var j =[];

  for (i=0; i<testvalue.length;i++) {
    if (testvalue[i] == 'Rank') {
      data.push.apply(data,sheet.getRange(i+2,1,1,2).getValues());
      j.push(i);
      Logger.log(i)
     
      sheet.getRange(i+1,3,data.length,data[0].length).setValues(data);
    }
  }
}

Here is the sheet:
Roster Report

function copyrange() {
  var ss = SpreadsheetApp.openById("1Smd4fQ1nG_O7wXlcTzEqeZw9ghdcB_keqEy0d-CBVFM")
  var sheet = ss.getSheetByName('Roster Report'); 
  var testrange = sheet.getRange('B:B');
  var testvalue = (testrange.getValues());
  var data = [];
  var j =[];

for (i=0; i<testvalue.length;i++) {
  if ( testvalue[i] == 'Rank') {
  data.push.apply(data,sheet.getRange(i+2,1,1,2).getValues());
  j.push(i);
  Logger.log(i)
 
 sheet.getRange(i+1,3,data.length,data[0].length).setValues(data);
 }

}
}

答案1

得分: 1

以下是翻译好的部分：

在我的理解中，您正在尝试将列B中的Rank名称复制到列D中相邻的单元格中，如下所示。

然而，如果有任何误解，请通过更新您的问题以提供更多明确的信息来澄清您的目标。

建议

与其使用for循环并在每次迭代中手动设置每个单元格范围的值，您可以尝试使用map()和filter()方法来操作当前工作表数据。这种方法允许您仅更改需要更改的值，并在单次运行中检索需要修改的列。以下是提供更多上下文的调整后脚本的示例：

脚本

function copyrange() {
  var sheet = SpreadsheetApp.openById('1Smd4fQ1nG_O7wXlcTzEqeZw9ghdcB_keqEy0d-CBVFM').getSheetByName('Roster Report');
  var sheetValues = sheet.getDataRange().getValues();
  
  //产生初始结果
  var initialResult = sheetValues.map((row, index) =>
    row[1] == 'Rank' ? //检查是否在行中找到'Rank'单元格
      row.map(column => column == 'Name' ? //如果为'true'，找到'Name'
        sheetValues[index + 1][1] //更改具有与'Rank'相同索引的当前值为具有与'Rank'相同索引的下一行的值。
      : column) //如果未找到'Name'（false），则不更改值
    : row); //如果未找到'Rank'（false），则不更改值
  
  //过滤值并仅返回列'D'的值。
  var result = initialResult.map(data => data.map((x, i) => i == 3 ? x.trim()+' ' : null).filter(x => x) );

  //设置列'D'中的值。
  sheet.getRange(`D1:D${result.length}`).setValues(result);
}

演示

在运行脚本后

参考

• [map()][3]
• [filter()][4]

<details>
<summary>英文:</summary>

In my understanding, you are trying to copy the `Rank` names from Column `B` into the adjacent cell in Column `D` as shown below. 

&gt; [![enter image description here][1]][1]

However, if there is any misunderstanding, please clarify your goal by updating your question to provide more clarity.

SUGGESTION
==========

Instead of using a for loop &amp; manually setting the values for each range of cells in each iteration, you can try using the `map()` &amp; `filter()` methods to manipulate your current sheet data. This approach allows you to change only the values that need to be changed and retrieve only the column that requires modification in a single run. Here is an example of a tweaked script that provides more context:

Script
======

    function copyrange() {
      var sheet = SpreadsheetApp.openById(&#39;1Smd4fQ1nG_O7wXlcTzEqeZw9ghdcB_keqEy0d-CBVFM&#39;).getSheetByName(&#39;Roster Report&#39;);
      var sheetValues = sheet.getDataRange().getValues();
      
      //Produce initial result
      var initialResult = sheetValues.map((row, index) =&gt;
        row[1] == &#39;Rank&#39; ? //Checks if &#39;Rank&#39; cell is found in a row
          row.map(column =&gt; column == &#39;Name&#39; ? //If &#39;true&#39;, find the &#39;Name&#39; 
            sheetValues[index + 1][1] //Alter the current value that has the same index as the &#39;Name&#39; with the value after the row that has the same index as the &#39;Rank&#39;.
          : column) //if &#39;Name&#39; is not found (false), do not alter the value
        : row); //if &#39;Rank&#39; is not found (false), do not alter the value
      
      //Filter the values and only return the values for Column &#39;D&#39;.
      var result = initialResult.map(data =&gt; data.map((x, i) =&gt; i == 3 ? x.trim()+&#39; &#39; : null).filter(x =&gt; x) );
    
      //Set the values in Column &#39;D&#39;.
      sheet.getRange(`D1:D${result.length}`).setValues(result);
    }

Demo
====

&gt; After running the script

[![enter image description here][2]][2]

Reference
=========

- [map()][3]
- [filter()][4]


  [1]: https://i.stack.imgur.com/eryY6.png
  [2]: https://i.stack.imgur.com/8I2Nz.gif
  [3]: https://www.w3schools.com/jsref/jsref_map.asp
  [4]: https://www.w3schools.com/jsref/jsref_filter.asp

</details>