我正在处理大约700行的工作表,并且每一行的第一列上都有一个短语。
我正在尝试编写一个脚本,通过给定类似于我的工作表的表格,会在右侧添加一列,其中包含该行中的单词数,并在每一行中添加计数后,将对其进行排序表格根据添加的列按降序排列。
发生的事情是,显然存在异步性,它首先在第二列中排序,然后才添加计数...我环顾四周,找不到解决这个问题的合适方法...有什么想法吗?
编码:
function sortByNumOfKeywords(lang, col, sheet) {
var file = findFileByName("Translate Keywords List");
var spreadsheet = SpreadsheetApp.openById(file.getId());
//Getting the sheet name from the list above
var sheet = findSheetByName(spreadsheet,'Spanish');
//Getting Table bounds (Two-Dimensional array);
var lastRow = sheet.getLastRow();
var lastColumn = sheet.getLastColumn();
//add one column (Column H) to hold the number of words of each keyword in the first column
addWordsCounterCol(sheet, col, lastRow, lastColumn);
//sorting by the added column(H)
sheet.sort(8, false);
}
//sets a new column at the end of the table with the number of keywords of each cell in the given Row
function addWordsCounterCol(sheet, col, lastRow, lastCol){
sheet.activate();
var colChar = String.fromCharCode(65 + col-1); //Converts the Col number to it cohherent Big letter
var formulas = []; //Formulas will be set as a two-dimensional array
for(var i=2; i<lastRow+1; i++){
var cell = sheet.getRange("" +colChar + i);
var numOfKeys = "=COUNTA(SPLIT(trim("+ colChar + i + "), \" \"))";
formulas[i-2] = []; //initializing row with a new array
formulas[i-2].push(""+numOfKeys); // each row has only one column, with this specific String
}
var cells = sheet.getRange("H2:H"+(lastRow));
cells.setFormulas(formulas);
}
最佳答案
尝试使用flush()
。应用所有待处理的电子表格更改。
在您的代码中对我来说效果很好:
...
SpreadsheetApp.flush();
//sorting by the added column(H)
sheet.sort(8, false);
...