CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Gaspar Lam
Gaspar Lam

Reputation: 47

multiple users using google spreadsheet as data entry form how to use lock service

Update:

I thought by adding SpreadsheetApp.flush() will solve the problem entirely. However, if I have a SpreadsheetApp.getUi().alert somewhere inside the function, the lock doesn't work anymore, this is the changed code. Any method to get lock to work with msg box/alert? Below is the new code that doesn't work. Overwriting each other record

function getNewID1() {

  const lock = LockService.getDocumentLock()

  lock.tryLock(5000)
  var nextidCell = db.getRange("K1")
  var nextID = nextidCell.getValue()

  var lr = getLastRowSpecial(db, "A1:I")
  SpreadsheetApp.flush()
  lock.releaseLock();

  SpreadsheetApp.getUi().alert("confirmation message")

  db.getRange(lr + 1, 1, 1, 1).setValue(nextID)

  nextidCell.setValue(nextID + 1)
}

function getNewID3() {

  const lock = LockService.getDocumentLock()

  lock.tryLock(5000)
  var nextidCell = db.getRange("K1")
  var nextID = nextidCell.getValue()

  var lr = getLastRowSpecial(db, "A1:I")

  SpreadsheetApp.flush()
  lock.releaseLock();


  SpreadsheetApp.getUi().alert("confirmation message")

  db.getRange(lr + 1, 1, 1, 1).setValue(nextID)

  nextidCell.setValue(nextID + 1)


}

// following function getLastRowSpecial() for getting the last row with blank row"
function getLastRowSpecial(sheetlastrow, rangeString) {

  var rng = sheetlastrow.getRange(rangeString).getValues();
  var lrIndex;

  for (var i = rng.length - 1; i >= 0; i--) {
    lrIndex = i;

    if (!rng[i].every(function (c) { return c == ""; })) {
      break;
    }
  }

  return lrIndex + 1;

}

I have a database which two users will enter record with a data entry form. One of the tasks will be assigning IDs to each transactions.

I have stored the ID in cell "K1" which starts from 1. After assigning the ID on each recording, I will add 1 to the ID cell. In this simplified case, user 1 will use getNewID1 and user 2 will use getNewID2. They both take the "ID" from K1 and write it to a new row. Even with the lock in place, sometimes they will still get the same ID if they call the function simultaneously. And sometimes only one of the functions will work and only 1 new row appears. It will look like

| 1      |
| 1      | 
| 2      | 
| 3      | 
| 4      | 
| 4      |

How could I improve the code?

var ss = SpreadsheetApp.getActiveSpreadsheet()
var db = ss.getSheetByName("DB")

function getNewID1 (){

const lock = LockService.getDocumentLock()

lock.tryLock(5000)
var nextidCell = db.getRange("K1")
var nextID = nextidCell.getValue()
db.appendRow([nextID])

nextidCell.setValue(nextID + 1)
Utilities.sleep(3000);
lock.releaseLock();
}

function getNewID2 (){
const lock = LockService.getDocumentLock()

lock.tryLock(5000)
var nextidCell = db.getRange("K1")
var nextID = nextidCell.getValue()
db.appendRow([nextID])

nextidCell.setValue(nextID + 1)
Utilities.sleep(3000);
lock.releaseLock();
}

Upvotes: 0

Views: 171

Answers (1)

Gaspar Lam
Gaspar Lam

Reputation: 47

I just found that adding SpreadsheetApp.flush() before I release the lock completely solved this problem.

Upvotes: 3

Related Questions