asked on Google Sheets script, find next row
Hello, I have the following script for a google sheets. I would like the row of data to be copied down to the next available (empty) row and then the original data to be cleared, ready for the the next input.
I have two problems:
1, var targetRange = ss.getRange('Active Jobs!A3:F3');
this line needs to read... in plain english..... find the last available row and insert
2, The delete does not seem to clear the last visited cell. So the last cell that the user inputs into is F2. If the user does not click out of that cell before pressing save then this cell is ignored.
thanks for your help.
https://docs.google.com/spreadsheets/d/1qSauIqjvSEhEEZJ6z4pJUnR_HZcoMi98frAx94zl6a8/edit?usp=sharing
function saveButton() {
var ss = SpreadsheetApp.getActive();
// [START modifiable parameters]
var sourceRange = ss.getRange('Active Jobs!A2:F2');
var targetRange = ss.getRange('Active Jobs!A3:F3');
// [END modifiable parameters]
sourceRange.copyTo(targetRange);
ss.toast('Data copied to ' + targetRange.getSheet().getName() + '. ' +
'To undo, press Control+Z (on a Mac, ⌘Z).', 'Action button - Save');
var ss = SpreadsheetApp.getActive();
// [START modifiable parameters]
var rangeToClear = ss.getRange('Active Jobs!A2:F2');
// [END modifiable parameters]
rangeToClear.clearContent();
ss.toast("Cleared range '" + rangeToClear.getSheet().getName() + "'!" + rangeToClear.getA1Notation() + '. ' +
'To undo, press Control+Z (on a Mac, ⌘Z).', 'Action button - Clear');
rangeToClear.activate();
}
I have two problems:
1, var targetRange = ss.getRange('Active Jobs!A3:F3');
this line needs to read... in plain english..... find the last available row and insert
2, The delete does not seem to clear the last visited cell. So the last cell that the user inputs into is F2. If the user does not click out of that cell before pressing save then this cell is ignored.
thanks for your help.
https://docs.google.com/spreadsheets/d/1qSauIqjvSEhEEZJ6z4pJUnR_HZcoMi98frAx94zl6a8/edit?usp=sharing
VB ScriptGoogleGoogle WorkspaceScripting Languages
Last Comment
Zac
ASKER
the only problem remaining now is the 2nd issue.
Cell F2 is the last cell that the user inputs into. if this cell remains highilighted and then clicks the save button then F2 is not saved below and the cell is not cleared ready for the next input.
Cell F2 is the last cell that the user inputs into. if this cell remains highilighted and then clicks the save button then F2 is not saved below and the cell is not cleared ready for the next input.
Adding this two lines did not help:
var button = ss.getRange('Active Jobs!G2');
button.activate();
Intention was to move the focus out of F2 cell.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Even displaying the cell value does not work :(
var ss = SpreadsheetApp.getActive();
var cell = ss.getActiveCell();
var a1 = cell.getA1Notation();
var val = cell.getValue();
SpreadsheetApp.getUi().alert("The active cell "+a1+" value is: ["+val+"]");
I have also tested:
SpreadsheetApp.flush();
and:
ss.getRange('E2').activate();
Intention was to store the last entered cell value before copy the range but nothing worked.
As you see in the screenshot the value does not post from GUI to backend.
ASKER
thank you for your help. I think i will just have to teach the user he must click out of that cell before hitting save and then it works
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
For me it looks like you have managed it yourself : )
Thanks for letting me in :)