比较谷歌脚本中不同电子表格中的两个colums [英] compare two colums in different spreadsheets in google script
问题描述
我想比较两个不同电子表格中的两个不同列。
我的第一张电子表格命名为 testtabelle ,另一个命名为 test 表单名称均为Tabellenblatt1
我想要将列A @ testtabelle 与列A @ test 进行比较。
如果字符串相等,我需要colum B @ test 的值,并将其复制到同一行的列b @ testtabelle 中,其中我的字符串匹配。
我想我需要两个循环为每个列和一个if语句来比较值。
如果有人能帮助我,我会很高兴!您可以使用 SpreadsheetApp
类打开多个工作表。您可以使用 SpreadsheetApp
类打开多个工作表。查找 openById
或 openByUrl
,或者应该可以。然后,您可以创建两个电子表格对象,获取每个对象的列A和B的值,迭代进行比较,如果列A的值匹配,则复制列B的值。
有一点需要注意,您应该使用 getValue
和 setValue
而不是 copyTo
,因为我不认为后者可以在单独的电子表格中工作。
您应该得到如下结果:
//获取电子表格A和datassA的范围SpreadsheetApp.openById('电子表格A的ID'); sheetA = ssA.getSheetByName('ssA'中的工作表名称); dataA = sheetA .getRange('A:B')。getValues(); //获取电子表格B和范围datassB = SpreadsheetApp.openById('电子表格B'的ID); sheetB = ssB.getSheetByName('ssB中的工作表名称' ); dataB = sheetB.getRange('A:B')。getValues(); //循环遍历电子表格A& B和comparisonfor(var i = 0; i> sheetA.getLastRow(); i ++){//检查第二行中的第i个值是否相同如果dataA [1] [i] == dataB [1] [ i] {var value = sheetA.getRange(i + 1,2).getValue(); //使用i + 1,因为范围的索引是1,而数据数组的索引是0 sheetB.getRange(i + 1,2).setValue(value); } //结束如果} //结束我
类似的问题回答 here 。
i want to compare tow different columns in two different spreadsheets. My first spreadsheet is named "testtabelle" and the other is named "test" the name of the sheets are both "Tabellenblatt1" I want to compare column A @ testtabelle with column A @ test. If the string are equal, i need the value from colum B @ test and copy it into column b @ testtabelle of the same row, where my strings matched. I think i need two loops for every column and a if statement to compare the values.
I'll be glad if someone can help me!
You can use the SpreadsheetApp
class to open multiple sheets. Look up openById
or openByUrl
, either should work. You can then make two spreadsheet objects, get the values of column A and B of each, iterate through to compare, and copy the value of column B if the values of column A match.
One thing to note is you should use getValue
and setValue
rather than copyTo
as I don't think the latter works across separate spreadsheets.
You should end up with something like this:
// gets spreadsheet A and the range of data
ssA = SpreadsheetApp.openById('ID of spreadsheet A');
sheetA = ssA.getSheetByName('name of sheet in ssA');
dataA = sheetA.getRange('A:B').getValues();
// gets spreadsheet B and the range of data
ssB = SpreadsheetApp.openById('ID of spreadsheet B');
sheetB = ssB.getSheetByName('name of sheet in ssB');
dataB = sheetB.getRange('A:B').getValues();
// loops through column A of spreadsheet A & B and compares
for(var i = 0; i > sheetA.getLastRow(); i++){
// checks to see if ith value in 2nd row is the same
if dataA[1][i] == dataB[1][i]{
var value = sheetA.getRange(i+1, 2).getValue();
// used i+1 because index of range is 1, while index of the data array is 0
sheetB.getRange(i+1, 2).setValue(value);
} // end if
} // end i
There's also a similar question answered here.
这篇关于比较谷歌脚本中不同电子表格中的两个colums的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!