How to commit changes made in active cell before running script? (Google Sheets / Google Apps Script-CodePudding

I'm creating a submission form in Google Sheets using Google Apps Script. The form is on one page, with submissions being moved to a second, hidden page. When users have filled in the form, they press a button on the submission page in order to activate the script.

The issue I am running into is that when the user has filled in the last cell and then clicks on the button, the data being entered into the last cell does not appear to be committed to the page yet, and so is not grabbed by the script (using sheet_sub.getRange("C7").getValue();). I tried to activate() another cell and flush(), but that does not appear to work. Everything else about the script is functional.

How could I ensure the data gets saved to the sheet so that I can access it? Thank you in advance for your help.

Edit: Including code below

function submitNomination() {
  var sheet_sub = getSheet("Submission Form");
  var sheet_list = getSheet("List of Nominations");

  // Find last row in list
  var lastRow = sheet_list.getRange("A1").getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();

  // Grab values from submission form
  var nominee = sheet_sub.getRange("C5:F5").getValue();
  var nominator = sheet_sub.getRange("C6:F6").getValue();
  var category = sheet_sub.getRange("C7:F7").getValue();
  var reason = sheet_sub.getRange("C8:F12").getValue();
  
  // Enter values into the list
  sheet_list.getRange('R' (lastRow 1) 'C1').setValue(Utilities.formatDate(new Date(), "America/Detroit","yyyy-MM-dd HH:mm:ss"));
  sheet_list.getRange('R' (lastRow 1) 'C2').setValue(nominee);
  sheet_list.getRange('R' (lastRow 1) 'C3').setValue(nominator);
  sheet_list.getRange('R' (lastRow 1) 'C4').setValue(reason);
  sheet_list.getRange('R' (lastRow 1) 'C5').setValue(category);

  // Clear submission form
  sheet_sub.getRange("C5:F12").clearContent();

}

function getSheet(sheetName){
  var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  return sheet;
}

CodePudding user response：

As long as the value fo the last cell is not committed, it exists only on the client-side of the user's browser session and can not be retrieved from Google Sheets / Apps Script or the Sheets API

Think about to users working on the same spreadsheet. Before user A commits his changes, user B will not see them in his version of the spreadsheet, neither will he have any power to commit those changes.

So if you want users modifications to be retrievable on button click - the only way is to prompt the user to commit his changes before pressing the button. You can do it e.g. with an alert / message showing up on button click asking the user either he committed the changes before clicking on the button.

Alternatively, you can think of an implementation without a button.

For example, you can publish your script as a

This might look less aesthetic than an actual button, however by clicking into the button cell, user's last changes will be automatically committed.