CODEWITHSUNDEEP
javascriptasynchronousgoogle-apps-scriptgoogle-sheets-api
sale108
sale108

Reputation: 597

Synchronizing function calls on google app script

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

Upvotes: 0

Views: 235

Answers (1)

oshliaer
oshliaer

Reputation: 4979

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

Upvotes: 1

Related Questions