Avatar of Zac
Zac
Flag for United Kingdom of Great Britain and Northern Ireland 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.

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

Open in new window



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

Avatar of undefined
Last Comment
Zac

8/22/2022 - Mon
Zvonko

For me it looks like you have managed it yourself  :  )


Thanks for letting me in  :)

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.
Zvonko

Adding this two lines did not help:

  var button = ss.getRange('Active Jobs!G2'); 
  button.activate();

Open in new window

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
Zvonko

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+"]");

Open in new window





Zvonko

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.


Zac

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
Zac

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
Sign up - Free for 7 days
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