I've built a form system using Google Sheet. I'm wondering if there is a function to valid a cell entry if the user still has the cell selected.

Right now, if the user click on the save button, the change made in cell B7:G7 won't be saved.

So I'm looking for a function to add to my save script. Is there such a thing ?

function save() {
  var sheet = SpreadsheetApp.getActiveSheet()
  var db = SpreadsheetApp.openById("1OsPjUQDlq_mrCbnJspoTLFOamuIgGVp2fCeJc-tRq20").getActiveSheet()
  var lastRow = db.getLastRow()   1
  
  // formula to validate cell entry  

  var id = sheet.getRange("G4:I4").getValue()
  var date = sheet.getRange("B7:G7").getValue()
 
  var idCol = 1 
  var dateCol = 2 

  db.getRange(lastRow, idCol).setValue(id)
  db.getRange(lastRow, dateCol).setValue(date)

}

CodePudding user response：

Activate the active range.

Example:

function save(){
   SpreadsheetApp.getActiveRange().activate();
   // do the whatever else should be done
}

Note: Activating a different range, will make that the value entered will be wrote to the activated range.

CodePudding user response：

Here's a simple example:

code:

function submitData() {
  const ss = SpreadsheetApp.getActive();
  const ui = SpreadsheetApp.getUi();
  const sh = ss.getSheetByName("Sheet0");
  const vs = sh.getRange(2, 2, sh.getLastRow() - 1).getValues().flat();
  let valid = true;
  vs.forEach((e, i) => {
    if (!e) {
      ui.alert(`No data at B${i   2}`);
      valid = false;
    }

  });
  if (valid) {
    ui.alert('Data was submitted');
  } else {
    ui.alert('Data not submitted');
  }
}

Form:

Demo: