Allow single user to edit Google Spreadsheet at a time

Question

I have Google Spreadsheet say "A" that acts as a repository. Other users can read its content. I want to allow other users to run a script from a different spreadsheet to modify content of "A", but I want it to happen one by one so that changes from all users are recorded & not lost.

I tried doing it by having an additional sheet in "A" with its first cell value having current editor's user name & when current editor releases the sheet it sets value of first cell to blank. So that other users in queue can check if its available for edit. They can start editing it by setting first cell to their username. The problem is a waiting script at waiting editor's end is not able to capture a change in the value of first cell. So even if current editor releases "A", others are not able to start editing.

Please help.

Example: SS - "A" Its a data repository which contains data w.r.t. each user.

SS - "B" This is distributed to all users. They can add their information in it & then press Save so that it gets added to "A". Now if two users click save together its possible that one user's updates are overwritten by second user.

I have this code in SS - "B" It keeps checking for cell A1 in "Lock" sheet to be empty before proceeding with save command.

function savedata(){

  var s1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");  

  var wb = SpreadsheetApp.openById("XYZ");
  var lck = wb.getSheetByName("Lock");

  var uname = getUserEmail();

//Code stucks here, even if cell A1 in "Lock" sheet is set to blank if this loop has started it keeps on looping here. Logger continues to report old value of A1 cell.

  while(lck.getRange(1,1).getValue()!=""){
    Logger.log(lck.getRange(1,1).getValue(), lck.getRange(1,1).getValue());
  }

  lck.getRange(1,1).setValue(uname);

   //Save data commands

  lck.getRange(1,1).setValue("");

};

Have you consider using [Properties classe](https://developers.google.com/apps-script/guides/properties) instead of put the name on a modifible cell ? You can put a condition on this variable, which can block user if a user is already editing your file. — Pierre-Marie Richard, Apr 11 '17 at 07:58
But can i access Properties class of a spreadsheet from a different spreadsheet? I am not only looking to block other users, I want them to wait & keep polling so that whenever sheet is free they can start editing. — Pratidhwani Srivastava, Apr 11 '17 at 08:06
Can you give us a spreadsheet of example and some example of use ? — Pierre-Marie Richard, Apr 11 '17 at 08:15

score 2 · Accepted Answer · edited May 23 '17 at 11:54

If all the users edit using the same script from the same sheet, you can use Lock service as mentioned here to prevent concurrent access to the code

Modify the code like so:

function savedata(){

  var s1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");  

  var wb = SpreadsheetApp.openById("XYZ");
  var lck = wb.getSheetByName("Lock");

  var uname = getUserEmail();
  var lock = LockService.getScriptLock();
  var success = lock.tryLock(10000);            // Try getting lock for 10sec 
  if (!success) {
    Logger.log("Currently Busy, Try again later") 
    return
    } else {
           Logger.log(lck.getRange(1,1).getValue(), lck.getRange(1,1).getValue());
    }

   lck.getRange(1,1).setValue(uname);

   //Save data commands

   lck.getRange(1,1).setValue("");
   lock.releaseLock()             //release lock so someone else can update. 
    };

This question explains how to use lock service in a concise format, if you would like to know.

Hope that helps!

Thank you Jack :) – Pratidhwani Srivastava Apr 12 '17 at 07:36 — Pratidhwani Srivastava, Apr 12 '17 at 07:36

Allow single user to edit Google Spreadsheet at a time

1 Answers1