CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Sheils
Sheils

Reputation: 333

How to lock spreadsheet while standalone script is running

I have a spreadsheet that calls functions in a standalone script through the onEdit trigger. The standalone function have loops and can to a few seconds to execute. If the user edits another row while the standalone script is running the information from the second row gets mixed up with the one from the first row in the loop.

code in standalone script is:-

var tasklistID="mytaslistid"
function getTasks() {
    var tasks=Tasks.Tasks.list(tasklistID)
    return tasks
}

Code in spreadsheet is:-

function getTasks(){
    TaskManagerScript.getTasks()
}

Installed onEdit trigger calls the spreadsheet getTasks function which in turns calls the standalone getTasks function

NB: This it a simplified version of my code. The actual version also filters the task to extract tasks from a particular date. This involves looping through the tasks in the list which takes time

So I need a way to lock the spreadsheet from editing until the function in the standalone script have complete its execution.

Thanks

Upvotes: 1

Views: 2105

Answers (2)

Sheils
Sheils

Reputation: 333

Taking on-board TheMaster's comment I have come with a work around which involves the use of an empty while loop. Code now modified as follows.

In Bonded script:-

function getTasks(){
    \\code to open modal dialog
    showDialog()

    \\Empty while loop with call to standalone script ==true as condition to end loop
    while(TaskManagerScript.addTask(e)==false){}

    \\code to close modal dialog
    closeDialog()

}

In Standalone script:-

var tasklistID="mytaslistid"
function getTasks() {
    var tasks=Tasks.Tasks.list(tasklistID)
    \\some more codes
    return true
}

The does the trick

Upvotes: 0

TheMaster
TheMaster

Reputation: 50382

You can use a modal dialog:

Modal dialogs prevent the user from interacting with anything other than the dialog.

// Display a modal dialog box with custom HtmlService content and script
var htmlOutput = HtmlService
    .createHtmlOutput('<p>Please wait...</p>')
    .append('<script>google.script.run.withSuccessHandler(google.script.host.close).callStandaloneFunction()</script>')
    .setWidth(250)
    .setHeight(300);
SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'My add-on');

Upvotes: 3

Related Questions