multiple users using google spreadsheet as data entry form and Database, overwriting each others dat-CodePudding

I want to make a sales entry system with google spreadsheet for multiple users.

User 1 will use the data entry form tab named "Main", using inputdata1() User 2 will use the data entry form tab named "Sub", using inputdata2()

Each of them will write data in a new row that I found it by using the getlastrowspecial function (e.g. it this case lets say its row 10) If both of them execute the code simultaneously. Sometimes I will see User 1's data being written on row 10 and User 2's data overwriting User 1's data in the same row on row 10. The checking to see if the row is empty before written is not working. I wanted to keep User 2 to wait until User 1's code is completely executed. I dont mind if User 2 need to call the function again. Please help if there is any method to do this. Thank you very much!

var ss = SpreadsheetApp.getActiveSpreadsheet()
var db = ss.getSheetByName("DB")
var mainInput = ss.getSheetByName("Main")
var subInput = ss.getSheetByName("Sub")


function inputData1() {
  var input1 = mainInput.getRange("E2:I2").getValues()

  Logger.log(input1)

  var lr = getLastRowSpecial(db, "A1:I")
  Logger.log(lr)
  if (db.getRange(lr   1, 5).getValue() !== "") {
    SpreadsheetApp.getUi().alert("Please try again")
    return
  } else {
    db.getRange(lr   1, 5, 1, input1[0].length).setValues(input1)

  }


}

function inputData2() {
  var input2 = subInput.getRange("E2:I2").getValues()

  var lr = getLastRowSpecial(db, "A1:I")
  if (db.getRange(lr   1, 5).getValue() !== "") {
    SpreadsheetApp.getUi().alert("Please try again")
    return
  } else {

    db.getRange(lr   1, 5, 1, input2[0].length).setValues(input2)
  }

}




// 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;

}

CodePudding user response：

Replace Range.setValues() with Sheet.appendRow(), like this:

current:

    db.getRange(lr   1, 5, 1, input1[0].length).setValues(input1)

new:

    db.appendRow(input1[0]);

If you need the values to start in column E, use this:

    db.appendRow([null, null, null, null].concat(input1[0]));

Alternatively, follow Cooper's advice and use the Lock Service. If you choose to go this route, also consider using the appendRows_() utility function.