Create an alert when data filled

Question

I'm trying to write a small project for my own use and come to this. I need to fill data for current sheet (column A). When it's done, a "done" alert should appear. Here is my gs code:

function fillData() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 for (var i = 1; i < 1000; i++){
   ss.getActiveSheet().getRange(i, 1).setValue(i);
 }
}

function done(){
 Browser.msgBox("done");
}

function doIt(){
 fillData();
 done();
}

However, the alert seems to appear before filling data done! How can I improve my code?

Answer 1

Use SpreadsheetApp.flush() to complete the pending sheet changes so the message can appear after the sheet changes:

function doIt(){
   fillData();
   SpreadsheetApp.flush();
   done();
}

If this does not work consistently then try to use sleep, again between fillData and done, e.g. Utilities.sleep(3000); to wait for example for 3 seconds.

Bonus Information / Optimal Solution:

• Unfortunately, the current approach is not efficient because it is iteratively calling Google Apps Script methods like getRange and setValue which is a very slow and time consuming approach.

• Also, you don't need a for loop for this purpose. There are solutions in StackOverflow like this one which can return an array of 1000 elements between 1 and 1000.

Work with the arrays instead:

function fillData() {
 const ss = SpreadsheetApp.getActiveSpreadsheet();
 const as = ss.getActiveSheet();
 const N = 1000; // Choose the number of elements to add
 const ar = Array.from({length: N}, (_, i) => i + 1).map(r=>[r]);
 as.getRange(1,1,N,1).setValues(ar);
}

and use the version of doIt in my answer (with flush included).

Answer 2

Could you possibly try to just place an if statement right when you hit 999?

function fillData() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 for (var i = 1; i < 1000; i++){
   ss.getActiveSheet().getRange(i, 1).setValue(i);
   
   if (i == 999) {

   Browser.msgBox("done");

   }
 }
}