CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsspreadsheet
user8702720
user8702720

Reputation: 115

Prevent different users simultaneously data entry in spreadsheet

My spreadsheet acts as a sales tool, where info is entered in various cells to run sensitivity analysis. Once the salesperson picks a desired option they "submit it". The data submitted is then stored on a separate sheet in the spreadsheet (call it the "database").

I want the database sheet to store all user submissions. The problem is when multiple users are using the sales tool worksheet at the same time, the info they enter gets overwritten by other user's inputs before they can submit.

I'd like multiple users to be able to use the "sales tool" sheet simultaneously without overwriting eachother, but still be able to submit data to the "database" sheet.

For Reference, Here is spreadsheet. And Here is App Script.

function submitData() {
     
  var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet 

  var shUserForm= myGooglSheet.getSheetByName("User Feedback form"); //delcare a variable and set with the User Feedback form worksheet

  var datasheet = myGooglSheet.getSheetByName("Database"); ////delcare a variable and set with the Database worksheet

  //to create the instance of the user-interface environment to use the messagebox features
  var ui = SpreadsheetApp.getUi();
  
  // Display a dialog box with a title, message, and "Yes" and "No" buttons. The user can also
  // close the dialog by clicking the close button in its title bar.
  var response = ui.alert("Submit", 'Do you want to submit the data?',ui.ButtonSet.YES_NO);

  // Checking the user response and proceed with clearing the form if user selects Yes
  if (response == ui.Button.NO) 
  {return;//exit from this function
  } 
 
  //Validating the entry. If validation is true then proceed with transferring the data to Database sheet
 //if (validateEntry()==true) {

    var blankRow=datasheet.getLastRow()+1; //identify the next blank row

    datasheet.getRange(blankRow, 1).setValue(shUserForm.getRange("C4").getValue()); //POD Lead Name
    datasheet.getRange(blankRow, 2).setValue(shUserForm.getRange("F4").getValue()); //Member Email
    datasheet.getRange(blankRow, 3).setValue(shUserForm.getRange("C6").getValue()); //FT Score
    datasheet.getRange(blankRow, 4).setValue(shUserForm.getRange("C8").getValue()); //BT Score
    datasheet.getRange(blankRow, 5).setValue(shUserForm.getRange("C10").getValue()); //SI Score
    datasheet.getRange(blankRow, 6).setValue(shUserForm.getRange("C12").getValue()); //CI Score
    datasheet.getRange(blankRow, 7).setValue(shUserForm.getRange("C14").getValue());//ED Score
    datasheet.getRange(blankRow, 8).setValue(shUserForm.getRange("F6").getValue()); //FT feedback
    datasheet.getRange(blankRow, 9).setValue(shUserForm.getRange("F8").getValue()); //BT feedback
    datasheet.getRange(blankRow, 10).setValue(shUserForm.getRange("F10").getValue()); //SI feedback
    datasheet.getRange(blankRow, 11).setValue(shUserForm.getRange("F12").getValue()); //CI feedback
    datasheet.getRange(blankRow, 12).setValue(shUserForm.getRange("F14").getValue());//ED feedback
   
    // date function to update the current date and time as submittted on
    datasheet.getRange(blankRow, 14).setValue(new Date()).setNumberFormat('yyyy-mm-dd'); //Submitted On
    
    //get the email address of the person running the script and update as Submitted By
    datasheet.getRange(blankRow, 13).setValue(Session.getActiveUser().getEmail()); //Submitted By
    
    ui.alert(' "Feedback Saved' + shUserForm.getRange("F4").getValue() +' "');
  
  //Clearnign the data from the Data Entry Form

   shUserForm.getRange("C4").clear(); //POD Lead Name
  shUserForm.getRange("F4").clear();//Member Email
  shUserForm.getRange("C6").clear(); //FT Score
  shUserForm.getRange("C8").clear(); //BT Score
  shUserForm.getRange("C10").clear(); //SI Score
  shUserForm.getRange("C12").clear(); //CI Score
  shUserForm.getRange("C14").clear();//ED Score
  shUserForm.getRange("F6").clear(); //FT feedback
  shUserForm.getRange("F8").clear(); //BT feedback
  shUserForm.getRange("F10").clear(); //SI feedback
  shUserForm.getRange("F12").clear(); //CI feedback
  shUserForm.getRange("F14").clear();//ED feedback
      
 //}
}

Upvotes: 0

Views: 671

Answers (1)

Tanaike
Tanaike

Reputation: 201368

In your situation, how about protecting the sheets when the function submitData() is run? And, when the script of submitData() is finished, the protects are removed. When this is reflected in your script, it becomes as follows.

Modified script:

From:

This is your current script of submitData()submitData().

function submitData() {

  // This is your current script.

}

To

Please rename the function name as follows.

function submitData_org() {

  // This is your current script.

}

And, please add the following function.

function submitData() {
  const lock = LockService.getDocumentLock();
  if (lock.tryLock(350000)) {
    try {
      const sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
      sheets.forEach(s => {
        const p = s.protect();
        p.removeEditors(p.getEditors());
        p.addEditor(Session.getEffectiveUser());
      })

      submitData_org(); // Here, your script is called.

      sheets.forEach(s => s.protect().remove());
    } catch (e) {
      throw new Error(e.message);
    } finally {
      lock.releaseLock();
    }
  } else {
    throw new Error("timeout");
  }
}
  • In this modification, when submitData() is run, the sheets are protected, and when your script of submitData_org() is finished, the protects are removed. This flow is run with the LockService.

References:

Upvotes: 1

Related Questions