Google Script > How to exit a cell programmatically?

Viewed 88

This is my first Google Script. The interface is in French. Users fill cells in a sheet named "Add song". By clicking the "Enregistrer" button (=record), the data is copied to another sheet ("SongList").

screen capture

It's a natural behavior to click the button without exiting the last filled cell. So it remains in edit mode. And of course the value of the cell is not copied to the SongList sheet. Only 3 fields are mandatory. So there's no control for the other 3.

Any idea on how to force the interface to leave the cell? Of course I tried getRange and setActiveCell methods, but they don't work (by the way, very surprised to see that a cell can be selected while another is in edit mode).

Here's the code:

function addData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var addSong = ss.getSheetByName('Add song');
  var songList = ss.getSheetByName('SongList');
  var band = addSong.getRange('D4').getValue();
  var song = addSong.getRange('H4').getValue();
  var user = addSong.getRange('D15').getValue();

  // DATA CONTROL
  if(band.length==0) {
    addSong.getRange('D4').activate();
    Browser.msgBox('Merci de renseigner le nom du groupe !', Browser.Buttons.OK);
    return;
  }

  if(song.length==0) {
    addSong.getRange('H4').activate();
    Browser.msgBox('Merci de renseigner le titre de la chanson !', Browser.Buttons.OK);
    return;
  }

  if(user.length==0) {
    addSong.getRange('D15').activate();
    Browser.msgBox('Merci de renseigner le nom du membre !', Browser.Buttons.OK);
    return;
  }

  // DATA RECORD
  var lastRow = songList.getLastRow()+1;

  songList.getRange(lastRow, 1).setValue(Utilities.formatDate(new Date(), "GMT+1", "dd/MM/yyyy"));
  songList.getRange(lastRow, 2).setValue(band);
  songList.getRange(lastRow, 3).setValue(song);
  songList.getRange(lastRow, 4).setValue(user);
  songList.getRange(lastRow, 18).setValue(addSong.getRange('D9').getValue());  // Tuning
  songList.getRange(lastRow, 19).setValue(addSong.getRange('H9').getValue());  // Song duration
  songList.getRange(lastRow, 20).setValue(addSong.getRange('D11').getValue()); // Tempo

  Browser.msgBox('Données enregistrées !', Browser.Buttons.OK);

  // RESET FORM
  addSong.getRange('D4:D11').clearContent();
  addSong.getRange('H4:H9').clearContent();
  addSong.getRange('D4').activate();
}

Thank you!

1 Answers

Edit:

solution 1 : replace the button with a custom menu

function onOpen() {
  SpreadsheetApp.getUi().createMenu(' Menu ')
    .addItem(' valider ici', 'addData')
    .addToUi();
}

solution 2 : include the form (html) in sidebar

Related