英文:
Levenshtein Distance/Custum Function in Google Sheets Query
问题
我添加了一个名为levenshteinDistanceDP(string1, string2)的自定义函数到AppScript中。它返回一个整数。此外，我在一个表格中有一个查询：
QUERY(Expenses!A1:H, "SELECT A, B, C, D WHERE LOWER(H) 包含 LOWER("""&K3&""") ";1)
除了"WHERE LOWER(H) 包含 LOWER("""&K3&""")"之外，我想要包括那些使levenshteinDistanceDP(lower(H), Lower(K3))小于3的结果。
我猜可能有一种方法，可以在"Expenses"表中添加一个列，该列使用自定义函数，然后查询只检查该列。如果可能，我希望在查询中完成。有什么想法吗？
编辑：使用额外列的想法特别不适合，因为自定义函数在每一行加载时大约需要一秒钟。对于几千行来说是不可行的，因为K3可以由用户（我）随意更新，然后每次都需要重新计算。我猜这个事实可能会危及整个想法，但也许在查询中运行会快一些。这更加促使将其包含在查询中。
英文:
I added a function
levenshteinDistanceDP(string1,string2)
as a custom function in AppScript. It returns an Integer. Additionally I have the Query in a sheet:
QUERY(Expenses!A1:H;"SELECT A, B, C, D WHERE LOWER(H) contains LOWER("""&K3&""") ";1)
Complementary to "WHERE LOWER(H) contains LOWER("""&K3&""")" i want to include results where levenshteinDistanceDP(lower(H),Lower(K3)) is smaller than lets say 3.
I guess there would be a way, by adding a column to "Expenses" which uses the custom function, and the query just checks for that column. If possible I would like to do it within the query tho. Any ideas?
Edit: The idea with the extra column is especially unsuitable, since the custom function takes around a second to load in each row. For several thousand rows unfeasible, since K3 can be updated at the will of the user (me) and would then be recalculated every time. I guess that fact could jeopardize the entire idea, but maybe it runs somewhat faster in the query. One reason more to include it in the query.
答案1
得分: 2
在查询中，您将无法应用Levenshtein距离公式。我建议的方法是不将您的函数作为单元格内的自定义函数使用，而是在onEdit函数中进行一次性计算。
function onEdit(e) {
var sh = e.source.getActiveSheet();
var shname = e.range.getSheet().getName();
var column = e.range.getColumn();
var row = e.range.getRow();
var range = e.range;
if (shname == "Expenses") {
if (range.getA1Notation() == "K3") {
var rangewords = sh.getRange("H:H");
var words = rangewords.getValues();
for (i = 0; i < words.length; i++) {
if (words[i] == "" || words[i] == null) {
return;
} else {
sh.getRange(i + 1, 10).setValue(levenshteinDistanceDP(words[i].toString().toLowerCase(), range.getValue().toString().toLowerCase()));
}
}
} else if (column == 8) {
sheet.getRange(row, 10).setValue(levenshteinDistanceDP(range.getValue().toString().toLowerCase(), sh.getRange("K3").getValue()].toString().toLowerCase()));
}
}
}
更改K3时，它将重新计算整列，更改H列中的值时，它将仅计算该行一次。请尝试并告诉我。如果有必要，您可以共享一个包含函数以进行调试的示例文件。
英文:
Inside the QUERY you won't have the option to apply the Levenshtein Distance formula. What I suggest to make it more feasible is not to use your function as a custom function inside a cell but to make it calculate once with onEdit.
function onEdit(e){
var sh = e.source.getActiveSheet()
var shname = e.range.getSheet().getName()
var column = e.range.getColumn()
var row = e.range.getRow()
var range = e.range
if(shname == "Expenses"){
if(range.getA1Notation() == "K3"){
var rangewords = sh.getRange("H:H")
var words = rangewords.getValues()
for(i=0;i<words.length;i++){
if(words[i]=="" || words[i]==null) {return}
else {sh.getRange(i+1,10).setValue(levenshteinDistanceDP(words[i].toString().toLowerCase(),range.getValue().toString().toLowerCase()))} //change 10 with the number of column you desire to use as auxiliar for this
}
}
else if(column == 8){
sheet.getRange(row,10).setValue(levenshteinDistanceDP(range.getValue().toString().toLowerCase(),sh.getRange("K3").getValue()].toString().toLowerCase())) //change 10 with the number of column you desire to use as auxiliar for this}
}
}
}
When you change K3 it will re-calculate all the column once, when you change a value in column H it will only calculate that row once. Try it and let me know. You may share a sample file with the functions to debug if necessary
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论