0

I have a sheet for users to enter a cash-count and their initials then hit a button which runs a script and stores these inputs in a table.

The problem is, if the user types the information (e.g. their initials) and hits the button without first pressing Return or selecting another cell, the information is not saved.

I've seen a similar post: How to force flush a user's input? and tried the solutions, some don't work and none are really what I'm looking for. I know that I could use a Checkbox instead of a button but I'd like to find a way do it without resorting to that. The table is a cash-count for a till so not all cells require a value (there may be no $100 notes for example) and I won't know which was the last cell edited.

[The Data][1] [1]: https://i.sstatic.net/mdKXk.png

The Code:

function Accept() { 
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  var db = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DATA");
  var vals = db.getRange("B:B").getValues();
  var last = vals.filter(String).length;
  var ui = SpreadsheetApp.getUi();
  var user = ui.prompt("Notes:");
  var values = [[
      sheet.getRange("I7").getValue(),    //Date/Time
      sheet.getRange("N16").getValue(),   //Amount
      sheet.getRange("E17").getValue(),   //Initials
      user.getResponseText(),             //Notes
    ]];
  db.getRange(last+1,2,1,4).setValues(values);
}

1 Answer 1

1

One solution is to utilize a checkbox as the Accept button, and use an onEdit(e) simple trigger to run your Accept() function.

See the checkboxButtons_ script for sample code.

Sign up to request clarification or add additional context in comments.

1 Comment

Yeah, that was my original work-around but I'm trying to style the spreadsheet like the online portal we currently use (I'm creating a backup for when the portal is offline) and changing all the buttons to checkboxes really ruins the look. It seems stupid that they've created buttons without coming up with a reliable way to use them!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.