Google Apps Script admin SDK speed

Question

i'm creating a google sheet which translates given data from schools to a admin SDK Upload to Google Apps. I know there a create user limit of 10 per second, hence the 120ms Delay time. but, when coloring each row in sheets which is processed the speed is around 500ms - 2 seconds per entry. Which causes the script to stop at the maximum execution time, because there are more than 600 users to be added. Where does it go wrong?

function UploadUsers() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getSheetByName("Upload");
ss.setActiveSheet(sh); 

var column = sh.getRange('A1:I5000');

  var values = column.getValues(); // get all data in one call

  
  var uploadRows = 0;
  while ( values[uploadRows][0] != "" ) {
    uploadRows++;
  }

  var i=0
  var uiACU = SpreadsheetApp.getUi();
  var ACUsersMessage0= "Upload Users";
  var ACUsersMessage1= "Indien u op OK drukt worden er : "+ uploadRows + " Users aangemaakt! " 
                       
    var result = uiACU.alert( 
   ACUsersMessage0,
   ACUsersMessage1,
         uiACU.ButtonSet.OK_CANCEL);
 
  if (result == uiACU.Button.OK) { 
 for (i=0; i<uploadRows;i++){

var uniqueId=[i][0];
var mailAdress=values[i][3];
var voorNaam=values[i][1];
var achterNaam=values[i][2];
var Ou=values[i][8];
var Pass=values[i][4];



   Utilities.sleep(12);
  
 AdminDirectory.Users.insert ({
   "kind": "admin#directory#user",
   "password" : Pass,
   "primaryEmail": mailAdress,
   "orgUnitPath": Ou,
   "changePasswordAtNextLogin": "TRUE",
   "name": {
      "givenName": voorNaam,
      "familyName": achterNaam,
   },
    "externalIds": [
      {
    "value": uniqueId,
    "type": "account",
    "customType": "gappsUniqueId"
      }
  ]
   })   
   
 ss.getRange("D"+ (i+1)).setBackground("red")  

}    
 
    
  } else {
     //Full Stop
  }

}

Answer 1

It goes wrong because every google script function has the 6minutes execution time, you can convey this in a couple of ways:

Read the best practices, the most important thing in there is to do thing in batches, instead of getting just a ROW and turn it RED, get several ROWs and do the sime, 1 ROW costs you 500ms, 20 ROWs will cost 505ms. There's probably a way for batch insert the users also, but I don't use the AdminSDK.

If there's no Batch for user insert, you can monitor the time of execution of the function in the beggining of the for(), if the time comes close the 6minutes (I recommend stopping at 5), save the last ROW inserted in the properties service, create a Progamatic Trigger that will run the function again in 7minutes, then paint the ROWs red. It will take a long time to run entirely, but will work.

function insertUsers(){
   var timeStart = new Date().getTime();
   var rowStart = PropertiesService.getScriptProperties().getProperty('lastRow') || 0;

   for( from rowStart to endOfSheet ){
      if( (new Date().getTime()) - timeStart > (5 * 60 * 1000) ){
         PropertiesService.getScriptProperties().setProperty('lastRow', currentRow);

      createTriggerToRun-insertUsers-in6Minutes;

      return 1;
     }

   // code to insert users here
   }
}