这是样本表。由此
1 Search area Bounty list Bullet
2 a i z a b c abc
3 e b d d e f def
4 y f h g h i ghi
5
6 1 2 3 4 5 6 7 8
7 Column #
对此
1 Search area Bounty list Bullet
2 abc ghi z a b c abc
3 def abc def d e f def
4 y def ghi g h i ghi
5
6 1 2 3 4 5 6 7 8
7 Column #
将从(2,5)或“ a”开始的“赏金列表”中取一个值“赏金”,从a,i,z,e,b,d, y,f,h。然后,如果找到一个或多个等于“赏金”值的单元格,则会将当前“赏金”行中第8列的“子弹”值放置到这些单元格中。该过程将按“赏金列表”中的a,b,c,d,e,f,g,h,i的顺序重复。这两个过程都向左和向下移动。
function menuItem1()
{
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var target = sheet.getDataRange().getValues();
for (var BountyRow = 2; BountyRow<target.length; BountyRow++)//switching rows in bounty list//
{
var bullet = sheet.getRange(BountyRow, 8).getValue(); //cell value to paste on targets//
for (var BountyColumn = 5; BountyColumn<8; BountyColumn++) //switching columns in bounty list//
{
var bounty = sheet.getRange(BountyRow, BountyColumn).getValue(); // cell value to search for//
if (bounty !=0)
{
for (var SearchRow = 1; SearchRow<target.length; SearchRow++) //switching row on search area//
{
for(var SearchColumn = 0; SearchColumn<4;SearchColumn++)//switching column on search area//
{
if(target[SearchRow][SearchColumn] == bounty) //if search target is found//
{
var found = target[SearchRow][SearchColumn];
sheet.getRange(SearchRow+1, SearchColumn+1).setValue(bullet);
Logger.log((found)+ " in "+"row"+(SearchRow+1)+", column"+(SearchColumn+1));
}
}
}
}
}
}
}
它涉及成千上万次始终使用一分钟以上的搜索,我想知道是否有更有效的方法?
而不是为每个单元格使用getValue()
和setValue()
(这会使您的代码运行缓慢)
getValues()
setValues()
样品:
function menuItem1(){
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lastRow=sheet.getLastRow();
var searchValues=sheet.getRange(2,1,lastRow-2+1,3).getValues();
var bountyValues=sheet.getRange(2,5,lastRow-2+1,3).getValues();
var bulletValues=sheet.getRange(2,8,lastRow-2+1,1).getValues();
for (var i = 0; i<bountyValues.length; i++){
for (var j = 0; j<bountyValues[0].length; j++){
if (bountyValues[i][j] !=0){
replaceValues(searchValues, bountyValues[i][j], bulletValues[i][0]);
}
}
}
sheet.getRange(2,1,lastRow-2+1,3).setValues(searchValues)
}
function replaceValues(search, bounty, bullet) {
for(var k=0;k<search.length;k++){
search[k]=search[k].map(function(search) {
var regex=new RegExp("\\b"+bounty+"\\b","g");
return search.toString().replace(regex, bullet);
});
}
}
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句