Google-sheets – Is it possible to prevent people from entering data into the Google Spreadsheet

google sheetsgoogle-apps-script

Is it possible that I fix the order of user's entries with a script? For example, the user must fill cell A1 first, only after that he can fill cell A2.

Maybe my explanation is better with an example template from me: Reading scheduler

Question on Google+ community: https://plus.google.com/110138757494391019716/posts/U91woskXd8C

Best Answer

With the code below you can have a fairly responsive interaction with the user.

Code

function onEdit(e) {
  // allow book name to be added
  var cell = e.range.getA1Notation();  
  if(cell == "A7") {
    return;
  }

  // only if conditional color is present
  var color = e.range.getBackground(); 
  if(cell != "A7" && (color == "#cfe2f3" || color == "white")) {
    var first = e.range.offset(-1,-1).getValue(); 
    var second = e.range.offset(-1,0).getValue();

    // first entry
    if(typeof first == "string" && second == "Current") {
      return;
    } 

    // non consecutive weeks
    if(typeof first == "number" && typeof second == "string") {
      respond(e, "Not consecutive enty !!");
    } 

    // out of range  
    if(first == "") {
      respond(e, "This entry is out of range");
    }
  } else {
    respond(e, "Out of bounds");
  }
}

function respond(e, text) {
  e.range.clearContent();
  e.source.toast(text);
  return;
}

Example

I've created an example file for you: Copy of Reading scheduler. See also revised code for removing data.

Related Topic