How to lock spreadsheet while standalone script is running

1
votes

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

2
google-apps-scriptgoogle-sheets
The question is too broad as there are several ways to do this (change the spreadsheet sharing settings, protect the sheet/range, use Apps Script lock service, use a modal dialog(already mentioned on an answer). - Rubén
So TaskManagerScript is a library. Right? - TheMaster
Yes it is a library - Sheils
return true or something from the getTasks() function. You probably just need to change callStandaloneFunction() in my answer to getTasks(). Installed onEdit() should run another function that just opens my modal dialog. My modal will call getTasks() and will close after a successful return from the getTasks() function. - TheMaster
The getTasks function need to run in the standalone script otherwise you get the user's task. I need the users to see task that I have created. Also as I understand standalone scripts are not able to open ui in spreadsheet. - Sheils

2 Answers

3
votes

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');
0
votes

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