如何使用命名范围中的验证在新列中设置值? [英] How to set values in new column with validation from a Named Range?
问题描述
遵循上一个问题
我想通过在下一列中添加标签来对文本条目进行分类.
I want to classify text entries by adding a tag in the next column.
我可以使用正则表达式来完成它,但是编写所有类似这样的条件将花费太多时间:
I could do it using regex but it will take too much time writing all conditions like :
if(String(data[i][0]).match(/acme|brooshire|dillons|target|heb|costco/gi))
{
labValues[i][0]='Supermarket';
}
相反,我创建了一个包含所有商店名称的命名列表(在另一张纸上).
Instead I created a named list with all stores names (in another sheet).
如果条目与列表中的术语匹配,则下一列设置为"超市".
If an entry matches a term in the list, the next column is set to "Supermarket".
我在下面使用此脚本...没有错误,但是执行时什么也没发生!
I am using this script below... No bugs but nothing happens when executed !
function tagStore() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange('A2:A655')
var store = range.getValues();
var tag = sheet.getRange('B2:B655');
var tagvalues= tag.getValues();
var storeList= SpreadsheetApp.getActive().getRangeByName("store_list");
for (var i = 0; i<store.length; i++)
{
if(String(store[i][0]).match(storeList))
{
tagvalues[i][0]='Supermarket';
}
}
tag.setValues(tagvalues);
}
使用正则表达式很重要,因为商店"的值与"store_list"的值并不完全相同.
It is important to use a Regex as the "store" Values are not exactly the same as the "store_list".
Store Values : ["Acme Store", "HEB PLaza", "Dillons Group"...]
Store_List : [acme, heb, dillons...]
推荐答案
不是尝试使用regEx方法,而是通过将范围作为列表进行检索,这是一种更直接的方法.
Instead of trying to go with the regEx approach there is a more straightforward approach by retrieving the range as a list.
// For a Column
var storeList = SpreadsheetApp.getActive().getRangeByName("store_list").getValues().map(function(r){return r[0];});
// For a Row
var storeList = SpreadsheetApp.getActive().getRangeByName("store_list").getValues()[0];
然后使用尝试一下:
function tagStore() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange('A2:A655')
var store = range.getValues();
var tag = sheet.getRange('B2:B655');
var tagvalues= tag.getValues();
var storeList= SpreadsheetApp.getActive().getRangeByName("store_list").getValues().map(function(r){return r[0];});//if it's a column
//var storeList=SpreadsheetApp.getActive().getRangeByName("store_list").getValues()[0];//if it's a row
for (var i=0;i<store.length; i++) {
if(storeList.indexOf(store[i][0])!=-1) {
tagvalues[i][0]='Supermarket';
}
}
tag.setValues(tagvalues);
}
这篇关于如何使用命名范围中的验证在新列中设置值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!