Enable other users to run Google App Script Function on protected sheet

Question

I'm new to Google App Script and have created this function that allows members to submit data through a form. I want to protect the sheet where form records the data in so that the other users can't modify them post-submission. Problem is, when I lock the Data sheet, it prevents the other users from using the function with this error:

Exception: You are trying to edit a protected cell or object. Please contact the spreadsheet owner to remove protection if you need to edit.

I've seen similar questions answered but I'm having a hard time converting my script to the desired solution. I believe it involves using doGet but I haven't read up enough about this function. Can I ask for your help with this please?

Below is the function that I created

var workBook = SpreadsheetApp.getActiveSpreadsheet();
var codesBook = SpreadsheetApp.openById("1L6ERNbbH1kbQtGnhgcsYKafkP57U8OSMNJQniF4R9_Y")
var formSheet = workBook.getSheetByName("Form")
var dataSheet = workBook.getSheetByName("Data")
var printOut = workBook.getSheetByName("Print Out")
var passwordsBook = SpreadsheetApp.openById('16CCT17bh4Gyu3zPV00a9T9cVPup0I_oLa94QkMEs_qc')
var passwordsSheet = passwordsBook.getSheetByName("Sheet1")

function submitForm(password, branch, index) {
  var ui = SpreadsheetApp.getUi();
  var data = formSheet.getRange("B3:C11").getValues();
  if ((data[4][1] + data[5][1] + data[6][1] + data[7][1] + data[8][1]) > 5) {
    ui.alert("Max 5 transactions are allowed only.")
    return
  }
  // data = [].concat(...data)
  if (data[0][0].toString().trim().length == 0 || data[1][0].toString().trim().length == 0 && data[2][0].toString().trim().length == 0 || data[3][0].toString().trim().length == 0 || (data[4][1] + data[5][1] + data[6][1] + data[7][1] + data[8][1]) < 1) {
    ui.alert("Please fill the form")
  }
  else {
    var output = []
    var errorMessage = [];
    for (var i = 4; i < data.length; i++) {
      if (data[i][1] == 0)
        continue;
      var codesSheet = codesBook.getSheetByName(data[i][0])
      var codes = codesSheet.getDataRange().getValues();
      var countCodes = codes.filter((row) => row[2] != "VOID" && row[2] != true)
      if (countCodes.length - 1 < data[i][1]) {
        if ((countCodes.length - 1) == 0)
          errorMessage.push("No Codes are available for " + data[i][0] + " denomination")
        else
          errorMessage.push("Only " + (countCodes.length - 1) + " are remaining for " + data[i][0] + " denomination")
      }
      else {
        count = 0;
        for (var c = 1; c < codes.length; c++) {
          if (count == data[i][1])
            break;
          else if (codes[c][2] != "VOID" && codes[c][2] != true) {
            output.push([Utilities.getUuid(), data[0][0], data[1][0], data[2][0], data[3][0], data[i][0], codes[c][1], password, branch, "Hi " + data[0][0] + " please see your purchased wallet code of P" + data[i][0] + " below:" + codes[c][1]])
            codes[c][2] = true;
            count++;
          }
        }
        codesSheet.getRange(1, 1, codes.length, codes[0].length).setValues(codes)
      }
    }

    if (errorMessage.length > 0)
      ui.alert(errorMessage.join("\n"))
    else {
      if (output.length > 0) {
        dataSheet.getRange(dataSheet.getLastRow() + 1, 1, output.length, output[0].length).setValues(output)
        var printOutData = output.map((r) => [r[0], r[5], r[6]])
        printOutData.push(['Date of Purchase', new Date(), ''])
        printOut.getRange("A7:C").clearFormat().clearContent()
        printOut.getRange("A1").setValue("Hi, " + output[0][1])
        printOut.getRange(7, 1, printOutData.length, printOutData[0].length).setValues(printOutData).setNumberFormat("@").setFontColor("blue").setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.SOLID).setHorizontalAlignment("center")
        SpreadsheetApp.flush();
        var lr = printOut.getLastRow();

        printOut.getRange(lr, 1).setFontColor("black").setFontWeight("bold")
        printOut.getRange("B" + lr + ":C" + lr).merge()

        var note = [['Note: Should you have any issues in using the voucher codes, please raise the issue'], ['24 hours of purchase. Please present this printout to the Grab representative to '], ['validate your purchase']]

        var instructions = [['Instructions:'], ['1. Pumunta sa iyong Cash Wallet sa Grab Driver app at i-click ang Top-up.'], ['2. Ilagay ang amount na gusto mong i-top up at pindutin ang Next.'], ['3. Pindutin ang Top Up Now para makumpleto ang transaction.']]
        printOut.getRange(lr + 2, 1, note.length, 1).setValues(note).setFontColor("red").setFontWeight("bold")
        printOut.getRange(lr + 6, 1, instructions.length, 1).setValues(instructions).setFontColor("black").setFontWeight("bold")
      }
      // passwordsSheet.getRange("C" + index).setValue(true)
      formSheet.getRange("B3:C6").clearContent()
      formSheet.getRange("C7:C11").clearContent()
      ui.alert("All codes assigned successfully.")
    }
  }

}



function authenticate() {
  var passwords = passwordsSheet.getDataRange().getValues();
  console.log(passwords)
  var ui = SpreadsheetApp.getUi();
  var result = ui.prompt("Please enter the password to continue...");

  //Get the button that the user pressed.
  var button = result.getSelectedButton();

  if (button === ui.Button.OK) {
    Logger.log("The user clicked the [OK] button.");
    var pwd = result.getResponseText();
    if (pwd.trim() == "") {
      ui.alert("Password must not be empty.")
      return
    }
    var matched = false
    for (var i = 0; i < passwords.length; i++) {
      if (passwords[i][0].toString().trim() == pwd.trim()) {
        // passwordsSheet.getRange("C" + (i + 1)).setValue(true)
        matched = true;
        submitForm(passwords[i][0], passwords[i][1], i + 1)
      }
    }
    if (!matched) {
      ui.alert("Incorrect Password!")
    }
  } else if (button === ui.Button.CLOSE) {
    Logger.log("The user clicked the [X] button and closed the prompt dialog.");
  }

}

Answer 1

Basically you deploy the doGet(e) with your acces rights. The users call's this function via the deployment url instead of calling the function directly. I only can not see how you passing the password, branch and index in the function?

Basic setup:

function theFunctionThatWillBeCalledByTheUser(){
  const doGetDeploymentUrl = 'xxx'
  UrlFetchApp.fetch(doGetDeploymentUrl)
}

function doGet(e){
  //Calling the function:
  submitForm()

  return ContentService.createTextOutput(JSON.stringify({ message: 'ok' })).setMimeType(
    ContentService.MimeType.JSON
  );
}