Synchronizing function calls on google app script

Question

I'm working with a sheet that has around 700 rows, and each row has a phrase on the first Column. I'm trying to write a script, that by given a sheet similar to the one I have - would add an additional column to the right with the number of words in this row and after adding the counting on each row, it will sort this sheet by descending order based on the added column.

What happens, is that apparently there's asynchronicity, it first sorts by the second column and only then adds the counting... I looked around and couldn't find a decent solution to this problem... any ideas?

The code:

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);
}

oshliaer · Accepted Answer · 2018-06-11 16:59:11Z

1

Try use flush(). Applies all pending Spreadsheet changes.

It works fine for me in your code:

...
SpreadsheetApp.flush();
//sorting by the added column(H)
sheet.sort(8, false);
...

edited Jun 11, 2018 at 16:59

answered Jun 11, 2018 at 16:50

oshliaer

4,9875 gold badges35 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Synchronizing function calls on google app script

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related