Google form script executes before entry is made into the spreadsheet if not used for a long time

Question

After google form is submitted and the entry is submitted to the spreadsheet, i want to further add a random 10 digit code for the entry. I wrote this script in the script editor:

function onFormSubmit(e) {

    var random = Math.floor((Math.random() * 10000000000));
    var lock = LockService.getPublicLock();
    lock.waitLock(30000);

    var sheet = SpreadsheetApp.openById('******************************').getSheetByName('testing (Responses)');
    var range = sheet.getRange(sheet.getLastRow(),3); 
    range.setValue(random);

    lock.releaseLock();

}

This works very well when I submit the form. But if I don't use the form for a few hours, and then submit the form, this script executes before the entry is made into the spreadsheet, and hence the random code here replaces the random code of the previous entry. And eventually when the entry is made into the spreadsheet, the cell for this random code is empty. It's weird because this only happens if the form isn't submitted for a few hours, but works perfectly fine if I do it again soon enough.

Yeah, I already put Utlilities.sleep(5*1000) to avoid this from happening, I was hoping to find the cause of this. But thanks! — Shivam Suri

Umair Mohammad Umair Mohammad · Accepted Answer · 2018-07-23T10:35:42

Try modifying your something like this:

var SHEET_ID = '******************************'

function onFormSubmit(e) {
    var random = Math.floor((Math.random() * 10000000000));
    var sheet = SpreadsheetApp.openById().getSheetByName('testing (Responses)');
    var range = sheet.getRange(sheet.getLastRow(),3);
    // Iterate throw all the rows
    for (var i = 2; i <= sheet.getLastRow(); i++) {
        random = Math.floor((Math.random() * 10000000000));
        // If the random number is not present, then set one. Otherwise skip
        if (sheet.getRange(i, 3).getValue() == "") {
            sheet.getRange(i, 3).setValue(random);
        }
    } 
}

Google form script executes before entry is made into the spreadsheet if not used for a long time

1 Answers