We help IT Professionals succeed at work.

Google Sheets script, find next row

Zac
Zac asked
on
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
Comment
Watch Question

View Solution Only

ZvonkoSystems architect
CERTIFIED EXPERT
Top Expert 2006

Commented:

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


Thanks for letting me in  :)

Zac

Author

Commented:
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.
ZvonkoSystems architect
CERTIFIED EXPERT
Top Expert 2006

Commented:

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.




ZvonkoSystems architect
CERTIFIED EXPERT
Top Expert 2006

Commented:

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





ZvonkoSystems architect
CERTIFIED EXPERT
Top Expert 2006

Commented:

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

Author

Commented:
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
Commented:
we can close this now. We will just live with teaching user of the spreadsheet not to leave the cell in question high-lighted before pressing the button