根据另一个单元格中的值追加行 [英] Append row based on value in another cell

问题描述

使用Google App脚本，我希望从Sheet2中找到存在于Sheet1中的所有ID，然后在Sheet1中的注释字段中附加在Sheet2中的注释字段中列出的内容。

Using a Google App Script I'm looking to find any ID's from Sheet2 which exist in Sheet1 and append the comment field within Sheet1 with what is listed in the Comment field in Sheet2.

Sheet1：保存所有基于ID的数据

Sheet1: Holds all data based on ID

Sheet2：保存与Sheet1中某些ID相关的注释

Sheet2: Holds comments relating to some ID's in Sheet1

Sheet1示例

ID  Type    In Stock    Comment
1   Apple   Yes 
2   Banana  No  
3   Orange  Yes 

Sheet2示例

ID  Comment
1   Text
2   Text 

代码

这是我一直在使用的代码否则，它将遍历我的源数据以标识一个名为是的变量，显然，在这种情况下，这是行不通的，因为ID是我们需要找到的动态变量。

This is code I've been using for something else which loops through my source data to identify a variable called "Yes", obviously this won't work for this case as the ID is the variable we need to find which is dynamic.

我对如何修改此代码略有迷惑，因此它将遍历Sheet2，获取所有ID，并对照Sheet1检查这些ID。如果Sheet1中存在这些ID，请使用Sheet2中已列出的注释更新Sheet1的注释

I'm just a bit lost on how to modify this code so that it will loop through Sheet2, get all the IDs, check those IDs against Sheet1. If those IDs exist in Sheet1 update the comment of Sheet1 with the comment already listed in Sheet2

    function setComment(){
      var outputSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
      var lastRow = outputSheet.getLastRow();
      var lastCol = outputSheet.getLastColumn();
      var targetValues = [];

      var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
      var lastSourceRow = sourceSheet.getLastRow();
      var lastSourceCol = sourceSheet.getLastColumn();

      var sourceRange = sourceSheet.getRange(1, 1, lastSourceRow, lastSourceCol);
      var sourceData = sourceRange.getValues();

      { 
        //Loop through every retrieved row from the Source
        for (row in sourceData) {
          //IF Column I in this row has 'Yes', then work on it.
          if (sourceData[row][1] === 'Yes') {
            //Save it ta a temporary variable
            var tempvalue = [sourceData[row][0], sourceData[row][7]];
            //then push that into the variables which holds all the new values to be returned
            targetValues.push(tempvalue);
          }
        }

        //Save the new range to the appropriate sheet starting at the last empty row
        outputSheet.getRange(lastRow + 1, 1 , targetValues.length, 2).setValues(targetValues);
      }
    }

推荐答案

是您需要遍历一组ID。在该循环中，您需要嵌套一个遍历另一组ID的循环。

Yes you need to loop through one group of IDs. In that loop you need to nest a loop that goes through the other group of IDs.

代码

function setComments() {

  var ss = SpreadsheetApp.getActive(),
      compare1 = "", compare2 = "",

      outputSheet = ss.getSheetByName("Sheet1"),
      sourceSheet = ss.getSheetByName("Sheet2"),

      range1 = outputSheet.getDataRange(),
      range2 = sourceSheet.getDataRange(),

      lastCol1 = range1.getNumColumns(),
      lastCol2 = range2.getNumColumns(),

      values1 = range1.getValues(),
      values2 = range2.getValues(),

      // get the range of the titles
      titleSection1 = outputSheet.getRange(1,1,1, lastCol1),
      titleSection2 = sourceSheet.getRange(1,1,1, lastCol2),

      // get the values from the titles
      titles1 = titleSection1.getValues(),
      titles2 = titleSection2.getValues(),

      // get the column # for "ID" and "comment"
      idCol1 = titles1[0].indexOf("ID"),
      idCol2 = titles2[0].indexOf("ID"),
      commentsCol1 = titles1[0].indexOf("comment"),
      commentsCol2 = titles2[0].indexOf("comment");

  // get the IDs from range1
  for (i = 1; i < values1.length; i++) { 
    compare1 = values1[i][idCol1];

    // get the IDs from range2
    for (j = 1; j< values2.length; j++){
      compare2 = values2[j][idCol2];

      // if same ID, change the values array
      if (compare1 == compare2) {
        values1[i][commentsCol1] = values2[j][commentsCol2];
      }
    }
  }
  // set values based on the values array
  range1.setValues(values1);
}

这篇关于根据另一个单元格中的值追加行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！