How to force flush a user's input?

Question

I was wondering if it is possible to force flush of a user input from a script.

The context is the following : I have a sheet in which people are supposed to input data, and a button where they click when they are done, which will process the data.

It happens that sometimes people double click a cell, write what they want, but then they click the button before hitting return. Thus the script sees the cell as still empty.

Would there be a way to either force flush with what they have currently typed, or detect that a cell is currently being edited ?

Answer 1

The cell is never "edited" until they press enter, or select another cell. So you could instruct them click another cell, or press enter before clicking the button. I understand that this is not the best solution. But there is no way to detect what the user is about to enter in the cell.

You could make it a menu option that they click. Clicking on a menu item (see addMenu) writes the text to the spreadsheet so that you will be able to read it when they use their menu function. Again, this may not be the best solution, but it is a solution, nonetheless.

Answer 2

As you found, using an image to act as a button that should be clicked after the user entered a value into a cell doesn't force that the value be actually entered.

The way to force that a cell change it's state from "entering mode" to "display mode" is by

• pressing Enter
• pressing Tab
• pressing a keyboard shortcut (I didn't tested this yet)
• clicking another cell or a menu (including a custom menu)

A workaround could be to replace you "button" by

• A custom menu item
• A button in the sidebar or a modal-less dialog
• An edit trigger, simple or installable

Answer 3

If you force the application to activate this cell, you can get the new value to memory for example:

var mycell = sheet.getActiveSelection();  
var cellcol = mycell.getColumn() ;
var cellrow = mycell.getRow();

sheet.setActiveCell(sheet.getDataRange().offset(0, 0,cellcol, cellrow)); 

Answer 4

I had the same issue. Solution that worked for me: In the script that's triggered by the button, I prepended some code that activates another sheet in the spreadsheet:

  function buttonScript() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('A_DIFFERENT_SHEET'), true);
  // REST OF THE CODE in your script
  }

Apparently this forces the user input to be "entered" in the cell.

Answer 5

6 years after this is asked, but thankfully so, I had the same issue and tried everyone's options. Like the comments, it sometimes works and sometime doesn't.

So I simply made a if else statement to make sure all entries aren't blank and only then run the code.

function move() {

  var source = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('PROCESS');
  var source_range = source.getRange(3, 15, 1, 6).getValues();

  var target = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SUBMISSIONS');
  var target_range = target.getRange(target.getLastRow() + 1, 2, 1, 6);

  var check = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('INPUT');
  var check_order = check.getRange(7, 15, 1, 1);
  var check_firstname = check.getRange(9, 15, 1, 1);
  var check_lastname = check.getRange(11, 15, 1, 1);
  var check_email = check.getRange(13, 15, 1, 1);

   if (check_order.getValue() === '' ) {
    Browser.msgBox('Error','Please make sure all fields are filled.', Browser.Buttons.OK);
    } else if ( check_firstname.getValue() === '' ) {
    Browser.msgBox('Error','Please make sure all fields are filled.', Browser.Buttons.OK);
    } else if ( check_lastname.getValue() === '' ) {
    Browser.msgBox('Error','Please make sure all fields are filled.', Browser.Buttons.OK);
    } else if ( check_email.getValue() === '' ) {
    Browser.msgBox('Error','Please make sure all fields are filled.', Browser.Buttons.OK);
    } else {

    target_range.setValues(source_range);

    var datecell = target_range.offset(0, -1, 1, 1);

    datecell.setValue(new Date()).setNumberFormat('YY-MM-DD HH:mm:ss');

    var clearsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('INPUT');
    clearsheet.getRange("J3:K4").clearContent();
    clearsheet.getRange("B8:B25").clearContent();
    clearsheet.getRange("H8:H9").clearContent();
    clearsheet.getRange("H11").clearContent();
    clearsheet.getRange("O7:P16").clearContent();
  }
}

Works perfect. Even better, you probably can customize the .msgBox to reference the error, and another if all good— saying what was submitted.