Google脚本根据另一个单元格中的值追加行 [英] Google script to append row based on value in another cell
问题描述
使用Google App脚本我正在寻找Sheet1中存在的Sheet1中的任何ID，并将Sheet1中的注释字段与Sheet2中的Comment字段中列出的内容附加在一起。
Sheet1：保存所有基于ID的数据
Sheet2：保存与Sheet1中某些ID相关的注释
ID类型库存评论
1 Apple是
2香蕉否
3橙色是
Sheet2示例
ID评论
1文本
2文本
代码
这是我一直在使用的代码否则循环访问我的源数据以识别名为是的变量，显然这对于这种情况不起作用，因为ID是我们需要查找的变量，它是动态的。
我只是在升级这段代码的过程中迷失了方向，因为它会循环遍历Sheet2，获取所有ID，并根据Sheet1检查这些ID。如果Sheet1中存在这些ID，则使用Sheet2中已列出的注释更新Sheet1的注释
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（）;
{
//从源代码循环遍历每个检索到的行
（sourceData中的行）{
// IF如果这一行中的列I有'是'，然后工作。
if（sourceData [row] [1] ==='Yes'）{
//将它保存为临时变量
var tempvalue = [sourceData [row] [0]，sourceData [行] [7]];
//然后将其推入保存所有要返回的新值的变量
targetValues.push（tempvalue）;
//将新范围保存到从最后一个空行开始的相应工作表
outputSheet.getRange（lastRow + 1，1，targetValues。长度，2）.setValues（targetValues）;
$ b
是的，你需要循环访问一组ID。在该循环中，您需要嵌套一个循环，以便通过另一组ID。
代码
函数setComments（）{
var ss = SpreadsheetApp.getActive（），
compare1 =，compare2 = ，
$ b 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（），
//获得标题的范围
titleSection1 = outputSheet.getRange（1,1,1 ，lastCol1），
titleSection2 = sourceSheet.getRange（1,1,1，lastCol2），
//从标题中获取值
titles1 = titleSection1.getValues（） ，
titles2 = titleSection2.getValues（），
//获得ID和comment的列＃
idCol1 = titles1 [0] .indexOf（ID），
idCol2 = titles2 [0] .indexOf（ID） ，
commentsCol1 = titles1 [0] .indexOf（comment），
commentsCol2 = titles2 [0] .indexOf（comment）;
//从范围1获得ID
（i = 1; i< values1.length; i ++）{
compare1 = values1 [i] [idCol1];
//从范围2获得ID
（j = 1; j
//如果ID相同，则更改值数组
if（compare1 == compare2）{
values1 [i] [commentsCol1] = values2 [j] [commentsCol2];
}
}
}
//根据值数组设置值
range1.setValues（values1）;
}
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: Holds all data based on ID
Sheet2: Holds comments relating to some ID's in Sheet1
Sheet1 Example
ID Type In Stock Comment
1 Apple Yes
2 Banana No
3 Orange Yes
Sheet2 Example
ID Comment
1 Text
2 Text
Code
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.
I'm just abit 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);
}
}
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.
Code
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);
}
这篇关于Google脚本根据另一个单元格中的值追加行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
