CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Mike Eburne
Mike Eburne

Reputation: 351

Google Apps Script Lockservice Not Working

I am calling a sidebar in a Google Sheets bound script. I am trying to prevent multiple users from opening the sidebar at a time. The code below is how I am attempting to achieve this:

function loadM1DocsSideBar() {
  var lock = LockService.getScriptLock();
  lock.tryLock(0);
  if (!lock.hasLock()) {
    SpreadsheetApp.getUi().alert("WARNING! Function currently in use by another user. Please try again later.");
    return;
  }

  Logger.log(lock);
 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName("Metadata");
  var dataRange = sh.getRange("metadataRecord").clearContent();
  
  var form = HtmlService.createHtmlOutputFromFile('M1DocsConfiguratorSidebar').setTitle('Verification Project Library Creator');
  SpreadsheetApp.getUi().showSidebar(form);

  lock.releaseLock();
}

During testing both first and second users can launch the sidebar at the same time. Can anyone enlighten me where I am going wrong.

Upvotes: 0

Views: 96

Answers (1)

Iamblichus
Iamblichus

Reputation: 19309

Issue:

The script becomes inaccessible only during the brief time the first execution hasn't finished. After that, other users can execute this, even if the sidebar is opened by user #1: the script has already ended execution. The fact that a certain user has the sidebar opened is not registered by your script.

Workaround:

A possible workaround would be using Properties Service to set and retrieve information about whether the sidebar is open, instead of using LockService. The idea would be the following:

  • When your main function starts, check whether there's a script property SIDEBAR_OPEN equal to true (see getProperty). If that's the case, show your alert and stop the execution. This would be parallel to your current tryLock and hasLock sections.
  • If the script property is not present, or not equal to true, that means no-one else has opened the sidebar. Your script can now open the sidebar, and set the script property SIDEBAR_OPEN to true (see setProperty.
  • On your sidebar, have a button that will close the sidebar, and which will as well call a function (setClosedSidebar in the sample below) that will set SIDEBAR_OPEN to false.

Code sample:

function loadM1DocsSideBar() {
  var sidebarOpen = isSidebarOpen();
  if (sidebarOpen) {
    SpreadsheetApp.getUi().alert("WARNING! Function currently in use by another user. Please try again later.");
    return;
  }
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName("Metadata");
  var dataRange = sh.getRange("metadataRecord").clearContent();
  var form = HtmlService.createHtmlOutputFromFile('M1DocsConfiguratorSidebar').setTitle('Verification Project Library Creator');
  setOpenedSidebar();
  SpreadsheetApp.getUi().showSidebar(form);
}

function setOpenedSidebar() {
  var props = PropertiesService.getScriptProperties();
  props.setProperty("SIDEBAR_OPEN", "true");
}

function setClosedSidebar() { // Call me when closing the sidebar
  var props = PropertiesService.getScriptProperties();
  props.setProperty("SIDEBAR_OPEN", "false");
}

function isSidebarOpen() {
  var props = PropertiesService.getScriptProperties();
  return JSON.parse(props.getProperty("SIDEBAR_OPEN"));
}

Note:

  • As a downside to this workaround, this will only work if the sidebar is closed through the button, not by clicking the closing icon nor by refreshing the tab. You should probably set a timeout anyway via Utilities.sleep, so that the sidebar becomes accessible to other users after some time. Be careful with this.
  • Alternatively, you could use Utilities.sleep to keep the script running for some time after displaying the sidebar, so that other users cannot open the sidebar right after the first user did that.

Upvotes: 2

Related Questions