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").
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!