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;
}